When SQLite Is (and Isn't) the Right Choice

This is this course's own central chapter — the point where six chapters of architecture, type behavior, concurrency, transactions, and real-world deployment stop being separate facts and become one honest, usable decision framework.

Revisiting This Course's Own Throughline

sqlite1-1 opened this course by stating that SQLite isn't "MySQL but weaker" — it solves a different problem, and the real question is whether an application needs a server at all. This chapter turns that abstract claim into an actual, practical framework: does this specific application need a server, and if a server-side deployment is being considered, does it fit the single-server-plus-WAL shape sqlite1-6 described?

When SQLite Is Not the Right Choice

• High write concurrency across multiple separate servers — the single clearest, least-debatable case. sqlite1-4's own single-writer-at-a-time limitation becomes a genuine, hard architectural wall the moment more than one physically separate machine needs to write concurrently.
• Database-level, per-user access control — if different application users genuinely need different database-level permissions, not just application-level authorization, SQLite offers nothing comparable to mysql1's or postgres1-2's own role systems (previewed further in sqlite1-9).
• Very large, multi-terabyte analytical workloads across a distributed architecture — not SQLite's own design center at all.
• Real-time, multi-node replication for failover — SQLite has no native equivalent to postgres1-11's own streaming/logical replication; third-party extensions exist, but this isn't SQLite's own built-in strength.

When SQLite Genuinely Is the Right Choice

• Mobile, desktop, and embedded applications — sqlite1-6's own strongest, essentially uncontested case.
• CLI tools and small utilities needing structured local storage.
• Testing — sqlite1-2's own in-memory database material is a genuinely excellent fit.
• Local-first / offline-capable applications — data needs to work with zero network connection at all, a strength no client-server database can match by definition.
• Single-server, low-to-moderate-traffic web applications — sqlite1-6's own real, growing production pattern: read-heavy or moderately write-heavy workloads, one deployment target, and genuine value placed on operational simplicity over theoretical headroom that will likely never actually be needed.
• Prototyping and early-stage projects where migrating to MySQL/Postgres later remains a realistic, deliberately-deferred option — start simple, migrate only if genuine multi-server needs actually materialize, not preemptively.

A Practical Decision Framework

1. Will more than one physically separate server ever need to write to this data? If genuinely yes — not SQLite.
2. Does this need database-level, per-user access control? If genuinely yes — not SQLite.
3. Is this embedded in a single application, or does it need to serve many independent networked clients? Embedded/single application — a strong SQLite fit.
4. Is operational simplicity worth more than headroom for hypothetical future scale that may never materialize? Yes — SQLite is a legitimate, deliberate choice, not a compromise.

The Honest Middle Ground

Some cases are genuinely ambiguous, and reasonable people disagree. This chapter's own goal isn't a rigid flowchart that removes judgment — it's making sure that judgment is informed by the real trade-offs from sqlite1-1, sqlite1-4, sqlite1-5, and sqlite1-6, rather than either reflexive dismissal ("SQLite is a toy") or reflexive overclaiming ("SQLite can replace any database").

Hands-On Exercises