How do I correct a wrong Room migration?

Question

I added a column to a table, then added the following migration (version 56 to 57):

private val MIGRATION_56_57 = object : Migration(56, 57) {
    override fun migrate(database: SupportSQLiteDatabase) {
        database.execSQL("ALTER TABLE `app_stage` ADD COLUMN hasSeenBusinessOwnerQuestion INTEGER DEFAULT 0 NOT NULL")
    }
}

After building and releasing the app to our existing users, they get a migration error and the app crashes. To correct the error, I just need to change:hasSeenBusinessOwnerQuestion INTEGER DEFAULT 0 NOT NULL to:hasSeenBusinessOwnerQuestion INTEGER DEFAULT 0 .

Should I just add another migration from version 57 to 58 as:

 private val MIGRATION_57_58 = object : Migration(57, 58) {
    override fun migrate(database: SupportSQLiteDatabase) {
        database.execSQL("ALTER TABLE `app_stage` ADD COLUMN hasSeenBusinessOwnerQuestion INTEGER DEFAULT 0 ")
    }
}

? Will existing users upgrading from version 56 all the way to 58 get that same migration error? How do I add another migration to version 58 to avoid migration errors?

Answer 1

? Will existing users upgrading from version 56 all the way to 58 get that same migration error?

No Room will invoke all the migrations in sequence (56-57 and then 57-58) and then after all the migrations have been performed continue with the database build.

However you cannot use the ALTER command to ADD an already existing column, which would be the case going from 57-58. So the migration would fail.

Although later versions of SQLite (3.35.0) support ALTER TABLE .... DROP COLUMN ...., this version is not available at present on Android devices and that there is no ALTER COLUMN. You will have to use an alternative means to alter the column.

• The DROP COLUMN is also quite restrictive

You could do the following (where ? represents the table in question):-

1. DROP TABLE IF EXISTS ?_old
   1. this is just in-case it exists (it should not)
2. Use the ALTER TABLE ? RENAME TO ?_old (_old just a suggested name for what is to be a temporary version of the table)
3. Use CREATE TABLE IF NOT EXISTS ....
   1. Room demands that the create table SQL creates the table according to how it interprets the class annotated with @Entity.
   2. It is suggested that you retrieve the create table SQL from the generated java that is available after compiling the project. The SQL will be in the createAllTables method of the class that is the same as the class that is annotated with @Database but suffixed with _Impl
4. Use INSERT INTO ? SELECT * FROM ?_old to copy existing data into the newly created version of the table
5. Use DROP TABLE IF EXISTS ?

So apart from the CREATE TABLE .... (which would have to be altered, see points above) the following would cater for all scenarios (new users, users on 57 and users on 56) :-

    private val MIGRATION_57_58 = object : Migration(57, 58) {
        override fun migrate(database: SupportSQLiteDatabase) {
            database.execSQL("DROP TABLE IF EXISTS `app_stage_old`")
            database.execSQL("ALTER TABLE `app_stage` RENAME TO `app_stage_old`")
            /* NOTE the CREATE TABLE IF NOT EXISTS .... SHOULD BE ALTERED ACCORDINGLY */
            database.execSQL("CREATE TABLE IF NOT EXISTS `app_stage` (`id` INTEGER, `name` TEXT NOT NULL,`hasSeenBusinessOwnerQuestion` INTEGER DEFAULT 0, PRIMARY KEY(`id`))")
            database.execSQL("INSERT INTO `app_stage` SELECT * FROM `app_stage_old`")
            database.execSQL("DROP TABLE IF EXISTS `app_stage_old`")
        }
    }