Once you opt for using Room in your Android application, you must commit to a delicate process of database schema management. Going forward, whenever you’ll want to change the schema of your database, you’ll need to define so-called database migrations.

In this article, which is the third in my series of Room tutorials (part 1, part 2), I’ll explain what database migrations are and show how to perform them with Room.

Database Migrations

Let’s say that you have this Room entity:

@Entity(tableName = "appMemoryInfo")
data class AppMemoryInfo(
    @field:ColumnInfo(name = "id") @PrimaryKey(autoGenerate = true) val id: Long,
    @field:ColumnInfo(name = "consumedMemory") val consumedMemory: Float,
)

Room will generate this table based on the above specification (the values are just dummies):

Now imagine that you realize later that you need the timestamp at which each measurement was obtained. No problem, just add the corresponding property to AppMemoryInfo:

@Entity(tableName = "appMemoryInfo")
data class AppMemoryInfo(
    @field:ColumnInfo(name = "id") @PrimaryKey(autoGenerate = true) val id: Long,
    @field:ColumnInfo(name = "consumedMemory") val consumedMemory: Float,
    @field:ColumnInfo(name = "timestamp") val timestamp: Float,
)

Consequently, Room will generate an expanded table:

If you install your application from scratch after this update, everything will be fine. However, if your users will update their apps from the previous version to the new one, the app will crash on them. The problem is that while Room can create a new table with updated scheme alright, it doesn’t know what to do with the existing data if the table is already there. So, it just fails fast.

This situation is not exclusive to Room. Every relational database will behave similarly when it doesn’t know how to migrate the existing data from the previous schema to the new one. That’s where you, the developer, should step in and provide explicit migration instructions that the database will perform. Every instance of these instructions, which you define for each increment in database’s version, is called a migration.

Database Migrations with Room

Now let’s understand how you define migrations with Room.

First, after every change to the schema, you must increment the database version number:

@Database(
    entities = [AppMemoryInfo::class],
    version = 2
)
abstract class MyRoomDatabase : RoomDatabase() {
    abstract val appMemoryInfoDao: AppMemoryInfoDao
}

Room supports two types of migrations: automatic and manual. I’ve never used automatic migrations myself and, frankly, I don’t understand why they’re needed. As far as I can tell from the docs, automatic migrations support only the simplest of migrations, which don’t take more than several minutes to implement manually. Furthermore, automatic migrations become very cumbersome or even useless in more complex cases.

To implement a manual migration that handles the addition of a timestamp to AppMemoryInfo entity, let’s define this Migration_1_2 class (use whatever naming works for you):

class Migration_1_2: Migration(1, 2) {
    override fun migrate(db: SupportSQLiteDatabase) {
        db.execSQL("ALTER TABLE `appMemoryInfo` ADD COLUMN `timestamp` INTEGER NOT NULL")
    }
}

Note these aspects:

• Migration_1_2 extends Room’s Migration class and passes into its constructor the start (before the migration) and the end (after) versions of the database, respectively.
• The overriden migrate method is the place for your custom migration code. In this simple case, I add timestamp column to appMemoryInfo table.
• Manual migrations use raw SQL statements.

Just like with queries, I really like the fact that Room allows me to write raw SQL to handle migrations. In my opinion, that’s simpler than learning some tricky DSL (like the automatic Room migrations API).

Once we have the Migration_1_2 class, we need to hook it up to the database. So, when you instantiate MyRoomDatabase, pass an instance of Migration_1_2 to its builder’s addMigrations method:

 myRoomDatabase = Room.databaseBuilder(
            context,
            MyRoomDatabase::class.java,
            DatabaseConstants.DATABASE_NAME,
        ).apply {
            addMigrations(Migration_1_2())
        }.build()

Now you’re done. When your users will update the application and Room will notice that the database version changes from 1 to 2, it’ll delegate to Migration_1_2 instance to handle the migration.

The long-term result of this process is that you’ll have a collection of Migration objects bound to MyRoomDatabase, one for each increment of the database version.

Destructive Migrations

When you instantiate MyRoomDatabase, instead of specifying migrations, you can use fallbackToDestructiveMigration method. This will cause the database to wipe all data clean when the version changes. In that case, Room can simply destroy the previous tables and create new ones, so no migration will ever be needed. Use this approach with great caution because you can accidentally destroy user’s data.

There is also fallbackToDestructiveMigrationFrom variant that takes a list of versions as its argument. This configuration will apply destructive migration only if migrating from one of the versions in the list. A bit less dangerous approach, but still risky.

In general, if you’re using Room, chances are that you need to persist some data. So, you probably wouldn’t want the entire database to be nuked when you add some column, in most cases.

Trick for Simplifying the Writing of Migration SQL Code

Room generates code based on the configuration you specify. So, for MyRoomDatabase class, Room will generate MyRoomDatabase_Impl class, which you can inspect. There is much code there, but what I want you to concentrate on is createAllTables method (belongs to one of the internal classes).

For version 1 of the database, before I edited AppMemoryInfo entity, that’s what I had:

@Override
public void createAllTables(@NonNull final SupportSQLiteDatabase db) {
  db.execSQL("CREATE TABLE IF NOT EXISTS `appMemoryInfo` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `consumedMemory` REAL NOT NULL)");
  db.execSQL("CREATE TABLE IF NOT EXISTS room_master_table (id INTEGER PRIMARY KEY,identity_hash TEXT)");
  db.execSQL("INSERT OR REPLACE INTO room_master_table (id,identity_hash) VALUES(42, '2f9b03f2898b256266ed42b5d788f20e')");
}

After I added the timestamp property and rebuilt the project:

@Override
public void createAllTables(@NonNull final SupportSQLiteDatabase db) {
  db.execSQL("CREATE TABLE IF NOT EXISTS `appMemoryInfo` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `timestamp` INTEGER NOT NULL, `consumedMemory` REAL NOT NULL)");
  db.execSQL("CREATE TABLE IF NOT EXISTS room_master_table (id INTEGER PRIMARY KEY,identity_hash TEXT)");
  db.execSQL("INSERT OR REPLACE INTO room_master_table (id,identity_hash) VALUES(42, 'db6cf3c40726de5eae41f0b3d4e42051')");
}

By comparing these pieces of code, you’ll notice that the main difference is the additional timestamp INTEGER NOT NULL column in appMemoryInfo table. Well, that’s exactly the part that I added in Migration_1_2!

Granted, in this case, it’s very simple migration, but this trick works for much more complex migrations as well. It’s especially handy if you add new tables because, then, you can just copy-paste the entire table creation SQL (which can get really long for complex tables).

Conclusion

If you decide to use SQLite database in your application, whether through Room or otherwise, you’ll most probably need to deal with data migrations. I hope that this article gave you the tools to handle this task.

As usual, thank you for reading and please consider subscribing to my email list if you liked this article.