SQLite as Your Primary Database: What Nobody Warns You About

I shipped my first production service on SQLite in mid-2024. It handles about 40,000 requests per day for an internal reporting tool, stores ~8 GB of data, and has been running without incident since. I'm a convert.

But I made mistakes early on that cost me time, and I've seen the same mistakes made by others who wrote about SQLite in production without mentioning them. So here's the list.

Enable WAL mode. Do it on every connection open.

The default journal mode (DELETE) uses an exclusive write lock that blocks all readers during a write. WAL (Write-Ahead Log) mode allows concurrent reads during writes. For any service with more than one goroutine, this is not optional.

PRAGMA journal_mode=WAL;
PRAGMA synchronous=NORMAL;
PRAGMA busy_timeout=5000;

Run these on every new connection, not just once. If your connection pool creates new connections, each one needs this setup. I use a ConnectHook in Go's database/sql for this. busy_timeout is important — without it, concurrent write attempts return SQLITE_BUSY immediately instead of waiting.

Use one write connection, multiple read connections

WAL mode supports concurrent reads, but writes are still serialized. The correct architecture for a Go service is a connection pool for reads (I use 4-8 connections) and a single write connection with a mutex. Attempting concurrent writes just queues them at the SQLite level anyway, but doing it explicitly in your code makes the behavior obvious and prevents subtle bugs from connection pool interactions.

type DB struct {
    write *sql.DB   // single connection, mutex-protected
    read  *sql.DB   // pool of read connections
    mu    sync.Mutex
}

func (db *DB) Exec(query string, args ...any) (sql.Result, error) {
    db.mu.Lock()
    defer db.mu.Unlock()
    return db.write.Exec(query, args...)
}

The query pattern that will hurt you

SELECT ... WHERE id IN (?, ?, ?, ...) with a dynamically-sized list. SQLite recompiles a statement for every unique argument count. If you're doing this in a hot path with varying list lengths, you'll see unexpected CPU usage from statement compilation.

The fix: use a temporary table or a WITH clause with VALUES rows. Or batch your queries to use fixed-size chunks (e.g., always 50 IDs, padding with a dummy value if needed). Not elegant, but it works.

Backup strategy

SQLite's .backup API (or the equivalent VACUUM INTO) produces a consistent snapshot even while the database is being written to. I run this every hour to object storage via a small sidecar process. The backup file is a complete, self-contained SQLite database — no WAL files needed, no pg_dump equivalent.

# Simple backup script
sqlite3 /data/app.db ".backup '/backups/app-$(date +%Y%m%d-%H%M%S).db'"

Restoring from backup is copying the file and restarting the service. I've done this once in anger (my fault — I ran a migration without testing it). It took four minutes including the time I spent panicking.

Schema migrations

SQLite's ALTER TABLE is limited — you can add columns but not much else. The standard pattern for complex migrations is: create new table, copy data, drop old table, rename new table. This is atomic if done in a transaction and works fine for tables up to a few million rows. Above that you need to think about doing it in batches.

I use goose for migration management. It works with SQLite without modification.

When not to use SQLite

Multiple writers from separate processes. If you have two services that both need to write to the same database, SQLite is the wrong choice — the file locking model doesn't compose well across process boundaries on network filesystems. Use Postgres.

Also: if your write volume is high enough that you're measuring write latency in hundreds of milliseconds regularly, you've grown past what SQLite is designed for. That's a good problem to have, and at that point the migration to Postgres is straightforward because your data model is already sane.

← Dotfiles · All posts · Neovim year →