Android Room Persistence Library: Upsert

AndroidSqliteAndroid RoomAndroid Architecture-Components

Android Problem Overview

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.

Android Solutions

Solution 1 - Android

Perhaps you can make your BaseDao like this.

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

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
	public abstract void update(T obj);

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

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

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

	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) {

		if (!updateList.isEmpty()) {

Solution 2 - Android

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);

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

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);

public void upsert(Entity entity) {
    try {
    } catch (SQLiteConstraintException exception) {

Solution 3 - Android

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);

public void upsert(List<MyEntity> entities) {

Solution 4 - Android

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 - Android

This is the code in Kotlin:

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

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

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

Solution 6 - Android

Just an update for how to do this with Kotlin retaining data of the model (Maybe to use it in a counter as in example):

//Your Dao must be an abstract class instead of an interface (optional database constructor variable)
abstract class ModelDao(val database: AppDatabase) {

@Insert(onConflict = OnConflictStrategy.FAIL)
abstract fun insertModel(model: Model)

//Do a custom update retaining previous data of the model 
//(I use constants for tables and column names)
 abstract fun updateModel(modelId: Long)
//Declare your upsert function open
open fun upsert(model: Model) {
    try {
    }catch (exception: SQLiteConstraintException) {

You can also use @Transaction and database constructor variable for more complex transactions using database.openHelper.writableDatabase.execSQL("SQL STATEMENT")

Solution 7 - Android

I found an interesting reading about it here.

It is the "same" as posted on But, if you want an idiomatic and clean Kotlin version, here you go:

    open fun insertOrUpdate(objList: List<T>) = insert(objList)
        .filter { it.value == -1L }
        .forEach { update(objList[it.index]) }

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

    abstract fun update(obj: T)

Solution 8 - Android

Alternatively to make UPSERT manually in loop like it's suggested in @yeonseok.seo post, we may use UPSERT feature provided by Sqlite v.3.24.0 in Android Room.

Nowadays, this feature is supported by Android 11 and 12 with default Sqlite version 3.28.0 and 3.32.2 respectively. If you need it in versions prior Android 11 you can replace default Sqlite with custom Sqlite project like this (or built your own) to have this and other features that are available in latest Sqlite versions, but not available in Android Sqlite provided by default.

If you have Sqlite version starting from 3.24.0 on device, you can use UPSERT in Android Room like this:

@Query("INSERT INTO Person (name, phone) VALUES (:name, :phone) ON CONFLICT (name) DO UPDATE SET")
fun upsert(name: String, phone: String)

Solution 9 - Android

Another approach I can think of is to get the entity via DAO by query, and then perform any desired updates. This may be less efficient compared to the other solutions in this thread in terms of runtime because of having to retrieve the full entity, but allows much more flexibility in terms of operations allowed such as on what fields/variable to update.

For example :

private void upsert(EntityA entityA) {
   EntityA existingEntityA = getEntityA("query1","query2");
   if (existingEntityA == null) {
   } else {

Solution 10 - Android

Here is a way to use a real UPSERT clause in Room library.

The main advantage of this method is that you can update rows for which you don't know their ID.

  1. Setup Android SQLite support library in your project to use modern SQLite features on all devices:
  2. Inherit your daos from BasicDao.
  3. Probably, you want to add in your BasicEntity: abstract fun toMap(): Map<String, Any?>

Use UPSERT in your Dao:

private suspend fun upsert(entity: SomeEntity): Map<String, Any?> {
    return upsert(
// An entity has been created. You will get ID.
val rawEntity = someDao.upsert(SomeEntity(0, "name", "key-1"))

// An entity has been updated. You will get ID too, despite you didn't know it before, just by unique constraint!
val rawEntity = someDao.upsert(SomeEntity(0, "new name", "key-1"))


import android.database.Cursor
import androidx.sqlite.db.SimpleSQLiteQuery
import androidx.sqlite.db.SupportSQLiteQuery

abstract class BasicDao(open val database: RoomDatabase) {
     * Upsert all fields of the entity except those specified in [onConflict] and [excludedColumns].
     * Usually, you don't want to update PK, you can exclude it in [excludedColumns].
     * [UPSERT]( syntax supported since version 3.24.0 (2018-06-04).
     * [RETURNING]( syntax supported since version 3.35.0 (2021-03-12).
    protected suspend fun upsert(
        table: String,
        entity: Map<String, Any?>,
        onConflict: Set<String>,
        excludedColumns: Set<String> = setOf(),
        returning: Set<String> = setOf("*")
    ): Map<String, Any?> {
        val updatableColumns = entity.keys
            .filter { it !in onConflict && it !in excludedColumns }
            .map { "`${it}`=excluded.`${it}`" }

        // build sql
        val comma = ", "
        val placeholders = { "?" }.joinToString(comma)
        val returnings = returning.joinToString(comma) { if (it == "*") it else "`${it}`" }
        val sql = "INSERT INTO `${table}` VALUES (${placeholders})" +
                " ON CONFLICT(${onConflict.joinToString(comma)}) DO UPDATE SET" +
                " ${updatableColumns.joinToString(comma)}" +
                " RETURNING $returnings"

        val query: SupportSQLiteQuery = SimpleSQLiteQuery(sql, entity.values.toTypedArray())
        val cursor: Cursor = database.openHelper.writableDatabase.query(query)

        return getCursorResult(cursor).first()

    protected fun getCursorResult(cursor: Cursor, isClose: Boolean = true): List<Map<String, Any?>> {
        val result = mutableListOf<Map<String, Any?>>()
        while (cursor.moveToNext()) {
            result.add(cursor.columnNames.mapIndexed { index, columnName ->
                val columnValue = if (cursor.isNull(index)) null else cursor.getString(index)
                columnName to columnValue

        if (isClose) {
        return result

Entity example:


    tableName = SomeEntity.TABLE_NAME,
    indices = [Index(value = [SomeEntity.SOME_UNIQUE_KEY], unique = true)]
data class SomeEntity(
    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = ID)
    val id: Long,

    @ColumnInfo(name = NAME)
    val name: String,

    @ColumnInfo(name = SOME_UNIQUE_KEY)
    val someUniqueKey: String,
) {
    companion object {
        const val TABLE_NAME = "some_table"
        const val ID = "id"
        const val NAME = "name"
        const val SOME_UNIQUE_KEY = "some_unique_key"

    fun toMap(): Map<String, Any?> {
        return mapOf(
            ID to if (id == 0L) null else id,
            NAME to name,
            SOME_UNIQUE_KEY to someUniqueKey

Solution 11 - Android

Should be possible with this sort of statement:

INSERT INTO table_name (a, b) VALUES (1, 2) ON CONFLICT UPDATE SET a = 1, b = 2

Solution 12 - Android

If you have legacy code: some entities in Java and BaseDao as Interface (where you cannot add a function body) or you too lazy for replacing all implements with extends for Java-children.

> Note: It works only in Kotlin code. I'm sure that you write new code in Kotlin, I'm right? :)

Finally a lazy solution is to add two Kotlin Extension functions:

fun <T> BaseDao<T>.upsert(entityItem: T) {
    if (insert(entityItem) == -1L) {

fun <T> BaseDao<T>.upsert(entityItems: List<T>) {
    val insertResults = insert(entityItems)
    val itemsToUpdate = arrayListOf<T>()
    insertResults.forEachIndexed { index, result ->
        if (result == -1L) {
    if (itemsToUpdate.isNotEmpty()) {


All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionTunji_DView Question on Stackoverflow
Solution 1 - Androidyeonseok.seoView Answer on Stackoverflow
Solution 2 - Androiduser3448282View Answer on Stackoverflow
Solution 3 - AndroidTunji_DView Answer on Stackoverflow
Solution 4 - AndroidVikas PandeyView Answer on Stackoverflow
Solution 5 - AndroidSamView Answer on Stackoverflow
Solution 6 - AndroidemiruaView Answer on Stackoverflow
Solution 7 - AndroidFilipe BritoView Answer on Stackoverflow
Solution 8 - AndroiddevgerView Answer on Stackoverflow
Solution 9 - AndroidatjuaView Answer on Stackoverflow
Solution 10 - AndroidJames BondView Answer on Stackoverflow
Solution 11 - AndroidBrill PappinView Answer on Stackoverflow
Solution 12 - AndroidDaniil PavlenkoView Answer on Stackoverflow