Siren Call of SQLite on the Server

Edge and distributed SQLite

• Several comments connect server-side SQLite (Fly, Cloudflare, Turso, LiteFS) to “edge compute”: colocating DB and compute to reduce latency.
• This is seen as especially attractive for read-heavy or mostly-static datasets that can be “file-copied” to many edge locations.
• Others argue that existing distributed SQL systems (e.g. Cockroach) or traditional client–server DBs could serve similar purposes in practice, with fewer bespoke trade-offs.
• There’s interest but also caution: distributed SQLite is still seen as young, with tricky semantics around reads/writes and high availability.

When SQLite on the server makes sense

• Advocates highlight: no network hop, very low latency on NVMe, tiny operational footprint, easy backups via file copy or tools like Litestream, gobackup, sqlite_rsync, and good fit for small/medium apps or on-prem tools.
• Multiple production anecdotes: SQLite + Litestream + simple web servers running for years with minimal downtime; low-cost self-hosting after leaving managed cloud databases; hermetic unit tests.
• Detractors counter that network latency to a colocated Postgres/MySQL instance is negligible and the operational overhead of a DB server is small; starting with Postgres avoids painful migration when scaling beyond a single webserver.

Per-user / per-tenant database pattern

• A major pro-SQLite theme is “datastore-per-user/tenant”: one SQLite DB per user/org, plus a small global metadata DB.
• Claimed benefits: easy sharding and horizontal scaling, strong isolation (no accidental cross-user leaks), simpler reasoning, and backups via whole-file copies.
• Critics question global constraints (e.g., unique email), cross-DB atomicity, and the need to implement consistency and messaging in the application. Responses suggest: immutable user IDs, global indexes for identifiers, eventual consistency, and careful partition boundaries.

Technical drawbacks and concerns

• Concurrency: default locking model, single writer, and multi-process access require care; WAL mode and busy_timeout mitigate but don’t remove limits.
• Schema migrations: SQLite’s ALTER TABLE is weaker because it stores raw SQL definitions; complex migrations can be fragile.
• Data integrity: default “dynamic typing” and disabled foreign keys worry some; strict tables and pragma settings help but must be explicitly enabled.
• Tooling ecosystem: mainstream ELT/BI tools rarely target SQLite directly; workarounds involve copying files or using other engines (e.g., DuckDB) for analytics.