Install and Use SQLite on AlmaLinux 9

In this tutorial, we want to teach you to Install and Use SQLite 3 on AlmaLinux 9.

SQLite is a software library that provides a relational database management system. The lite in SQLite means lightweight in terms of setup, database administration, and required resources.

SQLite has the following noticeable features: self-contained, serverless, zero-configuration, and transactional.

Steps To Install and Use SQLite 3 on AlmaLinux 9

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 guide on Initial Server Setup with AlmaLinux 9.

Install Required Packages For SQLite

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

sudo dnf update -y

Then, install the Epel repository on your server by using the following command:

sudo dnf install epel-release -y

Next, use the following command to install the required packages:

sudo dnf install make automake cmake gcc -y

Set up SQLite 3 on AlmaLinux 9

At this point, you can easily install SQLite 3 on your server. You can use both the DNF repo or Source code.

Install SQLite via Default Repository

By default, SQLite is available on the AlmaLinux 9 Stream base repository. Let’s install the latest version of SQLite using the following command below:

sudo dnf install sqlite-devel -y

When your installation is completed, you can verify it by checking its version:

sqlite3 --version

Output
3.34.1 2021-01-20 14:10:07 10e20c0b43500cfb9bbc0eaa061c57514f715d87238f4d835880cd846b9ealt1

Install SQLite from the official source

In this method, you can visit the SQLite Downloads page and get the latest version of SQLite from the official page using the wget command:

sudo wget https://www.sqlite.org/2022/sqlite-autoconf-3400100.tar.gz

When your download is completed, extract your downloaded file with the command below:

sudo tar xvfz sqlite-autoconf-3400100.tar.gz

Move your extracted file to the /opt/sqlite3 directory and navigate to it by using the commands below:

# sudo mv sqlite-autoconf-3400100 /opt/sqlite3
# cd /opt/sqlite3

Finally, compile and install SQLite by using the following commands:

# ./configure --prefix=/usr
# make -j 2
# nproc
# sudo make install

When you are done, verify your SQLite installation by checking its version:

sqlite3 --version

How To Use SQLite on AlmaLinux 9

Here we want to show you basic usage of SQLite on AlmaLinux 9.

Create a Databse in 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.34.1 2021-01-20 14:10:07
Enter ".help" for usage hints.
sqlite>

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.

Create a Table for Database in SQLite

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

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);

Insert Values To SQLite Table

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 AlmaLinux 9 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:

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 AlmaLinux 9. 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 have learned to Install and Use SQLite 3 on AlmaLinux 9.

Hope you enjoy using it. You may be like these guides too:

Steps To Install PHP 7.4 on AlmaLinux 9

Install and Configure GitLab on AlmaLinux 9