Saving cats with Insert or Update in Room

Room is one of the best Android persistence libraries, it provides a robust and efficient database layer. However, being built over SQLite, it can hide away some gotchas.

Having worked with Realm and ORMLite previously, I was used to having an Insert or Update operation (also known as Upsert) which allows for inserting or updating data in a single line of code. The way it works is simple. If data with the same primary key already exists, update the existing row, otherwise insert a new row.

Naturally the first thing I did when starting with Room, was to look for its equivalent. Most of the examples I found used this method:

@Insert(onConflict = OnConflictStrategy.REPLACE)
fun insertOrUpdate(owners: List<Owner>)

So what's the problem?

Say you have an Owner table with a foreign key pointing towards a Cat table, with a cascade delete attribute (onDelete = ForeignKey.CASCADE). It won't be long before some cats start mysteriously disappearing!

This is down to the way OnConflictStrategy.REPLACE works. Instead of being a true upsert operation it does something different under the hood. If the primary key is found in the database, instead of updating the row, SQLite deletes the row and then inserts a new row.

In most cases this isn't a problem, but since Owner has a foreign key towards Cat, with cascade delete, SQLite will delete the Cats along with the Owner. When the Owner is reinserted the Cats are not.

So what's the solution?

Room and SQLite do not have any built-in methods to upsert a row, but you can add your own. You can make all of the Room Daos extend a BaseDao which can contain our upsert method insertOrUpdate. In fact there are two, one for single objects, and one for lists of objects.

@Dao
abstract class BaseDao<T> {
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    abstract fun insert(obj: T): Long

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    abstract fun insert(obj: List<T>): List<Long>

    @Update
    abstract fun update(obj: T)

    @Update
    abstract fun update(obj: List<T>)

    @Transaction
    open fun insertOrUpdate(obj: T) {
        val id = insert(obj)
        if (id == -1L) update(obj)
    }

    @Transaction
    open fun insertOrUpdate(objList: List<T>) {
        val insertResult = insert(objList)
        val updateList = mutableListOf<T>()

        for (i in insertResult.indices) {
            if (insertResult[i] == -1L) updateList.add(objList[i])
        }

        if (!updateList.isEmpty()) update(updateList)
    }
}

Now when you update an Owner they won't lose their Cats 😻