10. Data Migration

Written by Aldo Olivares

In the last chapter, you finally learned how to integrate your Room components with other architecture components like LiveData and ViewModel to make your app display a nice set of questions to your users.

But what happens if you want to modify your database schema to organize questions by category or difficulty?

Well, in this chapter you’ll learn how Room helps you change your database schema in a predictable way by providing migrations that help you deal with your data.

Along the way you’ll learn:

• How to create a migration.
• How to add a migration to your database.
• How to perform SQLite queries.
• How to fall back to a destructive migration.

Ready? It’s time to get started.

Getting started

To begin, open the starter project, which you can find in this chapter’s attachments. Then open the project in Android Studio 3.3 or greater by going to File ▸ New ▸ Import Project and selecting the build.gradle file in the root package.

If you’ve been following along up to this point, you should already be familiar with the code. If you’re just getting started, here’s a quick recap:

• The data package contains two packages: db and model. db contains QuestionDatabase, which implements your Room database. The model package contains your entities, Question and Answer. It also includes Repository, which helps your ViewModels interact with your DAOs.
• The view package contains all your activities: MainActivity, QuestionActivity and ResultActivity.
• The viewmodel package contains the ViewModels of your class: MainViewModel and QuestionViewModel.

Once the starter project finishes loading and building, run the app on a device or emulator.

Important: Tap the START button to start a quiz, then stop the app running in Android Studio.

Cool! Now, it’s time to start working with migrations.

Migrations

Before creating your first migrations with Room, you need to learn what migrations are, right?

Simply put, a database migration or schema migration is the process of moving your data from one database schema to another. There are many reasons why you might want to move your data to another database schema. For example, you might need to add a new table because you want to implement a new feature. Some data are not available and some columns of your database can be removed. Or, the API you’re invoking provides different information and you want to add some new columns to an existing table. Or, other table can be removed because part of an A/B test and you need to save space.

The process of migrating your data from one schema to another could be as simple as copying data from one table to another or as complex as reorganizing your entire database. Either way, properly planning your migrations comes with benefits:

• Reversible: Sometimes, you might want to roll back your changes and return to an old schema. Without migrations, this process might be a complete nightmare. You’d have to manually apply all the changes, and you might not even remember how your old database looked.
• No data loss: With migrations, it’s much easier to move your data from one schema to another in a predictable manner without losing data.

Understanding Room migrations

SQLite handles database migrations by specifying a version number for each database schema you create. In other words, each time you modify your database schema by creating, deleting or updating a table, you have to increase the database version number and modify SQLiteOpenHelper.onUpgrade(). onUpGrade() will tell SQLite what to do when one database version changes to another.

For example, say one of your users still has database version 1 but a new update of your app now uses database version 2. SQLite would realize that the current database version is obsolete and needs an upgrade. Then, SQLite would look for SQLiteOpenHelper.onUpgrade(db, 1, 2) and trigger its body to migrate to the new schema. If SQLiteOpenHelper.onUpgrade(db, 1, 2) doesn’t exist, it will trigger an error.

Room migrations work in a very similar way. The difference is that Room provides an abstraction layer on top of the traditional SQLite methods with a Migration class.

Migration(startVersion, endVersion) is the base class of a database migration; it can move between any two migrations defined by the startVersion and endVersion parameters. The reason I’ve emphasized any is because you don’t necessarily need to specify a sequential migration. For example, say Room opens database version 2 and the latest version is 5. Normally, Room would execute migrations in this order:

Migration(2, 3)

Migration(3, 4)

Migration(4, 5)

The beauty of Room is that you can also specify a Migration that goes directly from version 2 to version 5 like this: Migration(2, 5), which makes the migration process much faster. Of course, there won’t always be a direct path from migration X to migration Y, so executing all you migrations one by one might be necessary, but it’s usually a good practice to specify a direct migration if possible.

If you don’t specify an appropriate migration for the current database version, Room will throw a runtime error and the app will crash.

Note: You can also call fallbackToDestructiveMigration() when building your database. This will tell Room to destructively recreate tables if you haven’t specified a migration. The advantage is that you won’t need to create any migrations and your app won’t crash. The disadvantage is that you will delete your data every time you specify a new database version.

Now that you know the theory, you can move on to creating your first Room migrations!

Creating Room migrations

