Flip the switch and migrate (Source)

Understanding migrations with Room

Performing database migrations with the SQLite API always made me feel like I was defusing a bomb — as if I was one wrong move away from making the app explode in my users’ hands. If you’re using Room to handle your database operations, migrations are as easy as flipping a switch.

With Room, if you change the database schema but don’t upgrade the version, your app will crash. If you upgrade the version but don’t provide any migrations your app will crash or, the database tables are dropped and your users will lose their data. Don’t risk your (app’s) life by guessing w̶h̶i̶c̶h̶ ̶s̶w̶i̶t̶c̶h̶ ̶t̶o̶ ̶f̶l̶i̶p̶ how to implement migrations. Rather, understand how Room works internally, to migrate your database with confidence.

Database migrations under the hood

What SQLite API does

SQLite databases handle schema changes with the help of database versioning. More precisely, every time you alter your schema by adding, removing or modifying tables, you have to increase the database version number and update your implementation of SQLiteOpenHelper.onUpgrade method. This is how you tell SQLite what it needs to do when going from an old version to a new version.

This is also the first call that gets triggered when your app starts working with the database. SQLite will first try to handle the version upgrades and only then it will open the database.

What Room does

Room provides an abstraction layer to ease SQLite migrations in the form of the Migration class. A Migration class defines the actions that should be performed when migrating from one specific version to another. Room uses its own implementation of SQLiteOpenHelper and, in the onUpgrade method, will trigger the migrations you defined.

Here’s what happens when you access the database for the first time:

  1. The Room database is built
  2. SQLiteOpenHelper.onUpgrade method is called and Room triggers the migrations
  3. The database is opened

If you don’t provide migrations but you increase the database version, your app might crash or your data may be lost, depending on some circumstances that we will consider below.

An important part in the migration internals is played by an identity hash String that is used by Room to uniquely identify every database version. This identity hash for the current version is kept in a configuration table managed by Room in your database. So, don’t be surprised if you peek into your database to see the room_master_table table in there.


Let’s take a simple example where we have a users table, with two columns:

  • an ID, int, that is also the primary key
  • a user name, String

The users table is part of a database whose version is 1, implemented using the SQLiteDatabase API.

Let’s consider that your users are already using this version and you want to start using Room. Let’s see how Room handles a few scenarios.

Migrate SQLite API code to Room

In another post we looked at migrating your app to Room, let’s build on this and go into more detail about the data migration. Let’s assume that the User entity class and UserDao, the data access object class, are created, and focus only on the UsersDatabase class, that extends RoomDatabase.

@Database(entities = {User.class}, version = 1)
public abstract class UsersDatabase extends RoomDatabase

Scenario 1: keep the database version unchanged — app crashes

Here’s what Room is doing behind the scenes if we keep the database version unchanged and we run our app.

Step 1: Try to open the database

  • Check the identity of the database by comparing the identity hash of the current version with the one saved in the room_master_table. But, since there’s no identity hash saved, the app will crash with an IllegalStateException
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.
Room will always throw an IllegalStateException if you modify the database schema but do not update the version number.

Let’s listen to the error and increment the database version.

@Database(entities = {User.class}, version = 2)
public abstract class UsersDatabase extends RoomDatabase

Scenario 2: version increased, but no migration provided — app crashes

Now, when to run the app again Room is doing the following:

Step 1: Try to upgrade from version 1 (installed on device) to version 2

  • Since there are no migrations, the application crashes with IllegalStateException. ❌
java.lang.IllegalStateException: A migration from 1 to 2 is necessary. Please provide a Migration in the builder or call fallbackToDestructiveMigration in the builder in which case Room will re-create all of the tables.
Room will throw an IllegalStateException if you don’t provide a Migration.

Scenario 3: version increased, fallback to destructive migration enabled — database is cleared

If you don’t want to provide migrations and you specifically want your database to be cleared when you upgrade the version, call fallbackToDestructiveMigration in the database builder:

database = Room.databaseBuilder(context.getApplicationContext(),
UsersDatabase.class, "Sample.db")
.fallbackToDestructiveMigration()
.build();

Now, when to run the app again Room is doing the following:

Step 1: Try to upgrade from version 1 (installed on device) to version 2

  • Since there are no migrations and we fallback to destructive migration, the tables are dropped and the identity_hash is inserted. 🤷

Step 2: Try to open the database

  • Identity hash of the current version and the one saved in the room_master_table are the same. ✅

So now, our app doesn’t crash, but we lose all the data. So be sure that this is how you specifically want to handle migrations.


Scenario 4: version increased, migration provided — data is kept

To keep the user’s data, we need to implement a migration. Since the schema doesn’t change, we just need to provide an empty migration implementation and tell Room to use it.

