11. Data Migration

Written by Subhrajyoti Sen

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 predictably change your database schema 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 in Android Studio 4.2 or greater by going to File ▸ Open and selecting the project from this chapter’s attachments.

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: SplashActivity, MainActivity, QuestionActivity and ResultActivity.
• The viewmodel package contains ViewModels of your classes: MainViewModel and QuestionViewModel.

Build and run the app on a device or emulator.

Important: Prepopulate the database and then tap START to start a quiz.

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 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 isn’t 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, another 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 versions defined by the startVersion and endVersion parameters. The reason for emphasizing 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 your 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’ll 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!

Exporting schemas

It’s considered good practice to start exporting the schema before you start writing your first migration. The export is a JSON representation of the database schema. This representation comes in very handy when you want to understand the changes taking place over various database versions.

Open QuizDatabase.kt and set the exportSchema attribute of @Database to true as follows:

@Database(entities = [(Question::class), (Answer::class)],
  version = 1,
  exportSchema = true
)

Next, you have to specify the directory to store the schemas in. Add the following code inside defaultConfig of the app-module build.gradle:

kapt {
  arguments {
    arg("room.schemaLocation", "$projectDir/schemas".toString())
  }
}

The code above passes an argument to the Kotlin annotation processer that sets the location of the schema to the schemas directory inside the project directory, which in this case is app.

Build the app. Once the build is complete, you’ll find the schema export at app/schemas/com.raywenderlich.android.droidquiz.data.db.QuizDatabase/1.json where 1 represents the database version. Whenever you’ll change the database version and build the project, the new schema will be exported.

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 questions 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. 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 = "questions", 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 line 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 START to see if it works. And the app crashed!

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

Note: If the app didn’t crash, you might have forgotten to press START earlier, when you ran the app before modifying. 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?

Upgrading the database version

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
  exportSchema = true
) 

Build and run the app again and press START. And the app crashes again!

Open the Logcat console to see the problem.

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’ll 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.

Implementing a 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 migrate() :

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 questions table.

Modify migrate() as follows:

override fun migrate(database: SupportSQLiteDatabase) {
  database.execSQL("ALTER TABLE questions 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 questions 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 bottom 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 QuizApplication.kt and modify your database builder inside onCreate() as follows:

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.

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

Changing column type in the schema

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 = "questions", 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  
)

The code above changes the data type of difficulty from Int to String.

Now, create a new class under the migrations package and name it Migration2To3. Add the following code to the file:

class Migration2To3 : Migration(2, 3) {
  override fun migrate(database: SupportSQLiteDatabase) {
    database.execSQL(
      "CREATE TABLE question_new (question_id INTEGER NOT NULL, " +
          "text TEXT NOT NULL, " +
          "difficulty 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) " +
          "SELECT question_id, text, difficulty FROM questions"
    )//3

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

    database.execSQL("ALTER TABLE question_new RENAME TO questions") //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 questions 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 questions using an ALTER TABLE with a RENAME TO statement.

Open QuizDatabase.kt and modify QuizDatabase like this:

@Database(
  entities = [(Question::class), (Answer::class)], 
  version = 3, // Changes the db version
 	exportSchema = true
)	
abstract class QuizDatabase : RoomDatabase() {
  abstract fun quizDao(): QuizDao
  
   companion object{
    val MIGRATION_1_TO_2 = Migration1To2()
    val MIGRATION_2_TO_3 = Migration2To3() // Adds a reference to your new migration
  }
}

Just like before, you’ve changed the database version to 3 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, DB_NAME)
  .addMigrations(QuizDatabase.MIGRATION_1_TO_2, QuizDatabase.MIGRATION_2_TO_3)
  .build()

Build and run, then press START.

Creating a direct migration

You might have noticed that you now have two different migrations for three 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 4 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 3.

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

class Migration1To3 : Migration(1, 3) {
  override fun migrate(database: SupportSQLiteDatabase) {
    database.execSQL("ALTER TABLE questions ADD COLUMN difficulty TEXT NOT NULL DEFAULT '0'")
  }
}

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 QuizDatabase.kt and add the following line to the companion object to create a reference to your new migration:

val MIGRATION_1_TO_3 = Migration1To3()

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

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

Cool! You now have a migration that goes directly from database version 1 to 3. If you build the app, the migration won’t execute since your app is already on database version 3, 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.

Automated migrations

The migrations that you wrote in the previous section can seem like a lot of code to achieve simple changes. Luckily, from version 2.4.0-alpha01 onwards, Room supports automatic migrations.

Automatic migrations work great only for simple schema changes like:

• Deleting a column (@DeleteColumn)
• Deleting a table (@DeleteTable)
• Renaming a column (@RenameColumn)
• Renaming a table (@RenameTable)

In the points above, you can see their related annotations. In this section, you’ll use @RenameColumn but the procedure to use the other annotations is quite similar.

Consider that you want to rename the difficulty attribute to something like challengeLevel.

Create a new file inside migrations and name it Migration3To4. Adding the following code to it:

@RenameColumn(
  tableName = "questions", 
  fromColumnName = "difficulty", 
  toColumnName = "challengeLevel"
)
class Migration3To4 : AutoMigrationSpec

The code above does a couple of things:

1. Automated migrations extend AutoMigrationSpec instead of Migration.
2. @RenameColumn specifies the original name of the column as well as the table name, and also the new column name.

Next, open QuizDatabase.kt and add the following paramter to @Database:

autoMigrations = [
  AutoMigration(
    from = 3,
    to = 4,
    spec = Migration3To4::class
  )
]

In the code above, autoMigrations takes an array of automated migrations and starts applying them one by one.

Also, change the database version to 4.

Your @Database should now look like the following:

@Database(entities = [(Question::class), (Answer::class)],
  version = 4,
  exportSchema = true,
  autoMigrations = [
    AutoMigration(
      from = 3,
      to = 4,
      spec = Migration3To4::class
    )
  ]
)

Build and run the app. Click START. You will notice that the app runs fine, verifying that the migration was successful.

Room internally uses the exported schemas to figure out the changes needed to make the migration work.

You have successfully added your first automated migration.

Key points

• Simply put, a database 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 at https://developer.android.com/training/data-storage/room/migrating-db-versions is always a good resource.

See you in the next Room, er, chapter!