Right now, you have a very nice app that displays a series of random questions to your users. You store these questions in a question table, which is represented as a Question entity class in your code.

But what happens if you want to add difficulty levels like easy, medium and hard?

Well, right now your question table doesn’t have an attribute to classify questions based on their difficulty. So your first step is to add a new column to provide that functionality to your users. Here’s how you do that:

Open Question.kt under the data ▸ model package. Right now, your entity looks like this:

@Entity(tableName = "question", indices = [Index("question_id")])
data class Question(
  @PrimaryKey(autoGenerate = true)
  @ColumnInfo(name = "question_id")
  var questionId: Int,
  val text: String
)

You want to represent the difficulty in terms of numbers such as 1, 2 or 3, where 1 is the lowest difficulty and 3 is the highest. To represent this concept, modify your question class to add a difficulty property like so:

@Entity(tableName = "question", indices = [Index("question_id")])
data class Question(

  @PrimaryKey(autoGenerate = true)
  @ColumnInfo(name = "question_id")
  var questionId: Int,
  val text: String,
  val difficulty: Int = 0 //Only this lane changes
)

This property will represent the difficulty of the question and will have a default value of 0.

Now, build and run the app and press the Start button to see if it works.

And the app crashed!

Open the logcat console and take a look at the error displayed:

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.

Note: If the app didn’t crash, you might have forgotten to press the START button earlier, when you ran the app before making the modification. If the app crashed but you got a different error message, try uninstalling the app from your device or emulator and repeating the steps above.

Did you expect that crash?

Each time you change the database schema, you need to change the database version. This will help Room know which migrations to run when building the database.

The error seems simple enough to fix, right? According to the logcat console, you just need to increase the version number, so try that now.

Open QuizDatabase.kt under the data ▸ db package and increase the database version by changing the version parameter value to 2 in the @Database notation:

@Database(entities = [(Question::class), (Answer::class)], version = 2) //version change
abstract class QuizDatabase : RoomDatabase() {
  abstract fun questionsDao(): QuestionDao
}

Build and run the app again and press START….

And the app crashes again!

Open the logcat console to see the problem. You should see the message below:

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.*

It looks like you’re making some progress, since the error is different now. The error indicates that Room doesn’t know how to change the database schema from 1 to 2, so it’s giving you two options:

• Create a migration that goes from database schema 1 to 2.

• Call fallbackToDestructiveMigration() when building your database.

The second option is the easiest one to implement since you only need to add a single line of code. The only problem with this approach is that you will lose all your data when changing the schema from version 1 to 2. This is fine for your app since you have a handy button to populate your database in the main menu, but it might not be a good idea for other projects where you want to preserve user data.

With the above in mind, you’re going to follow the first approach and create a new migration. Create a new package under the db package and name it migrations.

Inside migrations, create a new class and name it Migration1To2. Make your class extend Migration like this:

class Migration1To2 : Migration(1, 2) {
}

The first parameter in the constructor represents the start version of the database. The second one represents the end version after you’ve applied this migration.

Next, press Control + I (implement methods) so Android Studio displays all missing members. Select all of them and press OK. Your class should now have a migrate() method:

class Migration1To2 : Migration(1, 2) {
  override fun migrate(database: SupportSQLiteDatabase) {
    TODO("not implemented") //To change body of created functions use File | Settings | File Templates.
  }
}

Inside migrate(), you should execute all the queries you need to properly change the database schema to the version indicated in the constructor.

Now, since the change is a very simple one, you’ll only need to execute a single ALTER query to change your question table.

Modify migrate like this:

override fun migrate(database: SupportSQLiteDatabase) {
  database.execSQL("ALTER TABLE question ADD COLUMN difficulty INTEGER NOT NULL DEFAULT 0")
}

database.execSQL() executes the query passed as a parameter. Here, you’re executing an ALTER TABLE query in your question table that adds a new difficulty column that only accepts integers. It has a default value of 0.

Now that you’ve defined your migration, you need to tell Room to execute it before building your database.

Open QuizDatabase.kt and add the following companion object to the top of your class:

companion object {
  val MIGRATION_1_TO_2 = Migration1To2()
}

You’ll use this companion object to store a reference to all the migrations that you’ll define later.

Now, open QuizAplication.kt and modify your database builder inside onCreate():

