A practical look at when SQLite is a genuinely good production database, where it breaks, and the tooling that makes it viable.
For years the reflex answer was no. SQLite was the thing that stored your phone's contacts or your browser history, a file format pretending to be a database, fine for a unit test but not for anything real. If you were building a service, you reached for Postgres or MySQL without thinking about it.
That reflex is now wrong more often than it's right, and it's worth understanding why.
Start with scale. SQLite is almost certainly the most-deployed database engine on the planet. It ships in every browser, every phone, most cars, and countless embedded devices. It is boring, battle-tested code that has been fuzzed and audited to an extreme degree. "Nobody got fired for choosing Postgres" has a quiet cousin: nobody has ever found SQLite to be the flaky part of their stack.
Then there's raw speed. Because the database lives in the same process as your application, a read is a function call, not a network round trip. There is no connection pool, no wire protocol, no separate server to saturate. For read-heavy work, a well-indexed SQLite query returns before a networked database has finished its TCP handshake.
The thing that actually revived it, though, is the edge and serverless wave. When your compute runs in dozens of locations close to users, a single central Postgres instance becomes the slow part. Suddenly a database that lives as a file next to your code, replicable to every region, is not a compromise. It's the point.
The workloads where SQLite shines are more common than people assume:
- Read-heavy apps: blogs, docs sites, dashboards, catalogs, anything where reads outnumber writes by a wide margin.
- Single-node services: if your app runs on one box (and plenty of profitable apps do), a local database removes an entire tier of operational complexity.
- Per-tenant databases: give each customer their own SQLite file. Isolation is physical, backups are per-tenant, and a heavy customer can't slow everyone else down.
- Embedded and edge apps: CLIs, desktop apps, IoT, and edge functions that need local state without shipping a database server.
- Low-to-moderate write concurrency: internal tools, small SaaS, side projects that pay rent. Most apps do not actually have thousands of concurrent writers.
The common thread is valuing simplicity. No separate database process means nothing to provision, patch, secure, or wake up for at 3 a.m.
Be honest about the constraint: SQLite has a single writer. Writes serialize. Only one write transaction can be in flight at a time for a given database file. There is also no built-in network access or replication. The database is a file on a disk, full stop.
Those two facts are the whole story of "SQLite in production," and both are addressable.
The first fix is Write-Ahead Logging. In the default rollback-journal mode, a writer blocks readers. WAL mode changes that so readers and one writer proceed concurrently, which is exactly what a web app wants. Turn it on once and it sticks with the database file:
-- Run once per database (WAL is persistent across connections)
PRAGMA journal_mode = WAL;
-- Set on every connection your app opens
PRAGMA busy_timeout = 5000; -- wait up to 5s for a lock instead of erroring
PRAGMA synchronous = NORMAL; -- safe with WAL, much faster than FULL
PRAGMA foreign_keys = ON;
PRAGMA cache_size = -20000; -- ~20MB page cache (negative = KiB)
The busy_timeout is the setting people forget. Without it, a write that hits a momentary lock fails immediately with SQLITE_BUSY. With it, the connection waits and retries for you, which turns most concurrency problems into a few milliseconds of latency instead of an error. Because writes serialize, funnel them through a single connection or a small serialized pool rather than a large one.
The second fix is replication and durability, and this is where the modern tooling lives:
- Litestream streams your WAL to object storage (S3, R2, GCS) continuously, giving you point-in-time backups and disaster recovery with almost no overhead.
- LiteFS is a FUSE filesystem that replicates the database across nodes, so read replicas in other regions stay current.
- Turso and libSQL are a managed fork built for exactly this, with edge replication baked in. See the Turso tutorial for a hands-on walkthrough.
- Cloudflare D1 is a managed SQLite that runs inside the Workers platform, replicated across their network.
Litestream is the one I reach for first because it solves the scariest part (losing data on a single node) with a tiny config:
# /etc/litestream.yml
dbs:
- path: /var/lib/app/data.db
replicas:
- type: s3
bucket: my-app-backups
path: prod/data.db
region: us-east-1
# Snapshot periodically, stream WAL continuously
sync-interval: 1s
retention: 72h
Run litestream replicate alongside your app and restore with litestream restore on a fresh box. If you need multi-region reads, LiteFS layers on top, electing a primary for writes and serving reads locally everywhere else.
The honest limits, stated plainly:
- High write concurrency: if you genuinely have many writers hammering the same database at once, the single-writer model becomes your bottleneck. Per-tenant sharding can push this out, but a true multi-writer OLTP system wants Postgres.
- Large horizontal write scaling: SQLite does not shard writes across machines. If write throughput has to grow past one node, it is the wrong tool.
- Postgres-specific features: rich types,
LISTEN/NOTIFY, advanced full-text and vector extensions, stored procedures, fine-grained roles. If your design leans on those, use the database that has them.
Reach for SQLite when reads dominate, when your writes fit comfortably through one serialized channel, when you value one fewer moving part, or when you're deploying to the edge and latency to a central database is the enemy. Per-tenant SaaS, content-driven sites, internal tools, and embedded apps all land squarely in that zone. For the broader picture of where this fits, see our serverless and edge databases guide and the deeper dive on edge databases for low-latency apps.
Default to SQLite for new read-heavy or single-node services, and don't apologize for it. Turn on WAL, set busy_timeout, serialize your writes, and put Litestream in front of it on day one so a dead disk is a non-event. Reach for Turso or LiteFS the moment you need reads in multiple regions. Only graduate to Postgres when you hit a real write-concurrency wall or need a feature SQLite doesn't have, and by then you'll know exactly why you're paying for the extra tier.
The old assumption was that SQLite couldn't do production. The current reality is that for a large slice of applications, it's the simpler and faster choice, and the tooling has closed the gap on everything else.
