Most applications don’t need a database server. They need durable, queryable state — and somewhere along the way the industry decided that meant a Postgres container, a connection pooler, a network hop, and a password rotation schedule. Meanwhile, the database that already ships with your runtime sits unused in the standard library. Python’s sqlite3 module is in the stdlib for a reason: for a huge class of applications, one file on disk is the entire persistence layer.
SQLite is the most deployed database engine in the world by a margin that isn’t close. It runs on every phone, every browser, and a remarkable share of embedded hardware. Yet in backend discussions it still gets treated as a toy — something for prototypes and side projects, to be replaced by a “real” database before production. That reputation is outdated. With write-ahead logging enabled and a couple of pragmas set deliberately, SQLite handles production traffic that would surprise people who last looked at it a decade ago.
This post covers the embedding model that makes SQLite different, WAL mode and what it actually guarantees, the busy timeout and SQLITE_BUSY handling, durability tuning, honest limits, a complete Python setup, and the myths that keep teams from using it.
The Embedding Model: No Server, No Network Hop
SQLite is not a server you connect to. It’s a C library you link into your process, and the database is a single ordinary file on the filesystem. There’s no daemon, no port, no authentication handshake, no wire protocol. Your application makes a function call, and the query executes in-process, reading and writing the file directly. The official guidance on when to use SQLite describes this as the key distinction from client/server databases, and it’s worth internalizing: SQLite lives with your data, not in front of it.
That architecture wins in a specific set of places:
- Edge and IoT. No daemon to supervise, tiny footprint, and reads that never leave the machine. A sensor gateway on a $5 board can run real SQL locally.
- Desktop and mobile apps. The app’s data lives next to the app. Offline-first is the default state, not a feature you bolt on.
- Read-heavy websites. Sites that serve mostly rendered content can read straight from a local file with no pool contention.
- CI and build caches. A cache that’s one file is a cache you can copy, diff, invalidate atomically, and ship between runners with
scp. - Embedded analytics. Load data once, query repeatedly — a sweet spot for per-tenant data files in SaaS systems that shard by customer.
The trade is symmetrical: because there’s no server, there’s no network access either. Clients that need to reach the database from other machines must go through your application process. That’s a feature for isolation and a limit for multi-host deployments, and it’s the first thing to check when deciding whether SQLite fits.
WAL Mode: Readers Don’t Block the Writer
Out of the box, SQLite uses rollback-journal mode: every write takes an exclusive lock over the database file, and readers wait. Under any concurrent traffic that’s brutal — one slow read stalls every write. Write-ahead logging (WAL) changes the layout. Writers append committed changes to a separate -wal file and update the main file later, in batches. Readers walk the main file plus the WAL to see a consistent snapshot.
The consequence is the headline: readers don’t block the writer, and the writer doesn’t block readers. Concurrent processes can run SELECTs against the database while another process commits a transaction. Only two writers still conflict with each other — and that’s the fundamental shape of SQLite concurrency, which we’ll get to in the limits section. The WAL documentation covers the full mechanics; for daily use, what matters is that enabling it is one statement, persisted in the database file itself:
-- Enable write-ahead logging (persists across connections)
PRAGMA journal_mode = WAL;
-- Verify: should report "wal"
PRAGMA journal_mode;WAL is worth enabling on essentially every database you’ll concurrently access. Its costs are minor and worth knowing: readers and writers must be on the same machine (WAL doesn’t work over network filesystems, reliably), the -wal and -shm sidecar files must live beside the database, and opening a WAL database read-only requires those files to be present.
busy_timeout: Handling SQLITE_BUSY Without Surprises
With WAL on, two concurrent writers still serialize — one of them will hit SQLITE_BUSY, which Python raises as sqlite3.OperationalError: database is locked. The naive experience of that error is a crash at a random moment under load. The fix is a timeout, not a retry loop: busy_timeout tells SQLite to block and retry internally for up to N milliseconds before giving up and returning the error.
-- Wait up to 5 seconds for a conflicting lock before raising SQLITE_BUSY
PRAGMA busy_timeout = 5000;Set it on every connection. It’s per-connection, not per-database. Five seconds absorbs nearly all contention between a handful of writer processes; if you still see busy errors at that setting, your write volume is the signal, and it’s pointing at the exit to a client/server database.
Durability: synchronous NORMAL vs FULL
The synchronous pragma controls how carefully SQLite flushes commits to disk. FULL fsyncs on every commit — maximum durability, maximum latency. NORMAL, the recommended pairing with WAL, syncs only at checkpoints. Transactions remain atomic and consistent in both settings; the difference is that with NORMAL, a power loss (not an application crash) can lose the most recently committed transactions, while the database file itself never corrupts. For an application cache or an analytics scratchpad, that trade is usually a bargain: commits drop from milliseconds to microseconds.
-- WAL + NORMAL is the standard high-performance pairing
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
-- Use FULL when every commit must survive a power cut (financial records)
PRAGMA synchronous = FULL;A Complete Python Setup: Schema, WAL, Upserts
Here’s the connection boilerplate worth copying. isolation_level=None puts the stdlib driver in autocommit mode so you control transaction boundaries explicitly — the default module behavior of implicitly opening transactions around your statements surprises people and interacts badly with pragmas. Everything else is the standard setup: WAL, a busy timeout, and a schema with an upsert pattern.
import sqlite3
def connect(path: str) -> sqlite3.Connection:
conn = sqlite3.connect(path, isolation_level=None, timeout=5.0)
conn.execute("PRAGMA journal_mode = WAL")
conn.execute("PRAGMA busy_timeout = 5000")
conn.execute("PRAGMA synchronous = NORMAL")
conn.execute("PRAGMA foreign_keys = ON")
return conn
conn = connect("metrics.db")
conn.executescript("""
CREATE TABLE IF NOT EXISTS counters (
name TEXT PRIMARY KEY,
value INTEGER NOT NULL DEFAULT 0,
updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_counters_updated
ON counters(updated_at);
""")Upserts are native since SQLite 3.24 — no more INSERT OR IGNORE followed by an UPDATE. The ON CONFLICT ... DO UPDATE clause maps a conflict target to an update, and excluded refers to the row you attempted to insert:
rows = [("api_hits", 3), ("cache_misses", 1), ("api_hits", 7)]
conn.executemany("""
INSERT INTO counters (name, value, updated_at)
VALUES (?, ?, datetime('now'))
ON CONFLICT(name) DO UPDATE SET
value = counters.value + excluded.value,
updated_at = excluded.updated_at
""", rows)
for row in conn.execute("SELECT name, value FROM counters ORDER BY value DESC"):
print(row)Limits, and When to Graduate to Postgres
SQLite’s concurrency model is simple: one writer at a time, with WAL letting readers proceed concurrently. Writes serialize, and each transaction — even in WAL — commits at roughly the speed of one durable disk sync under FULL. Practical write throughput lands in the low tens of thousands of small transactions per second on commodity SSDs, which is far more than most applications need, and far less than a hot multi-tenant SaaS backend needs.
Graduate to Postgres when one of these is true:
- Sustained high-concurrency writes from many processes or machines that genuinely need parallel writers. SQLite will serialize them; a server with MVCC and background writers won’t.
- Network clients. Multiple application servers that each want direct database access need a database that speaks a network protocol.
- Huge working sets that don’t fit the page cache, or analytical queries that would starve the same machine your app runs on.
The maximum database size — 281 terabytes by default — is not a limit anyone hits. The real limits are architectural: one machine, one writer at a time. Note also that neither of those means “one user”: SQLite sites regularly serve substantial concurrent read traffic, because reads parallelize cleanly.
Myths Worth Retiring
“Single writer means single user.” No. It means one writer at a time, for the duration of each transaction. With WAL, any number of concurrent readers run simultaneously, and readers never wait behind the writer. A content site, a config store, a metrics sink with bursty writes — these support thousands of concurrent users on SQLite without strain.
“SQLite doesn’t scale.” Too vague to be useful. What scales and what doesn’t: read throughput scales with the machine because reads are parallel; write throughput is capped by serialized transactions, roughly at disk-sync speed. That’s a ceiling around tens of thousands of writes per second — which covers an enormous share of real workloads. When people say SQLite doesn’t scale, they usually mean “my write volume exceeds one machine’s sync rate” or “I need multiple app servers writing over the network.” Both are real, specific, and checkable — so check them instead of assuming.
Wrapping Up
SQLite in production isn’t a hack — it’s the original design target. Enable WAL, set a busy timeout, choose synchronous deliberately, and use the same ON CONFLICT upserts you’d use anywhere else. When your write concurrency or your network topology outgrows a single file, the migration path to Postgres is well-trodden. But a lot of databases are replaced by servers they never needed. The full SQLite documentation is short enough to actually read, and it starts from the premise that got lost somewhere: for most situations, SQLite is a legitimate answer — and it’s already in your standard library.