database = Room.databaseBuilder(this, QuizDatabase::class.java, "question_database")
    .addMigrations(QuizDatabase.MIGRATION_1_TO_2) //Only this line changes
    .build()

addMigrations() accepts one or more migration objects. Room will use these migrations to bring the database to the latest version.

Build and run your app and press START to verify that your migration works properly:

Sweet! It looks like your app works now.

Next, imagine that you want to sort your questions not only by difficulty but also by a category, such as iOS or Android. This way, you can expand the functionality of your app to display Android questions to Android developers and iOS questions to iOS developers.

Expanding the functionality of your app to support categories requires some changes in your database:

• A new property inside your question entity called category of type String.
• A new migration that goes from database version 2 to 3 and handles the new schema change.
• Adding the new migration to your database builder.

Start by opening the Question.kt file and adding a new category property to your class:

@Entity(tableName = "question", indices = [Index("question_id")])
data class Question(
  @PrimaryKey(autoGenerate = true)
  @ColumnInfo(name = "question_id")
  var questionId: Int,
  val text: String,
  val difficulty: Int = 0,
  val category: String = "android" //Only this line changes
)

Now, create a new class under the migrations package and name it Migration2To3. Replace everything inside with the following:

class Migration2To3 : Migration(2, 3) {
  override fun migrate(database: SupportSQLiteDatabase) {
    database.execSQL("ALTER TABLE question ADD COLUMN category TEXT NOT NULL DEFAULT 'android'")
  }
}

Here, you’re creating a new Migration object that goes from database version 2 to 3. The query executes an ALTER TABLE statement that adds a category column of type text with a default value of android.

Open the QuizDatabase class and modify it like this

@Database(entities = [(Question::class), (Answer::class)], version = 3) //change version to version 3 
abstract class QuizDatabase : RoomDatabase() {
  companion object{
    val MIGRATION_1_TO_2 = Migration1To2()
    val MIGRATION_2_TO_3 = Migration2To3() //adds migration
  }
  abstract fun questionsDao(): QuestionDao
}

The above code changes the database version to 3 and creates a reference to your new migration.

Now, open the QuizApplication.kt file and add the migration you just created to your database builder inside onCreate():

database = Room.databaseBuilder(this, QuizDatabase::class.java, "question_database")
    .addMigrations(QuizDatabase.MIGRATION_1_TO_2, QuizDatabase.MIGRATION_2_TO_3)
    .build()

Build and run your app, then press START:

Sweet! It looks like your migration works as expected.

Until now, the changes that you’ve made to your database schema have been really simple, since you only needed to add a new column to your tables.

But what happens if you need to modify a previously-created column?

Well, it turns out that the ALTER TABLE statement is very limited; the only operations that you can perform with it are RENAME TABLE, RENAME COLUMN and ADD COLUMN.

If you want to perform complex schema changes such as changing the type affinity of a column, you’ll need to use more than one query. But don’t worry, the following steps summarize the process:

1. Create a new temporary table with the new schema.
2. Copy the data from the original table to the temporary table.
3. Drop the original table.
4. Rename the temporary table with the same name as the original table.

To illustrate this process, imagine you want to modify the type affinity of the difficulty column to TEXT instead of INTEGER so that you can store the operating system that the question refers to.

To do this, open Question.kt and modify the category property:

@Entity(tableName = "question", indices = [Index("question_id")])
data class Question(
  @PrimaryKey(autoGenerate = true)
  @ColumnInfo(name = "question_id")
  var questionId: Int,
  val text: String,
  val difficulty: String = "0", //Only this line changes  
  val category: String = "android"
)

Now, create a new class under the migrations package and name it Migration3To4. Modify everything like this:

class Migration3To4 : Migration(3, 4) {
  override fun migrate(database: SupportSQLiteDatabase) {
    database.execSQL(
      "CREATE TABLE question_new (question_id INTEGER NOT NULL, " +
          "text TEXT NOT NULL, " +
          "difficulty TEXT NOT NULL, " +
          "category TEXT NOT NULL, " +
          "PRIMARY KEY (question_id))"
    ) //1

    database.execSQL("CREATE INDEX index_question_new_question_id ON question_new(question_id)") //2

    database.execSQL(
      "INSERT INTO question_new (question_id, text, difficulty, category) " +
          "SELECT question_id, text, difficulty, category FROM question"
    )//3

    database.execSQL("DROP TABLE question") //4

    database.execSQL("ALTER TABLE question_new RENAME TO question") //5

  }
}

