Better ways of using Room Database

This article is not another complete Room guide. There are tens of them. Instead, I wrote about better ways of using it, mistakes and tips leveraging changes from Room v2.1.0+.

This article covers:

• Do you need a database?
• Better data queries (the main topic)
• Making sure that your database works
• Additional tips

Do you need a database?

SQLite database used in Room is not always superior to Shared Preferences.

Before talking about better data queries we need to consider if and why we need Room or Sqlite at all.

A Shared Preferences file is a xml file with a map of keys and values. So every time you want to change 1 value you store there, you have to override the whole file. Doesn’t it seem chunky? It’s fast enough if you don’t store thousands of values.

But look, if you want smaller files, maybe you could just divide your data into multiple shared preference files. You can have many of them. For example, you could have separate files for users, projects, tools and so on and store models as JSONs. What a solution! Then, why don’t people do that and bother with databases?

If the data size is not the biggest reason to use a database, then it’s managing data. The biggest drawback of Shared Preferences is a lack of relationships between data. For example, you can’t optimally search, sort, filter, join and perform other operations. Of course, you can do these operations in Java or Kotlin but it’s not as efficient and readable as SQL.

That’s why you should consider shared preferences for storing small chunks of data that don’t need to be heavily managed.
Things like:

• user id
• data list filtering settings
• is it the first application launch info boolean
• WiFi settings
• other user preferences

I suggest using databases when you need structured data. Preferably lots of data. Otherwise, Shared Preferences are enough. Of course, unless you want to use a database only for learning purposes.

Better data queries

Why Bother?

When Room came out and I used it for the first time, I implemented queries pragmatically. I used @Get, @Insert, @Update, @Remove annotations provided by Room that generated SQL code for me. That was enough for smaller apps. Unfortunately, sooner or later, as applications grew, or in new bigger projects, I encountered some performance and code readability issues.

Over time I learned a lot of unknown unknowns. Some of that is general database/SQL practices. Usually, the things that Android developers often don’t think or know about.

But do you need to learn a better way of using database queries now? Look, as soon, as you learn them, better queries are not time-consuming. It’s better to use these practices right away and learn them on smaller projects. Not on an actual project with a tight deadline where you need them. At least be familiar that these things exist.

Get

When you want to count records in your database, don’t return a list of entities to count them using Java/Kotlin. Use the COUNT query.

@Query("SELECT COUNT(*) FROM employees WHERE company_id IS :companyId")
fun countEmployees(companyId: Int): Single<Int>

But that’s not the only way of using it. Let’s say that you want to get a Company entity from your database and also count its employees. Would you use 2 queries: @Get company and then additional COUNT employees query? Unfortunately, that would be an n+1 query. An anti-pattern. Then, why wouldn’t we just store an employee count in the Company entity? That’s because we would have to update the Company model every time there is a new employee (increment employee count). It’s even worse than the n+1 query.

It’s better to use a result model that returns everything we want.

class CompanyDetails {

  @Embedded
  var company: Company? = null

  @ColumnInfo(name = "employee_count") var employeeCount: Int? = null

}

Our model that wraps a Company entity and an additional employee count is ready. Remember not to register the CompanyDetails model as a database entity. We don’t want Room to create a new table for this. Think of it as of a wrapper, not an entity. Now we need to use it in a new query. Room won’t figure out what “employee_count” is without any instructions.

@Transaction
@Query(
  """
  SELECT C.*,
      (SELECT COUNT(E.*) FROM employees E WHERE E.company_id = :companyId) AS employee_count
  FROM companies C
  WHERE C.id = :companyId
"""
)
fun getCompany(companyId: Int): Single<CompanyDetails>

This query is annotated with @Transaction. You should use transaction annotation when you perform more than 1 operation and you need all of your operations executed or none if something fails. So if companies count fails, we don’t accept the result. We want both operations to finish successfully. It’s even more important in insert queries.

If we want to, we can nest other entities using nested relations, like Employee.

class CompanyDetails {

  @Embedded
  var company: Company? = null

  @ColumnInfo(name = "empolyee_count") var employeeCount: Int? = null

@Relation(
  parentColumn = "id",
  entityColumn = "company_id",
  entity = Employee::class
)
var employees: List<Employee>? = null

}

One company can have multiple employees. In this case, any Employee entity will do as long as it has the “company_id” table. The value of the “company_id” of each employee you store in the database, will be compared to the “id” of the company. If it matches, it will be returned. As described in @Relation annotation.

Note that when we add a Relation annotation, we don’t need to add anything to SQL GET queries.

Now, the Employee is an entity. Instead of a list of Employees, you could even return another entity wrapper model (like CompanyDetails), for example, List<EmployeeDetails>. So you can nest wrappers inside of other wrappers and so on. Of course, until Room breaks down. There is a point when Room gets confused. Also, there is a point when you need a WHERE query for your relations. Unfortunately, Room doesn’t allow you to use it within the @Relation annotation. There are pending requests for this feature on issue tracker though. In simple cases DatabaseView may be sufficient.

