update to GRDB 7.7.0 causes test failures when using the `saved()` method on `MutablePersistableRecord`

[yqiang]

After upgrading to GRDB 7.7.0 from GRDB 7.6.1 I noticed that several of my unit tests started to fail. Specifically, it seems like something about the .saved() method seems to have changed. I narrowed down the failure to a code snippet like this, where we start with an empty, in-memory database:

try await appDatabase.writer.write { db in
  _ = try food1.food.saved(db)
  _ = try food2.food.saved(db)
  _ = try serving1.saved(db)
  _ = try serving2.saved(db)
  _ = try section.saved(db)
}

In this instance serving1 has a foreign key dependency on on food and I get the following error:

Test "Test frequently paired foods" recorded an issue at FrequentlyPairedFoodsTests.swift:14:4: 
Caught error: SQLite error 19: FOREIGN KEY constraint failed - while executing `INSERT INTO "serving" ("id", "description", "nutritionMultiplier", "amount", "measure", "metricUnit", "metricValue", "sortOrder", "isPublic", "derivedFrom", "nutritionPanelId", "foodId") VALUES (?,?,?,?,?,?,?,?,?,?,?,?)` with arguments ["eb46ddbf-e15b-4be2-8daa-8ab83c657953", "1 piece", 1.0, NULL, NULL, NULL, NULL, 0, 0, NULL, NULL, "food2"]

If I replace the saved calls here with inserted, the tests pass again. Hopefully this is enough info to go on, but if not, I'm happy to try to create a sample project.

Environment

• Xcode 26.0
• iOS 26.0

[groue]

Thanks for the report @yqiang, I'll check quickly.

[groue]

The error says "SQLite error 19: FOREIGN KEY constraint failed". The application is trying to insert a serving which references a non-existing relationship (probably the foodId with id "food2").

If you think the error is related to the GRDB upgrade, please provide a reproducing sample code.

[yqiang]

Here's a sample repro. It was a bit tricky to track down but it seems to have something to do with having an FTS table associated one of the tables.

https://github.com/yqiang/GRDBBug

Here's the repro and the log output in case it's more convenient than cloning a repo:

import SwiftUI
import GRDB

struct Serving: Codable, FetchableRecord, PersistableRecord {
  let id: UUID
  var description: String
  var foodId: String
  
  static let author = hasOne(Food.self)
}

struct Food: Codable, FetchableRecord, PersistableRecord {
  let id: UUID
  var name: String
  var foodId: String
}


@main
struct GRDBBugApp: App {
  init() {
    do {
      try setupDatabase()
    } catch {
      print(error)
    }
  }
  
  func setupDatabase() throws {
    var config = Configuration()
    config.prepareDatabase { db in
      db.trace(options: .profile) {
        print("\($0.expandedDescription)")
      }
    }
    
    let dbQueue = try DatabaseQueue(configuration: config)
    
    try dbQueue.write { db in
      try db.create(table: "food") { t in
        t.column("id", .blob).primaryKey()
        t.column("name", .text)
        t.column("foodId", .text).unique()
      }
      
      try db.create(table: "serving") { t in
        t.column("id", .blob).primaryKey()
        t.column("description", .text)
        t.column("foodId", .text).references("food", column: "foodId")
      }
      
      try db.create(virtualTable: "food_fts", using: FTS5()) { t in
        t.synchronize(withTable: "food")
        
        t.column("name")
      }

    }
    
    // This is not OK
    try dbQueue.write { db in
      let foodNames = ["Apple", "Banana", "Strawberry"]
      let foods = foodNames.map {
        Food(id: UUID(), name: $0, foodId: $0.lowercased())
      }
      
      let servings = foods.map {
        Serving(id: UUID(), description: "1 \($0.name)", foodId: $0.foodId)
      }
      
      for food in foods {
        _ = try food.saved(db)
      }
      
      for serving in servings {
        _ = try serving.saved(db)
      }
    }
 
    // This works
    // try dbQueue.write { db in
    //   let foodNames = ["Apple", "Banana", "Strawberry"]
    //   let foods = foodNames.map {
    //     Food(id: UUID(), name: $0, foodId: $0.lowercased())
    //   }
    //
    //   let servings = foods.map {
    //     Serving(id: UUID(), description: "1 \($0.name)", foodId: $0.foodId)
    //   }
    //
    //   for food in foods {
    //     _ = try food.inserted(db)
    //   }
    //
    //   for serving in servings {
    //     _ = try serving.inserted(db)
    //   }
    // }
    
  }
  
