SQLite guide

What SQLite is

SQLite is a self-contained, serverless SQL database engine. There is no separate server process — the application library reads and writes a single database file (e.g. app.db). The sqlite3 command-line tool is the standard way to inspect and administer databases on Linux servers.

Good use cases

Embedded and edge — mobile apps, IoT devices, single-node services
Development and testing — zero-config local database
Read-heavy caches — config stores, session data with modest write rates
Data analysis — portable datasets, ETL staging, ad-hoc SQL on files
Low-traffic web apps — small deployments where a full RDBMS is overhead

When to be cautious

SQLite is not a replacement for PostgreSQL or MySQL when you need many concurrent writers, network access from many hosts, or fine-grained user permissions. Multiple processes or threads writing at once can hit database is locked errors. WAL mode (Write-Ahead Logging) lets readers proceed during writes and reduces lock contention, but there is still only one writer at a time. For multi-writer production workloads, use a client/server database or ensure writes are serialized (queue, single worker).

How access works

Open — application opens the database file with the SQLite library
Lock — SQLite acquires filesystem locks (POSIX advisory locks) for reads/writes
Execute — SQL runs in the library; changes go to the journal or WAL file
Commit — transaction commits; WAL checkpoint may move data to the main file

Sidecar files matter: app.db-wal and app.db-shm exist in WAL mode — back up or copy all three files together, or use the backup API.

Production PRAGMA settings

Set these at connection time (or in sqlite3 init) for solid production behavior:

PRAGMA journal_mode = WAL;        -- better read/write concurrency
PRAGMA synchronous = NORMAL;      -- safe with WAL; use FULL for max durability
PRAGMA foreign_keys = ON;         -- enforce referential integrity
PRAGMA busy_timeout = 5000;       -- wait ms before "database is locked"
PRAGMA cache_size = -64000;       -- ~64 MB page cache (negative = KiB)
PRAGMA temp_store = MEMORY;       -- temp tables in RAM
PRAGMA mmap_size = 268435456;     -- 256 MB memory-mapped I/O (optional)

Use synchronous=FULL if you cannot afford any window of data loss on power failure (slower writes). Run PRAGMA integrity_check; periodically and after unclean shutdowns.

Backups

Do not copy the .db file while the app is writing — you may get a inconsistent snapshot. Safe methods:

Online backup API — .backup backup.db in the sqlite3 CLI, or backup() in application code
VACUUM INTO 'backup.db' — consistent copy to a new file (SQLite 3.27+)
Stop the app — then copy .db + -wal + -shm together
Litestream — streams SQLite changes to object storage (S3, etc.) for continuous replication and point-in-time restore; ideal for single-node SQLite in production

Learning resources

SQLite documentation — sqlite.org/docs
WAL mode — sqlite.org/wal
Backup API — sqlite.org/backup
Litestream — litestream.io (continuous SQLite replication)
Appropriate uses — sqlite.org/whentouse

Practice scenarios

Hands-on SQLite scenarios on live Linux VMs: sqlite

Troubleshooting →