Install and Use SQLite on Ubuntu 20.04

In this article, we want to teach you How To Install and Use SQLite on Ubuntu 20.04.

SQLite is a database engine. It is software that allows users to interact with a relational database. In SQLite, a database is stored in a single file — a trait that distinguishes it from other database engines.

How To Install and Use SQLite on Ubuntu 20.04

Before you start to install SQLite on Ubuntu 20.04, you need to log in to your server as a non-root user with sudo privileges. To do this, you can follow our article the Initial Server Setup with Ubuntu 20.04.

Now follow the steps below to set up SQLite on Ubuntu 20.04.

Set up SQLite on Ubuntu 20.04

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

sudo apt update

Then, you can use the command below to install SQLite on your server:

sudo apt install sqlite3

Verify your SQLite installation by checking its version:

sqlite3 --version

Output
3.31.1 2020-01-27 19:55:54 3bfa9cc97da10598521b342961df8f5f68c7388fa117345eeb516eaa837balt1

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

How To Use SQLite

At this point, we will show you how to create a database in SQLite. To do this, run 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.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.

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 20.04.

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.

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

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

sqlite> ALTER TABLE orca ADD COLUMN age integer;

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

Output
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:

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

Also, you can delete a specific row in your SQLite table on Ubuntu 20.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.

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 learn to install SQLite on Ubuntu 20.04. Also, you learn some basic usage of it.

Hope you enjoy it.

May you will be interested in these articles:

Install and Configure Zabbix on Ubuntu 20.04.

Install and Secure Fathom Analytics on Ubuntu 20.04.

Set up Iptables Firewall on Ubuntu 20.04.