SQLite
Master SQLite: embedded database design, optimization, mobile apps, and local storage patterns.
What is SQLite?
SQLite is an embedded relational database engine. The application links the SQLite library and reads or writes a database file directly instead of sending SQL over a network to a separate database server. Tables, indexes, views, triggers, and schema metadata live in a portable file format, with journal or write-ahead-log files used as needed for transaction safety.
SQLite is a strong fit when one application or device owns local relational state: mobile and desktop applications, embedded devices, local caches, application file formats, test fixtures, and service-side stores whose write access is deliberately serialized. It is a poor fit when many machines need to write one database file directly or when database administration must be centralized across independent clients.
Core invariant
The database file has one serialized writer at a time. WAL mode allows readers to overlap a writer, but it does not turn one file into a multi-writer distributed database.
No server hop
Embedded engine
SQL executes inside the application process. Deployment and failure ownership stay with that application rather than a separate database service.
Portable state
Database file
The main file contains database pages in a stable format. Journal, WAL, and shared memory companions are part of safe operation, not disposable noise.
Transactions
Pager
Coordinates page caching, locking, atomic commit, rollback, and durability through the operating-system storage interface.
Concurrency boundary
Application owner
The application controls connection count, transaction length, busy handling, checkpoints, backup, schema migration, and access to the file.
Budget the single-writer path
The key capacity question is not a universal reads-per-second benchmark. It is how much serialized writer time the workload requires. If 100 transactions arrive each second and each holds the writer for 8 ms, the model already demands 800 ms of writer time per second before bursts, checkpoints, or storage variance.
Keep the write lock short and the ownership boundary local
SQLite serializes writes to one database file. Change arrival rate and transaction duration to see how quickly one writer becomes the limiting resource.
Workload shape
Write-path verdict
The modeled writer has headroom
8 writes per second holding the writer for 12 ms require 96 ms of writer time each second. Keep transactions short and measure lock wait under representative bursts.
10%
required serialized writer time
83/s
1,000 ms divided by lock duration, before overhead
4
Readers can continue from a stable WAL snapshot while one writer appends.
1000 ms
bounded contention wait
Reduce writer pressure by grouping related statements into one short transaction, preparing work before BEGIN, indexing the predicates used by updates, avoiding network calls while a transaction is open, and funneling writes through a bounded owner. A longer busy timeout can absorb short contention; it cannot create capacity.
Choose rollback journal or WAL from failure behavior
Rollback mode preserves original pages in a journal before the main database changes. WAL mode appends changed pages to a log and later checkpoints them into the database. Select scenarios to trace which files and locks participate.
WAL usually improves reader/writer overlap because readers use a stable snapshot while one writer appends. Important constraints remain:
- only one writer appends at a time;
- a long-running reader can prevent a checkpoint from completing;
- the WAL and shared-memory state must be managed with the database;
- all processes must be on the same host, so WAL is not a design for multiple machines writing a database on a network filesystem;
- durability and latency depend on synchronous settings and the storage system's behavior.
The official WAL documentation describes concurrency, checkpointing, and operational limitations.
Put SQLite behind the right ownership boundary
Excellent fit
Local application state
One installed application owns the file and needs transactions, offline access, searchable data, and schema evolution without a database server.
Deliberate fit
Application-specific service
One service process accepts network requests, owns one or more local database files, and serializes writes. Clients never mount or open the files directly.
Use client/server
Shared enterprise repository
Independent users or machines need concurrent writes, centralized access control, replication, remote administration, or horizontal database scaling.
SQLite's own appropriate-use guide frames this as a difference in problem ownership: SQLite provides local storage for an application or device, while client/server engines emphasize a shared, centralized repository.
Open, migrate, transact, and back up explicitly
1 Connection
Open with policy
Set a bounded busy timeout, enable foreign-key enforcement for each connection, and choose journal and synchronous modes deliberately rather than assuming defaults.
2 Schema
Migrate atomically
Read
PRAGMA user_version, apply ordered migrations inside transactions where the operations permit it, and test upgrade and rollback behavior on real database copies.3 Transaction
Keep writes short
Prepare input before acquiring write intent, use parameterized SQL, commit related changes together, and release the writer before calling another service.
4 Operations
Verify recovery
Use SQLite's backup API or a documented consistent-copy procedure, restore into a new location, run integrity checks, and exercise the application against the result.
Test the states that corrupt assumptions
Correctness and recovery
- terminate the process during a write and verify committed versus rolled-back state;
- restore a backup and run
PRAGMA integrity_checkbefore declaring success; - enable and test foreign keys on every connection;
- exercise schema migration from every supported application version;
- test disk-full, read-only, permission, and unavailable-directory errors.
Contention and operations
- measure writer lock duration and
SQLITE_BUSYoutcomes under representative bursts; - detect long readers and WAL growth;
- choose an explicit checkpoint policy for sustained WAL workloads;
- keep the database,
-wal, and-shmfiles under the same ownership and lifecycle; - avoid copying a live database with a generic file copy unless the documented mode makes that copy consistent;
- do not place a write-active database on a filesystem whose locking semantics SQLite cannot rely on.
Use prepared statements for values, indexes for measured query paths, and transactions for related changes. Run ANALYZE or PRAGMA optimize according to the deployed SQLite version and workload evidence rather than scheduling indiscriminate maintenance.