Wrap-up — When SQLite Is the Right Default

The one-paragraph version

SQLite is the right default when your data fits on one machine and the writers are co-located with the data. That covers far more products than the 2010s 'use Postgres for everything' default suggested. The renaissance is real because the cost of putting compute near data — edge runtimes, single-server deploys, local-first apps — finally caught up with the technology that was always there.

Pick SQLite when:

You have one writer (or can shard so each shard has one).
Your reads are dominated by a single machine's reach.
You value deployment simplicity (no daemon, no port, no auth tier).
You can use Litestream / Turso / a per-tenant pattern to add replication where you need it.
You want backup that's cp.

Pick Postgres (or another server DB) when:

Multiple writers across multiple machines must reach the same logical data.
You need fine-grained per-user database authentication / row-level security.
You need built-in synchronous replication and managed failover.
Your team's expertise is concentrated there and the deployment story is solved.

Self-reference: Pippa is built on SQLite for exactly the reasons above — one writer (Dad's machine), one user, deployment is 'launchctl kickstart', backup is rsync to NAS. The renaissance gave the existing 'right tool for the job' a new generation of context to live in. This quest is the engineering map of that tool.

Keep going from here: build something local-first. Index a real corpus with FTS5. Replicate a real database with Litestream. The renaissance is happening because people are doing those things, not because they're reading about them.

Code

Where to go next·text

Local-first frontiers worth exploring after this quest:

  Litestream / LiteFS  — replication on plain SQLite
  Turso / libSQL       — distributed SQLite-as-a-service
  Cloudflare D1        — SQLite inside the Workers runtime
  rqlite / dqlite      — Raft-replicated SQLite
  Inertia / TinyBase   — local-first frontend state stores backed by SQLite
  Datasette            — instant API + UI on top of any SQLite file

Read Ben Johnson, Simon Willison, and the SQLite docs themselves
(www.sqlite.org/changes.html keeps a complete history).

Exercise

Pick one of the projects from the next-step list (Datasette, Litestream, libSQL, D1, Inertia) and spend 30 minutes building something tiny with it. Ship it (a public repo, a blog post, a working URL). The renaissance is real because people ship local-first; this lesson exists so you become one of them.

Wrap-up — When SQLite Is the Right Default

The one-paragraph version

Code

External links

Exercise

Progress

Comments 0