Sqldelight: After upgrading to SQLDelight 1.4.0 last_insert_rowid() stopped working on iOS.

[Ti-m]

Runtime Environment
SQLDelight version: 1.4.0
Application OS: iOS 13.5.1
Kotlin version 1.3.72

Describe the bug

I use following table

CREATE TABLE IF NOT EXISTS connection (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
start INTEGER,
end INTEGER,
has_error INTEGER AS Boolean DEFAULT 0,
error INTEGER AS Int DEFAULT -1,
message TEXT);

I insert rows like this

insertConnection:
INSERT INTO connection(start, end, has_error, error, message) VALUES (?, ?, ?, ?, ?);

With SQLDelight 1.3.0 I could query the last row like this:

getLastInsertedConnectionId:
SELECT last_insert_rowid();

Since SQLDelight 1.4.0 the query always returns 0.
If I use the query on Android it still works.
So I guess the issue is in
"com.squareup.sqldelight:native-driver:1.4.0"

[benasher44]

last_insert_rowid() only works on the connection where the write happened. There was a previous bug (in the native driver) where writes that weren't explicitly inside of a transaction would be run on the connection used for queries, but writes wrapped in an explicit transaction would be run on the connection used for writes (all writes should go to the same connection). In practice, this can cause spurious SQLITE_LOCKED-type errors that are hard to diagnose. This was fixed in 1.4.0, but as a result it means that if you want your last_insert_rowid() query to work properly, you will need to wrap it inside of a transaction to force it onto the writer connection.

[Ti-m]

Ok, thx for the information.
But this still worked on Android without using an explicit transaction.
Maybe the android driver has the bug too?

[benasher44]

That's a bit different unfortunately. Android driver relies on the SupportSqlite interfaces. For that, it then depends on which system is in use behind the Android driver. For example, you could be using Android system SQLite that comes with the OS or an open source one like requery/sqlite-android. Both implement SupportSqlite interfaces and can be used as the implementation behind your Android driver, and the behavior there could be different based on how either manages its connections. It may be that the one you're using only has one connection that's used for both reads and writes, which is why this might work there — also a valid way to manage connections.

My takeaway from this personally is that these connection-specific functions in SQLite are fragile unless you control the implementation of the driver. Your code will be more resilient by avoiding them.

[Ti-m]

Ok, thank you very much. I will avoid the function.

[benasher44]

No problem!

[joblolin95]

I have been bitten by this bug on iOS as well.

@benasher44 Do you have any proposed alternatives for retrieving last row id changed or number of rows changed, instead of using last_insert_rowid() and changes() functions?

I many times do manual upserts based on specific clauses, therefore not having reliable functions for checking how many rows were impacted after an update query is rather disconcerting.

[benasher44]

Nope sorry there are no alternatives. As I stated above, that function is connection specific. The only way to guarantee correct/reliable usage of it in SQLDelight is to use it inside of a transaction, forcing it onto the connection that has that insert information. Given the level of abstraction SQLDelight aims to provide, it's unsafe to make assumptions about the underlying SQLDelight connection uses, especially when performing reads. For example, we want to eventually allow parallel reads on native, which requires a larger connection pool. In that case, you have even fewer guarantees for reads. The newer/fixed behavior allows for exception-free concurrent reads and writes, unblocking those further concurrency improvements in the future.

[rnstewart]

So there's simply no way for us to reliably find the id of the object we just inserted? That's... kind of a serious problem.

[benasher44]

There is. You just do it inside a transaction. At the level of abstraction that SQLDelight provides, that's the most reliable way.

[rnstewart]

@benasher44, thanks for the response. Can you point me to an example of how this would work? Would I just use the transaction Kotlin call, or is there a way to do it entirely in a the SQL function?

[benasher44]

Yep! Have a look at this test updated in the PR where this change was made https://github.com/cashapp/sqldelight/pull/1788/files#diff-914a73aa60d59650179d631323939acc5edf6523191c4bcf6c4cd27bb12a7951R86-R91

You would use one of the transaction methods, such as transactionWithResult

[rnstewart]

Aha! transactionWithResult was the missing piece of the puzzle.

Thank you, @benasher44!