Android Room Persistence Library: Upsert

Question

Android's Room persistence library graciously includes the @Insert and @Update annotations that work for objects or collections. I however have a use case (push notifications containing a model) that would require an UPSERT as the data may or may not exist in the database.

Sqlite doesn't have upsert natively, and workarounds are described in this SO question. Given the solutions there, how would one apply them to Room?

To be more specific, how can I implement an insert or update in Room that would not break any foreign key constraints? Using insert with onConflict=REPLACE will cause the onDelete for any foreign key to that row to be called. In my case onDelete causes a cascade, and reinserting a row will cause rows in other tables with the foreign key to be deleted. This is NOT the intended behavior.

Solution 1

Perhaps you can make your BaseDao like this.

secure the upsert operation with @Transaction, and try to update only if insertion is failed.

@Dao
public abstract class BaseDao<T> {
    /**
    * Insert an object in the database.
    *
     * @param obj the object to be inserted.
     * @return The SQLite row id
     */
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    public abstract long insert(T obj);

    /**
     * Insert an array of objects in the database.
     *
     * @param obj the objects to be inserted.
     * @return The SQLite row ids   
     */
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    public abstract List<Long> insert(List<T> obj);

    /**
     * Update an object from the database.
     *
     * @param obj the object to be updated
     */
    @Update
    public abstract void update(T obj);

    /**
     * Update an array of objects from the database.
     *
     * @param obj the object to be updated
     */
    @Update
    public abstract void update(List<T> obj);

    /**
     * Delete an object from the database
     *
     * @param obj the object to be deleted
     */
    @Delete
    public abstract void delete(T obj);

    @Transaction
    public void upsert(T obj) {
        long id = insert(obj);
        if (id == -1) {
            update(obj);
        }
    }