  var body: some Scene {
    WindowGroup {
      ContentView()
    }
  }
}

Log output:

0.000s SELECT * FROM sqlite_master LIMIT 1
0.000s PRAGMA journal_mode
0.000s BEGIN IMMEDIATE TRANSACTION
0.000s CREATE TABLE "food" ("id" BLOB PRIMARY KEY, "name" TEXT, "foodId" TEXT UNIQUE)
0.000s CREATE TABLE "serving" ("id" BLOB PRIMARY KEY, "description" TEXT, "foodId" TEXT REFERENCES "food"("foodId"))
0.000s SELECT QUOTE('food')
0.000s PRAGMA database_list
0.000s SELECT type, name, tbl_name, sql FROM sqlite_master
0.000s PRAGMA main.table_xinfo("food")
0.000s PRAGMA main.table_list("food")
0.000s SAVEPOINT grdb
0.000s CREATE TABLE 'main'.'food_fts_data'(id INTEGER PRIMARY KEY, block BLOB)
0.000s CREATE TABLE 'main'.'food_fts_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID
0.000s REPLACE INTO 'main'.'food_fts_data'(id, block) VALUES(1,x'')
0.000s REPLACE INTO 'main'.'food_fts_data'(id, block) VALUES(10,x'00000000000000')
0.000s CREATE TABLE 'main'.'food_fts_docsize'(id INTEGER PRIMARY KEY, sz BLOB)
0.000s CREATE TABLE 'main'.'food_fts_config'(k PRIMARY KEY, v) WITHOUT ROWID
0.000s REPLACE INTO 'main'.'food_fts_config' VALUES('version',4)
0.000s SELECT k, v FROM 'main'.'food_fts_config'
0.000s CREATE VIRTUAL TABLE "food_fts" USING fts5(name, content='food')
0.000s PRAGMA database_list
0.000s SELECT type, name, tbl_name, sql FROM sqlite_master
0.000s PRAGMA main.table_xinfo("food")
0.000s PRAGMA main.table_list("food")
0.000s CREATE TRIGGER "__food_fts_ai" AFTER INSERT ON "food" BEGIN
    INSERT INTO "food_fts"("rowid", "name") VALUES (new."rowid", new."name");
END
0.000s CREATE TRIGGER "__food_fts_ad" AFTER DELETE ON "food" BEGIN
    INSERT INTO "food_fts"("food_fts", "rowid", "name") VALUES('delete', old."rowid", old."name");
END
0.000s CREATE TRIGGER "__food_fts_au" AFTER UPDATE ON "food" BEGIN
    INSERT INTO "food_fts"("food_fts", "rowid", "name") VALUES('delete', old."rowid", old."name");
    INSERT INTO "food_fts"("rowid", "name") VALUES (new."rowid", new."name");