Remember to create separate GET queries for various use cases. For example, you may need a Single<CompanyDetails> result for “Company Details” screen and DataSource.Factory<Int, SimpleCompany> for a “Companies” screen. You can run into serious performance issues if you query complicated models everywhere.

Many to many

This was a one-to-many query. But if many companies can have many employees and employees work for many companies? You can use a many-to-many relation like below:

  class CompanyDetails {
    @Embedded
    var company: Company? = null
    @ColumnInfo(name = "empolyee_count") var employeeCount: Int? = null
    @Relation(
      parentColumn = "id",
      entityColumn = "id",
      entity = Employee::class,
      associateBy = Junction(
        value = CompanyEmployeeJoin::class,
        parentColumn = "company_id",
        entityColumn = "employee_id"
      )
    )
    var employee: List<Employee>? = listOf()
}

 

Leverage the new associateBy property (came out in Room 2.1.0) to declare your junction table.

Now you need a CompanyEmployeeJoin entity with 2 tables: company_id and employee_id

@Entity(
  tableName = "company_employee_join",
  primaryKeys = ["employee_id", "company_id"],
  foreignKeys = [
    ForeignKey(
      entity = Employee::class,
      parentColumns = ["id"],
      childColumns = ["employee_id"],
      onDelete = ForeignKey.CASCADE,
      onUpdate = ForeignKey.CASCADE
    ),
    ForeignKey(
      entity = Company::class,
      parentColumns = ["id"],
      childColumns = ["company_id"],
      onDelete = ForeignKey.CASCADE,
      onUpdate = ForeignKey.CASCADE
    )
  ],
  indices = [Index("employee_id"), Index("company_id")]
)
data class CompanyEmployeeJoin(
  @ColumnInfo(name = "employee_id") val employeeId: Int,
  @ColumnInfo(name = "company_id") val companyId: Int
)

This is an association table, known better in Android as a junction table or join table. When you map Company with Employee, you can name it as: “CompanyEmployeeJoin”, “CompanyEmployee”, “CompanyHasEmployee”, “CompanyToEmployeeMap” or even “Contracts” if that is more informative to you.

Don’t let the code fool you. The join entity only looks complicated but it’s really simple and you can copy-paste everything and only change names. Also, there are many in-depth tutorials on this topic.

Disclaimer:
I don’t want to write a detailed guide about database relationships. For more information about nested objects in Room, check out the documentation.  

Insert / Update

Insert queries can return the id of a created entity. Though, if you use OnConflictStrategy.IGNORE and you try to insert an entity that already exists in your database, the query will return -1. On the other hand, when you use OnConflictStrategy.REPLACE and the entity exists, it will replace it but also generate a new id (if you use auto-generated ids). Be aware of that, I spent hours on debugging this issue when I assumed that id would stay the same.

So you may want to use @Update query instead. But this query can be easily misunderstood too. It returns the number of affected queries, not the id of the updated entity. Anyway, @Update works well in many cases.

Though you may want to create custom Update queries to update only certain columns. You can use a custom Update query.

It’s especially useful for situations like getting data from API and storing it in-app. Models from API usually come with an identifier (in our case “backend_id”) that we can use in the WHERE clause. When you receive data from API you have to know if you already store it in your database or not. That’s where the “upsert” function comes in handy. It means- update if exists, else insert.

// DAO
@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insertEmployee(element: Employee): Long
@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insertJoin(element: CompanyEmployeeJoin): Long
@Transaction
fun upsert(companyId: Int, employee: Employee) {
  val id = insert(employee)
  if (id == -1L) {
    rawSql(Employee.updateQuery(element))
  } else {
    insertJoin(CompanyEmployeeJoin(companyId = companyId, employeeId = id))
  }
}
// Entity
@Entity(
tableName = "employee",
foreignKeys = [
  ForeignKey(
    entity = Company::class,
    parentColumns = ["id"],
    childColumns = ["company_id"],
    onDelete = ForeignKey.CASCADE,
    onUpdate = ForeignKey.CASCADE
  )
]
)
data class Employee(
@ColumnInfo(name = "name") val name: String? = null,
@ColumnInfo(name = "description") val description: String? = null,
@ColumnInfo(name = "company_id") val companyId: Int? = null,
@ColumnInfo(name = "backend_id") val backendId: String? = null,
@ColumnInfo(name = "updated_at") val updatedAt: OffsetDateTime? = null,
...
) {
@ColumnInfo(name = "id")
@PrimaryKey(autoGenerate = true)
var id: Int = 0
companion object {
  fun updateQuery(element: Employee): SimpleSQLiteQuery {
    val query = """
      UPDATE OR ABORT employee SET
        name = ?1,
        description = ?2,
        updated_at = ?3,
      WHERE backend_id = ?4
    """.trimIndent()
    val args = arrayOf(
      element.name,
      element.description,
      DateTimeConverter.fromDateTime(OffsetDateTime.now(ZoneId.of("UTC"))),
      element.backendId
    )
    return SimpleSQLiteQuery(query, args)
  }
}
}

If you want to create a new employee, first insert a new Employee entity. Using id returned by your insert query, create a CompanyEmployeeJoin record. Why in this order? Because otherwise, you will run into code 787 Foreign Key Constraint Failed exception. If you use ForeignKey, it means that related records you refer to must exist in the database (That’s why you have to be sure that the Company entity also exists in database).