Briefly:

1. Creates a new temporary table with the new schema called question_new.
2. Adds an index to the question_id column of your question_new table.
3. Retrieves all the data from your original question table using a SELECT statement and adds it to your question_new table using an INSERT INTO statement.
4. Drops the original question table using a DROP TABLE statement.
5. Renames your question_new table to question using an ALTER TABLE with a RENAME TO statement.

Open the QuizDatabase class and modify it like this:

@Database(entities = [(Question::class), (Answer::class)], version = 4)//Changes the db version
abstract class QuizDatabase : RoomDatabase() {
  companion object{
    val MIGRATION_1_TO_2 = Migration1To2()
    val MIGRATION_2_TO_3 = Migration2To3()
    val MIGRATION_3_TO_4 = Migration3To4() //Adds a reference to your new migration
  }
  abstract fun questionsDao(): QuestionDao
}

Just like before, you’ve changed the database version to 4 and created a reference to your new migration inside the companion object.

Finally, add your new migration to your database by opening the QuizApplication.kt file and changing your database builder inside onCreate():

database = Room.databaseBuilder(this, QuizDatabase::class.java, "question_database")
    .addMigrations(QuizDatabase.MIGRATION_1_TO_2, QuizDatabase.MIGRATION_2_TO_3, QuizDatabase.MIGRATION_3_TO_4)
    .build()

Build and run your app, then press START:

Cool!

You might have noticed that you now have three different migrations for four different versions of your database. If one of your users had the first version of your database installed and wanted to update the app to the latest version, Room would execute each migration one by one. Since four is still a relatively low number, the process should be quick, but imagine if you had 50 versions of your database! It would be much better to have a shortcut right?

Well, Room allows you to define a migration path that starts from and goes to any version of your database. To illustrate this concept, define a migration that goes from database version 1 to 4.

Create a new class under the migrations package and name it Migration1To4. Replace everything inside with the following:

class Migration1To4 : Migration(1, 4) {
  override fun migrate(database: SupportSQLiteDatabase) {
    database.execSQL("ALTER TABLE question ADD COLUMN difficulty TEXT NOT NULL DEFAULT '0'")
    database.execSQL("ALTER TABLE question ADD COLUMN category TEXT NOT NULL DEFAULT 'android'")
  }
}

The above simply executes two ALTER TABLE statements to add difficulty and category columns. Since these columns didn’t exist in the version 1 database, we don’t have to worry about the more complex SQL for the migration in the prior step.

Open the QuizDatabase class and add the following line to your companion object to create a reference to your new migration:

val MIGRATION_1_TO_4 = Migration1To4()

Now go to the QuizApplication.kt file and add your new migration to the database builder:

database = Room.databaseBuilder(this, QuizDatabase::class.java, "question_database")
    .addMigrations(
      QuizDatabase.MIGRATION_1_TO_2, 
      QuizDatabase.MIGRATION_2_TO_3,
      QuizDatabase.MIGRATION_3_TO_4, 
      QuizDatabase.MIGRATION_1_TO_4
    ).build()

Cool! You now have a migration that goes directly from database version 1 to 4. If you build the app, the migration won’t execute since your app is already on database version 4, but you can now be sure that all your users on database version 1 will properly migrate to the new version when they update the app.

Key points

• Simply put, a database migration or schema migration is the process of moving your data from one database schema to another.
• SQLite handles database migrations by specifying a version number for each database schema that you create.
• Room provides an abstraction layer on top of the traditional SQLite migration methods with Migration.
• Migration(startVersion, endVersion) is the base class for a database migration. It can move between any two migrations defined by the startVersion and endVersion parameters.
• fallbackToDestructiveMigration() tells Room to destructively recreate tables if you haven’t specified a migration.

Where to go from here?

By now, you should have a very good idea of how Room migrations work. Of course, the process will differ from project to project, since the queries you’ll need to execute will depend on your database schema, but the basic idea is always the same:

• Change the database version.
• Create a migration.
• Add the migration to your database builder.

If you want to learn more about Room migrations the official documentation is always a good resource.

See you in the next Room, er, chapter!