Android Room: How to Migrate Column Renaming?

Question

Issue

My app is crashing because I am not handling migration properly. I'm looking for a solution to migrate the name of 1 column in my table.

In my project I a have a room table named 'content' with a Double attribute 'archivedCount'. In the latest version of the app the attribute archivedCount attribute is re-named to dismissCount, still as type Double.

Original Content model

@Entity(tableName = "content")
data class Content(@PrimaryKey var id: String, var archiveCount: Double) : Parcelable {...}

New Content model

@Entity(tableName = "content")
data class Content(@PrimaryKey var id: String, var dismissCount: Double) : Parcelable {...}

Attempted Solution

After reading a Google Developer Advocate's explanation Understanding migrations with Room, I attempted her solution outlined in the post's section Migrations with complex schema changes which entails making a copy of the original table, deleting the old table, then renaming the newly created table.

With the following approach below there is a runtime error on this line: database.execSQL("INSERT INTO content_new (id, dismissCount) SELECT id, archiveCount FROM users"); because I already cleared my app's cache so the old table no longer exists.

Can I update a single column without re-creating the entire table?

static final Migration MIGRATION_1_2 = new Migration(1, 2) {
@Override
public void migrate(SupportSQLiteDatabase database) {
    // Create the new table
    database.execSQL(
            "CREATE TABLE content_new (id TEXT, dismissCount REAL, PRIMARY KEY(id))");
    // Copy the data
    database.execSQL("INSERT INTO content_new (id, dismissCount) SELECT id, archiveCount FROM users");
    // Remove the old table
    database.execSQL("DROP TABLE content");
    // Change the table name to the correct one
    database.execSQL("ALTER TABLE content_new RENAME TO content");
  }
};

Answer 1

Solution

Thanks to the guidance from @TimBiegeleisen we discovered that the Android implementation of SQLite 3.19 for API 27 and 28 has not yet upgraded to the version 3.25 SQLite which allows this feature outlined in this StackOverflow post.

Once Android upgrades a command such as this to alter a table column will be possible: database.execSQL("ALTER TABLE content RENAME COLUMN archiveCount TO dismissCount")

Answer 2

There is a solution without migration - use ColumnInfo:

data class Content(@PrimaryKey var id: String,  @ColumnInfo(name = "archiveCount") var dismissCount: Double) : Parcelable{...}

Database column will be still archiveCount, but in Kotlin property will be renamed.