Transactional database updates when needed

[opatry]

Some action requires several DB updates.

ex: Moving a task to a new list must first create the list, then create the task, then update tasks positions of both source & destination lists.

Having a DB transaction to achieve this would be desirable.

It appears that it's currently not available (?) in Room KMP.

@Transaction in Dao isn't enough, we need several Dao calls interleaved with logic in Kotlin.

Why RoomDatabase.withTransaction is defined in RoomDatabase.android.kt and not available commonly?
Is the db.useWriterConnection { it.immediateTransaction } the way to go in shared code? It feels like a workaround.

Given this answer, there might be another direction to explore:
https://kotlinlang.slack.com/archives/C7KS458SJ/p1744951192031609?thread_ts=1731240823.762209&cid=C7KS458SJ

Phuc YNWA:

… Room Multiplatform has function Transactor.withTransaction (https://android.googlesource.com/platform//frameworks/support/+/refs/heads/androidx-main/[…]roidx/room/Transactor.kt?autodive=0%2F%2F%2F%2F%2F%2F%2F), you can get it as a receive parameter in useWriterConnection block.

suspend fun <R> RoomDatabase.useWriterConnection(block: suspend (Transactor) -> R): R  =
   withContext(getCoroutineScope().coroutineContext) { useConnection(isReadOnly = false, block) }
       .also { invalidationTracker.refreshAsync() }

For example:

database.useWriterConnection {
   it.withTransaction(Transactor.SQLiteTransactionType.IMMEDIATE) {
        userDao.insertAll(entities)
   }
}

[opatry]

Check if it improves/fixes

• Investigate (and ideally fix) why there is a 2 step update with a delay in task move actions #171
• Despite task position recomputation, it takes some time (sync?) to update task menu state #137