@Database(entities = {User.class}, version = 2)
public abstract class UsersDatabase extends RoomDatabase {
static final Migration MIGRATION_1_2 = new Migration(1, 2) {
@Override
public void migrate(SupportSQLiteDatabase database) {
// Since we didn't alter the table, there's nothing else to do here.
}
};
database =  Room.databaseBuilder(context.getApplicationContext(),
UsersDatabase.class, "Sample.db")
.addMigrations(MIGRATION_1_2)
.build();

When running the app, Room does the following:

Step 1: Try to upgrade from version 1 (installed on device) to version 2

  • Trigger the empty migration ✅
  • Update the identity hash in the room_master_table

Step 2: Try to open the database

  • Identity hash of the current version and the one saved in the room_master_table are the same. ✅

So now, our app opens, and the user’s data is migrated! 🎉

Migration with simple schema changes

Let’s add another column: last_update, to our users table, by modifying the User class. In the UsersDatabase class we need to do the following changes:

1. Increase the version to 3

@Database(entities = {User.class}, version = 3)
public abstract class UsersDatabase extends RoomDatabase

2. Add a Migration from version 2 to version 3

static final Migration MIGRATION_2_3 = new Migration(2, 3) {
@Override
public void migrate(SupportSQLiteDatabase database) {
database.execSQL("ALTER TABLE users "
+ " ADD COLUMN last_update INTEGER");
}
};

3. Add the migration to the Room database builder:

database = Room.databaseBuilder(context.getApplicationContext(),
UsersDatabase.class, "Sample.db")
.addMigrations(MIGRATION_1_2, MIGRATION_2_3)
.build();

When running the app, the following steps are done:

Step 1: Try to upgrade from version 2 (installed on device) to version 3

  • Trigger the migration and alter the table, keeping user’s data ✅
  • Update the identity hash in the room_master_table

Step 2: Try to open the database

  • Identity hash of the current version and the one saved in the room_master_table are the same. ✅

Migrations with complex schema changes

SQLite’s ALTER TABLE… command is quite limited. For example, changing the id of the user from an int to a String takes several steps:

  • create a new temporary table with the new schema,
  • copy the data from the users table to the temporary table,
  • drop the users table
  • rename the temporary table to users

Using Room, the Migration implementation looks like this:

static final Migration MIGRATION_3_4 = new Migration(3, 4) {
@Override
public void migrate(SupportSQLiteDatabase database) {
// Create the new table
database.execSQL(
"CREATE TABLE users_new (userid TEXT, username TEXT, last_update INTEGER, PRIMARY KEY(userid))");
// Copy the data
database.execSQL(
"INSERT INTO users_new (userid, username, last_update) SELECT userid, username, last_update FROM users");
// Remove the old table
database.execSQL("DROP TABLE users");
// Change the table name to the correct one
database.execSQL("ALTER TABLE users_new RENAME TO users");
}
};

Multiple database version increments

What if your users have an old version of your app, running database version 1, and want to upgrade to version 4? So far, we have defined the following migrations: version 1 to 2, version 2 to 3, version 3 to 4, so Room will trigger all migrations, one after another.

Room can handle more than one version increment: we can define a migration that goes from version 1 to 4 in a single step, making the migration process faster.

static final Migration MIGRATION_1_4 = new Migration(1, 4) {
@Override
public void migrate(SupportSQLiteDatabase database) {
// Create the new table
database.execSQL(
"CREATE TABLE users_new (userid TEXT, username TEXT, last_update INTEGER, PRIMARY KEY(userid))");

// Copy the data
database.execSQL(
"INSERT INTO users_new (userid, username, last_update) SELECT userid, username, last_update FROM users");
// Remove the old table
database.execSQL("DROP TABLE users");
// Change the table name to the correct one
database.execSQL("ALTER TABLE users_new RENAME TO users");
}
};

Next, we just add it to the list of migrations:

database = Room.databaseBuilder(context.getApplicationContext(),
UsersDatabase.class, "Sample.db")
.addMigrations(MIGRATION_1_2, MIGRATION_2_3, MIGRATION_3_4, MIGRATION_1_4)
.build();
Note that the queries you write in the Migration.migrate implementation are not compiled at run time, unlike the queries from your DAOs. Make sure that you’re implementing tests for your migrations.

Show me the code

You can check out the implementation in this sample app. To ease the comparison every database version was implemented in its own flavor:

  1. sqlite — Uses SQLiteOpenHelper and traditional SQLite interfaces.
  2. room — Replaces implementation with Room and provides migration to version 2
  3. room2 — Updates the DB to a new schema, version 3
  4. room3 — Updates the DB to a new, version 4. Provides migration paths to go from version 2 to 3, version 3 to 4 and version 1 to 4.

Conclusion

Has your schema changed? Just increase the database version and write a new Migration implementation. You’ll ensure that your app won’t crash and your user’s data won’t be lost. It’s as easy as flipping a switch!

But, how do you know if you flipped the right switch? How do you test that you migrated from your SQLiteDatabase implementation to Room correctly, that you implemented the correct migration between different database versions, or that your database is indeed starting as it should in a specific version? We talked about testing migrations in detail, covering several different scenarios here: