Migrations Room doesn't handle

Room has AutoMigration Annotation to help with migrations for developers, But It doesn’t handle some migrations that we need. Let’s learn what these Unsupported migrations are and how to manage them.

List of changes that SQLite doesn’t support:

• Column Type(nullability, data type)
• Add/Remove/Rename Column
• Add/Remove Constraints

So what is the solution? Unfortunately, It’s sucks, but fortunately, you can test It 😁. Let’s Add, Remove and Rename a column from a table.

Solution

For any change in table columns, You need to make another table with those columns and the changes you need. Then copy the content of the old table into the new table, remove the old table and finally rename the new table. So let’s see all of these in code.

Assume we have table Members with these columns:

• id
• fname
• lname
• age(nullable)
• phoneNumber

we want to rename fname, lname to firstName, lastName, remove phoneNumber and make age nullable. So:

val migrationFrom1To2 = object: Migration(1, 2) {
  // create a new table with new schema
  database.execSQL("CREATE TABLE IF NOT EXISTS `New_Members` (`id` INTEGER NOT NULL, `firstName` TEXT NOT NULL, `lastName` TEXT NOT NULL, `age` INTEGER, `phoneNumber` TEXT NOT NULL, PRIMARY KEY(`id`))");

  // insert old data to new table(`phoneNumber` not included because we wanna delete it)
  database.execSQL("INSERT INTO `New_Members` (`id`, `firstName`, `lastName`, `age`) SELECT `id`, `fname`, `lname`, `age` FROM `Members`")

  // delete old `Members` table
  database.execSQL("DROP TABLE `Members`")

  // rename new table to `Members`
  database.execSQL("ALTER TABLE `New_Members` RENAME TO `Members`")
}

Let’s test It with Room testing library. First, we need to add this library:

// Room test library
androidTestImplementation "androidx.room:room-testing:$room_version"

Second, Make a class in androidTest section in the project tree:

Migrations Room doesn’t handle - Writing tests

After creating our Test class, we have to initialize Room MigrationTestHelper class to:

@get:Rule
val helper = MigrationTestHelper(
    InstrumentationRegistry.getInstrumentation(),
    com.roomtest.AppDatabase::class.java,
    emptyList(),
    FrameworkSQLiteOpenHelperFactory()
)

Now, we can write our test:

@Test
fun migrate_from_1_to_2() {
    helper.createDatabase(DB_NAME, 1).apply {
        // confirm our table is created
        val tableExistQuery = query("SELECT name FROM sqlite_master WHERE type='table' AND name='Members'")
        Assert.assertEquals(tableExistQuery.count, 1)

        // check for columns
        val cursor = query("PRAGMA table_info(Members)")
        val columns = arrayListOf<String>()
        while (cursor.moveToNext()) {
            val name = cursor.getString(cursor.getColumnIndex("name"))
            columns.add(name)
        }
        Assert.assertEquals(/* columns count */ 5, columns.size)

        close()
    }

    helper.runMigrationsAndValidate(DB_NAME, 2, true, migrationFrom1To2).apply {
        val cursor = query("PRAGMA table_info(Members)")
        val columns = arrayListOf<String>()
        while (cursor.moveToNext()) {
            val name = cursor.getString(cursor.getColumnIndex("name"))
            columns.add(name)
        }
        Assert.assertEquals(/* columns count */ 4, columns.size)

        val isFNameExist = columns.contains("fname")
        Assert.assertEquals(isFNameExist, false)

        val isLNameExist = columns.contains("lname")
        Assert.assertEquals(isLNameExist, false)

        val isFirstNameExist = columns.contains("firstName")
        Assert.assertEquals(isFirstNameExist, true)

        val isLastNameExist = columns.contains("lastName")
        Assert.assertEquals(isLastNameExist, true)

        val isPhoneNumberExist = columns.contains("phoneNumber")
        Assert.assertEquals(isPhoneNumberExist, false)

        close()
    }
}

So what have we done here? First, we need to create a database for our test with the version that we want to start the migration. Then confirm that the table is created, and the size of the columns is equal to the size of our columns.

After that, we need to run our migration to check and write our tests if the changes Are applied or not. As we remove the phoneNumber there will be Four columns. Then we check if fname, lname is exists or not, and the expectation is not.

Then we check if firstName, lastName is exists, and our expectation is True. And the final test is the existence of phoneNumber as we removed it. Of course, We can do more tests but I believe you get the idea.

That’s it. We made it.