← Back to glossary

SQLite in Production

database PHP 5.1+ Intermediate

Also Known As

SQLite SQLite PHP SQLite production file database

TL;DR

SQLite is a serverless, file-based SQL database that is appropriate for production use in single-server applications, edge deployments, and embedded systems — with specific limitations around concurrent writes that make it unsuitable for multi-server setups.

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

✗ SQLite is only for development and testing. SQLite handles millions of reads per day easily and is used in production by companies like Expensify and Tailscale. The constraint is concurrent writes — one at a time — not read volume.

Why It Matters

Many PHP applications that use MySQL could be simpler and faster on SQLite — no separate database server, no connection pooling needed, backups are a file copy. Understanding SQLite's production characteristics helps you choose it confidently for single-server apps and avoid it correctly for multi-server or write-heavy workloads.

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

✗ Vulnerable

<?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');

✓ Fixed

<?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'

SQLite in Production

Also Known As

TL;DR

Explanation

Common Misconception

Why It Matters

Common Mistakes

Code Examples

References

Tags

SQLite in Production

Also Known As

TL;DR

Explanation

Common Misconception

Why It Matters

Common Mistakes

Code Examples

References

Tags

Related Terms