Key not found in table <table>: [rowid:NULL] when updating an existing record

[voltangle]

I'm currently implementing a caching system with GRDB, and that involves syncing new data with the cached one.

I have a singleton class, CacheService, which has a nice API for other modules to use for interacting with the cache database. Here is the API that implements updating a record:

Private method

private func modify<Object>(
    object: Object.Type,
    at id: Int,
    from db: Database,
    transform: (inout Object) -> Void) throws
where Object: FetchableRecord, Object: PersistableRecord {
    if var value = try object.filter(Column("id") == id).fetchOne(db) {
        try value.updateChanges(db) {
            transform(&$0)
        }
    }
}

Public method

func modify<Object>(object: Object.Type, at id: Int, transform: (inout Object) -> Void) throws
where Object: FetchableRecord, Object: PersistableRecord {
    try dbQueue.write { db in
        try modify(object: object, at: id, from: db, transform: transform)
    }
}

And then it's usage:

let objects = try cache.getObjects(as: Caching.ChatFilter.self)
TdApi.logger.debug("Going over received chat filters")
for chatFilter in update.chatFilters {
    if objects.contains(where: { $0.id == chatFilter.id }) {
        TdApi.logger.debug("Updating filter with id \(chatFilter.id) in database")
        try cache.modify(object: Caching.ChatFilter.self, at: chatFilter.id) {
            $0.title = chatFilter.title
            $0.id = chatFilter.id
            $0.iconName = chatFilter.iconName
        }
    } else {
        TdApi.logger.debug("Creating a new one with id \(chatFilter.id)")
        try cache.save(object: Caching.ChatFilter(
            title: chatFilter.title,
            id: chatFilter.id,
            iconName: chatFilter.iconName
        ))
    }
}
TdApi.logger.debug("Going over filters in database")
for object in objects {
    if !update.chatFilters.contains(where: { $0.id == object.id }) {
        TdApi.logger.debug("Update does not contain filter with id \(object.id), removing from database")
        try cache.delete(object: object)
    }
}

I placed some debug logs in between the code so I can see what it does so I can share it. And below is exactly what it does:

<time> Moc[45457:2189580] [Updates] Going over received chat filters
<time> Moc[45457:2189580] [Updates] Updating filter with id 130 in database
Key not found in table chatFilter: [rowid:NULL]
<time> Moc[45457:2189580] [Updates] Updating filter with id 5 in database
Key not found in table chatFilter: [rowid:NULL]
Key not found in table chatFilter: [rowid:NULL]
<time> Moc[45457:2189580] [Updates] Updating filter with id 126 in database
<time> Moc[45457:2189580] [Updates] Updating filter with id 100 in database
<time> Moc[45457:2189580] [Updates] Updating filter with id 79 in database
<time> Moc[45457:2189580] [Updates] Updating filter with id 7 in database
<time> Moc[45457:2189580] [Updates] Updating filter with id 2 in database
<time> Moc[45457:2189580] [Updates] Updating filter with id 31 in database
<time> Moc[45457:2189580] [Updates] Going over filters in database
Key not found in table chatFilter: [rowid:NULL]
Key not found in table chatFilter: [rowid:NULL]
Key not found in table chatFilter: [rowid:NULL]
Key not found in table chatFilter: [rowid:NULL]
Key not found in table chatFilter: [rowid:NULL]
Key not found in table chatFilter: [rowid:NULL]

For some reason, it can't find the record to update. What am I doing wrong? :) Thanks in advance!

[groue]

Hello @ggoraa,

For some reason, it can't find the record to update. What am I doing wrong? :) Thanks in advance!

The "Key not found" error message comes from PersistenceError, which is thrown by the update method (and related ones such as updateChanges), when it asked to update a row that does not exist in the database.

It says: "well, you asked me to update the chatFilter whose rowid is NULL, and I could not find any, so I prefer telling you that the update has failed with an error, so that you don't make wrong assumptions".

Indeed, no row in the database can have the value NULL in the rowid column.

Now, you may wonder why the update method attempts at identifying a row with a NULL rowid column.

This is a mix between the database schema, and how your record type implements the PersistableRecord protocol:

• The update method can only update a row if this row can be identified: make sure your database table has a primary key. Documentation.
• The update method can only update a row if a record type provides a non-nil value for this primary key in its implementation of the PersistableRecord protocol: make sure your record type does so. Documentation.

[voltangle]

Thanks, @groue! Updated the schema, now the id field is a primary key, but now another issue arises, the order of records in the table matters, and by default, it's sorting everything by the primary key, and that's not what I want, I don't want any sorting by a primary key. Btw, here is the implementation:

public struct ChatFilter: Codable, FetchableRecord, PersistableRecord {
    public var title: String
    public var id: Int
    public var iconName: String
    public var unreadCount: Int
    
    public init(
        title: String,
        id: Int,
        iconName: String,
        unreadCount: Int = 0
    ) {
        self.title = title
        self.id = id
        self.iconName = iconName
        self.unreadCount = unreadCount
    }
}

[voltangle]

Anyways, found a workaround using an order column, big thanks for help!

[groue]

Great, @ggoraa, I'm happy you could make it work 👍

Strictly speaking, the order method is not a "workaround", because any specific ordering must always be made explicit if your app relies on it. This is because databases won't guarantee any ordering unless specified. The default order by primary key is not even something you can rely on, because any future version of operating system and SQLite could change its mind! The rule is: when you need an ordering, make it explicit with the order method!

Happy GRDB!

[voltangle]

No, you didn't quite understood me, i mean an order column, not a method

Like this:

And then I just use .order(Column("order").asc) method when fetching :)

[groue]

Oh, I was confused! Thanks for clarifying :-)