Migrations in Room db in android made simple with Kotlin

If you are new to Room db and its building blocks please read through this short article. If you are already aware of the basics of room db but want to know about migration in room db this article is for you.

Migrations means changing the entity and the underlying table structure. We will need to use this when we already have our entities setup and data present in our database, but we need to change the entity fields or add new entities or delete it. This might sound unnecessary but this happens in almost all apps when we need to add new features.

For the sake of demonstrating how we can achieve migration in room db we will get started with the 2 entities below

Entity 1

User Table

User Entity ->

@Entity
data class User(
    @PrimaryKey val id: Long,
    val name: String,
    val age: Int,
    val gender:String,
)

Entity 2

@Entity
data class Car(
    @PrimaryKey val id: Long,
    val company: String,
    val model: String,
    val type: String
)

Let’s say i want to achieve the below points

• Add a new column called phone to User table
• Rename the name column to fullName
• We want to delete the column gender
• Rename the User table to UserEntity
• Delete the car entity

There are 2 ways to achieve this using room

1. Automated migrations
2. Manual migrations

Automated migrations

Room handles a few things automatically if we specify it to automatically migrate using @AutoMigration.

To achieve the first point -> Add a new column called phone to User table, all we have to do is add a new field to the user entity class as below

@Entity
data class User(
    @PrimaryKey val id: Long,
    val name: String,
    val age: Int,
    val gender:String,
    val phone:String?
)

The next step will be to add the autoMigration to our database class like below

@Database(
  version = 2,
  entities = [User::class],
  autoMigrations = [
    AutoMigration (from = 1, to = 2)
  ]
)
abstract class AppDatabase : RoomDatabase() {
  ...
}

Note that before this migration the version would be 1. To migrate we need to increase the version number.

This works fine. But the other points below cannot be achieved directly through auto migration.

• Rename the name column to fullName
• We want to delete the column gender
• Rename the User table to UserEntity
• Delete the car entity

This is where we need to implement AutoMigrationSpec.

Room db provides the below annotations for deleting table, renaming table, deleting column and renaming column respectively.

• @DeleteTable
• @RenameTable
• @DeleteColumn
• @RenameColumn

So to achieve the remaining 3 points we can do the following

@Database(
  version = 2,
  entities = [User::class],
  autoMigrations = [
    AutoMigration (
      from = 1,
      to = 2,
      spec = AppDatabase.MyAutoMigration::class
    )
  ]
)
abstract class AppDatabase : RoomDatabase() {
   @RenameColumn(fromColumnName = "name", toColumnName = "fullName",        tableName = "User")
   @DeleteColumn(columnName = "gender", tableName = "User")
   @RenameTable(fromTableName = "User", toTableName = "UserEntity")
   @DeleteTable(tableName = "Car")
   class MyAutoMigration : AutoMigrationSpec
}

Note that the annotations run sequentially.

Manual Migrations

In this case we use SQL queries to update the database.

In case of complex schema changes automatic migrations wont work. Even though for our use case automatic migrations will do. Let’s have a look at how to do those using manual migrations.

val MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(database: SupportSQLiteDatabase) {
        database.execSQL("ALTER TABLE User RENAME COLUMN name TO fullName")
        database.execSQL("ALTER TABLE User RENAME TO UserEntity")
        database.execSQL("Drop table Car")
    }
}

Room.databaseBuilder(applicationContext, MyDb::class.java, "database-name")
  .addMigrations(MIGRATION_1_2).build()

We create a migration object and add that to databaseBuilder using .addMigrations method. If we have more than one migration we can add those in the addMigrations method split by ,

As you can see above we have not implemented delete column. Sqlite has a limitation wherein it does not support a drop column. So to drop the gender column we do the below transaction

val MIGRATION_2_3 = object : Migration(2, 3) {
    override fun migrate(database: SupportSQLiteDatabase) {
        database.execSQL(
                "BEGIN TRANSACTION;" +
                "CREATE TEMPORARY TABLE User_Backup(id,name,age,phone);" +
                "INSERT INTO user_backup SELECT id,fullName,age,phone FROM user;" +
                "DROP TABLE User;" +
                "CREATE TABLE User(id,fullName,age,phone);" +
                "INSERT INTO User SELECT id,fullName,age,phone FROM User_Backup;" +
                "DROP TABLE User_Backup;" +
                "COMMIT;")
    }
}

We can add this in addMigrations method as below

.addMigrations(MIGRATION_1_2,MIGRATION_2_3)

Hope this article makes migrations a bit more understandable.

If you like this article please clap and follow to get notified on a new article each week.

Happy Coding!