How to Install and Configure SQLite on RHEL 7

How to Install and Configure SQLite on RHEL 7

SQLite is a self-contained, serverless, zero-configuration relational database engine that stores an entire database as a single file on disk. Unlike PostgreSQL or MySQL, SQLite requires no dedicated daemon, no user accounts, and no network configuration — the database engine is a library linked directly into the application. On RHEL 7, SQLite is available from the standard repositories and is already present on many systems as a dependency of other packages. It is an excellent choice for embedded applications, development and testing environments, desktop tools, scripts, and any scenario where a full client-server database is unnecessary overhead. This guide covers installation, interactive CLI usage, schema management, and integration with Python and PHP.

Prerequisites

• RHEL 7 server or workstation with root or sudo access
• Active subscription or configured yum repository (BaseOS or RHEL Optional)
• Python 2.7 or Python 3 installed (for Python integration)
• PHP 5.4 or later installed (for PHP integration)

Step 1: Install SQLite via yum

Check whether SQLite is already installed on your system:

sqlite3 --version

If the command is not found, install the SQLite package from the RHEL 7 repositories:

sudo yum install sqlite -y

The sqlite package includes both the SQLite shared library and the sqlite3 command-line interface. Confirm the installation:

sqlite3 --version
which sqlite3

Expected output:

3.7.17 2013-05-20 00:56:22 ...
/usr/bin/sqlite3

For development work, also install the development headers (required when compiling applications that link against SQLite):

sudo yum install sqlite-devel -y

To get a newer version of SQLite than what ships with RHEL 7 (3.7.17), you can compile from source or use the Software Collections Library (SCL) if available in your subscription.

Step 2: Open the SQLite CLI and Create a Database

SQLite databases are plain files. To open or create a database, pass the file path as an argument to sqlite3:

sqlite3 /opt/myapp/inventory.db

If the file does not exist, SQLite creates it automatically when you first write data. You will see the SQLite prompt:

SQLite version 3.7.17 2013-05-20 00:56:22
Enter ".help" for instructions
sqlite>

The database file is not created on disk until at least one table is created or a write operation is performed.

Step 3: SQLite CLI Commands

SQLite has a set of dot-commands (meta-commands) that control the CLI itself. These begin with a period and are not SQL statements:

-- Show all attached databases and their file paths
.databases

-- List all tables in the current database
.tables

-- Show the CREATE TABLE statement for a specific table
.schema products

-- Show all table schemas
.schema

-- Enable column headers in query output
.headers on

-- Set output mode to columns (formatted)
.mode column

-- Show current settings
.show

-- Read and execute SQL from a file
.read /tmp/seed_data.sql

-- Export query results to a CSV file
.output /tmp/export.csv
.mode csv
SELECT * FROM products;
.output stdout

-- Quit the CLI
.quit

Step 4: Create Tables and Insert Data

SQLite supports standard SQL DDL and DML. Create a simple products table:

CREATE TABLE products (
    id        INTEGER PRIMARY KEY AUTOINCREMENT,
    name      TEXT    NOT NULL,
    price     REAL    NOT NULL,
    stock     INTEGER DEFAULT 0,
    created   TEXT    DEFAULT (datetime('now'))
);

Insert rows:

INSERT INTO products (name, price, stock) VALUES ('Widget A', 9.99, 150);
INSERT INTO products (name, price, stock) VALUES ('Gadget B', 24.50, 80);
INSERT INTO products (name, price, stock) VALUES ('Tool C', 5.75, 300);

Query the data:

SELECT * FROM products;
SELECT name, price FROM products WHERE stock > 100 ORDER BY price ASC;

Update and delete:

UPDATE products SET stock = stock - 10 WHERE name = 'Widget A';
DELETE FROM products WHERE stock = 0;

Create an index for faster lookups:

CREATE INDEX idx_products_name ON products(name);

Step 5: SQLite File Location and Management

Because SQLite databases are files, their location, permissions, and backup strategy follow standard Linux file management practices.

# Set appropriate ownership and permissions
sudo chown appuser:appgroup /opt/myapp/inventory.db
sudo chmod 640 /opt/myapp/inventory.db

# Check file size
ls -lh /opt/myapp/inventory.db

# Back up the database by copying the file (safe while no writes are in progress)
cp /opt/myapp/inventory.db /backups/inventory_$(date +%Y%m%d).db

# Or use the SQLite online backup via the CLI (safe during active writes)
sqlite3 /opt/myapp/inventory.db ".backup /backups/inventory_$(date +%Y%m%d).db"

SQLite uses file-level locking. Only one writer can access the database at a time, making SQLite unsuitable for high-concurrency write workloads. For such scenarios, PostgreSQL or MySQL is the appropriate choice.

