Sqlite is a lightweight but feature-rich database management system that is widely used in embedded systems like mobile devices. It is basically a relative database management system used for storing structured data in large tables. Other Major Database Management Systems in this series include Microsoft’s SQL Server, MySQL, PostgreSQL, IBM’s DB2,  and Oracle Database. Being open-source, SQLite source code can be modified as per the requirement of developers. It is also available for free use in both commercial and non-commercial projects.

SQLite runs without the need for a separate server process. Since no server is required for setting up SQLite, an SQLite database instance can be created just like opening a file. It is a C library that has direct access to its stored files. The whole database system is contained in a single library. It is integrated directly into the host program. It is fully compliant with ACID. It uses minimum system resources.

With the SQLite browser, we can directly manipulate the files in the SQLite database. It is open source. DB Browser is an example of an SQLite browser. It can be used for creating and editing database files. With the visual interface of a DB browser, you do not need to remember SQL commands. This feature makes it more flexible for new users as well as for developers.

In this guide, we will see how to install SQLite and SQLite Browser on an Ubuntu 20.04 system. There are two ways to install SQLite browser. In the first method, we will use the Ubuntu default repository. In the second method, we will use Snap’s pre-packaged application. Let us move on to the installation process.

Prerequisites

  1. Basic knowledge of running commands on Linux terminal.
  2. A user account with administrative (‘sudo’) access.
  3. Access to the internet for fetching various files.

Installing SQLite from the Ubuntu 20.04 Official Repository

Step 1. Before proceeding to install SQLite, update the repository list with the below command:

sudo apt update

Step 2. Now we can continue to install SQLite using the command:

sudo apt install sqlite3


<img alt="SQLite" data-ezsrc="https://kirelos.com/wp-content/uploads/2021/04/echo/1.png6080663185a9c.jpg" ezimgfmt="rs rscb3 src ng ngcb3" height="437" loading="lazy" src="data:image/svg xml,” width=”732″>

Once the SQLite is installed, you can check the installed version by:

sqlite --version

<img alt="Check SQLite version" data-ezsrc="https://kirelos.com/wp-content/uploads/2021/04/echo/2.png608066337b3a1.jpg" ezimgfmt="rs rscb3 src ng ngcb3" height="174" loading="lazy" src="data:image/svg xml,” width=”725″>


Installing SQLite Browser on Ubuntu 20.04 from official repository

Step 1. To install SQLite browser, use the command:

sudo apt install sqlitebrowser


<img alt="Installing SQLite Browser" data-ezsrc="https://kirelos.com/wp-content/uploads/2021/04/echo/3.png60806634103a8.jpg" ezimgfmt="rs rscb3 src ng ngcb3" height="463" loading="lazy" src="data:image/svg xml,” width=”738″>

Step 2. Now launch the SQLite browser from the command line using:

$ sqlitebrowser 

<img alt="SQLite Browser" data-ezsrc="https://kirelos.com/wp-content/uploads/2021/04/echo/4.png608066349f267.jpg" ezimgfmt="rs rscb3 src ng ngcb3" height="461" loading="lazy" src="data:image/svg xml,” width=”750″>

Or use the start menu and search for SQLite browser as shown here:

Installing SQLite Browser from the Snap Store

Step 1. On the Ubuntu system(>16.04), Snap comes pre-installed. In case you have a minimal installation of Ubuntu, you can install Snap from the snapd package as here:

sudo apt install snapd

If snapd is already installed, the above command will upgrade it if a new version is available.

Step 2. For updating the Snap’s path, either log out and log in again or restart your computer.

Step 3. Now after the Step 2, open a new terminal and install SQLite from Snap :

sudo snap install sqlitebrowser

The SQLite browser or DB browser has controls and wizards for various operations such as creating database and tables, importing and exporting tables from and to CSV files, executing SQL queries, and so on.

Using The SQLite Command Line Interface

Let us create a database in SQLite and populate it with some data. To create a database named as ‘spare’, run the command:

sqlite3 spare.db

Here ‘spare ‘is the name of the database. The terminal prompt will now change to the SQLite shell as:

sqlite>

Using the .help command on the sqlite3 prompt we can list all the SQLite commands:

<img alt="SQLite shell" data-ezsrc="https://kirelos.com/wp-content/uploads/2021/04/echo/7.png60806638e0f89.jpg" ezimgfmt="rs rscb3 src ng ngcb3" height="231" loading="lazy" src="data:image/svg xml,” width=”731″>

To create a table for storing data, we need to specify its name and column in the create table statement. Basic syntax for creating a table is:

CREATE TABLE [IF NOT EXISTS] [database_name].table_name( col_1 datatype PRIMARY KEY(one or more columns), col_2 datatype, col_3 datatype, ... ... ... );

Using the above syntax, we have created the below table named as ‘parts’:

CREATE TABLE parts ( part_id INTEGER PRIMARY KEY, part_name TEXT NOT NULL,brand_name TEXT NOT NULL UNIQUE );


<img alt="SQLite – creating a table" data-ezsrc="https://kirelos.com/wp-content/uploads/2021/04/echo/8.png6080663963577.jpg" ezimgfmt="rs rscb3 src ng ngcb3" height="236" loading="lazy" src="data:image/svg xml,” width=”737″>

The part_id field is the primary key of the parts table. The UNIQUE constraint is used to specify a unique field in the table. 

To insert data into this table, we need the INSERT INTO statement. The syntax for the INSERT query is :

INSERT INTO TABLE_NAME (col_1, col_2, col_3,...col_N)  VALUES (val_1, val_2, val_3,...val_N);  

Let’s insert some data in our ‘parts’ table:

INSERT INTO parts (part_id,part_name,brand_name) VALUES (111, ‘screw’, ‘thunder’);

<img alt="Insert data into a SQLite database" data-ezsrc="https://kirelos.com/wp-content/uploads/2021/04/echo/9.png6080663a1d034.jpg" ezimgfmt="rs rscb3 src ng ngcb3" height="155" loading="lazy" src="data:image/svg xml,” width=”738″>

To check if the data is properly inserted, we will use the SELECT query to display it on the terminal. The syntax for SELECT query is:

SELECT col_1, col_2, . . . . col_N FROM table_name;

In our case the SELECT query will be:

SELECT part_id,part_name,brand_name from parts;

<img alt="Query SQLite database" data-ezsrc="https://kirelos.com/wp-content/uploads/2021/04/echo/10.png6080663be05f2.jpg" ezimgfmt="rs rscb3 src ng ngcb3" height="145" loading="lazy" src="data:image/svg xml,” width=”732″>

If you want to exit the sqlite3 prompt, just enter the .quit command.

Conclusion

That’s all. In this guide, we have learned to install SQLite on Ubuntu 20.04 OS. SQLite is a lightweight and fast database application. Try to build a mobile application using different databases and compare their performance with SQLite.

Show Comments