Jump to content

How to Install and Use SQLite in Fedora Linux


Linux Hint

Recommended Posts

This guide demonstrates how to install and use SQLite in Fedora Linux.

Prerequisites:

To perform the steps that are demonstrated in this guide, you need the following components:

SQLite on Fedora Linux

SQLite is an open-source C library that implements a lightweight, high-performance, self-contained, and reliable SQL database engine. It supports all the modern SQL features. Each database is a single file that’s stable, cross-platform, and backward compatible.

For the most part, various apps use the SQLite library to manage the databases rather than using the other heavyweight options like MySQL, PostgreSQL, and such.

Besides the code library, there are also SQLite binaries that are available for all the major platforms including Fedora Linux. It’s a command-line tool that we can use to create and manage the SQLite databases.

At the time of writing, SQLite 3 is the latest major release.

Installing SQLite on Fedora Linux

SQLite is available from the official package repos of Fedora Linux. Besides the official SQLite package, you can also obtain the prebuilt SQLite binaries from the official SQLite download page.

Installing from the Official Repo

First, update the package database of DNF:

$ sudo dnf makecache

 
Picture1-8.png
Now, install SQLite using the following command:

$ sudo dnf install sqlite

 
Picture2-6.png
To use SQLite with various programming languages, you also have to install the following additional packages:

$ sudo dnf install sqlite-devel sqlite-tcl

 
Picture3-6.png
Installing from Binaries

We download and configure the SQLite prebuilt binaries from the official website. Note that for better system integration, we also have to tinker with the PATH variable to include the SQLite binaries.

First, download the SQLite prebuilt binaries:

$ wget https://www.sqlite.org/2023/sqlite-tools-linux-x86-3420000.zip

 
Picture4-4.png
Extract the archive to a suitable location:

$ unzip sqlite-tools-linux-x86-3420000.zip -d /tmp/sqlite-bin

 
Picture5-4.png
For demonstration purposes, we extract the archive to /tmp/sqlite-bin. The directory is cleaned next time the system restarts, so choose a different location if you want a persistent access.

Next, we add it to the PATH variable:

$ export PATH=/tmp/sqlite-bin:$PATH

 
Picture6-3.png
The command temporarily updates the value of the PATH environment variable. If you want to make permanent changes, check out this guide on adding a directory to the $PATH in Linux.

We can verify if the process is successful:

$ which sqlite3

 
Picture7-3.png
Installing from the Source

We can also download and compile SQLite from the source code. It requires a suitable C/C++ compiler and some additional packages. For general users, this method should be ignored.

First, install the necessary components:

$ sudo dnf groupinstall "Development Tools" "Development Libraries"

 
Picture8-3.png
Now, download the SQLite source code that contains a configure script:

$ wget https://www.sqlite.org/2023/sqlite-autoconf-3420000.tar.gz

 
Picture9-3.png
Extract the archive:

$ tar -xvf sqlite-autoconf-3420000.tar.gz

 
Picture10-3.png
Run the configure script from within the new directory:

$ ./configure --prefix=/usr

 
Picture11-3.png
Next, compile the source code using “make”:

$ make -j$(nproc)

 
Picture12-3.png
Once the compilation is finished, we can install it using the following command:

$ sudo make install

 
Picture13-3.png
If the installation is successful, SQLite should be accessible from the console:

$ sqlite3 --version

 
Picture14-2.png

Using SQLite

Unlike other database engines like MySQL or PostgreSQL, SQLite doesn’t require any additional configuration. Once installed, it’s ready to be used. This section demonstrates some common usages of SQLite.

These procedures can also serve as a way to verify the SQLite installation.

Creating a New Database

Any SQLite database is a standalone DB file. Generally, the file name serves as the name of the database.

To create a new database, run the following command:

$ sqlite3 <db_name>.db

 
Picture15-1.png
If you already have a database file with the specified name, SQLite opens the database instead. Then, SQLite launches an interactive shell where you can run the various commands and queries to interact with the database.

Creating a Table

SQLite is a relational database engine that stores the data in the tables. Each column is given with a label and each row contains the data points.

The following SQL query creates a table named “test”:

$ CREATE TABLE test (id INTEGER PRIMARY KEY, name TEXT);

 
Picture16.png
Here:

    • The table test contains two columns: “id” and “name”.
    • The “id” column stores the integer values. It’s also the primary key.
    • The “name” column stores the strings.

The primary key is important to relate the data to other tables/databases. There can be only one primary key per table.

Inserting the Data into the Table

To insert value in the table, use the following query:

$ INSERT INTO test (id, name) VALUES (9, 'hello world');
$ INSERT INTO test (id, name) VALUES (10, 'the quick BROWN fox');

 
Picture17.png
To view the result, run the following query:

$ SELECT * FROM test;

 
Picture18.png
Updating the Existing Row

To update the content of an existing row, use the following query:

$ UPDATE <table_name> SET <column> = <new_value> WHERE <search_condition>;

 
For example, the following query updates the content of row 2 of the “test” table:

$ UPDATE test SET id = 11, name = 'viktor' WHERE id = 10;

 
Picture19.png
Check the updated result:

$ SELECT * FROM test;

 
Picture20.png
Deleting the Existing Row

Similar to updating the row values, we can delete an existing row from a table using the DELETE statement:

$ DELETE FROM <table_name> WHERE <search_condition>;

 
For example, the following query removes “1” from the “test” table:

$ DELETE FROM test WHERE id = 9;

 
Picture21.png
Listing the Tables

The following query prints all the tables in the current database:

$ .tables

 
Picture22.png
Table Structure

There are a couple of ways to check the structure of an existing table. Use any of the following queries:

$ PRAGMA table_info(<table_name>);

 
Picture23.png
 

$ .schema <table_name>

 
Picture24.png
Altering the Columns in Table

Using the ALTER TABLE command, we can change the columns of a table in SQLite. It can be used to add, remove, and rename the columns.

The following query renames the column name to “label”:

$ ALTER TABLE <table_name> RENAME COLUMN name TO label;

 
Picture25.png
Picture26.png
To add a new column to a table, use the following query:

$ ALTER TABLE <table_name> ADD COLUMN test_column INTEGER;

 
Picture27.png
Picture28.png
To remove an existing column, use the following query:

$ ALTER TABLE <table_name> DROP COLUMN <column_name>;

 
Picture29.png
 

$ ALTER TABLE <table_name> DROP <column_name>;

 
Picture30.png
Data Query

Using the SELECT statement, we can query the data from a database.

The following command lists all the entries from a table:

$ SELECT * FROM <table_name>;

 
Picture31.png
If you want to apply certain conditions, use the WHERE command:

$ SELECT * FROM <table_name> WHERE <condition>;

 
Picture32.png
Exiting the SQLite Shell

To exit the SQLite shell, use the following command:

$ .exit

 
Picture33.png

Conclusion

In this guide, we demonstrated the various ways of installing SQLite on Fedora Linux. We also demonstrated some common usage of SQLite: creating a database, managing the tables and rows, querying the data, etc.

Interested in learning more about SQLite? Check out the SQLite sub-category that contains hundreds of guides on various aspects of SQLite.

Happy computing!

View the full article

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...