Install and Use SQLite on AlmaLinux 9 with Efficient Steps

In this tutorial, we will guide you through the process to Install and Use SQLite on AlmaLinux 9. SQLite is a software library that provides a relational database management system (RDBMS). The "lite" in SQLite refers to its lightweight nature in terms of setup, database administration, and resource requirements. It is perfect for smaller projects and embedded systems. Learning how to Install and Use SQLite on AlmaLinux 9 is a valuable skill for any developer working with this operating system.

SQLite is characterized by several key features: it’s self-contained, serverless, requires zero-configuration, and is transactional. Follow the steps below on the Orcacore website to set up SQLite on AlmaLinux 9 and you’ll be able to Install and Use SQLite on AlmaLinux 9 like a pro.

Before you begin, ensure you are logged into your AlmaLinux 9 server as a non-root user with sudo privileges. If you haven’t already, follow our guide on Initial Server Setup with AlmaLinux 9 to configure this.

1. Install Required Packages For SQLite

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

sudo dnf update -y

Then, install the EPEL (Extra Packages for Enterprise Linux) repository on your server using the following command:

sudo dnf install epel-release -y

Next, install essential development tools and libraries:

sudo dnf install make automake cmake gcc -y

2. Set up SQLite 3 on AlmaLinux 9

You can install SQLite 3 on your server using either the DNF repository or by compiling from source code. Both methods are explained below.

Install SQLite via Default Repository

SQLite is available in the AlmaLinux 9 Stream base repository. Install the latest version of SQLite using the following command:

sudo dnf install sqlite-devel -y

Once the installation is complete, verify the installation by checking the SQLite version:

sqlite3 --version

Install SQLite from the official source

Alternatively, you can download and compile SQLite directly from the official source. Visit the SQLite Downloads page to obtain the latest version using the wget command:

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

After the download is complete, extract the downloaded file:

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

Move the extracted directory to /opt/sqlite3 and navigate to it:

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

Finally, compile and install SQLite:

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

After the compilation and installation process, verify the installation by checking the SQLite version:

sqlite3 --version

3. How To Use SQLite 3 on AlmaLinux 9?

Here’s a demonstration of basic SQLite usage on AlmaLinux 9.

Create a Database in SQLite

To create a database in SQLite, use the following command:

sqlite3 orca.db

This command creates a database file named orca.db. If the file already exists, SQLite opens a connection to it; otherwise, it creates a new database file.

The output will look something like this:

If the orca.db file doesn’t exist and you exit SQLite without running any queries, the file won’t be created. Run an empty query by typing ";" and pressing Enter.

Create a Table for the Database in SQLite

Create a table in your SQLite database on AlmaLinux 9:

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

Insert Values To SQLite Table

Insert values into your SQLite table using the following command:

INSERT INTO tablename VALUES(values go here);

For example:

INSERT INTO orca VALUES (1, "olivia", "type1", 427);
INSERT INTO orca VALUES (2, "daniel", "type2", 600);
INSERT INTO orca VALUES (3, "sarra", "type3", 1800);

Note: You must enter a value for each of your columns because you defined NOT NULL.

To read your table, use the following command:

SELECT * FROM orca;

Output:

1|olivia|type1|427
2|daniel|type2|600
3|sarra|type3|1800

To view a specific value, use the following command:

SELECT * FROM orca WHERE id IS 1;

Output:

1|olivia|type1|427

You can add more columns to your SQLite table on AlmaLinux 9 with the command below:

ALTER TABLE orca ADD COLUMN age integer;

Then, update the values for each column:

UPDATE orca SET age = 25 WHERE id=1;
UPDATE orca SET age = 33 WHERE id=2;
UPDATE orca SET age = 35 WHERE id=3;

Read your table again, and 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

You can also delete a specific row in your SQLite table on AlmaLinux 9. For example, use the following command to delete a row where age is less than or equal to 30:

DELETE FROM orca WHERE age <= 30;

If you read your table again, you will see that olivia is deleted.

2|daniel|type2|600|33
3|sarra|type3|1800|35

For more information about SQLite, visit the SQLite Documentation page.

Conclusion

You have now learned how to Install and Use SQLite 3 on AlmaLinux 9. SQLite 3 is a lightweight, self-contained, and serverless database management system used for storing and managing structured data.