Step 6: Python Integration

Python’s standard library includes the sqlite3 module, so no additional packages are required. The following script demonstrates a complete workflow:

#!/usr/bin/env python
# -*- coding: utf-8 -*-
import sqlite3
import os

DB_PATH = '/opt/myapp/inventory.db'

def get_connection():
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row  # enables dict-like row access
    return conn

def create_table(conn):
    conn.execute("""
        CREATE TABLE IF NOT EXISTS orders (
            id       INTEGER PRIMARY KEY AUTOINCREMENT,
            product  TEXT NOT NULL,
            quantity INTEGER NOT NULL,
            total    REAL NOT NULL,
            created  TEXT DEFAULT (datetime('now'))
        )
    """)
    conn.commit()

def insert_order(conn, product, quantity, total):
    conn.execute(
        "INSERT INTO orders (product, quantity, total) VALUES (?, ?, ?)",
        (product, quantity, total)
    )
    conn.commit()

def get_orders(conn):
    cursor = conn.execute("SELECT * FROM orders ORDER BY created DESC")
    return cursor.fetchall()

if __name__ == '__main__':
    conn = get_connection()
    create_table(conn)
    insert_order(conn, 'Widget A', 5, 49.95)
    insert_order(conn, 'Gadget B', 2, 49.00)

    for row in get_orders(conn):
        print(dict(row))

    conn.close()

Save as /opt/myapp/db_test.py and run:

python /opt/myapp/db_test.py

Always use parameterized queries (? placeholders) when inserting user-supplied data. Never concatenate user input directly into SQL strings, as this creates SQL injection vulnerabilities.

Step 7: PHP Integration

PHP includes SQLite support through the PDO_SQLite and SQLite3 extensions. On RHEL 7, install the PHP SQLite extension:

sudo yum install php-pdo -y
# Restart the web server to load the extension
sudo systemctl restart httpd

Verify the extension is available:

php -m | grep -i sqlite

Example PHP script using PDO (the recommended interface):

<?php
$db_path = '/opt/myapp/inventory.db';

try {
    $pdo = new PDO('sqlite:' . $db_path);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Create table
    $pdo->exec("CREATE TABLE IF NOT EXISTS sessions (
        id      TEXT PRIMARY KEY,
        user_id INTEGER NOT NULL,
        created TEXT DEFAULT (datetime('now'))
    )");

    // Insert using prepared statement (prevents SQL injection)
    $stmt = $pdo->prepare("INSERT OR IGNORE INTO sessions (id, user_id) VALUES (:id, :uid)");
    $stmt->execute([':id' => uniqid(), ':uid' => 42]);

    // Fetch results
    $rows = $pdo->query("SELECT * FROM sessions ORDER BY created DESC LIMIT 10")->fetchAll(PDO::FETCH_ASSOC);
    foreach ($rows as $row) {
        echo $row['id'] . ' => user ' . $row['user_id'] . "n";
    }

} catch (PDOException $e) {
    error_log('Database error: ' . $e->getMessage());
    // Do NOT echo $e->getMessage() to end users in production
}
?>

Step 8: SQLite Browser GUI

DB Browser for SQLite (formerly SQLite Browser) is a graphical tool for viewing and editing SQLite databases. While it is primarily a desktop application, it can be useful on development workstations or accessed via X forwarding.

DB Browser for SQLite is not in the standard RHEL 7 repositories, but can be installed from the EPEL repository or via Flatpak:

# Install EPEL repository first
sudo yum install epel-release -y

# Check if sqlitebrowser is available in EPEL
sudo yum info sqlitebrowser

# If available, install
sudo yum install sqlitebrowser -y

Alternatively, on a desktop RHEL 7 workstation, download the AppImage from the official project page at https://sqlitebrowser.org and make it executable:

chmod +x DB_Browser_for_SQLite-v3.12.2-x86_64.AppImage
./DB_Browser_for_SQLite-v3.12.2-x86_64.AppImage

For server environments without a display, use the SQLite CLI exclusively. All operations available in the GUI can be performed with SQL statements and dot-commands in the terminal.

Conclusion

SQLite on RHEL 7 is quick to install, requires zero server configuration, and integrates naturally with both Python and PHP through their standard libraries. Its file-based architecture makes it trivially easy to back up, move, and version-control database files alongside application code. While it is not a replacement for PostgreSQL or MySQL in high-concurrency production environments, SQLite excels in development and test setups, command-line tools, configuration storage, mobile and embedded scenarios, and single-user applications. Understanding when to use SQLite versus a client-server database is as valuable as knowing how to use either one.