The SQLite Trick That Makes It Production-Ready

The myth

"SQLite is for prototypes and mobile apps, switch to Postgres when you go live." This advice is outdated. SQLite with the right pragmas runs sites with millions of reads/day on commodity hardware. Expensify ran on it, NASA's Mars rovers run on it, every browser on earth runs on it.

The one-line upgrade: WAL mode

PRAGMA journal_mode = WAL;

Write-Ahead Logging changes everything. Readers don't block writers, writers don't block readers. A single writer can coexist with many concurrent readers — which covers 95% of web-app workloads.

Enable it once per database file (persistent setting). In EF Core:

builder.Services.AddDbContext<AppDb>(opt =>
    opt.UseSqlite("Data Source=app.db")
       .UseSqliteOptions(o => o.ExecutionStrategy(/* ... */))
);

// run once at startup
await db.Database.ExecuteSqlRawAsync("PRAGMA journal_mode = WAL;");
await db.Database.ExecuteSqlRawAsync("PRAGMA synchronous = NORMAL;");
await db.Database.ExecuteSqlRawAsync("PRAGMA busy_timeout = 5000;");
await db.Database.ExecuteSqlRawAsync("PRAGMA cache_size = -20000;");

• synchronous = NORMAL: safe with WAL, 5× faster writes than FULL
• busy_timeout = 5000: wait 5 seconds instead of failing immediately on lock contention
• cache_size = -20000: 20 MB page cache (negative = KB, positive = pages)

Backups without downtime

VACUUM INTO 'backup.db';

Atomic snapshot, works while the app is running, produces a compact file you can rsync off-site. Run it from cron every hour.

When SQLite is not the answer

• Multiple write-heavy app servers behind a load balancer: you need a network-accessible DB. SQLite is file-based.
• Horizontal write scaling: hard ceiling around a few hundred writes per second on commodity SSDs.
• Complex analytical queries on 100GB+: Postgres's planner is better.

When SQLite is absolutely the answer

• Single-server web apps (even busy ones)
• Read-heavy APIs with a caching layer
• Embedded/edge deployments
• Any project where "zero DB administration" is a feature, not a compromise

Takeaway

Four PRAGMAs turn SQLite from "toy database" into "production database that outlives your codebase". No DBA needed. No connection-pool tuning. No backup daemon.