Disabling or Deferring Foreign Key Enforcement with GRDB for Database Migration

Question

I have taken the GRDB library into use in my mobile app. All current features are working nicely, and I have started implementing the migration from earlier databsase versions. For consistency with my implementation for other platforms, I have decided to use SQLite's user_version instead of GRDB's own migration framework, which uses its own table.

With tables being altered, copied, created, and deleted, it happens that changes within one migration step (database version) depend on each other. As changes are committed at the end of a transaction, this leads to foreign key violations, and the upgrade fails.

A way to work around this is to prevent foreign key enforcement either by deferring (defer_foreign_keys pragma) it or by temporarily disabling it for the transaction setting the foreign_keys pragma. Unfortunately, I have not had much luck with either of the options. After some testing noticed, for instance, that attempting to turn off the foreign key checking with

config.prepareDatabase { db in
    try db.execute(sql: "PRAGMA foreign_keys = OFF")
}

and reading the pragma using

    try dbQueue.write { db in
        print(try Bool.fetchOne(db, sql: "PRAGMA foreign_keys")! as Bool)
    }

or by inspecting the database indicates that the foreign_keys setting remained ON.

My migration steps look, slightly simplified, like this:

if try userVersion() < 2 {
    try dbQueue.write { db in
        try db.execute(sql: ...)
        try db.execute(sql: ...)
        ...
        try db.execute(sql: "PRAGMA user_version = 2")
    }
}
if try userVersion() < 3 {
    try dbQueue.write { db in
        try db.execute(sql: ...)
        try db.execute(sql: ...)
        ...
        try db.execute(sql: "PRAGMA user_version = 3")
    }
}

My initial GRDB setup is as follows:

var config = Configuration()
config.foreignKeysEnabled = true
let appSupportDirectory = NSSearchPathForDirectoriesInDomains(.applicationSupportDirectory, .userDomainMask, true).first!
dbPath = (appSupportDirectory as NSString).appendingPathComponent(dbName)

let fileManager = FileManager.default
if fileManager.fileExists(atPath: dbPath) {
    // Just connect to database.
    dbQueue = try DatabaseQueue(path: dbPath, configuration: config)
} else {
    // Create new database.
    dbQueue = try DatabaseQueue(path: dbPath, configuration: config)
    // Create tables
    ...
}

// Database migration steps:
...

What would be the best approach for preventing those foreign key failures during database migration, and why are my pragma statements not working?

Gwendal Roué · Accepted Answer · 2021-03-04T10:27:42.580

Quoting https://www.sqlite.org/pragma.html#pragma_foreign_keys:

This pragma [foreign_keys] is a no-op within a transaction; foreign key constraint enforcement may only be enabled or disabled when there is no pending BEGIN or SAVEPOINT.

The GRDB DatabaseQueue.write method opens a transaction. So when you want to disable foreign keys, you have to perform manual transaction handling:

// Add the `writeWithDeferredForeignKeys` method to
// DatabaseQueue and DatabasePool.
extension DatabaseWriter {
    func writeWithDeferredForeignKeys(_ updates: (Database) throws -> Void) throws {
        try writeWithoutTransaction { db in
            // Disable foreign keys
            try db.execute(sql: "PRAGMA foreign_keys = OFF");
            
            do {
                // Perform updates in a transaction
                try db.inTransaction {
                    try updates(db)
                    
                    // Check foreign keys before commit
                    if try Row.fetchOne(db, sql: "PRAGMA foreign_key_check") != nil {
                        throw DatabaseError(resultCode: .SQLITE_CONSTRAINT_FOREIGNKEY)
                    }
                    
                    return .commit
                }
                
                // Re-enable foreign keys
                try db.execute(sql: "PRAGMA foreign_keys = ON");
            } catch {
                // Re-enable foreign keys and rethrow
                try db.execute(sql: "PRAGMA foreign_keys = ON");
                throw error
            }
        }
    }
}

try dbQueue.writeWithDeferredForeignKeys { db in
    try db.execute(sql: ...)
    try db.execute(sql: ...)
}

I'm not familiar enough with defer_foreign_keys, but the above sample code should help you converge to the solution you're after.

Disabling or Deferring Foreign Key Enforcement with GRDB for Database Migration

1 Answers1