Backup/Restore - Patterns

[luco17]

I would like to build a 'recover data' feature for when users get a new phone. I'm imagining this flow, but not wedded to it:

1. User goes to settings, has option to 'backup data';
2. This stores a copy of the db and pushes it to some storage (maybe iCloud?);
3. One day user gets new phone, opens app, goes to settings, taps 'restore history', db is pulled from storage and their data is there.

Optional step
User uses app, when DB updates it saves the DB (can update the diff or overwrite the old one - this db isn't going to get huge);

Research so far

• I DO NOT need real time syncing/device handoff so I think I can discard most of this discussion: https://forums.swift.org/t/taking-grdb-beyond-local/75247

• My schema has many foreign-key relationships so as per this discussion a very slick CloudKit sync seems off CloudKit synchronization options #1569.

• The backup API seems a good starting point to get the copy and restore done locally: https://github.com/groue/GRDB.swift/blob/master/README.md#backup

---

What are some simple way/patterns to implement this? I am a hobbyist so my level is modest at best. If it helps to know where I'm storing my db here is Persistence.swift file

extension AppDatabase {
    static let shared = makeShared()

    private static func makeShared() -> AppDatabase {
        do {
            let fileManager = FileManager.default
            let appSupportURL = try fileManager.url(for: .applicationSupportDirectory, in: .userDomainMask, appropriateFor: nil, create: true)
            let directoryURL = appSupportURL.appending(path: "Database")
            try fileManager.createDirectory(at: directoryURL, withIntermediateDirectories: true)

            let databaseURL = directoryURL.appending(path: "db.sqlite")
            let dbPool = try DatabasePool(path: databaseURL.path)

            let appDatabase = try AppDatabase(dbPool)
            
            /// Seed the database with initial exercises
            try appDatabase.seedActivitiesIfEmpty()
            
            return appDatabase
        } catch {
            fatalError("Failed to create AppDatabase: \(error)")
        }
    }

---

A really useful answer would have info on:

• Confirmation that the backup API is a good approach;
• Recommendations on cloud storage choice (iCloud vs S3 for example) any guides you like for the iCloud piece;
• Any examples for the db upload/download flow;
• Any gotchas with periodic overwrites vs incremental updates;
• Links to similar implementations if available;

Even partial answers addressing some of these points would be valuable!

---

Lastly, if you read this Gwendal, thank you very much for the huge amount of effort you put into this library, its documentation and the help you give the community.

[groue]

Lastly, if you read this Gwendal, thank you very much for the huge amount of effort you put into this library, its documentation and the help you give the community.

Thank you Lucien :-)

Confirmation that the backup API is a good approach

I recommend reading Caveats Using Read-only SQLite Databases from the App Bundle by @twocentstudios. It discusses GRDB/SQLite techniques for backing up a database in a way that makes sure it is fully contained in a single (uploadable) file. It may sound like it is too technical, but I'm not sure these are details you can skip.

[luco17]

Thank you very much. I shall review this. Is there anything I could post afterwards that would be helpful?

[groue]

Is there anything I could post afterwards that would be helpful?

Maybe, when you have time, your own answers to your own questions?

[luco17]

So I've read the article you suggest but don't fully grasp what I'm meant to learn from it for these reasons:

1. My database is read-write, not read-only. He describes read-only scenarios;
2. My database is not in the app bundle. Per my Persistence.swift snippet above it's in the ApplicationSupportDirectory;

Your recommendation suggests to me that the interesting bit for my scenario is that I need to change the mode from Journal WAL to Journal DELETE to ensure a single file upload. This is a guess with some help from Claude though.

---

Regarding the filesync my research link is telling me it's better to store the DB in iCloud documents (for my particular use case) rather than try some sort of CloudKit sync job. Does anyone have opinions on this?

[groue]

So I've read the article you suggest but don't fully grasp what I'm meant to learn from it for these reasons

The relationship between the article and your question is that you both need to generate a standalone database file that can be later imported (in his case imported into the resources of this app, in your case downloaded from a backup).

Your recommendation suggests to me that the interesting bit for my scenario is that I need to change the mode from Journal WAL to Journal DELETE to ensure a single file upload.

Yes. The DELETE journal mode makes sure that the exported database file is truly standalone (single file, contains everything, can be opened read-only if needed).

[luco17]

Update

I have found a working pattern that uploads the database to users' iCloud using the private database CloudKit provides (Good detail on CloudKit here).

I decided not to use iCloud documents.

The approach

Enable iCloud
Set up iCloud and CloudKit: Docs

Back up the database

/// A class to manage the backup and restore of the application's database to iCloud.
class BackupManager {
    /// The shared singleton instance of the `BackupManager`.
    static let shared = BackupManager()
    
    /// The private CloudKit database used for storing backups.
    private let privateDB = CKContainer.default().privateCloudDatabase
    
    /// The record type used for storing backups in CloudKit.
    private let backupRecordType = "wrktBackup"
    
    /// Fetches the iCloud account status.
    ///
    /// This function checks if the user is signed in to iCloud and if the service is available.
    ///
    /// - Parameter completion: A closure to be called with the account status.
    func getAccountStatus(completion: @escaping (CKAccountStatus) -> Void) {
        CKContainer.default().accountStatus { status, error in
            if let error = error {
                print("Error fetching account status: \(error)")
                completion(.couldNotDetermine)
                return
            }
            completion(status)
        }
    }
    
    /// Creates a safe, temporary copy of the database for backup.
    ///
    /// This function creates a backup of the main database, vacuums it to minimise its size,
    /// and changes the journal mode to `DELETE` to ensure a single, safe file for upload.
    ///
    /// - Throws: An error if the backup copy cannot be created.
    /// - Returns: The URL of the temporary backup file.
    private func createSafeBackupCopy() throws -> URL {
        // Define the source and destination databases
        let sourceDB: DatabaseReader = AppDatabase.shared.reader
        let tempURL = URL.temporaryDirectory.appendingPathComponent(UUID().uuidString).appendingPathExtension("sqlite")
        
        // Create a fresh database for backup
        let backupDB: DatabaseQueue = try DatabaseQueue(path: tempURL.path)
        
        // Backup the main db
        try sourceDB.backup(to: backupDB)
        
        // Ensure all data is written and the database is in a clean state
        try backupDB.writeWithoutTransaction { db in
            // switch to DELETE mode for a single file upload
            _ = try db.execute(sql: "PRAGMA journal_mode=DELETE")
            
            // Force a full checkpoint to commit any WAL data.
            _ = try db.execute(sql: "PRAGMA wal_checkpoint(TRUNCATE)")
            
            // Run integrity check
            let integrityResult = try String.fetchOne(db, sql: "PRAGMA integrity_check")
            guard integrityResult == "ok" else {
                Logger.backupManager.error("Integrity check failed: \(integrityResult ?? "unknown")")
                throw BackupError.databaseVerificationFailed
            }
            
            // Ensure foreign keys are consistent
            let fkCheck = try Row.fetchAll(db, sql: "PRAGMA foreign_key_check")
            guard fkCheck.isEmpty else {
                Logger.backupManager.error("Backup Foreign key check failed: \(fkCheck)")
                throw BackupError.databaseVerificationFailed
            }
        }
        
        // Vacuum AFTER changing to DELETE mode and checkpointing
        try backupDB.vacuum()
        
        // Close the connection completely
        try backupDB.close()
        
        return tempURL
    }
    
    /// Backs up the application's database to iCloud.
    ///
    /// This function checks the iCloud account status, creates a safe copy of the database,
    /// and then uploads it to iCloud as a `CKRecord`.
    ///
    /// - Parameter completion: A closure to be called with an optional error if the backup fails.
    func backupDatabase(completion: @escaping (Error?) -> Void) {
        getAccountStatus { status in
            guard status == .available else {
                DispatchQueue.main.async {
                    completion(BackupError.iCloudUnavailable)
                }
                return
            }
            
            do {
                let safeBackupURL = try self.createSafeBackupCopy()
                
                let record = CKRecord(recordType: self.backupRecordType)
                record["databaseFile"] = CKAsset(fileURL: safeBackupURL)
                record["backupDate"] = Date()
                record["appVersion"] = Bundle.main.infoDictionary?["CFBundleShortVersionString"] as? String ?? "Unknown"
                record["deviceName"] = UIDevice.current.name
                
                self.privateDB.save(record) { _, error in
                    try? FileManager.default.removeItem(at: safeBackupURL)
                    DispatchQueue.main.async {
                        completion(error)
                    }
                }
            } catch {
                DispatchQueue.main.async {
                    completion(error)
                }
            }
        }
    }
}

Once this is done and you trigger these functions in some view you should have a database record in iCloud. You can see this by going to the iCloud dashboard. (note, you can try this by logging into iCloud in the simulator).

Restore the database

extension BackupManager {
    
    /// Restores the database from a backup metadata record.
    ///
    /// This function orchestrates the entire restore process:
    /// 1. Checks iCloud account availability.
    /// 2. Downloads the complete backup record (including the database file).
    /// 3. Verifies the backup's integrity and version compatibility.
    /// 4. Safely shuts down the current database connection.
    /// 5. Delegates to `replaceDatabaseWithBackup` to swap the files on disk.
    ///
    /// - Parameter metadataRecord: The backup metadata record (as returned by `fetchLatestBackupMetadata`).
    /// - Throws: A `BackupError` or other system error if the restore process fails at any step.
    func restoreDatabase(from metadataRecord: CKRecord) async throws {
        Logger.backupManager.info("Starting database restore process...")
        
        // 1. Check iCloud availability
        let status = await withCheckedContinuation { continuation in
            getAccountStatus { status in continuation.resume(returning: status) }
        }
        guard status == .available else {
            Logger.backupManager.error("Restore failed: iCloud is unavailable.")
            throw BackupError.iCloudUnavailable
        }
        
        // 2. Download the full backup record and get the database file URL
        Logger.backupManager.debug("Fetching complete backup record with ID: \(metadataRecord.recordID.recordName)")
        let fullBackupRecord = try await fetchCompleteBackupRecord(withID: metadataRecord.recordID)
        
        guard let asset = fullBackupRecord["databaseFile"] as? CKAsset, let backupFileURL = asset.fileURL else {
            Logger.backupManager.error("Restore failed: Backup asset could not be found in the downloaded record.")
            throw BackupError.downloadFailed
        }
        Logger.backupManager.info("Successfully downloaded backup asset to temporary location: \(backupFileURL.path)")
        
        // 3. Verify the downloaded backup file before proceeding
        try checkVersionCompatibility(record: fullBackupRecord)
        try verifyDatabaseIntegrity(at: backupFileURL)
        Logger.backupManager.info("Backup version and integrity have been verified.")
        
        // 4. Prepare for file replacement by safely shutting down the active database
        let appDatabase = AppDatabase.shared
        do {
            // Force a WAL checkpoint to merge all data into the main sqlite file.
            // This is critical to ensure no data is lost before we close.
            try await appDatabase.writer.writeWithoutTransaction { db in
                _ = try db.execute(sql: "PRAGMA wal_checkpoint(TRUNCATE)")
            }
            Logger.backupManager.debug("Successfully checkpointed the active database.")
            
            // Now, close all connections to release file locks.
            try appDatabase.close()
            Logger.backupManager.info("Closed all active database connections.")
        } catch {
            Logger.backupManager.error("Failed to safely shutdown the active database before restore. Aborting. Error: \(error)")
            // Re-throwing is important so the UI can know the restore failed.
            throw error
        }
        
        // 5. Delegate the file system operations to the specialized function
        do {
            try replaceDatabaseWithBackup(from: backupFileURL)
            Logger.backupManager.notice("Database restore complete. The app will need to restart to use the new database.")
        } catch {
            Logger.backupManager.fault("CRITICAL: Failed during the final file replacement step of the restore: \(error)")
            // Optional: Implement logic to restore the "pre-restore" backup here if this step fails.
            throw error
        }
    }
    
    /// Fetches metadata for the latest backup record from iCloud.
    ///
    /// This method only retrieves display metadata (date, device name, app version) for UI presentation.
    /// It does NOT download the actual backup file to improve performance and reduce bandwidth usage.
    /// To download the complete backup for restoration, use `fetchCompleteBackupRecord`.
    ///
    /// - Parameter completion: A closure to be called with the latest backup metadata record and an optional error.
    func fetchLatestBackupMetadata(completion: @escaping (CKRecord?, Error?) -> Void) {
        let query = CKQuery(recordType: backupRecordType, predicate: NSPredicate(value: true))
        query.sortDescriptors = [NSSortDescriptor(key: "backupDate", ascending: false)]
        
        let operation = CKQueryOperation(query: query)
        operation.desiredKeys = ["backupDate", "deviceName", "appVersion"]
        operation.resultsLimit = 1
        
        var fetchedRecord: CKRecord?
        
        operation.recordMatchedBlock = { recordId, result in
            switch result {
            case .success(let record):
                fetchedRecord = record
            case .failure(let error):
                print("Error fetching record: \(error)")
            }
        }
        
        operation.queryResultBlock = {result in
            DispatchQueue.main.async {
                switch result {
                case .success:
                    if let record = fetchedRecord {
                        completion(record, nil)
                    } else {
                        completion(nil, BackupError.backupNotFound)
                    }
                case .failure(let error):
                    completion(nil, error)
                }
            }
        }
        
        operation.qualityOfService = .utility
        privateDB.add(operation)
    }
    
    /// Fetches a complete backup record including the database file asset from CloudKit.
    ///
    /// This method downloads the entire record including the backup file asset.
    /// Use this when you need to actually restore a backup, not just display metadata.
    ///
    /// - Parameter recordID: The ID of the backup record to fetch completely
    /// - Returns: The complete CKRecord with all fields including the database file
    /// - Throws: An error if the fetch fails
    private func fetchCompleteBackupRecord(withID recordID: CKRecord.ID) async throws -> CKRecord {
        return try await withCheckedThrowingContinuation { continuation in
            privateDB.fetch(withRecordID: recordID) { record, error in
                if let error = error {
                    continuation.resume(throwing: error)
                } else if let record = record {
                    continuation.resume(returning: record)
                } else {
                    continuation.resume(throwing: BackupError.downloadFailed)
                }
            }
        }
    }
    
    /// Checks if the backup version is compatible with the current app version.
    ///
    /// - Parameter record: The `CKRecord` of the backup.
    /// - Throws: `BackupError.versionIncompatible` if the versions do not match.
    private func checkVersionCompatibility(record: CKRecord) throws {
        guard let backupVersion = record["appVersion"] as? String else {
            return
        }
        
        let currentVersion = Bundle.main.infoDictionary?["CFBundleShortVersionString"] as? String ?? "Unknown"
        
        if backupVersion != currentVersion {
            throw BackupError.versionIncompatible(backupVersion: backupVersion, currentVersion: currentVersion)
        }
    }
    
    /// Verifies the integrity of the downloaded backup file.
    ///
    /// This function runs a `PRAGMA integrity_check` on the downloaded database to ensure it is not corrupted.
    ///
    /// - Parameter url: The URL of the downloaded backup file.
    /// - Throws: `BackupError.databaseVerificationFailed` if the integrity check fails.
    private func verifyDatabaseIntegrity(at url: URL) throws {
        let testDB = try DatabaseQueue(path: url.path)
        defer { try? testDB.close() }
        
        try testDB.read { db in
            let integrityCheck = try String.fetchOne(db, sql: "PRAGMA integrity_check")
            if integrityCheck != "ok" {
                throw BackupError.databaseVerificationFailed
            }
        }
    }
    
    /// Replaces the current application database with a new database file.
    ///
    /// This is a low-level function responsible only for file system operations.
    /// It assumes the provided `sourceURL` points to a valid, verified database file
    /// and that the active `AppDatabase` connection has already been closed.
    ///
    /// - Parameter sourceURL: The URL of the verified backup database file to be copied into place.
    /// - Throws: An error if any file operations (backup, remove, copy) fail.
    private func replaceDatabaseWithBackup(from sourceURL: URL) throws {
        let fileManager = FileManager.default
        
        // Define paths for the production database
        guard let appSupportURL = fileManager.urls(for: .applicationSupportDirectory, in: .userDomainMask).first else {
            throw BackupError.directoryNotFound
        }
        let dbDirectoryURL = appSupportURL.appendingPathComponent("Database")
        let dbURL = dbDirectoryURL.appendingPathComponent("db.sqlite")
        
        // Create a final, one-time backup of the current database before replacing it.
        // This is a safety net in case the user wants to revert the restore.
        let preRestoreBackupURL = dbDirectoryURL.appendingPathComponent("db-pre-restore-\(Date().timeIntervalSince1970).sqlite")
        if fileManager.fileExists(atPath: dbURL.path) {
            try fileManager.copyItem(at: dbURL, to: preRestoreBackupURL)
            Logger.backupManager.info("Created a one-time safety backup of the current database at: \(preRestoreBackupURL.path)")
        }
        
        // Remove any lingering database files (-wal, -shm) to ensure a clean slate.
        let walURL = dbDirectoryURL.appendingPathComponent("db.sqlite-wal")
        let shmURL = dbDirectoryURL.appendingPathComponent("db.sqlite-shm")
        
        try? fileManager.removeItem(at: dbURL)
        try? fileManager.removeItem(at: walURL)
        try? fileManager.removeItem(at: shmURL)
        Logger.backupManager.debug("Removed existing database files (sqlite, wal, shm).")
        
        // Copy the verified backup file to the final destination.
        try fileManager.copyItem(at: sourceURL, to: dbURL)
        Logger.backupManager.info("Successfully copied new database into place.")
    }
}

Limitations

• I'm not sure how to manage all the backups in iCloud yet, perhaps there's a way to limit them to a certain number.
• The user has to restart the app after restoring the database (because it closes the db connection). I've not got round to a smarter solution yet.
• I've not implemented a silent 'backup' on key activities because of the above limitations.

Gotchas

• the WAL Checkpoint in the restore operation was really important. Without doing this there was some race condition where the data in the WAL file from app initialisation would overwrite the restored database.
• I kept getting an error when querying for backups. The error was recordName is not marked queryable. The dude in this video helped me solve it: YouTube link.

What next

If you have any observations on how to improve this approach I would welcome them.

[groue]

Wow, thank you for sharing your detailed solution! 🤩