SQLite: The Database I Keep Underestimating

I keep underestimating SQLite. Every time I reach for PostgreSQL or MySQL for a new project, I stop and think: does this actually need a server process running 24/7? Most of the time, the answer is no.

SQLite gets treated like a toy database, something for prototypes and side projects. That reputation is wrong. It powers production systems handling billions of reads. Your browser uses it. Your phone uses it. It is everywhere and it is fast.

Dark code screen — Yes, I use dark mode. No, I will not argue about it.

Why SQLite Works for Production

The single-writer limitation sounds like a dealbreaker. In practice, it rarely is. Most web applications have a write ratio of maybe 5-10%. SQLite handles concurrent reads just fine. WAL mode makes this even better.

Here is what you get with SQLite that you do not get with a client-server database:

Zero configuration. No connection strings, no user management, no listening port to secure.

Zero latency. The database runs in the same process as your application. No network round trips.

Zero admin. No replication lag to debug, no vacuum schedules to forget ( mostly ), no pg_hba.conf to misconfigure.

Full ACID. Transactions, isolation, durability. It is not pretending.

WAL Mode Changes Everything

The default journal mode is rollback journal. It works, but it locks the whole database for writes. WAL ( Write-Ahead Logging ) lets readers and one writer work simultaneously. This is the single most important setting to change.

import sqlite3

conn = sqlite3.connect('app.db')
conn.execute('PRAGMA journal_mode=WAL')
conn.execute('PRAGMA synchronous=NORMAL')
conn.execute('PRAGMA cache_size=-64000')  # 64MB cache

# That is it. Three lines and your SQLite is production-ready.

The NORMAL synchronous setting is safe with WAL. You get good durability without flushing to disk on every commit. The cache_size setting keeps more pages in memory, which matters when your reads are hitting the same hot data.

Schema Migrations Without the Drama

I do not use alembic or any migration framework for SQLite projects. The ALTER TABLE support got much better in SQLite 3.35.0 ( 2021 ). You can rename columns and drop columns now. For adding columns, that always worked.

-- Keep a migrations table
CREATE TABLE IF NOT EXISTS _migrations (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE,
    applied_at TEXT DEFAULT (datetime('now'))
);

-- Run migrations in a transaction
BEGIN;
ALTER TABLE users ADD COLUMN avatar_url TEXT;
INSERT INTO _migrations (name) VALUES ('001_add_avatar_url');
COMMIT;

Simple. A table, a transaction, done. No framework needed. I keep each migration as a .sql file, execute them in order, and record what ran. If something fails, the transaction rolls back and nothing breaks.

Backups Are Just File Copies

This is the part people forget. Backing up a PostgreSQL database requires pg_dump, coordinating with the running server, handling connections. With SQLite, you copy a file.

#!/usr/bin/env python3
"""Cold backup for SQLite."""
import sqlite3
import shutil
from datetime import datetime

def backup_db(db_path, backup_dir):
    # Option 1: safe online backup via API
    src = sqlite3.connect(db_path)
    dst = sqlite3.connect(f'{backup_dir}/app_{datetime.now():%Y%m%d_%H%M%S}.db')
    src.backup(dst)
    dst.close()
    src.close()

    # Option 2: just copy the file ( with WAL, also copy the -wal and -shm files )
    # shutil.copy2(db_path, f'{backup_dir}/app_{datetime.now():%Y%m%d}.db')
    # shutil.copy2(db_path + '-wal', backup_dir)
    # shutil.copy2(db_path + '-shm', backup_dir)

backup_db('/data/app.db', '/backups')

The sqlite3.Connection.backup() method is the safe way. It takes a consistent snapshot without locking the database. If you are feeling lazy and WAL is enabled, copying all three files ( .db, .db-wal, .db-shm ) also works if you do it quickly.

When NOT to Use SQLite

I am not religious about this. SQLite is the wrong choice when:

You need concurrent writes from multiple processes ( more than one writer at a time requires serialization )

You need to run on a network share or NFS ( file locking is unreliable )

Your database is larger than available RAM and you need complex joins on cold data

You need role-based access control at the database level ( SQLite has no users )

For everything else, SQLite is probably fine. And by fine I mean faster and simpler than what you were going to set up.

Connection Pooling in Python

One gotcha: SQLite connections are cheap but not free. In a web app, use a connection pool or at least reuse the connection properly.

import sqlite3
from contextlib import contextmanager
from threading import local

_local = local()

def get_connection(db_path='app.db'):
    """One connection per thread."""
    if not hasattr(_local, 'conn'):
        _local.conn = sqlite3.connect(db_path)
        _local.conn.execute('PRAGMA journal_mode=WAL')
        _local.conn.execute('PRAGMA foreign_keys=ON')
        _local.conn.row_factory = sqlite3.Row
    return _local.conn

@contextmanager
def query(db_path='app.db'):
    conn = get_connection(db_path)
    try:
        yield conn
    except Exception:
        conn.rollback()
        raise

# Usage
with query() as conn:
    users = conn.execute('SELECT * FROM users LIMIT 10').fetchall()

Thread-local storage is the easy way. Each thread gets its own connection, SQLite handles the locking internally. No pool manager, no dependency, no drama.

SQLite is not a stepping stone. It is the destination for a lot of applications. I spent years reaching for PostgreSQL by default when a file on disk with WAL mode would have done the job faster and simpler. Learn to recognize when you actually need the client-server model. It is less often than you think. :)