Room DB: Advanced Data Persistence

Indexes

When a database table begins to get a lot of records in it, queries can often start to slow down. To mitigate that with SQLite, you can add an index to fields you frequently query to speed up these queries. Under the hood, the database makes a copy of the fields that you are indexing in a data structure that is more efficient to query.

Note: Check out this Wikipedia article to learn more about indexing.

In your app, there can be several list_category records, and each list_category can have multiple list_item records. More importantly, you are regularly performing queries on the list_category_id field to query for them by for selected list_category IDs. Because of that, you are going to add an index to this field.

To add an index, start by replacing the @Entity annotation of the ListItem entity with the following:

@Entity(
    tableName = "list_items",
    foreignKeys = [ForeignKey(
        entity = ListCategory::class,
        parentColumns = ["id"],
        childColumns = ["list_category_id"],
        onDelete = CASCADE)],
    indices = [Index(value = ["list_category_id"],
        name = "index_list_category_id")])

Here, you’ve added an indices property to the entity that is passing in an array of Index objects. In that array, you are then creating the Index with two fields:

• value: Which field(s) you want to index.
• name: A unique name for the index.

Note: The index name is used for things such as migrations. When you perform queries, you still query against the fields as you did before adding the index.

Now that you have your index, you’re going to need to create a migration for users who have the previous version of the schema. To do that, create a file called Migration2To3 in the migrations package and paste in the following:

@VisibleForTesting
class Migration2To3 : Migration(2, 3) {

  override fun migrate(database: SupportSQLiteDatabase) {
    database.execSQL(
        "CREATE INDEX 'index_list_category_id' ON list_items('list_category_id')")
  }
}

Next, replace your AppDatabase with the following:

//1
@Database(entities = [ListCategory::class, ListItem::class], version = 3)
abstract class AppDatabase : RoomDatabase() {

  abstract fun listCategoryDao(): ListCategoryDao

  abstract fun listItemDao(): ListItemDao

  companion object {

    @VisibleForTesting
    val MIGRATION_1_TO_2 = Migration1To2()
    //2
    @VisibleForTesting
    val MIGRATION_2_TO_3 = Migration2To3()
  }
}

In this, you have done two things:

1. Incremented the version of the schema from 2 to 3.
2. Added a reference to your new migration.

Now, you need to tell your app database builder to use the new migration by replacing the onCreate() method in ListMasterApplication with:

  override fun onCreate() {
    super.onCreate()
    ListMasterApplication.database = Room.databaseBuilder(
        this,
        AppDatabase::class.java,
        "list-master-db")
        .addMigrations(AppDatabase.MIGRATION_1_TO_2)
        .addMigrations(AppDatabase.MIGRATION_2_TO_3)
        .build()
  }

Run the app to make sure it still runs correctly after indexing.

Index Drawbacks

While indexes can be very helpful, there are some drawbacks to be aware of. One big drawback stems from the need to add a record to another data structure, which means inserts into a table with an index may take longer to perform. Another drawback is that indexes increase the amount of storage needed by the database.

Some scenarios where an index to common query fields may be beneficial:

• You read data from a table more often than you write to it.
• Query speed is more important than insert speed.

Updating Tests

Since you made changes to your code, you might want to re-run your unit tests to make sure that you haven’t broken anything. Run them and you will see the following:

Oops! This is happening because you are instantiating your own version of the migrated Room database in your getMigratedRoomDatabase() method in ListItemMigrationTest. Currently, it is only migrating your database to version 2, but Room will not work unless your database is migrated to the current version, which is 3.

To fix this, add .addMigrations(AppDatabase.MIGRATION_2_TO_3) after the .addMigrations(AppDatabase.MIGRATION_1_TO_2) in getMigratedRoomDatabase(). Your method will now look like this:

  private fun getMigratedRoomDatabase(): AppDatabase {
    //1
    val appDatabase = Room.databaseBuilder(
        InstrumentationRegistry.getTargetContext(),
        AppDatabase::class.java, TEST_DB_NAME)
        //2
        .addMigrations(AppDatabase.MIGRATION_1_TO_2)
        .addMigrations(AppDatabase.MIGRATION_2_TO_3)
        //3
        .build()
    //4
    migrationTestHelperRule.closeWhenFinished(appDatabase)
    return appDatabase
  }

Since your tests are now testing version 3 of your migration instead of version 2, rename the existing tests from the following,

@Test
fun migrating_from_1_to_2_retains_version_1_data() {
  ...
}

@Test
fun inserting_a_record_into_list_items_after_migrating_from_1_to_2_succeeds() {
  ...
}

to:

@Test
fun migrating_from_1_to_3_retains_version_1_data() {
  ...
}

@Test
fun inserting_a_record_into_list_items_after_migrating_from_1_to_3_succeeds() {
  ...
}

Because the index does not affect the structure of your data or how you access it, you do not need change any other code.

Now, run your tests and all of them will pass!

Where to Go From Here?

You can download the final project using the Download materials button at the top or bottom of this tutorial.

As you start to work with more complex data, you can use Type Converters to map database types to your own custom types. If you are new to data persistence on Android and want more background, you can check out the Saving Data on Android video course, which covers Shared Preferences, saving to files, SQLite and migrations.

As a challenge, you can also try to:

• Add the ability to delete a list item.
• Create an onClick event for each list item that allows you to edit the item and save the update to your database.

Feel free to share your feedback or findings, and please post your questions in the comments section below or in the forums. I hope that you’ve enjoyed this tutorial on advanced data persistence with Room!