Android Room Database Tips and Tricks

One of the nice things about Android development is having the power of a real SQLite database to store data, along with built-in support in the framework using Room.

Room is an abstraction layer over SQLite. It has a lot of nice features, but it also has some limitations. For example, there is no way to express a one-to-one relationship and no support for an UPSERT (Insert or Update of a row). It is a relatively new library from Android, so I would expect it to improve over time.

In the meantime, here are some tips and tricks to deal with some of the limitations of Room.

Relationship Woes

Room’s syntax for defining relationships is definitely a bit clunky. The following example shows how much code is required to express the relationship between a week of the year and the days of that week—a classic one-to-many relationship.

If we also want one object with the properties of the week plus a list of days, we need to define a separate non-entity class (a “plain old Java object,” or POJO) to hold the week object plus the list of days. You’ll see that below the WorkWeekWithDays class.

@Entity(tableName = "work_week")
data class WorkWeek(
    @PrimaryKey
    @ColumnInfo(name = "start_date", index = true)
    val startDate: LocalDate,
    // ...
)

// If we wanted a database query to return the week and the list of days 
// we need to define a separate non-entity class and define the relation again.
// ForeignKeys are of no help here.
data class WorkWeekWithDays(
    @Embedded
    var week: WorkWeek? = null,

    @Relation(parentColumn = "start_date", entityColumn = "week_start_date")
    var days: List = emptyList()
)

@Entity(
    tableName = "work_day",
    foreignKeys = [
        ForeignKey(
            entity = WorkWeek::class,
            parentColumns = ["start_date"],
            childColumns = ["week_start_date"],
            onUpdate = ForeignKey.CASCADE,
            onDelete = ForeignKey.CASCADE)
    ]
)
data class WorkDay(
    @PrimaryKey
    @ColumnInfo(index = true)
    val date: LocalDate,

    @ColumnInfo(name = "week_start_date", index = true)
    val weekStartDate: LocalDate,

    // ...
)

The WorkWeekWithDays class contains two properties: one for the work week, and the other for the list of days. The @Embedded attribute is put on the “one,” and @Relation attribute is put on the “many.” Note that the @Relation has to be either a list or a set.

I should also note that I have defined foreign keys in the example above, but they are not required to query for the WorkWeekWithDays object. Foreign keys basically tell Room how to deal with updates and deletes and whether to cascade changes from the parent object to the children. Your SQL query to get the WorkWeekWithDays object would look like this:

@Dao
interface WorkWeekDao {
    @Transaction
    @Query("SELECT * FROM work_week WHERE start_date = :startDate")
    fun getWeekWithDays(startDate: LocalDate): LiveData< WorkWeekWithDays>
}

Unfortunately, Room does not have a convenient way to query for items in a one-to-one relationship. You have two options to get around this limitation. The example I will use is the relationship between a person and a passport. A person has one and only one passport.

Option 1: Use @Embedded and write the query to populate the POJO yourself.

This example uses the @Embedded attribute for each side of the one-to-one relationship. The PersonWithPassport contains both the person and the passport entity objects.

@Entity(tableName = "person")
data class Person(
    @PrimaryKey
    @ColumnInfo(index = true)
    val id: Int,
    // ...
)

data class PersonWithPassport(
    @Embedded
    var person: Person? = null,

    @Embedded
    var passport: Passport? = null
)

@Entity(
    tableName = "passport",
    foreignKeys = [
        ForeignKey(
            entity = Person::class,
            parentColumns = ["id"],
            childColumns = ["person_id"],
            onUpdate = ForeignKey.CASCADE,
            onDelete = ForeignKey.CASCADE)
    ]
)
data class Passport(
    @PrimaryKey
    @ColumnInfo(name = "passport_number", index = true)
    val passportNumber: Int,

    @ColumnInfo(name = "person_id", index = true)
    val personID: int,

    // ...
)