    @Transaction
    public void upsert(List<T> objList) {
        List<Long> insertResult = insert(objList);
        List<T> updateList = new ArrayList<>();

        for (int i = 0; i < insertResult.size(); i++) {
            if (insertResult.get(i) == -1) {
                updateList.add(objList.get(i));
            }
        }

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

Solution 2

For more elegant way to do that I would suggest two options:

Checking for return value from insert operation with IGNORE as a OnConflictStrategy (if it equals to -1 then it means row wasn't inserted):

@Insert(onConflict = OnConflictStrategy.IGNORE)
long insert(Entity entity);

@Update(onConflict = OnConflictStrategy.IGNORE)
void update(Entity entity);

@Transaction
public void upsert(Entity entity) {
    long id = insert(entity);
    if (id == -1) {
        update(entity);   
    }
}

Handling exception from insert operation with FAIL as a OnConflictStrategy:

@Insert(onConflict = OnConflictStrategy.FAIL)
void insert(Entity entity);

@Update(onConflict = OnConflictStrategy.FAIL)
void update(Entity entity);

@Transaction
public void upsert(Entity entity) {
    try {
        insert(entity);
    } catch (SQLiteConstraintException exception) {
        update(entity);
    }
}

Solution 3

I could not find a SQLite query that would insert or update without causing unwanted changes to my foreign key, so instead I opted to insert first, ignoring conflicts if they occurred, and updating immediately afterwards, again ignoring conflicts.

The insert and update methods are protected so external classes see and use the upsert method only. Keep in mind that this isn't a true upsert as if any of the MyEntity POJOS have null fields, they will overwrite what may currently be in the database. This is not a caveat for me, but it may be for your application.

@Insert(onConflict = OnConflictStrategy.IGNORE)
protected abstract void insert(List<MyEntity> entities);

@Update(onConflict = OnConflictStrategy.IGNORE)
protected abstract void update(List<MyEntity> entities);

@Transaction
public void upsert(List<MyEntity> entities) {
    insert(models);
    update(models);
}

Solution 4

If the table has more than one column, you can use

@Insert(onConflict = OnConflictStrategy.REPLACE)

to replace a row.

Reference - Go to tips Android Room Codelab

Solution 5

This is the code in Kotlin:

@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(entity: Entity): Long

@Update(onConflict = OnConflictStrategy.REPLACE)
fun update(entity: Entity)

@Transaction
fun upsert(entity: Entity) {
  val id = insert(entity)
   if (id == -1L) {
     update(entity)
  }
}

View more solutions

Comments

[jcuypers]

you might want to make it more efficient and check for return values. -1 signals conflict of whatever kind.

[Tunji_D]

this works well for individual entities, but is hard to implement for a collection. It'd be nice to filter what collections were inserted and filter them out from the update.

[Ohmnibus]

Better mark the upsert method with the @Transaction annotation

[Tunji_D]

@DanielWilson it depends on your application, this answer works well for single entities, however it's not applicable for a list of entities which is what I have.

[Tunji_D]

This would be bad for performance as there will be multiple database interactions for every element in the list.

[yeonseok.seo]

@Tunji_D can you be more specific? Which part is bad for performance?

[Tunji_D]

for each insert in the for loop, Room will create and execute multiple sql statements and transactions, each with their respective Java Object allocations and garbage collection overhead. This article goes into a bit more detail hackernoon.com/…

[yeonseok.seo]

but, there is NO "insert in the for loop".

[Tunji_D]

you're absolutely right! I missed that, I thought you were inserting in the for loop. That's a great solution.

[Sebastian Corradi]

I guess the proper way to do this is asking if the value was already on the DB (using its primary key). you can do that using a abstractClass (to replace the dao interface) or using the class that call to the object's dao

[Benoit Duffez]

This is gold. This led me to Florina's post, which you should read: medium.com/androiddevelopers/7-pro-tips-for-room-fbadea4bfbd‌​1 — thanks for the hint @yeonseok.seo!

[Pablo Rodriguez]

When you check if the id is -1, shouldn't it be -1L ? or it does not matter at all?

[yeonseok.seo]

@PRA as far as i know, it doesn't matter at all. docs.oracle.com/javase/specs/jls/se8/html/… Long will be unboxed to long and integer equality test will be performed. please point me to the right direction if i'm wrong.

[ElliotM]

For whatever reason, when I do the first approach, inserting an already existing ID returns a row number greater than what exists, not -1L.

[Alexandr Zhurkov]

Please don't use this method. If you have any foreign keys looking at your data, it will trigger onDelete listener and you probably don't want that

[aurilio]

@yeonseok.seo In case of autogenerate primary key we would need to query for object first and then only we can update it. Do you know any other way ?

[Vikas Pandey]

@AlexandrZhurkov, I guess it should trigger on update only, then any listener if implemented this would do it correctly. Anyway if we have listener on the data and onDelete triggers then it must be handled by code

[Barryrowe]

Are there known scenarios where a conflict will not return -1L? It doesn't seem like I ever get a conflict result, the current rowId (in my case the PK since it's an Int) just returns whether a conflict is found or not.

[Levon Vardanyan]

@Ohmnibus no ,because documentation says > Putting this annotation on an Insert, Update or Delete method has no impact because they are always run inside a transaction. Similarly, if it is annotated with Query but runs an update or delete statement, it is automatically wrapped in a transaction. See Transaction doc

[Ohmnibus]

@LevonVardanyan the example in the page you linked show a method very similar to upsert, containing an insert and a delete. Also, we're not putting the annotation to an insert or update, but to a method that contains both.

[m3g4tr0n]

In Kotlin Methods marked with @Transaction should be open like open fun upsert(List<MyEntity> entities)

[ubuntudroid]

@AlexandrZhurkov This works well when setting deferred = true on the entity with the foreign key.

[isabsent]

What do you mean? ON CONFLICT UPDATE SET a = 1, b = 2 is not supported by Room @Queryannotation.

[Kibotu]

long id = insert(entity) should be val id = insert(entity) for kotlin

[binrebin]

@Sam, how to deal with null values where I do not want to update with null but retain old value. ?

[Shadow]

@ubuntudroid It does not work well even when setting that flag on the entities foreign key, just tested. The delete call still goes through once the transactions complete because it does not get dismissed during the process, it just doesn't occur when it happens but at the end of the transaction still.

[Rawa]

This seems like it is flawed? It does not properly create a transaction.

[Dr.jacky]

As Ohmnibus said on the other answer, better mark the upsert method with the @Transaction annotation – stackoverflow.com/questions/45677230/…

[Hylke]

Can you explain why the @Update annotation has a conflict strategy of FAIL or IGNORE? In what cases will Room consider an update query to have a conflict anyways? If I would naively interpret the conflict strategy on the Update annotation, i would say that when there is something to update there is a conflict, and thus it will never update. But thats not the behaviour im seeing. Can there even be conflicts on update queries? Or do conflicts arise if an update causes another unique key constrain to fail?

[The incredible Jan]

@m3g4tr0n Why? "open" is for supporting extensions. Why should this matter here? I can't see the connection.

[zafar142003]

OnConflictStrategy.FAIL is deprecated now in version 2.3.0. According to documentation "OnConflictStrategy.FAIL does not work as expected. The transaction is rolled back. Use OnConflictStrategy.ABORT instead". See developer.android.com/reference/androidx/room/…

[James Bond]

The problem is this is NOT SQLite UPSERT equivalent. For example, if you have a unique key in the table, and you are inserting some row with 0 primary key and existing unique key with OnConflictStrategy.IGNORE strategy, then the row will not be inserted since unique key duplication, as expected. The problem is 0 primary key is a valid value for insert, but the wrong value for update, and the row will not be updated. In raw SQLite, you can use ON CONFLICT(word) DO UPDATE to handle this case.

[James Bond]

But you execute multiple update requests... What if you have a list of 100 items?

[James Bond]

The only one answer with real upsert... I have a feeling that other posters just don't understand that the main feature of upsert is the ability to update a row when you don't know its ID. With upsert DB can automatically update the row using unique constraint only, without a primary key, without additional requests.

[devger]

Yes, this is real UPSERT from Sqlite. But you can see it's supported only in Android 11 and 12, but in prior versions it's not supported. Now, Android Room still doesn't support annotations for UPSERT feature in Android 11 and 12 even though Sqlite on devices with this versions support it. So, we have only @Query("") option to call real UPSERT feature on Android 11 and 12. Also, the most of answers here are posted at the time when there were no Android 11 and 12, so Sqlite versions on devices were not supporting UPSERT, that's why people had to use some workarounds.