SQLite is a self-contained, serverless, zero-configuration SQL database engine that stores an entire database in a single file on disk. Unlike MySQL, PostgreSQL, and MongoDB which require a running server process, SQLite is a library that is linked directly into the application — there is no network connection, no authentication, and no administration overhead. SQLite is the world’s most widely deployed database: it is built into every Android and iOS device, every Python installation, every macOS and Windows system, and most browsers. It is ideal for applications with a single write user, embedded applications, mobile apps, desktop tools, testing environments, and applications where simplicity matters more than concurrent write performance. This guide covers installing SQLite on RHEL 9, using the CLI, creating databases from Python and PHP, and understanding when SQLite is the right choice.

Prerequisites

  • RHEL 9 with sudo/root access

Step 1 — Install SQLite

# SQLite is often already installed on RHEL 9
sqlite3 --version

# If not installed:
dnf install -y sqlite sqlite-devel sqlite-libs

Step 2 — Create a Database and Use the CLI

# Open (or create) a database file
sqlite3 /var/data/myapp.db
-- Inside sqlite3 CLI
-- Create a table
CREATE TABLE users (
    id      INTEGER PRIMARY KEY AUTOINCREMENT,
    name    TEXT NOT NULL,
    email   TEXT UNIQUE NOT NULL,
    created DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Insert rows
INSERT INTO users (name, email) VALUES ('Alice', '[email protected]');
INSERT INTO users (name, email) VALUES ('Bob', '[email protected]');

-- Query
SELECT * FROM users;

-- Show tables
.tables

-- Schema
.schema users

-- Quit
.quit

Step 3 — Useful SQLite CLI Commands

# Execute a query without entering interactive mode
sqlite3 /var/data/myapp.db "SELECT COUNT(*) FROM users;"

# Import CSV data
sqlite3 /var/data/myapp.db ".mode csv" ".import /tmp/data.csv users"

# Export to CSV
sqlite3 -header -csv /var/data/myapp.db "SELECT * FROM users;" > /tmp/users-export.csv

# Backup database (safe even while in use)
sqlite3 /var/data/myapp.db ".backup /var/backups/myapp.db"

# Check database integrity
sqlite3 /var/data/myapp.db "PRAGMA integrity_check;"

Step 4 — SQLite from Python

# Python's sqlite3 module is in the standard library
python3 <<'PYEOF'
import sqlite3

conn = sqlite3.connect('/var/data/myapp.db')
cur  = conn.cursor()

cur.execute("INSERT INTO users (name, email) VALUES (?, ?)", ("Charlie", "[email protected]"))
conn.commit()

for row in cur.execute("SELECT id, name, email FROM users"):
    print(row)

conn.close()
PYEOF

Step 5 — SQLite from PHP

prepare("SELECT name, email FROM users WHERE id = :id");
$stmt->bindValue(':id', 1, SQLITE3_INTEGER);
$result = $stmt->execute();
while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
    echo $row['name'] . ': ' . $row['email'] . "n";
}
$db->close();

Step 6 — When to Use SQLite vs MySQL/PostgreSQL

SQLite is appropriate when:

  • Only one process writes to the database at a time
  • The database is small (under 1 TB — SQLite supports up to 281 TB)
  • Simplicity and zero administration are priorities
  • Building a CLI tool, prototype, test fixture, or single-user desktop app

Use MySQL/PostgreSQL when you need: concurrent writes from multiple processes, network access, replication, user authentication, or databases serving multiple application servers simultaneously.

Conclusion

SQLite on RHEL 9 requires no configuration, no service management, and no user accounts — just a file path. Its built-in support in Python, PHP, and most programming languages makes it the fastest way to add persistent structured storage to a tool or application. For web applications with multiple concurrent writers, MySQL or PostgreSQL remain the appropriate choice.

Next steps: How to Install MySQL 8 on RHEL 9, How to Install PostgreSQL on RHEL 9, and How to Install Redis on RHEL 9.