Running SQLite in production

SQLite can run real production services, if you set it up deliberately and know where its limits are.

Many production services now run on SQLite. A solid baseline for a web app on one server:

PRAGMA journal_mode = WAL (once) and PRAGMA synchronous = NORMAL.
PRAGMA busy_timeout = 5000 and PRAGMA foreign_keys = ON on every connection.
Write transactions with BEGIN IMMEDIATE, kept short. Many apps funnel writes through a single writer connection (or a queue) and use a pool of read-only connections; this removes lock contention entirely.
PRAGMA optimize periodically; optionally PRAGMA mmap_size and a larger cache_size for read-heavy loads.
Keep the database on local disk (not NFS/SMB), with the app and database on the same machine.

Backups: never just copy a live file. Use the online backup API (con.backup(target) in Python, .backup in the shell), VACUUM INTO 'snapshot.db', or a continuous replication tool such as Litestream, which streams WAL changes to object storage for point-in-time restore. Run PRAGMA integrity_check on restored copies.

Limits: one writer at a time (writes per second depend on transaction size and disk sync speed), no built-in network access, user management or replication, and one machine. When you need many writers across machines, high availability with automatic failover, or fine-grained access control, move to a client-server database — SQL and schema knowledge transfers directly.

Example

import sqlite3, os, tempfile

def open_db(path, readonly=False):
    uri = f"file:{path}?mode={'ro' if readonly else 'rwc'}"
    con = sqlite3.connect(uri, uri=True, isolation_level=None, timeout=5)
    con.execute("PRAGMA foreign_keys = ON")
    con.execute("PRAGMA synchronous = NORMAL")
    return con

d = tempfile.mkdtemp()
writer = open_db(os.path.join(d, "app.db"))
writer.execute("PRAGMA journal_mode = WAL")
writer.execute("CREATE TABLE IF NOT EXISTS event (id INTEGER PRIMARY KEY, kind TEXT)")
writer.execute("BEGIN IMMEDIATE")
writer.executemany("INSERT INTO event (kind) VALUES (?)", [("signup",), ("login",)])
writer.execute("COMMIT")

reader = open_db(os.path.join(d, "app.db"), readonly=True)
print(reader.execute("SELECT count(*) FROM event").fetchone())        # (2,)

backup = sqlite3.connect(os.path.join(d, "backup.db"))
writer.backup(backup)                         # consistent online copy
print(backup.execute("PRAGMA integrity_check").fetchone())            # ('ok',)
writer.execute(f"VACUUM INTO '{os.path.join(d, 'snapshot.db')}'")

Remember

WAL + synchronous=NORMAL + busy_timeout + short IMMEDIATE transactions
A single writer connection plus read-only readers avoids contention
Back up with the backup API, VACUUM INTO or Litestream, never a raw copy

Watch out for

Backing up with cp while the app is writing, producing a corrupt or inconsistent copy.

Check yourself

What is the safest way to back up a live SQLite database from Python?

Official docs: sqlite.org/backup.html

Example

Remember

Watch out for

Check yourself

Builds on

Unlocks