The default story used to be simple: prototype on files, then "graduate" to Postgres when you are serious. That story is fraying. SQLite is now a deliberate choice for production systems where complexity is the real risk — mobile apps, embedded config stores, local-first sync engines, and plenty of modest web backends.
Here is why it keeps climbing the hype curve without falling off.
One file, full SQL
No separate database process to patch at midnight. Backups are file copies with clear semantics. For teams shipping alone or in pairs, operational surface area matters as much as query features.
That does not mean SQLite solves everything. It means you stop paying costs you do not yet need.
When SQLite is an easy yes
- Single-tenant or small multi-tenant apps with moderate write concurrency
- Read-heavy dashboards and content sites
- Edge-friendly stacks that want durable state without a regional always-on cluster
- Local-first clients where sync layers handle conflict resolution elsewhere
When to hesitate
- Many writers hammering the same hot rows continuously
- Very large cross-region active-active requirements without a replication story you trust
- Compliance regimes that mandate separation-of-duties database admin patterns you cannot model
If you are there, you already know — benchmarks and architects, not blog opinions, should decide.
Practical habits that matter
- WAL mode and sensible busy timeouts for concurrent reads and writes
- Migrations as code — no ad-hoc ALTER in prod
- Tests against the same engine you run in production — mocks hide transaction semantics
Pairing with the rest of the stack
You can still expose HTTP APIs, use ORMs, and deploy behind serverless or containers. SQLite becomes your persistence layer, not your entire architecture. If you later need Postgres, migrating a well-modeled schema is annoying but finite; migrating spaghetti is not.
Bottom line
Choosing SQLite is not nostalgia. It is risk management: fewer moving parts until data proves you need more. For many apps, that day never comes — and users never notice the database brand anyway.