SQLite
SQLite is a database without a database server — the entire database is a single file, and the engine is a library linked into your app. It's the most widely deployed database in the world, running in browsers, phones, and countless desktop apps, prized for being tiny, fast, and exceptionally reliable.
It shines wherever you want SQL without operating a server: mobile and desktop apps, edge devices, CLI tools, and test suites. Its one real limit is write concurrency — it's not built to be a multi-user server database.
TL;DR
- The whole database is one file; no server to run.
- Full SQL with ACID transactions; enable WAL mode for better concurrency.
- Ideal for mobile, desktop, edge, CLIs, and tests.
- High write concurrency across processes is its weak spot.
Quick Example
Turn on WAL for better concurrency, then use plain SQL — that's the whole setup:
Core Concepts
- The file is the database — the library reads and writes it directly.
- ACID transactions — full transactional guarantees despite being embedded.
- WAL (write-ahead logging) — lets readers proceed while a write is in progress.
- Serverless — no process to install, configure, or secure.
Common Uses
- Mobile app local storage (iOS/Android ship with SQLite).
- Desktop apps and CLI tools.
- Edge devices and offline-first apps.
- Development, testing, and small low-write services.
Best Practices
- Enable WAL mode for read/write concurrency.
- Wrap batches of writes in a transaction (huge speedup).
- Add indexes for common queries, just like any SQL database.
- Back up by safely copying the file (or use the backup API /
VACUUM INTO).
Comparison: SQLite vs PostgreSQL
See PostgreSQL.
Common Mistakes
Treating SQLite as a multi-user server
Hosting the file on a network filesystem
FAQ
When should I use SQLite?
When you want SQL without running a server: mobile/desktop apps, edge devices, CLI tools, test suites, and small services with modest write traffic. If you need many concurrent writers or networked multi-user access, choose a server database.
Can SQLite handle concurrent access?
Many concurrent readers, yes — and with WAL mode, readers don't block a writer. But only one writer at a time, so high write concurrency (especially across processes) is where it struggles.
Is SQLite suitable for production?
Absolutely, for the right workload — it's battle-tested and runs in billions of devices. It's production-grade for embedded, local, read-heavy, and low-write-concurrency use cases; it's just not a replacement for a multi-user server database.
How do I back up a SQLite database?
For a quiet database, copy the file. For a live one, use the online backup API or VACUUM INTO 'backup.db', which produces a consistent copy without stopping writes.
Related Topics
- PostgreSQL — When you outgrow embedded
- SQL Fundamentals — The query language SQLite speaks
- Mobile Development — SQLite on devices
- Databases — Choosing a data store