When SQLite IS the Right Choice

Where it shines

SQLite is the right database when one or more of these are true:

Single-machine workloads — desktop apps, CLI tools, mobile apps, embedded devices, single-server web apps. The data lives where the code runs.
Read-heavy traffic on a single node — readers are unlimited and concurrent under WAL mode. SQLite happily serves tens of thousands of reads per second from one file.
Local-first products — apps that work offline and sync later (Linear's local-first mode, Obsidian sync, mobile apps). The data layer is on the user's device.
Test fixtures and CI — instant fresh databases via :memory: or temp files, no Docker, no waiting on a Postgres container.
Edge / serverless — Cloudflare Durable Objects, Fly.io Litestream / LiteFS, Turso, Cloudflare D1. The database file follows the compute.
Append-only / observability — logs, metrics, event stores. Modern SQLite + WAL handles bursty append workloads gracefully.
Documents / config / cache — apps where the file format used to be JSON or XML. SQLite is a better application file format.

Self-reference: Every Pippa conversation logs through the JSONL ground truth + SQLite mirror pattern. SQLite carries the indexed/queryable view; JSONL carries the durable append-only event log. The two reinforce each other — JSONL replay can rebuild SQLite, and SQLite indexes power the WebUI.

The phrase SQLite Renaissance exists because the 2024–2026 web is rediscovering all of these — partly because edge compute makes 'data near code' viable, and partly because Postgres-on-RDS bills got expensive enough that local-first started looking attractive again.

Code

An entire CLI tool's persistence layer in 8 lines·python

import sqlite3, sys, pathlib

DB = pathlib.Path.home() / '.mytool' / 'state.db'
DB.parent.mkdir(exist_ok=True)

conn = sqlite3.connect(DB)
conn.execute('''
  CREATE TABLE IF NOT EXISTS commands(
    id INTEGER PRIMARY KEY,
    cmd TEXT, ts TEXT DEFAULT (datetime('now'))
  )''')
conn.execute('INSERT INTO commands(cmd) VALUES (?)', (' '.join(sys.argv[1:]),))
conn.commit()

When SQLite IS the Right Choice

Where it shines

Code

External links

Exercise

Progress

Comments 0