The query for the PersonWithPassport object has to INNER JOIN both tables to populate the person and passport @Embedded properties. Room doesn’t really help you out much in this case. You are basically dropping down to raw SQL to define what comes back in the query.

@Dao
interface PersonDao {
    @Query("SELECT passport.*, person.* FROM person INNER JOIN passport ON person.id = passport.person_id WHERE .id = :personID")
    fun getPersonWithPassport(personID: Int): LiveData< PersonWithPassport>
}

If you want multiple one-to-one relationships returned from the query, things can get quite complicated. It is probably best to take a hard look at your code to see if you even need a POJO with all of the one-to-one relationships combined into one. Returning less data will speed up the query and simplify it, as well.

Option 2: Pretend it is a one-to-many.

Another approach is to pretend the relationship is a one-to-many and only access the first item in the list. The following is the PersonWithPassport object with a @Relation attribute that is normally used for one-to-many relationships. The @Relation attribute has to be applied to a list or a set.

Unfortunately, we have to define a property called “passports” that contains a list of just one passport. I added a read-only property called “passport” that returns the first item in the list.

data class PersonWithPassport(
    @Embedded
    var person: Person? = null,

    @Relation(parentColumn = "id", entityColumn = "person_id")
    var _passports: List = emptyList()
) {
    val passport: Passport?
        get() { _passports.firstOrNull() }
}

The query in this case is much simpler because we are using the @Relation attribute. Room knows where to get the passport data because of the relation and the type of the Passport items in the list.

@Dao
interface PersonDao {
    @Transaction
    @Query("SELECT * FROM person WHERE .id = :personID")
    fun getPersonWithPassport(personID: Int): LiveData< PersonWithPassport>
}

Neither method is perfect. I don’t like the fact that I need to define a separate POJO, and I don’t like how the ForeignKeys and @Relation don’t seem to talk to one another. It seems I have to define the relationship multiple times.

Insert or Update (UPSERT)

A common pattern when receiving updated information from your API is to update an item if it already exists or insert an item if it doesn’t exist. Room has support for Insert, Delete, Update, and Query methods on your Data Access Object, but the Insert and Update operations have some issues that prevent me from doing what I want.

The problem with Update is it will not insert an item if it doesn’t exist. It will only update it if it already exists.

The Insert operation does have a conflict resolution to replace an item if it already exists. Ideally, this would have the similar effect as an UPSERT.

However, I have found that this causes unwanted consequences to foreign key constraints because it deletes an item from the database before inserting a new one. If you have generated an ID for your primary key, it will get a new number. For instance, with our one-to-many relationship example of weeks and days, inserting a week will first delete that week and all of its days from the database, then insert the new week with no days. This happens because the foreign key has a cascade effect specified in the onDelete action of the foreign key.

I have a couple workarounds for this situation.

Handling an UPSERT with individual items

In the approach, you set the OnConflictStrategy to IGNORE on the insert and have it return a Long. If it did not insert the item, it will return -1. Note: This is not the primary key but the rowID. If it returns -1, then update the item.

@Dao
interface WorkWeekDao {
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(week: WorkWeek): Long

    @Update(onConflict = OnConflictStrategy.REPLACE)
    fun update(week: WorkWeek)

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

}

Handling an UPSERT with lists of items

In this case, the insert can return a list of row IDs. From there, we can use the power of the Kotlin collection library to reduce the list of weeks to just those that need updating.

@Dao
@Dao
interface WorkWeekDao {
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(weeks: List< WorkWeek>): List< Long>

    @Update(onConflict = OnConflictStrategy.REPLACE)
    fun update(week: WorkWeek)

    @Transaction
    fun upsert(weeks: List< WorkWeek>) {
        val rowIDs = insert(weeks)
        val weeksToUpdate = rowIDs.mapIndexedNotNull { index, rowID -> 
            if (rowID == -1L) null else weeks[index] }
        weeksToUpdate.forEach { update(it) }
    }
}

Despite these annoyances, I do like Room. It saves me more time than it costs me. Hopefully, it will get some improvements as it matures.