Install and Use SQLite on AlmaLinux 9 with Efficient Steps

By Posted on

Install and Use SQLite on AlmaLinux 9 with Efficient Steps

Install and Use SQLite on AlmaLinux 9 with Efficient Steps

In this tutorial, we will guide you through the process of Install and Use SQLite on AlmaLinux 9. SQLite is a software library that provides a relational database management system. The "lite" in Install and Use SQLite on AlmaLinux 9 means lightweight in terms of setup, database administration, and required resources.

SQLite boasts several noticeable features: self-contained, serverless, zero-configuration, and transactional. Follow the steps below to set up Install and Use SQLite on AlmaLinux 9.

To complete this guide, you must log in to your server as a non-root user with sudo privileges. You can refer to our guide on Initial Server Setup with AlmaLinux 9 for assistance.

1. Install Required Packages For SQLite

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

sudo dnf update -y

Then, install the EPEL repository on your server:

sudo dnf install epel-release -y

Next, install the necessary development tools:

sudo dnf install make automake cmake gcc -y

2. Set up SQLite 3 on AlmaLinux 9

At this point, you can install SQLite 3. You can choose between using the DNF repository or compiling from source code.

Install SQLite via Default Repository

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

sudo dnf install sqlite-devel -y

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

sqlite3 --version
Install and Use SQLite on AlmaLinux 9

Install SQLite from the official source

Alternatively, download the latest version of SQLite from the official SQLite Downloads page using the wget command:

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

Extract the downloaded file:

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

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

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

Compile and install SQLite:

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

Verify the installation:

sqlite3 --version

3. How To Use SQLite 3 on AlmaLinux 9?

Here’s a basic guide to using SQLite on AlmaLinux 9.

Create a Database in SQLite

To create a database, run the following command:

sqlite3 <strong><mark>orca.db</mark></strong>

This command creates a database named orca.db. If the file already exists, SQLite will open a connection to it; if it doesn’t, SQLite will create it.

In your output you will see:

Use SQLite 3 on AlmaLinux 9

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

Let’s create a table for our SQLite database.

Use the following command to create a table with columns for different data:

