Using Embedded Database

Overview

An embedded database is not a database with the network removed. It is a different discipline — the application owns concurrency, the process owns the lock, and the file owns the durability contract.

Server databases (Postgres, MySQL) separate concerns across process boundaries: a dedicated server process serialises writes, mediates locks, and answers client connections from an independent runtime. When that runtime dies, a supervising process restarts it. When a client misbehaves, the server can forcibly disconnect it. The durability contract runs in a process the application does not own.

Embedded databases invert every one of these properties. The application is the lock manager. The application is the crash recovery agent. The write serialiser is whatever the application does before calling sqlite3_exec. When the application dies mid-write, the database file is what the OS left behind — and the database's own WAL or journal is the only recovery path. There is no separate supervisor; the application and the durability contract live in the same address space.

SQLite and DuckDB fail differently from each other, and both fail differently from server databases. SQLite is single-writer at the file level: WAL mode improves read concurrency but one writer still holds the write lock; a second writer sees SQLITE_BUSY and must retry or fail. DuckDB is column-oriented and optimised for analytical scans; it does not belong in a multi-process OLTP write path. Using either one outside its design envelope produces failure modes that a "just use a database" mental model does not predict.

This pack addresses five failure modes that recur in embedded-database production deployments:

1. PRAGMA mis-configuration — SQLite ships with safe-but-slow defaults (journal_mode=DELETE, synchronous=FULL, cache_size sized for 1985). Applications that never touch PRAGMAs are paying a performance tax in exchange for defaults they didn't choose.
2. Schema-migration mis-design — ad-hoc CREATE TABLE IF NOT EXISTS at startup, unversioned ALTER TABLE, no migration runner, no rollback path.
3. Transaction-isolation mis-choice — BEGIN used interchangeably with BEGIN IMMEDIATE and BEGIN EXCLUSIVE; deferred transactions that collide at first write; SERIALIZABLE isolation assumed where SNAPSHOT semantics apply.
4. Concurrent-write mis-coordination — multi-process writers without WAL, NFS mounts, advisory lock ceremonies that are skipped under load, optimistic updates with no version check.
5. Encryption mis-attribution — SQLCipher added to satisfy a compliance checkbox, keyed with a static string in source, with no analysis of what threat model it actually closes.