Migration in Room with an Android Array of Strings

Question

I have a database in Android with Room from which I have deleted a column. I was doing the migration, and I saw that it was not as simple as doing a DROP of the deleted column.

Then I have seen that I have to take a series of steps, creating a provisional table that will later be the new table with the deleted column, but the problem is that this table contains a field that is a String Array that I don't know how to declare in SQL.

@Entity(tableName = "recipe_table")
data class RecipesDb(
    @PrimaryKey
    @ColumnInfo(name = "id")
    val id: Long,
    @ColumnInfo(name = "name")
    val name: String,
    @ColumnInfo(name = "category")
    val category: List<String>,
    @ColumnInfo(name = "isRecommended")
    val isRecommended: Boolean,
    @ColumnInfo(name = "images")
    val images: List<String>,
    @ColumnInfo(name = "ingredients")
    val ingredients: List<String>,
    @ColumnInfo(name = "date")
    val date: Long,
    @ColumnInfo(name = "time")
    val time: Int,
    @ColumnInfo(name = "difficult")
    val difficult: String,
    @ColumnInfo(name = "originalUrl")
    val originalURL: String? = null,
    @ColumnInfo(name = "author")
    val author: String,
    @ColumnInfo(name = "siteName")
    val siteName: String
)

And now I have removed the ingredients column. I wanted to do something like this:

private val MIGRATION_3_2 = object : Migration(3,2) {
        override fun migrate(database: SupportSQLiteDatabase) {
            //Drop column isn't supported by SQLite, so the data must manually be moved
            with(database) {
                execSQL("CREATE TABLE Users_Backup (id INTEGER, name TEXT, PRIMARY KEY (id))")
                execSQL("INSERT INTO Users_Backup SELECT id, name FROM Users")
                execSQL("DROP TABLE Users")
                execSQL("ALTER TABLE Users_Backup RENAME to Users")
            }
        }
    }

But when I declare the new temporary table User_Backup, I have no idea how to specify that one of the fields is an Array. In the end I was able to do it with Room's AutoMigrations and creating an interface, but I would like to know how to do it this way as well.

MikeT · Accepted Answer · 2022-05-10T22:28:53.710

The simple way is to compile the code (Ctrl+F9) with the changed @Entity annotated classes in the list of entities of the @Database annotation.

Then look at the generated java (visible via the Android View in Android Studio). There will be a class that is the same name as the @Database annotated class but suffixed with _Impl.

In this class there will be a method that is named createAllTables, This includes the SQL that room uses for creating the tables.

Just copy and paste the appropriate SQL and then change the table name, this will not only use the correct type but also apply the correct column constraints that Room expects.

I would suggest

Adding an execSQL("DROP TABLE IF EXISTS the_backup_table_name;") before you create a table (just in case it already exists)
And instead of using execSQL("DROP TABLE Users") to use execSQL("DROP TABLE IF EXISTS the_original_table_name")

Personally I always RENAME the table name of the original, then RENAME the new table and then finally DROP the renamed original.

I would use:-

private val MIGRATION_3_2 = object : Migration(3,2) {
    override fun migrate(database: SupportSQLiteDatabase) {
        //Drop column isn't supported by SQLite, so the data must manually be moved
        with(database) {
            execSQL("DROP TABLE IF EXISTS Users_Backup")
            execSQL("CREATE TABLE IF NOT EXISTS ....) //<<<<< SEE NOTES BELOW, the SQL MUST BE CHANGED.
            execSQL("INSERT INTO Users_Backup SELECT id, name FROM Users")
            execSQL("ALTER TABLE Users RENAME TO Old_Users")
            execSQL("ALTER TABLE Users_Backup RENAME to Users")
            execSQL("DROP TABLE IF EXISTS Old_users")
        }
    }
}

note .... indicates that the SQL is copied from the generated java and that the table name is changed from Users to Users_Backup
The first line will drop the Uers_backup just in case it happens to exist, it's just a little less likely to fail under unusual circumstances.
Rather than dropping the Users table before the RENAME of the Users_Backup to Users. The 4th execSQL changes the name of the Users table, so should there be an issue with changing the Users_Backup table to be the Users table, then the original Uers table is available as Old_users.
When all has been complted then the original Users table, now named Old_Users is then dropped.
These are all just a little safer/secure.

Thank you very much for your reply! I didn't know the trick of the Impl class... Regarding the example, I didn't understand very well what you mean... — Ludiras, May 10 '22 at 22:03
@Ludiras I've updated the answer, hopefully explaining better. But in short just a little safer. — MikeT, May 10 '22 at 22:29
When creating a new table, is it not necessary to specify which columns it will have? — Ludiras, May 10 '22 at 23:40
@Ludiras yes hence why **`....`** needs to be replaced by the SQL as copied from the generated Java and the table name changed. Also hence the comment on the line as per `//<<<<< SEE NOTES BELOW, the SQL MUST BE CHANGED.` Although actually you can create a table using `CREATE TABLE AS SELECT .... ` (again .... represents more needed) see https://sqlite.org/lang_createtable.html#create_table_as_select_statements. However, this has limitations, and would probably NOT be suitable for Room which has expectations as to exactly what is required. — MikeT, May 11 '22 at 00:30
OK, now I understand, thank you very much. I mark it as an answer! — Ludiras, May 11 '22 at 07:37

Migration in Room with an Android Array of Strings

1 Answers1