You should insert entities in a single transaction query, to make sure, that everything is saved. By the way, Room executes simple @Delete @Update and @Query with SET command queries in a transaction by default. So sometimes it’s redundant but if you are not sure, it’s better to do it yourself.

Use IGNORE strategy and -1 results we covered before, to know if the record already exists in the database.

Now let’s try to change only a description of a company. Our company entity can big and we don’t want to construct the whole model. Don’t fill up information about employee’s pets when you only want to change his description.

val employee = Employee(
  name,
  newDescription,
  age,
  specialization,
  certificates,
  hasSignedAgreement,
  mother,
  father,
  pets
)

Neither we want to do something like that. It’s bearable but there is a better solution.

fun updateEmployeeDescription(id: Int, newDescription: String) {
  return getEmployee(id)
    .map { it.copy(description = newDescription) }
    .map { updatedEntity -> upsertEmployee(updatedEntity) }
}

It’s best to use a custom SQL query that updates only description (and timestamps if exist).

Query("UPDATE employee SET description = :newDescription, updated_at = :updatedAt WHERE id = :employeeId")
fun updateDescription(employeeId: Int, newDescription: String, updatedAt: OffsetDateTime): Completable

As you can see, it’s not that hard. This is especially convenient when your entity gets bigger. This way it’s much harder to make a mistake and update a table you didn’t intend to change or so.

Remove

Sometimes we need to archive something instead of completely removing it (hard deleting). We can soft delete records or archive them by using either a new column or a new “archive” table. A new column is usually boolean value or a time stamp. For example, to soft delete something, we can use a SET command, we talked about earlier.

@Query("UPDATE employee SET archived_at = :archivedAt, updated_at = :archivedAt WHERE id = :employeeId")
fun softDeleteEmployee(employeeId: Int, archivedAt: OffsetDateTime): Completable

Why have I used a timestamp instead of a boolean value? The former simply gives us more information.
So is it soft deleted now? It’s not yet, now we need to filter other queries, so we don’t return and display soft deleted queries in the app. Using the WHERE clause everywhere can be a lot of boilerplate. If you want to avoid it, you can use archive tables.

How to know if you should use an archive table or soft delete? If you want to let users access the archived data, it may be better to use the soft delete method. You just have to control the WHERE archived_at IS NULL clause. Whereas archive tables may end up generating more problems and boilerplate when used incorrectly.

Now, what is the practical use of the soft delete? An application that works in offline mode. When a user removes an employee, simply soft delete it in the database. Then send a DELETE request to API. And guess what, API will probably archive it instead of hard-removing. Finally, after a positive response from API, we can hard-delete it in the database. In this case, we can also use a status enum.

@Query("UPDATE employee SET sync_status = :status, synced_at = :updatedAt, updated_at = :updatedAt WHERE id = :employeeId")
fun setEmployeeStatus(employeeId: Int, status: SyncStatus, updatedAt: OffsetDateTime): Int

enum class SyncStatus {
  UNKNOWN,
  PENDING,
  IN_PROGRESS,
  SUCCESS,
  ERROR,
  REMOVED
}

Now if API is temporarily broken and returns an error, we can change the status to ERROR and display retry/cancel options. Else, we can set it to REMOVED. Error handling in offline-first applications is big enough for a few standalone articles, I won’t cover everything here. But that’s not the point, I just want to give you some hints.

By the way, enums like SyncStatus are very helpful when dealing with WorkManager (hence retry/cancel options). Especially for error handling in Workers, like changing statuses when a worker fails, retries, and finally succeeds.

Making sure that DB works

So now we have a bug-free and optimal database. Well, in theory. We can’t avoid all issues and ultimately we will want to see what’s going inside the database.

Where to find the database file

To find a database file, use Device Explorer

Find your application in /data/data/(your.app.package.here)/databases. Make sure that your phone is rooted to have permission to access this directory. Or else, use any emulator from Android Studio.

How to browse the database

To browse the DB file, you can use DBBrowser or any other preferably light application.

Sometimes you may have a .mwb file with EER diagram of a database, that you may want to preview. You can use MySQL Workbench.

Use migration tests

Before you release your app with any database updates, make sure to add migration tests. As in this article. If I had to pick the only type of tests to write in an application, I would pick the database migration tests. When migrations don’t run successfully, you can end up with a completely broken application.

Tips

• If you use RxJava, make sure you use Room 2.1.0+. This way you don’t have to wrap some of your queries in RxJava sources. In Room 2.1.0 and higher, you can set query function return type to Completable, Single, Observable, Flowable or any other. There is no need for wrapping non-reactive result types into Single.fromCallable, Completable.defer and others.
• If you want to add a default value to your entity columns, you can use a defaultValue property in your @ColumnInfo annotations.
• If your entity class inherits some kind of BaseEntity, leverage ignoredColumns property in @Entity to ignore redundant columns.
• Make sure to learn about Foreign Keys if you don’t know much about them yet to ensure good data integrity.

Stay in touch

If you like articles like this one, subscribe to my newsletter below!