SQLite
SQLite is a relational database engine that runs inside your application process and stores an entire database — tables, indexes, and schema — in a single file. There's no server to install, no network protocol, and no configuration. It's the most widely deployed database in the world: it ships in every iOS and Android device, every major browser, macOS and Windows, countless desktop apps, cars, and embedded systems.
For years SQLite was pigeonholed as a "toy" or test database. That's changed. With write-ahead logging, fast modern hardware, and tools that replicate and back it up continuously, SQLite now powers production web applications, edge databases, and local-first apps. Understanding its concurrency model tells you when it's a great fit and when a client-server database like PostgreSQL is the better choice.
TL;DR
- SQLite is an embedded database: a library plus one file, with zero configuration.
- Enable WAL mode for concurrent readers alongside a writer, and set a busy timeout.
- SQLite allows many readers but one writer at a time — design around short write transactions.
- Columns use type affinity; use
STRICTtables for enforced types. - It supports transactions, foreign keys (enable them!), window functions, JSON, and FTS5 full-text search.
- For servers, add continuous backup/replication (Litestream, LiteFS) or use an edge-hosted variant (Turso, Cloudflare D1).
Quick Example
Recommended pragmas for an application database, a STRICT table, and a query using JSON:
Using it from Node.js with better-sqlite3, batching writes in a transaction:
Core Concepts
Embedded Architecture
SQLite is a C library your program links against. Queries are function calls, not network round trips, so small queries take microseconds — the "N+1 query problem" matters far less than with a remote database. The database is a single cross-platform file you can copy, email, or check into test fixtures.
Concurrency and Locking
In WAL mode, writes append to a -wal file and readers see the last committed snapshot. Only one write transaction can proceed at a time; others wait (up to busy_timeout) or get SQLITE_BUSY. Keep write transactions short, and use BEGIN IMMEDIATE for transactions that will write, to avoid lock-upgrade deadlocks.
Types and Affinity
SQLite uses dynamic typing with type affinity: a column declared INTEGER prefers integers but will store a string if given one. STRICT tables (3.37+) enforce declared types (INTEGER, REAL, TEXT, BLOB, ANY). There's no native date type — store ISO 8601 text or Unix timestamps.
Features Worth Knowing
- Transactions and ACID guarantees, even across power loss.
- Window functions, CTEs, upserts (
ON CONFLICT DO UPDATE), andRETURNING. - JSON functions (
json_extract,->,->>) and JSONB storage. - FTS5 full-text search with ranking.
- Generated columns and partial indexes.
- R-tree for spatial queries; extensions like
sqlite-vecfor vector search.
SQLite in Production and at the Edge
Best Practices
Always Set the Pragmas
WAL mode, busy_timeout, and foreign_keys = ON fix most "database is locked" errors and silent integrity issues. Set them on every connection (except journal_mode, which persists).
Batch Writes in Transactions
Each standalone insert is its own transaction with an fsync. Wrapping many inserts in one transaction can be 100× faster.
Use One Writer Path
Route writes through a single connection or queue in your application, and let many connections read.
Back Up Safely
Don't copy a live database file with cp. Use the backup API, VACUUM INTO 'backup.db', or Litestream.
Run ANALYZE and PRAGMA optimize
Keep query planner statistics current, especially after bulk loads.
Keep the Database on Local Disk
Network filesystems (NFS, SMB) can break SQLite's locking and corrupt databases.
Common Mistakes
Leaving the Default Rollback Journal
Without WAL, readers block during writes and throughput collapses under concurrency.
Assuming Foreign Keys Are Enforced
They're off by default. Enable PRAGMA foreign_keys = ON on each connection.
Long-Running Write Transactions
Holding the write lock while doing network calls or heavy computation blocks every other writer.
Using SQLite for High Write Concurrency Across Many Servers
Many application servers writing to one database over a network is exactly what client-server databases are designed for.
Relying on Type Declarations
Without STRICT, a TEXT value can end up in an INTEGER column. Validate or use strict tables.
Comparison
FAQ
What is SQLite used for?
Local storage in mobile and desktop apps, browsers, embedded devices, application file formats, caches, testing, data analysis, and increasingly single-server and edge web applications.
Can SQLite handle production web traffic?
Yes, for many workloads. With WAL mode, a single server can handle thousands of reads and hundreds to thousands of writes per second. It struggles when many servers need to write concurrently.
Why do I get "database is locked" errors?
Another connection holds the write lock. Enable WAL mode, set a busy_timeout, keep write transactions short, and use BEGIN IMMEDIATE for write transactions.
Is SQLite a good choice for mobile apps?
Yes. It's built into iOS and Android and underlies frameworks like Core Data, Room, and many cross-platform storage libraries.
How do I back up a SQLite database?
Use the online backup API, VACUUM INTO, or continuous replication with Litestream. Avoid copying the file while it's being written.
Related Topics
- PostgreSQL — The client-server alternative for multi-writer workloads
- SQL — The query language SQLite implements
- Local-First Software — Apps built around an on-device database
- Mobile Development — Where SQLite is everywhere
- Edge Computing — SQLite-based databases near users