<mark><strong>sqlite&gt;</strong></mark> CREATE TABLE <mark>orca</mark>(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:

<strong><mark>sqlite&gt;</mark></strong> INSERT INTO tablename VALUES(values go here);

For example:

<strong><mark>sqlite&gt;</mark></strong> INSERT INTO <mark>orca</mark> VALUES (1, "olivia", "type1", 427);
<strong><mark>sqlite&gt;</mark></strong> INSERT INTO <mark>orca</mark> VALUES (2, "daniel", "type2", 600);
<strong><mark>sqlite&gt;</mark></strong> INSERT INTO <mark>orca</mark> VALUES (3, "sarra", "type3", 1800);

Note: You must enter a value for each column because you defined NOT NULL in your command.

Read your table using the following command:

<strong><mark>sqlite&gt;</mark></strong> SELECT * FROM <mark>orca</mark>;

In your output you will see:

<strong><mark>Output</mark></strong>
1|olivia|type1|427
2|daniel|type2|600
3|sarra|type3|1800

To view a specific value, use the following command:

<strong><mark>sqlite&gt;</mark></strong> SELECT * FROM <mark>orca</mark> WHERE id IS 1;
<strong><mark>Output</mark></strong>
1|olivia|type1|427

Add more columns to your SQLite table with the command below:

<strong><mark>sqlite&gt;</mark></strong> ALTER TABLE <mark>orca</mark> ADD COLUMN age integer;

Then, update the values for each column:

<strong><mark>sqlite&gt;</mark></strong> UPDATE <mark>orca</mark> SET age = 25 WHERE id=1;
<strong><mark>sqlite&gt;</mark></strong> UPDATE <mark>orca</mark> SET age = 33 WHERE id=2;
<strong><mark>sqlite&gt;</mark></strong> UPDATE <mark>orca</mark> SET age = 35 WHERE id=3;

Read your table again; the age column has been added:

<strong><mark>Output</mark></strong>
1|olivia|type1|427|25
2|daniel|type2|600|33
3|sarra|type3|1800|35

Delete a specific row in your SQLite table. For example, delete a row where the age is less than or equal to 30:

<strong><mark>sqlite&gt;</mark></strong> DELETE FROM <mark>orca</mark> WHERE age <= 30;

If you read your table again, you’ll see that "olivia" has been deleted.

<mark><strong>Output</strong>
</mark>2|daniel|type2|600|33
3|sarra|type3|1800|35

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

Conclusion

You have successfully learned 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. We hope you enjoyed learning about Install and Use SQLite on AlmaLinux 9.

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 excellent for lightweight, embedded databases, other options exist for managing data on AlmaLinux 9, especially when dealing with larger datasets, more complex queries, or the need for concurrent access. Here are two alternative solutions:

1. Using MariaDB

MariaDB is a popular open-source relational database management system (RDBMS) that is a drop-in replacement for MySQL. It’s well-suited for applications requiring higher performance, scalability, and concurrency than SQLite can offer.

Explanation:

  • Client-Server Architecture: MariaDB operates on a client-server architecture, where a dedicated server process handles database requests from multiple clients simultaneously. This allows for better resource management and concurrent access.
  • Scalability: MariaDB can handle larger datasets and more complex queries than SQLite due to its optimized query engine and support for indexing.
  • Security: MariaDB provides robust security features, including user authentication, access control, and encryption.

Installation and Basic Usage:

  1. Install MariaDB:

    sudo dnf install mariadb-server mariadb

  2. Start and Enable MariaDB:

    sudo systemctl start mariadb
sudo systemctl enable mariadb

  3. Secure MariaDB Installation:

    sudo mysql_secure_installation

    Follow the prompts to set a root password, remove anonymous users, disallow remote root login, and remove the test database.

  4. Connect to MariaDB:

    sudo mysql -u root -p

    Enter the root password you set during the mysql_secure_installation process.

  5. Create a 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;

  6. Connect as the New User:

    mysql -u orca_user -p -h localhost orca_db

    Enter the password ‘your_password’ you assigned to orca_user.

  7. Create a Table (similar to SQLite example):

    CREATE TABLE orca (
    id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    orcatype VARCHAR(255) NOT NULL,
    length INT NOT NULL
);

  8. Insert Data:

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

  9. Query Data:

    SELECT * FROM orca;

2. Using PostgreSQL

PostgreSQL is another powerful open-source RDBMS known for its advanced features, extensibility, and adherence to SQL standards. It is a robust option for applications that require complex data types, advanced indexing, and high reliability.

Explanation:

  • Advanced Data Types: PostgreSQL supports a wide range of data types, including arrays, JSON, and geometric types, making it suitable for diverse data storage needs.
  • Advanced Indexing: PostgreSQL offers various indexing methods, such as B-tree, Hash, GiST, and SP-GiST, allowing for optimized query performance based on specific data characteristics.
  • Concurrency Control: PostgreSQL provides robust concurrency control mechanisms, including Multi-Version Concurrency Control (MVCC), ensuring data consistency and integrity even under heavy load.

Installation and Basic Usage:

  1. Install PostgreSQL:

    sudo dnf install postgresql-server postgresql

  2. Initialize the Database:

    sudo postgresql-setup --initdb

  3. Start and Enable PostgreSQL:

    sudo systemctl start postgresql
sudo systemctl enable postgresql

  4. Switch to the postgres User:

    sudo su - postgres

  5. Connect to PostgreSQL:

    psql

  6. Create a Database and User:

    CREATE DATABASE orca_db;
CREATE USER orca_user WITH PASSWORD 'your_password';
GRANT ALL PRIVILEGES ON DATABASE orca_db TO orca_user;
q  -- Exit psql

  7. Connect as the New User:

    psql -U orca_user -d orca_db -h localhost

    Enter the password when prompted.

  8. Create a Table (similar to SQLite example):

    CREATE TABLE orca (
    id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    orcatype VARCHAR(255) NOT NULL,
    length INT NOT NULL
);

  9. Insert Data:

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

  10. Query Data:

    SELECT * FROM orca;

Both MariaDB and PostgreSQL offer significant advantages over SQLite in terms of scalability, concurrency, and features. The choice between them depends on the specific requirements of your application. MariaDB is often favored for its ease of use and compatibility with MySQL, while PostgreSQL excels in advanced features and data integrity. The steps to Install and Use SQLite on AlmaLinux 9 are useful for smaller projects, while these solutions will work better for larger, more complex projects.

Related posts:

Leave a Reply

Your email address will not be published. Required fields are marked *