SQLite in Production
debt(d7/e5/b7/t7)
Closest to 'only careful code review or runtime testing' (d7). The detection_hints.tools field is not specified, and there are no standard linters or SAST tools that flag SQLite misconfigurations (missing WAL mode, bad filesystem placement, multi-server deployments). Problems like SQLITE_BUSY errors or NFS-induced corruption typically only surface under concurrent load in production — they won't appear in unit tests or static analysis.
Closest to 'touches multiple files / significant refactor in one component' (e5). The quick_fix (PRAGMA journal_mode=WAL) is a one-liner for WAL mode, but the common_mistakes reveal multiple independent problems: fixing filesystem placement, adding busy_timeout, and — critically — if SQLite is deployed across multiple app servers, migrating to a networked database is an architectural change spanning infrastructure, connection config, migrations, and deployment scripts.
Closest to 'strong gravitational pull' (b7). The database choice shapes every future infrastructure decision: scaling strategy (single-server constraint), backup procedures (file copy vs. dump), deployment topology (no multi-server), ORM/query layer configuration, and DevOps tooling. Every new feature or scaling discussion is filtered through 'does this work with SQLite?' — the choice has strong reach across the system's operational characteristics.
Closest to 'serious trap (contradicts how a similar concept works elsewhere)' (t7). The canonical misconception is exactly cited: developers assume SQLite is dev/test-only and avoid it in production, OR conversely assume it scales like MySQL and use it in write-heavy multi-server contexts. Both failure modes are common, and the actual production constraint (concurrent writes, not read volume; single server, not network) contradicts intuitions formed from traditional client-server databases.
Also Known As
TL;DR
Explanation
SQLite is the most deployed database in the world — it powers every Android and iOS app, every browser, and most desktop software. For web applications, SQLite excels when: the application runs on a single server, writes are infrequent relative to reads, or you are using an edge/embedded deployment (Cloudflare D1, Fly.io LiteFS). SQLite uses WAL (Write-Ahead Log) mode for concurrent reads with one writer at a time. In PHP, it is accessed via the PDO SQLite driver or the native SQLite3 class. Limitations: no network access (file must be local), write operations serialize (only one writer at a time), and replication requires third-party tools (Litestream, LiteFS). For high-concurrency write workloads, PostgreSQL or MySQL remains the better choice.
Common Misconception
Why It Matters
Common Mistakes
- Not enabling WAL mode — the default rollback journal causes readers to block during writes; WAL mode is essential for concurrent PHP requests.
- Storing SQLite files in /tmp or network filesystems — SQLite requires a local filesystem with proper file locking; NFS and some Docker volume mounts break SQLite.
- Not setting busy_timeout — without it, concurrent write attempts immediately throw 'SQLITE_BUSY'; set a timeout so requests retry briefly.
- Using SQLite on multiple application servers — SQLite has no network protocol; it only works when all application code runs on the same machine as the file.
Code Examples
<?php
// ❌ SQLite without WAL mode — readers block on writes
$db = new PDO('sqlite:/var/db/app.db');
// Default journal mode causes readers to wait during writes
// No busy timeout — concurrent access throws 'database is locked'
$db->query('SELECT * FROM users WHERE id = 1');<?php
// ✅ SQLite with WAL mode and busy timeout
$db = new PDO('sqlite:/var/db/app.db');
$db->exec('PRAGMA journal_mode=WAL'); // Concurrent reads during writes
$db->exec('PRAGMA synchronous=NORMAL'); // Balance durability vs speed
$db->exec('PRAGMA busy_timeout=5000'); // Wait 5s before 'database locked' error
$db->exec('PRAGMA foreign_keys=ON'); // Enforce FK constraints (off by default)
$db->exec('PRAGMA cache_size=-64000'); // 64MB page cache
// Backup is a file copy — use Litestream for continuous replication
// litestream replicate /var/db/app.db s3://bucket/app.db
// Check if WAL mode is active
$mode = $db->query('PRAGMA journal_mode')->fetchColumn();
echo $mode; // 'wal'