END
0.000s DELETE FROM 'main'.'food_fts_data'
0.000s DELETE FROM 'main'.'food_fts_idx'
0.000s DELETE FROM 'main'.'food_fts_docsize'
0.000s REPLACE INTO 'main'.'food_fts_data'(id, block) VALUES(1,x'')
0.000s REPLACE INTO 'main'.'food_fts_data'(id, block) VALUES(1,x'')
0.000s REPLACE INTO 'main'.'food_fts_data'(id, block) VALUES(10,x'00000000000000')
0.000s REPLACE INTO 'main'.'food_fts_config' VALUES('version',4)
0.000s REPLACE INTO 'main'.'food_fts_config' VALUES('version',4)
0.000s SELECT T.'rowid', T.'name' FROM 'main'.'food' AS T
0.000s REPLACE INTO 'main'.'food_fts_data'(id, block) VALUES(1,x'0000')
0.000s PRAGMA 'main'.data_version
0.000s SELECT k, v FROM 'main'.'food_fts_config'
0.000s INSERT INTO "food_fts"("food_fts") VALUES('rebuild')
0.000s RELEASE SAVEPOINT grdb
0.000s REPLACE INTO 'main'.'food_fts_data'(id, block) VALUES(1,x'0000')
0.000s COMMIT TRANSACTION
0.000s BEGIN IMMEDIATE TRANSACTION
0.000s PRAGMA database_list
0.000s SELECT type, name, tbl_name, sql FROM sqlite_master
0.000s PRAGMA main.table_xinfo("food")
0.000s PRAGMA main.table_list("food")
0.000s PRAGMA 'main'.data_version
0.000s UPDATE "food" SET "name"='Apple', "foodId"='apple' WHERE "id"=x'beb2f9f7530d44d5bf3a1a38e628ad43'
0.000s REPLACE INTO 'main'.'food_fts_docsize' VALUES(1,x'01')
0.000s INSERT INTO "food" ("id", "name", "foodId") VALUES (x'beb2f9f7530d44d5bf3a1a38e628ad43','Apple','apple')
0.000s REPLACE INTO 'main'.'food_fts_data'(id, block) VALUES(1,x'0101')
0.000s REPLACE INTO 'main'.'food_fts_data'(id, block) VALUES(137438953473,x'0000000e06306170706c6501020204')
0.000s INSERT INTO 'main'.'food_fts_idx'(segid,term,pgno) VALUES(1,x'',2)
0.000s REPLACE INTO 'main'.'food_fts_data'(id, block) VALUES(10,x'000000000101010001010101')
0.000s UPDATE "food" SET "name"='Banana', "foodId"='banana' WHERE "id"=x'88a514eade84491f9c8564e32aaa0b57'
0.000s UPDATE "food" SET "name"='Strawberry', "foodId"='strawberry' WHERE "id"=x'f5842376f5574a41ab9f60001d9761a8'
0.000s REPLACE INTO 'main'.'food_fts_docsize' VALUES(2,x'01')
0.000s INSERT INTO "food" ("id", "name", "foodId") VALUES (x'f5842376f5574a41ab9f60001d9761a8','Strawberry','strawberry')
0.000s PRAGMA main.table_xinfo("serving")
0.000s PRAGMA main.table_list("serving")
0.000s REPLACE INTO 'main'.'food_fts_data'(id, block) VALUES(1,x'0202')
0.000s PRAGMA 'main'.data_version
0.000s REPLACE INTO 'main'.'food_fts_data'(id, block) VALUES(274877906945,x'000000130b307374726177626572727902020204')
0.000s INSERT INTO 'main'.'food_fts_idx'(segid,term,pgno) VALUES(2,x'',2)
0.000s REPLACE INTO 'main'.'food_fts_data'(id, block) VALUES(10,x'000000000102020002010101020101')
0.000s UPDATE "serving" SET "description"='1 Apple', "foodId"='apple' WHERE "id"=x'01b6bbc92283402fbadd886b90305c81'
0.000s UPDATE "serving" SET "description"='1 Banana', "foodId"='banana' WHERE "id"=x'eddd200ba5f04976b03493d2d8df686a'
0.000s INSERT INTO "serving" ("id", "description", "foodId") VALUES (x'eddd200ba5f04976b03493d2d8df686a','1 Banana','banana')
0.000s ROLLBACK TRANSACTION
SQLite error 19: FOREIGN KEY constraint failed - while executing `INSERT INTO "serving" ("id", "description", "foodId") VALUES (?,?,?)`

You can see that GRDB is not issuing INSERT statements after the first food.

[groue]

Thank you very much, @yqiang. You found a regression indeed 😬

A fix is on the way, please hold on.

[groue]

The fix has shipped in v7.7.1

[yqiang]

Thanks for the quick turnaround, and the amazing work on GRDB overall.

[groue]

You're welcome! Happy GRDB!