Hopefully, you enjoy using it. You may also like these guides:

Steps To Install PHP 7.4 on AlmaLinux 9

Install and Configure GitLab on AlmaLinux 9

Installing Apache Solr AlmaLinux 8

Install Apache Tomcat AlmaLinux 8

Install Apache Cassandra AlmaLinux 8

Install Mono AlmaLinux 8

How To Install Fail2Ban On AlmaLinux 8

How To Install Caddy Web Server On Alma Linux 8

Alternative Solutions for Managing Data on AlmaLinux 9

While SQLite is a great option for lightweight, embedded databases, there are alternative solutions for managing data on AlmaLinux 9 that may be more suitable for different use cases. Here are two such alternatives:

1. Using MariaDB/MySQL:

MariaDB is a popular, open-source relational database management system that is often used as a replacement for MySQL. It provides more features, better performance, and improved security compared to SQLite. While it requires more resources than SQLite, it is a better choice for applications that need to handle large amounts of data or support multiple concurrent users.

Explanation:

Instead of using SQLite for storing and managing data, you can set up a MariaDB server on your AlmaLinux 9 system. MariaDB allows you to create multiple databases, tables, and users, and provides a powerful SQL interface for querying and manipulating data. This approach is well-suited for web applications or any scenario where you need a robust and scalable database solution.

Code Example:

First, install MariaDB on your AlmaLinux 9 system:

sudo dnf install mariadb-server mariadb -y

Start and enable the MariaDB service:

sudo systemctl start mariadb
sudo systemctl enable mariadb

Secure your MariaDB installation by running the mysql_secure_installation script:

sudo mysql_secure_installation

Log in to the MariaDB shell:

sudo mysql -u root -p

Create a new database and user:

CREATE DATABASE orca_db;
CREATE USER 'orca_user'@'localhost' IDENTIFIED BY 'your_password';
GRANT ALL PRIVILEGES ON orca_db.* TO 'orca_user'@'localhost';
FLUSH PRIVILEGES;

You can then create tables and insert data using SQL commands similar to SQLite. However, MariaDB offers more advanced features like stored procedures, triggers, and views. This makes it a more flexible solution for complex data management needs.

2. Using PostgreSQL:

PostgreSQL is another powerful open-source relational database management system known for its advanced features, extensibility, and compliance with SQL standards. Like MariaDB, it’s more resource-intensive than SQLite, but it’s a good choice for applications that require advanced data types, complex queries, or high levels of data integrity.

Explanation:

PostgreSQL provides a rich set of features, including support for complex data types (like arrays and JSON), advanced indexing techniques, and transactional integrity. Setting up PostgreSQL on your AlmaLinux 9 server allows you to build robust and scalable data-driven applications. It’s a popular choice for enterprise-level applications and those requiring a high degree of reliability.

Code Example:

First, install PostgreSQL on your AlmaLinux 9 system:

sudo dnf install postgresql-server postgresql-contrib -y

Initialize the PostgreSQL database:

sudo postgresql-setup --initdb

Start and enable the PostgreSQL service:

sudo systemctl start postgresql
sudo systemctl enable postgresql

Switch to the postgres user:

sudo su - postgres

Create a new database and user:

createuser --interactive
createdb orca_db

Log in to the PostgreSQL shell:

psql orca_db

You can then create tables and insert data using SQL commands:

CREATE TABLE orca (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    orcatype VARCHAR(255) NOT NULL,
    length INTEGER NOT NULL
);

INSERT INTO orca (name, orcatype, length) VALUES ('olivia', 'type1', 427);
INSERT INTO orca (name, orcatype, length) VALUES ('daniel', 'type2', 600);
INSERT INTO orca (name, orcatype, length) VALUES ('sarra', 'type3', 1800);

SELECT * FROM orca;

PostgreSQL offers more robust features compared to SQLite and MariaDB, such as support for geospatial data, full-text search, and advanced security features. These features make it a great option for complex data management scenarios.

Choosing the right database solution depends on the specific requirements of your application. SQLite is suitable for lightweight, embedded applications, while MariaDB/MySQL and PostgreSQL are better choices for larger, more complex applications that need scalability, reliability, and advanced features. Now you understand better how to Install and Use SQLite on AlmaLinux 9 and its alternatives.