How to Install and Use SQLite on CentOS 8

SQLite is a self-contained, serverless, zero-configuration, transactional SQL database engine. It is the most widely deployed database in the world – it is used in most smartphones, many embedded systems, and countless desktop applications. In this comprehensive guide, we will learn how to install SQLite on CentOS 8 and go through some basic usage examples.

Installing SQLite on CentOS 8

Installing SQLite on CentOS 8 is straightforward since it is available in the default repositories.

First, update the package index and upgrade any available packages:

sudo dnf upgrade

Next, enable the EPEL repository which contains additional packages for CentOS:

sudo dnf install epel-release

Once EPEL is enabled, upgrade packages again:

sudo dnf upgrade

Finally, install SQLite:

sudo dnf install sqlite

To verify that SQLite installed correctly, check the version:

sqlite3 --version

This will output the installed SQLite version, which at the time of writing is 3.26.0

And that‘s it! SQLite is now installed and ready to use on your CentOS 8 system.

Creating a Database

Since SQLite databases are just regular files on disk, you can create them by simply specifying the filename when invoking the sqlite3 command.

For example, to create a new database called mydb.db, do:

sqlite3 mydb.db 

This will create the file mydb.db in the current directory and open the SQLite prompt where you can start issuing SQL commands.

To verify that you have connected to the new database, use the .databases command:

sqlite> .databases
seq  name             file
---  ---------------  ----------------------------------------------------------
0    main             /home/user/mydb.db

The main database is now set to mydb.db which we just created.

Creating Tables

To create a new table, use SQL‘s CREATE TABLE statement. For example:

CREATE TABLE inventory (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  quantity INTEGER
);

This creates a table called inventory with columns for ID, name, and quantity.

Verify that the table was created with .tables:

sqlite> .tables
inventory

And you can check the structure as well with .schema:

sqlite> .schema inventory 
CREATE TABLE inventory (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  quantity INTEGER
);

Inserting Data

To insert data into the table, use the SQL INSERT INTO statement. For example, let‘s insert some sample data into the inventory table:

INSERT INTO inventory VALUES 
  (1, ‘banana‘, 150), 
  (2, ‘orange‘, 154),
  (3, ‘apple‘, 100);

This inserts three new rows for different types of fruit and how many we currently have in stock.

Querying Data

To query the rows in the table, use the SQL SELECT statement. For example:

SELECT * FROM inventory;

This will output all rows currently in the table:

id          name        quantity  
----------  ----------  ----------
1           banana      150       
2           orange      154
3           apple       100

We can query specific columns as well, for example just the name and quantity:

SELECT name, quantity FROM inventory;

And that‘s the basics of working with a SQLite database! From here you can further build out your database schema, write more complex queries, set up indices to optimize performance, and use more advanced SQLite capabilities.

Using SQLite Functions

SQLite comes with a number of built-in functions for things like string manipulation, date math, aggregates, and more. These functions provide powerful functionality without needing to write complex SQL.

A few examples:

String Functions

Concatenate strings with ||:

SELECT "Hello " || "world!"; 

Get the length of a string with length():

SELECT length("Hello world!"); 

Change case with upper() and lower():

SELECT upper(‘hello world!‘);
SELECT lower(‘Hello World!‘);

Date Functions

Extract parts of a date with strftime():

SELECT strftime(‘%Y‘, ‘2022-01-01‘); -- 2022
SELECT strftime(‘%m‘, ‘2022-01-01‘); -- 01
SELECT strftime(‘%d‘, ‘2022-01-01‘); -- 01

Date math is easy with date() and +/- intervals:

SELECT date(‘now‘) AS today;

SELECT date(‘now‘, ‘+1 day‘) AS tomorrow; 

SELECT date(‘now‘, ‘-1 week‘) AS lastweek;

Aggregate Functions

Find the total, average, or count with:

SELECT SUM(quantity) FROM inventory; 

SELECT AVG(quantity) from inventory;

SELECT COUNT(id) FROM inventory; 

And many more functions exist – check the SQLite docs for a complete reference.

Importing and Exporting Data

There may be times where you need to migrate data in and out of an SQLite database from CSV or other formats. This can be accomplished from the command line with .import and .export.

For example, given a file inventory.csv:

id,name,quantity 
1,banana,150
2,orange,154
3,apple,100

You can import this into a table using:

sqlite> .import inventory.csv inventory

And export the inventory data back out to CSV with:

sqlite> .output inventory-export.csv 
sqlite> SELECT * FROM inventory;
sqlite> .output stdout

The .import and .export commands provide an easy way to move SQLite data to and from external file formats.

Conclusion

SQLite is an incredibly useful database engine that‘s bundled into CentOS 8 and ready to use with no complex setup required. Its simple file-based approach makes SQLite excellent for embedded and local applications where no separate database server is needed.

We walked through the installation of SQLite, creating a database file, building tables, inserting and querying data, and more. From here you can harness the power of the full SQL language in your own centOS software and scripts.

SQLite has many other features to explore too – things like views, joins, indices, triggers and transactions for more robust data handling and performance optimization. I hope this tutorial provided a good foundation for applying SQLite in your own CentOS projects!