SQLite

SQLite is what you reach for when the storage problem is "I need real SQL, real transactions, and zero operational footprint." It is the most-deployed database in history — every iOS app, every Android app, every browser’s localStorage backend, every aircraft flight system. Official docs at sqlite.org/docs own the SQL reference.

What it is

A self-contained, serverless, zero-configuration, transactional SQL database. The entire database lives in a single file on disk. No daemon, no port, no install — just link a library and open a file. ACID, with WAL mode for concurrent readers + one writer. Public domain — no license, no upstream, no rug-pull risk.

Distribution: source amalgamation (one .c file), system library on every modern OS, language bindings for everything. The wire protocol is "function calls into a library." That is the whole pitch.

The pitch: when the writer count is one (or near it), the database fits on one machine, and you do not need a server boundary, SQLite is the right primitive. The wrong primitive in Postgres for these workloads is "run a daemon, manage a port, configure auth, deploy backups." The wrong primitive in JSON files is "lose ACID, lose indexes, lose SQL." SQLite is the engineering answer.

When to use it

Reach for it when:

• The workload is single-process — a desktop app, a CLI tool, an embedded device, a small service.
• Reads dominate, writes are rare and sequential — config stores, analytics caches, search indexes, asset catalogs.
• You want portability — the database is a file you can copy, version, attach to an email, check into git for tiny seed data.
• The schema is small to medium — dozens to low-hundreds of tables, millions of rows. SQLite handles much more than people give it credit for.
• You need zero ops — no cluster, no failover, no DBA. The backup story is cp my.db my.db.bak.
• You want a first-class testing database — every test gets its own SQLite file; tests run in parallel; teardown is rm.

Skip it when:

• You have many concurrent writers — SQLite serializes writes. WAL mode helps; it doesn’t turn SQLite into Postgres.
• You need a network boundary — clients across a network want a server. SQLite is in-process.
• The data is inherently graph-shaped or time-series-heavy at scale. Specialized stores win.
• You need enterprise auth, row-level security, or extension ecosystems like pgvector / PostGIS. Reach for Postgres.

At a glance

Core concepts

• Database file — one file, one database. Multiple databases via ATTACH DATABASE.
• Journal modes — DELETE (default), WAL (write-ahead log, much better concurrency). Turn on WAL day one for anything non-trivial: PRAGMA journal_mode=WAL;.
• Type affinity — SQLite has a flexible type system; columns have affinity not strict types. Use STRICT tables (added 3.37) when you want Postgres-style discipline.
• In-memory — :memory: as the path gives you a transient database with no disk I/O. Fantastic for tests.
• FTS5 — built-in full-text search extension. Tokenizers, ranking, snippets. Good enough for most search needs without a separate engine.
• JSON1 — JSON column functions built in. Indexable via generated columns.

Famously safe

SQLite is one of the most heavily-tested pieces of software in existence: 100% branch coverage on the release builds; an OOM-injecting test harness; an I/O-error-injecting test harness; a fuzz-testing pipeline that has run for over a decade. The bar is engineering, not vibes.

How to integrate

Default integration for a new use case:

1. Pick the driver. Python: sqlite3 (stdlib). Node: better-sqlite3 (synchronous, very fast) or sqlite3 (async). Rust: rusqlite. Everywhere: it’s a one-line add.
2. Turn on WAL. PRAGMA journal_mode=WAL; once per database. Concurrent readers + one writer without blocking.
3. Set sensible pragmas. PRAGMA synchronous=NORMAL; is the right tradeoff for most apps (full fsync on checkpoint, not on every write). PRAGMA foreign_keys=ON; is off by default — turn it on.
4. Use prepared statements. Reuse them; don’t recompile per query. better-sqlite3 makes this idiomatic; the Python sqlite3 module does it under the hood with ? parameters.
5. Back up with .backup, not cp. The SQLite Online Backup API safely snapshots a database that’s in use. sqlite3 my.db ".backup my.db.bak".
6. Migrate with a versioned sequence — same discipline as Postgres. PRAGMA user_version is the cheapest way to track schema version.

In the GL stack

SQLite is not a primary store anywhere in the active focus today — Postgres handles the durable, multi-writer surfaces — but it shows up:

• Local test fixtures — fast, isolated database files for unit tests in scripts and tools.
• CLI state — small caches and indexes for utility scripts.
• Notebook scratch — DuckDB tends to win here when the workload is analytical, but SQLite is fine for plain-relational scratch state.

If a future GL surface needs a small, single-writer, server-less store — a desktop tool, an embedded device, a sidecar cache — SQLite is the first choice, not Postgres.

Gotchas

• One writer at a time. Even in WAL mode. If two processes try to write concurrently, one waits. Architect around it or use a different engine.
• Default synchronous=FULL is slow; OFF is unsafe. NORMAL is the right tradeoff in WAL mode — durable across application crashes, not across power loss without a battery-backed cache. Know what you’re trading.
• Network filesystems eat SQLite databases. NFS, SMB, Dropbox-sync folders — file locking semantics break SQLite’s assumptions. Keep the database on local disk.
• Schema migrations are awkward. ALTER TABLE is limited (no DROP COLUMN until recently; no renaming foreign keys). The canonical migration pattern is "create new table, copy rows, drop old, rename." Annoying but well-trodden.
• No native role-based auth. Anyone who can open the file can do anything. Auth lives outside SQLite.

Risks

• Single-machine ceiling. When you outgrow one box, you outgrow SQLite. The graduation path is Postgres, and it’s a real port — different concurrency model, different schema migration story, different operational shape.
• Process-local. A serverless function with ephemeral disk is not a place to run SQLite as a durable store. Litestream + S3 is a workaround; it’s also a footgun.
• No built-in replication. Litestream (continuous backup to S3), LiteFS (FUSE-based replication), or rqlite (Raft on top of SQLite) all exist. None of them are as boring as Postgres streaming replication.

Alternatives

• DuckDB — Wins for analytical workloads (large aggregations, Parquet, columnar scans). Loses on small-row OLTP. Same single-file ethos, different shape. See DuckDB.
• Postgres — Wins when you have a server boundary and concurrent writers. Loses on operational footprint for small projects. See Postgres.
• LMDB / RocksDB — Wins as embedded key-value stores when you don’t need SQL. Loses on every "I want to query that" question.
• Plain JSON files — Wins when the data is tiny and rarely changes. Loses on every concurrency, durability, and query-flexibility front. Reach for SQLite once you’re past one config file.

Related

• Postgres — the graduation path when single-machine, single-writer is no longer enough.
• DuckDB — the analytical sibling. Different workload, same file-first ethos.