How to migrate Room database painlessly

While intimidating, database migration is bound to come up from time to time. This article will guide you through the process of Room database migration.

So you've changed your models and now your app won't start. You sift through the logcat and find this error.

java.lang.IllegalStateException: Room cannot verify the data integrity. Looks like you've changed schema but forgot to update the version number. You can simply fix this by increasing the version number.

This is a lie. After increasing the version in your @Database notation, the app reports yet another error.

java.lang.IllegalStateException: A migration from 1 to 2 was required but not found. Please provide the necessary Migration path via RoomDatabase.Builder.addMigration(Migration ...) or allow for destructive migrations via one of the RoomDatabase.Builder.fallbackToDestructiveMigration* methods.

Want to know the easy way out? Allow destructive migration.

Room.databaseBuilder(context, AppDatabase::class.java, DB_NAME)
    .fallbackToDestructiveMigration()
    .build()

That's it, your app should start without a hitch. The downside to this approach is, as you might've guessed, that you'll lose all your data. To be more specific, all tables have been dropped and re-created. This is fine, as long as you're using the database exclusively for caching.

What about when you don't want to lose the data?

Theory

The goal of schema migration is to alter the database so that its tables correspond to your database models.

Unfortunately, to avoid losing the data in your app, you'll have to write some SQL. Before we jump into it though, let's see how to add a migration to the Room database.

Let's say we had a schema version 1 and we increased it to 2. Then our migration will be written like this:

1. Create a static val

val MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(database: SupportSQLiteDatabase) {
    // database.execSQL(...)
    }
}

2. Add the migration to your database builder

Room.databaseBuilder(context, AppDatabase::class.java, DB_NAME)
    .addMigrations(MIGRATION_1_2)
    .build()

Practice

Now onto the fun part, let's write some SQL! If you aren't doing anything crazy, you should make do with ALTER TABLE statements. If SQL isn't your forte, I have a cheap trick for you. The generated implementation of your abstract database class has all the SQL you'll need for now. To access it, simply click the blue gutter icon after a successful build.

In createAllTables method, you'll find all the SQL for creating your tables. If you don't care about losing your app's data in the changed tables, you can simply drop them and re-create them using this code. The migration might look something like this.

val MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(database: SupportSQLiteDatabase) {
        database.execSQL("DROP TABLE IF EXISTS `profile`")
        database.execSQL("CREATE TABLE IF NOT EXISTS `profile` (...)")
    }
}

Advanced migration

To avoid losing the content of altered tables, we have two options:

1. ALTER table - good for simple changes, like adding a column
2. Using temporary table - where ALTER table won't suffice

You can find an usage example with the ALTER command in the example application.

Migration using temporary table

• create a new temporary table with the new schema
• copy the contents from old to temporary table
• drop the old table
• rename the temporary table to the old one

Here is an example of how we merged firstname and lastname into nickname if it didn't exist and deleted those columns.

val MIGRATION_2_3 = object : Migration(2, 3) {
    override fun migrate(database: SupportSQLiteDatabase) {
        // Remove first and last name column from profile
        // create new table
        database.execSQL("CREATE TABLE IF NOT EXISTS `profile_new` (`id` INTEGER NOT NULL, `nickName` TEXT NOT NULL, `bio` TEXT NOT NULL, PRIMARY KEY(`id`))")
        // create nickname if needed
        database.execSQL("UPDATE `profile` SET `nickName` = `firstName` || ' ' || `lastName` WHERE `nickName` IS NULL")
        // copy data to new table
        database.execSQL("INSERT INTO `profile_new` (`id`, `nickName`, `bio`) SELECT `id`, `nickName`, `bio` FROM `profile`")
        // remove the old table
        database.execSQL("DROP TABLE `profile`")
        // rename new table
        database.execSQL("ALTER TABLE `profile_new` RENAME TO `profile`")
    }
}

Checkout the example application showcasing most of the code used here.

https://gitlab.com/synetech/public/blog/room-migration

With these tools, you should be able to handle the vast majority of cases. Happy migrating!