Testing iOS SQLite online backup consistency on a cloud Mac

A client asks for an “Export database” feature in the production app, with no interruption to order entry. The development team tests it on a cloud Mac and confirms that the file downloads, only to find missing records after restoring it. The problem may not be the transfer: it may be that the live SQLite main database file was copied directly. Choose a consistent backup method first, then verify recovery. “Copy succeeded” is not an acceptance criterion.

Choose the backup API instead of copying live files

sqlite3_backup is suitable for online exports. It reads the source through a database connection, writes the contents to a destination database, and supports copying pages in batches. An ordinary file copy of a live database is not suitable, particularly when the source uses WAL mode: committed changes may still be in the log file.

Copying the main file and its accompanying WAL and SHM files one after another does not guarantee that they represent the same point in time. The command-line experiments on a cloud Mac are for verifying database behavior. When integrating this into iOS, use the app’s existing database paths and connection management.

Online backup guarantees database consistency, not completion of a business workflow. If “add an order and its line items” spans multiple transactions, a backup can capture the state between those transactions.

Define the delivery contract first: is any consistent state acceptable, or must the backup reflect a particular completed batch? The latter requires a business-level barrier combined with an explicit snapshot strategy, not guesswork about when to copy files.

Build a time-bounded backup tool in Swift

The following example backs up only the main database. The destination must be in a task-specific directory and must not already exist; the source file must already exist. Save the code as backup.swift and run it on a cloud Mac with the command-line tools installed.

import Foundation
import SQLite3

enum BackupFailure: Error {
    case invalidArguments
    case sqlite(Int32)
    case timeout
}

func openDB(_ path: String, flags: Int32) throws -> OpaquePointer {
    var db: OpaquePointer?
    let rc = sqlite3_open_v2(path, &db, flags, nil)
    guard rc == SQLITE_OK, let handle = db else {
        if let db = db { sqlite3_close(db) }
        throw BackupFailure.sqlite(rc)
    }
    return handle
}

func backup(source: String, target: String) throws {
    guard !FileManager.default.fileExists(atPath: target) else {
        throw BackupFailure.invalidArguments
    }
    let src = try openDB(source, flags: SQLITE_OPEN_READONLY)
    defer { sqlite3_close(src) }
    let dst = try openDB(
        target,
        flags: SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE
    )
    var complete = false
    defer {
        sqlite3_close(dst)
        if !complete {
            try? FileManager.default.removeItem(atPath: target)
        }
    }
    guard let job = sqlite3_backup_init(dst, "main", src, "main") else {
        throw BackupFailure.sqlite(sqlite3_errcode(dst))
    }
    let deadline = ProcessInfo.processInfo.systemUptime + 10
    var rc: Int32 = SQLITE_OK
    repeat {
        if ProcessInfo.processInfo.systemUptime >= deadline {
            rc = SQLITE_INTERRUPT
            break
        }
        rc = sqlite3_backup_step(job, 64)
        if rc == SQLITE_OK || rc == SQLITE_BUSY || rc == SQLITE_LOCKED {
            Thread.sleep(forTimeInterval: 0.02)
        }
    } while rc == SQLITE_OK || rc == SQLITE_BUSY || rc == SQLITE_LOCKED
    let finish = sqlite3_backup_finish(job)
    guard rc == SQLITE_DONE else {
        if rc == SQLITE_INTERRUPT { throw BackupFailure.timeout }
        throw BackupFailure.sqlite(rc)
    }
    guard finish == SQLITE_OK else {
        throw BackupFailure.sqlite(finish)
    }
    complete = true
}

guard CommandLine.arguments.count == 3 else {
    throw BackupFailure.invalidArguments
}
try backup(source: CommandLine.arguments[1], target: CommandLine.arguments[2])

xcrun swiftc backup.swift -o backup -lsqlite3
mkdir -m 700 backup-run
./backup source.sqlite backup-run/snapshot.sqlite
sqlite3 backup-run/snapshot.sqlite \
  'PRAGMA integrity_check; PRAGMA foreign_key_check;'

The 10-second limit is the total budget for this experiment, not a universal service standard. Other threads must not use the destination connection concurrently. The example does not install a busy handler. If the app configures its own handler, time spent waiting inside it must also count toward the budget.

Verify transaction boundaries under concurrent writes

Prepare two tables: a batch table containing a batch ID and the expected number of line items, and a line-item table referencing that batch ID. On each iteration, the writer inserts one batch and a fixed number of line items in a single transaction, then commits. A separate connection repeatedly runs backups, using a new destination path for each one.

Check every backup: each batch’s declared item count must match its actual line-item count, there must be no orphaned line items, and batch IDs must be unique. Snapshots may contain different numbers of complete batches. They must never contain a partial transaction.

Do not mistake progress for a frozen snapshot

During an incremental backup, continued writes from other connections can cause backup progress to restart. Page-count progress is therefore not guaranteed to increase monotonically, and the result cannot be assumed to represent the state at the start of the call.

If a fixed read point is required, explicitly begin a read transaction on the source connection and perform a read to establish the snapshot before starting the backup. Release the transaction promptly afterward. In WAL mode, a long-running read transaction can prevent checkpoints from advancing, so monitor log growth as well. Test the locking effects of different journal modes separately.

Prove that failures cannot produce a false deliverable

Test three failure scenarios in a disposable fixture directory: an unwritable destination directory, another connection holding a write lock for an extended period, and termination of the backup process before completion. Run out-of-space tests only on a controlled test volume. Do not fill a disk that also hosts other clients’ tasks.

Every scenario must produce an unsuccessful exit, and the delivery directory must not contain a file that could be mistaken for a complete backup. The example removes the destination only when an error returns through normal control flow. Cleanup does not run if the process is forcibly terminated, so a production integration should write to a staging file first and publish it only after validation succeeds.

integrity_check should return only ok; foreign_key_check should return no rows. Even if both pass, check business-level aggregates and user_version. A structurally sound database is not necessarily correct at the business level.

Complete the test by restoring from an independent path

Close the backup connection, copy the result to a new task directory, and reopen it without the source database’s WAL or SHM files. Run read-only queries first. Then use a disposable copy to test inserts, updates, deletes, and reopening, confirming that the app can actually continue using the database.

Keep the source code commit ID, database version, backup duration, file digest, and check results in the delivery record, but do not retain the client’s plaintext data. When restoring in the app, close all old connections first so that no connection continues operating on the old database after the new file is put in place.

Finally, include both “no concurrent writes” and “continuous writes” in regression testing. The goal is not to produce an identical file every time. It is to produce a database that can be opened independently and satisfies transaction and business constraints every time—and to explicitly refuse publication when the backup fails.

Frequently asked questions