SQLite

The most deployed database in the world, and the one people underestimate. Worth knowing because “just use SQLite” is increasingly the right answer for small services, and because it’s in every Python install.

What it is

An embedded database: a library, not a server. Your process opens a file and reads and writes it directly. No connection, no port, no daemon.

import sqlite3
conn = sqlite3.connect("app.db")     # that's the whole setup

That’s the entire operational story, and it’s the reason to consider it: no server to run, patch, back up separately, or fail over.

The settings that matter

Defaults are conservative and thirty years old. Production SQLite needs configuration:

conn.execute("PRAGMA journal_mode = WAL")       # readers don't block writers
conn.execute("PRAGMA synchronous = NORMAL")     # safe with WAL, much faster
conn.execute("PRAGMA foreign_keys = ON")        # OFF by default!
conn.execute("PRAGMA busy_timeout = 5000")      # wait rather than fail on lock

Two of those are genuinely surprising:

• Foreign keys are off by default for backwards compatibility. Your REFERENCES clauses are decorative until you enable them, per connection.
• WAL mode is transformative. In the default rollback journal, a writer blocks all readers. With WAL, readers and one writer proceed concurrently, which is the difference between “toy” and “usable web backend”.

The concurrency model

One writer at a time, many concurrent readers (with WAL). Writes are serialised across the whole database, not per table or row.

That’s the constraint that decides suitability. It’s fine for:

• Read-heavy workloads — the overwhelming majority of web applications.
• Single-process services, CLI tools, desktop and mobile apps.
• Anything under roughly hundreds of writes per second.

It breaks down with sustained concurrent writes from many processes. SQLITE_BUSY under load is the symptom; busy_timeout mitigates but doesn’t remove it.

When SQLite is the right answer

More often than people assume:

• Small to medium web services. A read-heavy app on one box with WAL handles serious traffic.
• Tests. Fast, isolated, no container — though see the caveat below.
• Edge and embedded deployments.
• Local analytics — though DuckDB is better for column-oriented analytical work.
• Application file formats — a structured save file that’s queryable.

The 2026 angle worth mentioning: LiteFS and Litestream add replication and streaming backup, which removes the traditional “but there’s no failover” objection for single-writer workloads. That combination makes SQLite viable for production services in a way it wasn’t a few years ago.

When not to: multi-process concurrent writes, horizontal scaling across machines, or a need for the row-level concurrency Postgres gives you.

The type system

SQLite uses dynamic typing — types are suggestions, and it will happily store a string in an INTEGER column.

CREATE TABLE t (id INTEGER, n INTEGER) STRICT;   -- 3.37+ enforces types

STRICT tables, added in 3.37, enforce declared types. Use them. Without STRICT, type errors surface as data corruption rather than exceptions.

Also note: no native BOOLEAN (use INTEGER 0/1) and no native date type (store ISO-8601 text or Unix epoch integers).

Testing with SQLite — the caveat

Using SQLite as a stand-in for Postgres in tests is tempting and misleading:

Tests that pass on SQLite and fail on Postgres are a well-known waste of time. Use Testcontainers with real Postgres for anything database-specific. See ../../05_testing/integration/01_integration_tests.md.

Interview angle

• “When would you use SQLite in production?” — read-heavy single-process services, edge deployments, and anything where the operational simplicity of no server outweighs the single-writer constraint. With WAL plus Litestream or LiteFS for backup and replication, that covers more real services than people expect.
• “What’s the concurrency model?” — one writer at a time for the whole database, with many concurrent readers once WAL is enabled. Writes serialise globally, not per row, so sustained multi-process write load is where it stops working.
• “What defaults would you change?” — WAL journal mode, synchronous = NORMAL, busy_timeout, and foreign_keys = ON — foreign keys are off by default, so constraints are inert until you enable them per connection.
• “Would you test against SQLite instead of Postgres?” — no, not if the code uses anything Postgres-specific. Dynamic typing, missing JSONB and array support, limited ALTER TABLE and different concurrency mean tests can pass locally and fail in production. Use a real Postgres container.
• “What’s a STRICT table?” — SQLite 3.37+ opt-in type enforcement. Without it, declared types are advisory and a string will be stored in an INTEGER column, turning type errors into silent data problems.