Table of Contents
In this article, we will see how to install sqlite3 on Ubuntu 20.04 LTS (Focal Fossa). SQLite3 is the third version of SQLite, which is a widely used software library that provides a lightweight, disk-based database. SQLite3 does not require a separate server process or system, unlike traditional database systems like MySQL or PostgreSQL. It is fully self-contained, serverless, and operates in a zero-configuration mode, making it an ideal choice for applications requiring a simple, efficient, and reliable database system.
Key Features
- Self-contained: SQLite3 is famously known for being self-contained. It requires minimal support from the operating system and can be embedded directly into applications.
- Serverless Architecture: Unlike most other SQL databases, SQLite3 does not have a separate server process. SQLite databases read and write directly to ordinary disk files.
- Zero-Configuration: No installation or administration is required, making SQLite3 a go-to choice for applications that need an embedded database.
- Transactional: SQLite3 is transactional, meaning that all changes within a single transaction in SQLite3 are atomic, consistent, isolated, and durable (ACID), even after system crashes and power failures.
- Cross-Platform: The library can be used in various operating systems, and it supports various programming languages, making it highly versatile.
- Compact Size: It's designed with a small footprint, making it ideal for use in embedded systems and mobile applications, where memory, storage space, and efficiency are critical.
- SQL Compatible: SQLite3 uses a dialect of SQL as its query language, which means it can handle complex queries and standard SQL commands.
Common Uses
- Embedded Systems and IoT Devices: Due to its small size and self-contained nature, it's frequently used in embedded systems and Internet of Things (IoT) devices.
- Mobile Applications: SQLite3 is widely used in mobile app development, as it's embedded in both Android and iOS for local data storage.
- Application File Format: Some applications use SQLite3 databases as their primary file format for storing data.
- Web Browsers: It's used in web browsers for storing data like history, cookies, and sessions.
- Data Analysis and Prototyping: SQLite3 can be a quick solution for data analysis and for prototyping applications that will later be scaled to larger, server-based SQL databases.
How to Install sqlite3 on Ubuntu 20.04 LTS (Focal Fossa)
Also Read: How to Install Konsole terminal emulator on Ubuntu 20.04
Step 1: Prerequisites
a) You should have a running Ubuntu 20.04 LTS Server.
b) You should have sudo or root access to run privileged commands.
c) You should have apt or apt-get utility available in your Server.
Step 2: Update Your Server
As always, before installing any new packages, let's check for all the available updates and install them by using sudo apt update && sudo apt upgrade command as shown below.
cyberithub@ubuntu:~$ sudo apt update && sudo apt upgrade
[sudo] password for cyberithub:
Hit:1 http://ppa.launchpad.net/flatpak/stable/ubuntu focal InRelease
Hit:2 http://in.archive.ubuntu.com/ubuntu focal InRelease
Hit:3 http://security.ubuntu.com/ubuntu focal-security InRelease
Hit:4 http://ppa.launchpad.net/gencfsm/ppa/ubuntu focal InRelease
Hit:5 https://d3nt0h4h6pmmc4.cloudfront.net/ubuntu focal InRelease
Hit:6 https://dl.winehq.org/wine-builds/ubuntu focal InRelease
Hit:7 http://ppa.launchpad.net/juju/stable/ubuntu focal InRelease
Ign:8 https://pkg.jenkins.io/debian-stable binary/ InRelease
Hit:9 https://dl.google.com/linux/chrome/deb stable InRelease
Hit:10 https://pkg.jenkins.io/debian-stable binary/ Release
Hit:11 http://ppa.launchpad.net/libreoffice/ppa/ubuntu focal InRelease
Get:12 http://in.archive.ubuntu.com/ubuntu focal-updates InRelease [114 kB]
Hit:13 http://ppa.launchpad.net/mojo-maintainers/ppa/ubuntu focal InRelease
Hit:14 http://ppa.launchpad.net/ubuntu-toolchain-r/test/ubuntu focal InRelease
Hit:15 https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/focal pgadmin4 InRelease
Hit:16 https://download.sublimetext.com apt/stable/ InRelease
..............................................................
Step 3: Install sqlite3
Then to install sqlite3, use sudo apt install sqlite3 command as shown below. This will download and install the package from default ubuntu repo along with all its dependencies.
cyberithub@ubuntu:~$ sudo apt install sqlite3 [sudo] password for cyberithub: Reading package lists... Done Building dependency tree Reading state information... Done Suggested packages: sqlite3-doc The following NEW packages will be installed: sqlite3 0 upgraded, 1 newly installed, 0 to remove and 2 not upgraded. Need to get 0 B/860 kB of archives. After this operation, 2,803 kB of additional disk space will be used. Selecting previously unselected package sqlite3. (Reading database ... 295643 files and directories currently installed.) Preparing to unpack .../sqlite3_3.31.1-4ubuntu0.5_amd64.deb ... Unpacking sqlite3 (3.31.1-4ubuntu0.5) ... Setting up sqlite3 (3.31.1-4ubuntu0.5) ... Processing triggers for man-db (2.9.1-1) ...
Step 4: Check Version
You can check the installed version by using sqlite3 --version command as shown below.
cyberithub@ubuntu:~$ sqlite3 --version 3.31.1 2020-01-27 19:55:54 3bfa9cc97da10598521b342961df8f5f68c7388fa117345eeb516eaa837balt1
Step 5: Verify Installation
You can also verify the installation status by running dpkg -s sqlite3 command as shown below. To know more about dpkg command, check 21+ Practical dpkg Command Examples for Linux Beginners.
cyberithub@ubuntu:~$ dpkg -s sqlite3 Package: sqlite3 Status: install ok installed Priority: optional Section: database Installed-Size: 2737 Maintainer: Ubuntu Developers <ubuntu-devel-discuss@lists.ubuntu.com> Architecture: amd64 Multi-Arch: foreign Version: 3.31.1-4ubuntu0.5 Depends: libc6 (>= 2.29), libreadline8 (>= 6.0), zlib1g (>= 1:1.2.0), libsqlite3-0 (= 3.31.1-4ubuntu0.5) Suggests: sqlite3-doc Description: Command line interface for SQLite 3 SQLite is a C library that implements an SQL database engine. Programs that link with the SQLite library can have SQL database access without running a separate RDBMS process. Homepage: https://www.sqlite.org/ Original-Maintainer: Laszlo Boszormenyi (GCS) <gcs@debian.org>
Step 6: Create a Database
To create a database, you can simply run sqlite3 <database_name> command. For example, here we are creating a database called cyberithub.db using sqlite3 cyberithub.db query as shown below.
cyberithub@ubuntu:~$ sqlite3 cyberithub.db SQLite version 3.31.1 2020-01-27 19:55:54 Enter ".help" for usage hints. sqlite>
Step 7: Create a Table
To create a table, you can use create table query. For our demo purpose, we are going to create a table called employee with three columns in it - employee_id, employee_name and department. We are also going to set all column as not null to make sure it always contains a value.
sqlite> create table employee(employee_id not null, employee_name not null, department not null);
Step 8: Insert Values
Now that table is created, let's insert some values in it. We are going to insert information of two employees as mentioned below:-
a) Employee 1:-
Name - John Kennedy
Employee ID - 675898
Department - Marketing
b) Employee 2:-
Name - Andrew Walton
Employee ID - 875398
Department - Accounts
sqlite> insert into employee values(675898, "John Kennedy", "Marketing"); sqlite> insert into employee values(875398, "Andrew Walton", "Accounts");
After entering records, let's verify it by running select * from employee; query as shown below.
sqlite> select * from employee;
675898|John Kennedy|Marketing
875398|Andrew Walton|Accounts
Step 9: Update a Value
You can also update a value in table. For example, we are updating department of an employee with Employee ID 875398 from Accounts to Finance by using update employee set department="Finance" where employee_id=875398; query as shown below.
sqlite> update employee set department="Finance" where employee_id=875398;
Then verify the changes by running select * from employee; query as shown below.
sqlite> select * from employee;
675898|John Kennedy|Marketing
875398|Andrew Walton|Finance
Step 10: Delete a Table
If you are looking to delete a table then you can delete it by using drop table <table_name> syntax. For example, here we are deleting table employee by using drop table employee command as shown below.
sqlite> drop table employee;
Step 11: Uninstall sqlite3
Once you are done using sqlite3, you can also choose to uninstall sqlite3 from your system by using sudo apt remove sqlite3 command as shown below.
cyberithub@ubuntu:~$ sudo apt remove sqlite3 Reading package lists... Done Building dependency tree Reading state information... Done The following packages will be REMOVED: sqlite3 0 upgraded, 0 newly installed, 1 to remove and 2 not upgraded. After this operation, 2,803 kB disk space will be freed. Do you want to continue? [Y/n] Y (Reading database ... 295649 files and directories currently installed.) Removing sqlite3 (3.31.1-4ubuntu0.5) ... Processing triggers for man-db (2.9.1-1) ...