Install and Use SQLite on Ubuntu 22.04

This guide intends to teach you to Install and Use SQLite on Ubuntu 22.04.

SQLite is a relational database management system (RDBMS) that emphasizes speed and necessitates minimal support from the host environment. By design, SQLite is not a standalone database but rather an embedded database. It allows users to have database functionality inside their codebase with no additional installation of an RDBMS. Thus, it offers simplicity and fewer administrative configurations than standalone or client-server databases. 

Steps To Install and Use SQLite on Ubuntu 22.04

To complete this guide, you must log in to your server as a non-root user with sudo privileges. To do this, you can follow our article on Initial Server Setup with Ubuntu 22.04.

SQLite Installation on Ubuntu 22.04

First, you need to update your local package index with the following command:

sudo apt update

The latest version of SQLite is SQLite 3 which is available in the default Ubuntu repository. Then, you can use the command below to install SQLite 3 on your server:

sudo apt install sqlite3

Verify your SQLite installation by checking its version:

sqlite3 --version

Output
3.37.2 2022-01-06 13:25:41

Now that you have SQLite installed on your server, let’s see how to use it.

SQLite Usage on Ubuntu 22.04

At this point, we will show you how to use your SQLite.

Create a database with SQLite

To create a database, you can use the following command:

sqlite3 orca.db

With this command, you will create a database named orca. If the file sharks.db already exists, SQLite will open a connection to it; if it does not exist, SQLite will create it.

In your output you will see:

Output
SQLite version 3.37.2 2022-01-06 13:25:41
Enter ".help" for usage hints.
sqlite>

Note: If the orca.db file doesn’t exist and if you exit the SQLite without running any queries your file will not be created.

Just run an empty query by typing “;” and press enter.

Now let’s see how to create a table for our SQLite database on Ubuntu 22.04.

Create a Table for SQLite Database

You can use the following command to create a table with some columns for different data:

sqlite> CREATE TABLE orca(id integer NOT NULL, name text NOT NULL, orcatype text NOT NULL, length integer NOT NULL);

At this point, you can use the following command to insert values in your SQLite table.

sqlite> INSERT INTO tablename VALUES(values go here);

For example:

sqlite> INSERT INTO orca VALUES (1, "olivia", "type1", 427);
sqlite> INSERT INTO orca VALUES (2, "daniel", "type2", 600);
sqlite> INSERT INTO orca VALUES (3, "sarra", "type3", 1800);

Note: You must enter a value for each of your columns because you define NOT NULL in your command.

Read SQLite Tables

Also, you can use the following command to read your table:

sqlite> SELECT * FROM orca;

In your output you will see:

Output
1|olivia|type1|427
2|daniel|type2|600
3|sarra|type3|1800

To view a specific value you can use the following command:

sqlite> SELECT * FROM orca WHERE id IS 1;

Output
1|olivia|type1|427

Add Columns in SQLite Table

You can add more columns in your SQLite table on Ubuntu 22.04 with the command below:

sqlite> ALTER TABLE orca ADD COLUMN age integer;

Then, you need to update the values for each column:

sqlite> UPDATE orca SET age = 25 WHERE id=1;
sqlite> UPDATE orca SET age = 33 WHERE id=2;
sqlite> UPDATE orca SET age = 35 WHERE id=3;

Read your table again, in your output you will see that the age column is added:

sqlite> SELECT * FROM orca;
Output
1|olivia|type1|427|25
2|daniel|type2|600|33
3|sarra|type3|1800|35

Delete a Row in SQLite Table

Also, you can delete a specific row in your SQLite table on Ubuntu 22.04.

For example, use the following command to delete a table whose age is less than 30:

sqlite> DELETE FROM orca WHERE age <= 30;

If you read your table again, you will see that olivia is deleted.

sqlite> SELECT * FROM orca;
Output
2|daniel|type2|600|33
3|sarra|type3|1800|35

For more information about SQLite, you can visit the SQLite Documentation page.

Conclusion

At this point, you have learned to Install and Use SQLite on Ubuntu 22.04.

Hope you enjoy it. For more articles, you can visit the Orcacore website.