Next steps: choosing SQLite vs a client-server database
A workload decision guide, hybrid patterns that use both, the operational differences that matter, and a reading path forward.
The decision guide
| Situation | SQLite | Client-server |
|---|---|---|
| One process, one machine | Ideal | Unnecessary round trip and operations |
| Read-heavy, local data | Ideal - no network at all | Fine, but you gained nothing |
| Many concurrent writers | Serialised through one writer | Designed for it |
| Application on many servers | Each server has its own file, so no shared state | The point of a server database |
| Analytics on a large dataset | Columnar engine is better | Consider a warehouse, not the OLTP database |
| Embedded in a mobile or desktop app | The only sensible option | Cannot be embedded |
| Needs network-level access control | File permissions only | Roles, grants, row-level security |
| Strict durability with replication | Possible but you own it | Built in |
| Zero operations budget | Excellent | Managed services cost money and still need care |
- Most applications never exceed what a single SQLite file can serve. The bottleneck is usually the application architecture, not the engine.
- The strongest argument for a server database is concurrency and shared access, not raw speed. SQLite is fast; it is simply one writer.
- The strongest argument for SQLite is deployment: no connection strings, no credentials, no network failure mode, and a backup that is a file copy.
💡
The relevant question is not "how much data" but "how many writers, from how many processes, on how many machines". One process and one machine with fast local storage is SQLite's home ground, and it is a large home ground.
Hybrid patterns
- Per-user local database for offline work, with an explicit sync to a server database. The local file stays simple; the server owns identity and conflicts.
- SQLite for the read model of a single service instance, refreshed from the source of truth. Fast local reads, and a clear rule for when the copy is stale.
- SQLite in tests, the server database in production, with one schema definition shared between them. Cheap, but only if you test the production engine somewhere - otherwise dialect differences appear in production.
- SQLite as the application cache in front of a remote database, with a TTL and an explicit invalidation on write.
- DuckDB or a columnar engine beside SQLite when the same data is both transactional and analytical. Copy in bulk rather than querying the OLTP file.
-- if you share one schema across two engines, keep SQL within the common subset
-- and test the parts that differ, in CI, against both
-- differing areas to watch: date functions, upsert syntax,
-- type affinity, string concatenation, and identifier quotingWhere to read next
- Read the official documentation on the WAL mode and on the atomic commit, because they explain every concurrency behaviour people find surprising.
EXPLAIN QUERY PLANfor the runtime, plus the query planner documentation, which describes exactly how the engine chooses an index.- The pragma reference is short and worth reading in full: most tuning is a handful of pragmas applied consistently.
- Read the source of one good driver for your language. Understanding its transaction handling, statement caching and threading model prevents most bugs.
- Practise a restore: copy the file plus the WAL sidecar to a clean directory, verify it with
integrity_check, and confirm the application opens it. - Build something small end to end - a CLI, a desktop app, a WASM page - and you will have met every constraint described in this course in a single afternoon.
Fluency with SQLite is mostly knowing which of its constraints are fundamental (a single writer, file-based locking, no network access control) and which are configuration (journal mode, synchronous, cache size, busy timeout). The first set decides your architecture; the second set is an afternoon of tuning.
FAQ
Is SQLite production ready?
Yes, and it is embedded in more devices than any other database. It is also a poor fit for a multi-server application that needs shared writable state. Judge it against the workload, not against a reputation.
When should I migrate off SQLite?
When you have several processes on several machines that all need to write, when you need network-accessible access control, or when writes are consistently waiting on the single writer. Migrate for a specific measured limit, not in anticipation of one.
Related
LiteFS, libSQL and replication for serverless apps WAL mode, backups, and when not to use SQLite
Last refreshed 2026-09-18.