How to Install and Use SQLite on Arch Linux

SQLite is a lightweight and cross-platform database engine that is written in C. It is one of the most widely used database systems as it is bundled in mobile and desktop software. It can be installed on multiple operating systems, including Linux, macOS, Android, iOS, and Windows. It offers several useful features, including stable, cross-platform, backward compatibility, small size, precompiled binaries, and more.

In this post, we will show you how to install and use SQLite on Arch Linux.

Step 1 – Configure Repository

By default, the default repository is outdated in Arch Linux, so you will need to modify the default mirror list. You can do it by editing the mirrorlist configuration file:

nano  /etc/pacman.d/mirrorlist

Remove all lines and add the following lines:

## Score: 0.7, United States
Server = http://mirror.us.leaseweb.net/archlinux/$repo/os/$arch
## Score: 0.8, United States
Server = http://lug.mtu.edu/archlinux/$repo/os/$arch
Server = http://mirror.nl.leaseweb.net/archlinux/$repo/os/$arch
## Score: 0.9, United Kingdom
Server = http://mirror.bytemark.co.uk/archlinux/$repo/os/$arch
## Score: 1.5, United Kingdom
Server = http://mirrors.manchester.m247.com/arch-linux/$repo/os/$arch
Server = http://archlinux.dcc.fc.up.pt/$repo/os/$arch
## Score: 6.6, United States
Server = http://mirror.cs.pitt.edu/archlinux/$repo/os/$arch
## Score: 6.7, United States
Server = http://mirrors.acm.wpi.edu/archlinux/$repo/os/$arch
## Score: 6.8, United States
Server = http://ftp.osuosl.org/pub/archlinux/$repo/os/$arch
## Score: 7.1, India
Server = http://mirror.cse.iitk.ac.in/archlinux/$repo/os/$arch
## Score: 10.1, United States
Server = http://mirrors.xmission.com/archlinux/$repo/os/$arch

Save and close the file, then update all the package indexes with the following command:

pacman -Syu

Step 2 – Install SQLite on Arch Linux

By default, the SQLite package is included in the Arch Linux default repository. You can install it using the following command:

pacman -S sqlite

Once SQLite is installed, you can verify the SQLite version with the following command:

sqlite3 --version

You should see the following output:

3.39.4 2022-09-29 15:55:41 a29f9949895322123f7c38fbe94c649a9d6e6c9cd0c3b41c96d694552f26alt1

Step 3 – Create a Database in SQLite

The basic syntax to create a database in SQLite is shown below:

sqlite3 database-name

For example, to create a database named company.db, run the following command:

sqlite3 company.db

You will get the following output:

SQLite version 3.39.4 2022-09-29 15:55:41
Enter ".help" for usage hints.
sqlite> 

Step 4 – Create a Table in SQLite

To create a table named employee with some columns, run the following command:

CREATE TABLE employee(id integer NOT NULL, name text NOT NULL, employee text NOT NULL, length integer NOT NULL);

Next, run the following command to insert some data into the table:

INSERT INTO employee VALUES (1, "Hitesh", "Junagadh", 100);
INSERT INTO employee VALUES (2, "Jayesh", "Hydrabad", 200);
INSERT INTO employee VALUES (3, "Vyom", "Veraval", 300);

Step 5 – List Data from a Table in SQLite

To list the inserted data, run the following command:

SELECT * FROM employee;

You will get the following output:

1|Hitesh|Junagadh|100
2|Jayesh|Hydrabad|200
3|Vyom|Veraval|300

To list the data based on its id, run the following command:

SELECT * FROM employee WHERE id IS 2;

You will get the following output:

2|Jayesh|Hydrabad|200

To add a new column named age, run the following command:

ALTER TABLE employee ADD COLUMN age integer;

Next, to add values to the age column, run the following command:

UPDATE employee SET age = 42 WHERE id=1;
UPDATE employee SET age = 20 WHERE id=2;
UPDATE employee SET age = 40 WHERE id=3;

Next, verify all data again using the following command:

SELECT * FROM employee;

You will get the following command:

1|Hitesh|Junagadh|100|42
2|Jayesh|Hydrabad|200|20
3|Vyom|Veraval|300|40

You can also delete entries in your table based on specific arguments. For example, to delete all entries in a table whose age is less than 40, run the following command:

DELETE FROM employee WHERE age <= 40;

Conclusion

In this guide, we explained how to install SQLite on Arch Linux. We also explained how to create a database and a table and insert data in SQLite. You can try SQLite on dedicated hosting from Atlantic.Net!