Test iOS SQLite WAL crash recovery on a cloud Mac

An iOS app stores offline drafts in SQLite. A user sees “Saved,” but the process crashes on the next write. Will the draft still be there after relaunch? On a cloud Mac with an isolated simulator, you can turn that question into a repeatable experiment: commit a baseline row, deliberately crash while a second transaction is still open, then inspect the database directly. This tests the transaction boundary, not build speed. Being able to reopen the app is no substitute for checking its data.

Define the expected result first

Create a test table with just two columns, id and phase. Row 1|committed must be committed before the crash. Row 2|pending must be written in a new transaction but never committed. After the crash, the integrity check should return ok, and the query should return only the first row. If the second row appears too, first check whether the crash occurred after the commit. If the first row is missing, check whether it was actually written to the same database file.

WAL does not promise that “no writes will ever be lost.” This test asks only whether a successfully committed transaction remains readable after this process crash and whether an uncommitted transaction stays out of the saved data. It does not replace tests for power loss, storage damage, or application-level synchronization.

Use a disposable simulator and a test bundle ID. Fix the Xcode version and simulator runtime in advance, and record xcodebuild -version and the simulator identifier; use the same conditions for every run. Do not inject faults into your personal development simulator or a real user database.

Inject a crash at the transaction boundary

Keep the crash hook in debug builds only and require an explicit launch argument so ordinary runs cannot trigger it accidentally. The function below assumes the caller has already opened Documents/Recovery.sqlite and that every SQL call checks its return value. In this example, precondition makes a setup failure immediately visible rather than allowing a misleading query result later.

#if DEBUG
import Darwin
import SQLite3

func runRecoveryProbe(_ db: OpaquePointer) {
    func execute(_ sql: String) {
        precondition(sqlite3_exec(db, sql, nil, nil, nil) == SQLITE_OK)
    }

    execute("PRAGMA journal_mode=WAL")
    execute("CREATE TABLE IF NOT EXISTS probe (id INTEGER PRIMARY KEY, phase TEXT NOT NULL)")
    execute("INSERT OR REPLACE INTO probe VALUES (1, 'committed')")
    execute("BEGIN IMMEDIATE")
    execute("INSERT OR REPLACE INTO probe VALUES (2, 'pending')")
    abort()
}
#endif

Call it only when the launch arguments include a dedicated test flag. Before running the probe, confirm that it targets the experimental database and that no transaction is already open. Run PRAGMA journal_mode=WAL outside a transaction. abort() terminates the process abnormally, so there is deliberately no COMMIT after it. If the project uses a database abstraction, establish the same commit boundary through that abstraction. Do not bypass it and then claim to have validated the production write path.

Keep old records out of the test

Before each run, reset the test app’s data in the disposable simulator or install it on a newly created dedicated simulator. Do not merely run DELETE FROM probe while leaving other application tables and files behind. Before the first run, confirm that the database is actually inside the app’s data container. Record the database path, build version, and simulator identifier before and after injecting the crash, but do not copy real project data into the test container.

Inspect the database as it was left after the crash

Launch the app, then confirm that it terminated abnormally because of the intended fault before querying the database. A returned launch command alone does not prove the crash happened: the app might not have reached the test hook yet. Cross-check the process state in the simulator against its crash report. Treat any run without evidence of a crash as an incomplete test, not a pass.

Once the process has exited, use the cloud Mac’s terminal to read the app’s data container. Substitute the simulator identifier for this run and the test bundle ID:

UDID="your-simulator-udid"
BUNDLE_ID="com.example.RecoveryProbe"
DATA="$(xcrun simctl get_app_container "$UDID" "$BUNDLE_ID" data)"
sqlite3 "$DATA/Documents/Recovery.sqlite" \
  'PRAGMA integrity_check; SELECT id, phase FROM probe ORDER BY id;'

The expected output is ok followed by 1|committed. Do not delete or separately move the -wal and -shm files beside the database before checking it; let SQLite open the database as it stands. Do not launch an app process that might write again during the check. If the project has multiple databases, verify the filename and container path first so you do not mistake a query against the wrong file for a recovery failure.

Sort failures by likely cause

If the output includes 2|pending, check first for autocommit, a database abstraction that ended the transaction early, or a fault flag that took a different code path. If 1|committed is missing, verify that the first write succeeded and that the test used the same connection and file. If the query reports that the table does not exist, first confirm that the installed build includes the test hook and that the container path does not point to a simulator from a previous run.

integrity_check is not an application-level assertion: even if it returns ok, you still need to check the rows individually. Conversely, a crash report proves only that the process ended abnormally, not that transaction recovery worked. Save both kinds of evidence along with the Xcode version, simulator identifier, and test build identifier so you can compare conditions when reproducing a failure.

Preserve the test boundaries in CI

Give this test its own simulator. After injecting the fault and confirming that the process has exited, run a separate read-only check. Do not share the app’s data container with ordinary UI tests: concurrent writes would change the observed result. The crash hook must exist only in debug builds, not release builds, and the pipeline should check that explicitly.

Finally, run a control test without injecting a crash: commit the second row through the normal path and confirm that it can be read back. This verifies both that the uncommitted write was rolled back in the crash test and that the test itself does not block all writes. Together, those two results turn a one-off observation after relaunch into a reproducible durability check.

Frequently asked questions