How do you detect a damaged database or file, and what do you do about it?

13.11.2 · L3 · Interview

Predict first

What does integrity_check report before and after damage?

import Foundation
import CryptoKit
import SQLite3

let dir = FileManager.default.temporaryDirectory.appendingPathComponent(UUID().uuidString)
try FileManager.default.createDirectory(at: dir, withIntermediateDirectories: true)
let path = dir.appendingPathComponent("store.db").path
var h: OpaquePointer?
sqlite3_open(path, &h)
sqlite3_exec(h, "CREATE TABLE t(id INTEGER PRIMARY KEY, v TEXT); CREATE INDEX i ON t(v);", nil, nil, nil)
for i in 1...2000 { sqlite3_exec(h, "INSERT INTO t(v) VALUES('value-\(i)')", nil, nil, nil) }
sqlite3_close(h)

func check(_ path: String) -> String {
    var db: OpaquePointer?
    guard sqlite3_open(path, &db) == SQLITE_OK else { return "open failed" }
    defer { sqlite3_close(db) }
    var st: OpaquePointer?
    guard sqlite3_prepare_v2(db, "PRAGMA integrity_check", -1, &st, nil) == SQLITE_OK else { return "unreadable" }
    defer { sqlite3_finalize(st) }
    return sqlite3_step(st) == SQLITE_ROW ? String(cString: sqlite3_column_text(st, 0)) : "no rows"
}
print("healthy:", check(path))
var data = try Data(contentsOf: URL(fileURLWithPath: path))
let before = SHA256.hash(data: data)
for i in 0..<64 { data[8192 + i * 3] ^= 0xFF }
try data.write(to: URL(fileURLWithPath: path))
print("checksum changed:", before != SHA256.hash(data: data))
print("integrity_check after damage reports ok:", check(path) == "ok")

healthy: ok / checksum changed: true / integrity_check after damage reports ok: false
healthy: ok / … reports ok: true
healthy: not ok

Mental model. A tamper seal on a medicine bottle: you cannot prevent every accident, but you can tell at once when something is wrong, and you keep a spare bottle for when it is.

Corruption is rare but real (a full disk, a bad write, a failing storage controller, a copy of a WAL database without its log, a bug). Detect it at three levels. For SQLite, PRAGMA integrity_check verifies the structure of every table and index and returns a single row “ok” for a healthy database; in the check it returns “ok” first, and after flipping bytes inside a data page it returns error descriptions (“*** in database main *** Tree … page …”) instead. It reads the whole file, so run it on demand (after a crash report pointing at the store, in a diagnostic tool, or during a migration), not on every launch; PRAGMA quick_check is cheaper and skips some checks. For your own files (downloads, exported documents, caches), store a checksum (SHA-256) next to the data and compare after reading: the check shows the digest changes when the content changes. For structured files include a version and a length so truncation is detectable.

The response matters more than the detection. Keep a last known good copy: take a backup with SQLite’s online backup API (or VACUUM INTO) before risky migrations, and keep the previous file until the new one verifies. On failure, do not crash-loop: quarantine the bad file (rename it, keep it for diagnostics), try to recover what you can (.recover in the SQLite shell, or re-reading rows that still parse), then rebuild from the server or an iCloud copy and tell the user honestly what was lost. Prevent the common causes: atomic replacement for files (Q339), a single process writing the database, never copying a WAL database as one file, enough free space checks before big writes, and fsync-level durability only where you need it (PRAGMA synchronous). Record corruption events with a counter in analytics: a spike usually points at a bug or a device cohort.

A damaged page makes integrity_check return error rows, and the file checksum changes.

Common traps

Running integrity_check on every launch. It reads the entire database; use it on demand.
Deleting a damaged store immediately. Quarantine it first; you may be able to recover data and you will need it to diagnose the cause.

They might follow up with

How do I make a safe copy of a live SQLite database? The backup API or VACUUM INTO, not a file copy of an open WAL database.
What does PRAGMA synchronous trade? Durability against speed; FULL survives power loss, NORMAL with WAL is a common balance.

How do you detect a damaged database or file, and what do you do about it?

Predict first

Common traps

They might follow up with

Related questions