When SQLite is the right database

What it is

SQLite stores a whole database in one file and runs inside the application process; there is no server, no configuration and no network. The project's own guidance page lists suitable uses (embedded devices, application file formats, websites with moderate traffic, data analysis, caches, testing) and situations where a client-server database fits better (many concurrent writers, access over a network file system, very high write volume, terabyte-scale data).

Why it matters

Choosing a client-server database by default adds an operational component that small services do not need; choosing SQLite by default fails when several hosts must write concurrently. The decision follows from deployment shape, not fashion.

How to apply

• Enable write-ahead logging (PRAGMA journal_mode=WAL) for concurrent readers with one writer; set a busy timeout so writers wait instead of failing immediately.
• Keep the file on a local disk, not a network share; back up with the online backup API or VACUUM INTO, never by copying a live file without WAL checkpointing.
• Enforce foreign keys explicitly (PRAGMA foreign_keys=ON) and be aware of flexible typing unless STRICT tables are used.
• Plan the migration path: if a second writer host appears, move to PostgreSQL rather than adding locks around the file.

Pitfalls

Assuming SQLite is "not a real database" for production. Long-running write transactions blocking all other writers. Using it behind several application replicas with a shared volume.