The SQLite Bug Your Test Suite Will Never Find: The Crash Between Two Writes

The key insight: SQLite itself is remarkably crash-safe at the storage layer — but your application logic almost certainly isn't, and no amount of "does the SQL run correctly" testing will catch that. The bugs that actually cause data loss in production live in the timing gap between two writes that should have been one atomic unit but weren't.

Here's the scenario that gets everyone: you write a migration or upgrade path that does two things — say, update a schema_version row, then backfill a new column across a table. Both statements are individually correct SQL. Your tests run them, check the result, and pass. But what happens if the OS kills the process, the battery dies, or the container gets OOM-killed between those two writes? Now you have a database that thinks it's on schema v2 but has none of the v2 data. Your app opens it next time, sees the version flag, skips the migration, and silently operates on corrupt assumptions forever.

This isn't a hypothetical edge case for edge services and local-first apps — it's the normal operating condition. Desktop apps get force-quit. Mobile apps get backgrounded and killed. Edge workers get evicted mid-request. If your durability story only works when the process shuts down cleanly, you don't have a durability story.

SQLite's own test suite actually builds this scenario intentionally: spawn a child process, have it write to the database, kill it at a random point mid-operation, then have the parent reopen the DB and verify integrity. That's the bar you should be testing against, not just "does my migration function return without throwing."

Why this bites people specifically with SQLite

SQLite guarantees atomicity per transaction — if you commit, you get all of it; if you crash before commit, you get none of it, and the next open will roll back the partial journal automatically. That part genuinely works. The bug is almost always architectural: developers split one logical operation into two or more transactions because it's easier to reason about, or because they're mixing a read-then-decide-then-write pattern that spans multiple BEGIN/COMMIT blocks.

1# BAD: two separate transactions, crash-vulnerable
2conn.execute("UPDATE meta SET version = 2")
3conn.commit()
4
5# ...crash could happen right here...
6
7conn.execute("ALTER TABLE users ADD COLUMN region TEXT")
8conn.executemany("UPDATE users SET region = ? WHERE id = ?", backfill_data)
9conn.commit()

1# GOOD: one atomic transaction for the whole logical unit
2conn.execute("BEGIN IMMEDIATE")
3try:
4    conn.execute("ALTER TABLE users ADD COLUMN region TEXT")
5    conn.executemany("UPDATE users SET region = ? WHERE id = ?", backfill_data)
6    conn.execute("UPDATE meta SET version = 2")
7    conn.commit()
8except Exception:
9    conn.rollback()
10    raise

Notice the BEGIN IMMEDIATE — this matters more than people realize. If you start a transaction with a read and only later discover you need to write, SQLite can hand you SQLITE_BUSY right at the write attempt, even with a busy timeout configured, because you've upgraded a read lock to a write lock mid-flight. If you already know a transaction will write, start it that way. It's a small habit that eliminates an entire category of lock-contention bugs layered on top of your crash-safety problem.

How to actually build the crash test

The pattern SQLite's own developers use translates well to application-level testing:

1. Run the upgrade/migration in a child process so you can kill it externally without corrupting your test harness.

2. Inject the kill at multiple points: right after the first write, right before commit, right after commit, mid-schema-change, and — if you're on WAL mode — during a checkpoint.

3. Reopen the database from the parent process after each kill and assert:

• PRAGMA integrity_check passes
• the schema is in a known, valid state (not half-migrated)
• application-level invariants hold (not just "the table exists" but "the data is coherent")

4. Run it many times. Timing bugs are timing-dependent by definition — one run passing tells you almost nothing. SQLite's own crash tests rely on repeated randomized crash points specifically because rare windows are rare.

If you're on WAL mode, don't skip this — WAL introduces its own recovery edge cases. SQLITE_BUSY_RECOVERY exists specifically because another process may be replaying the write-ahead log after a crash when you try to connect. If your test suite has never simulated a crash during a checkpoint, you don't actually know how your app behaves in that state, you're just hoping.

Why this matters: Correctness tests answer