How to handle an auto migration in android with a delete table operation

Question

I've been trying to do an auto migration, where a table is to be deleted, to my room database. I specify an automigration as follows:

@Database(
  version = 9,
  entities = [...],
  views = [...],
  exportSchema = true,
  autoMigrations = [
    ...
    AutoMigration(8, 9)
  ]
)
@TypeConverters(
  value = [...],
  builtInTypeConverters = BuiltInTypeConverters(enums = BuiltInTypeConverters.State.ENABLED)
)

Once I try to build the project, I get the following error:

AutoMigration Failure: Please declare an interface extending 'AutoMigrationSpec',
and annotate with the @RenameTable or @DeleteTable annotation to specify the change
to be performed:
1) RENAME:
    @RenameTable.Entries(
        @RenameTable(
            fromTableName = "some_table",
            toTableName = <NEW_TABLE_NAME>
        )
    )
2) DELETE:
    @DeleteTable.Entries(
        @DeleteTable(
            tableName = "some_table"
        )
    )

I've also removed the table class i.e SomeTable::class from the list of entities.

After the error I declare an AutoMigrationSpec class Annotated with @DeleteTable as follows:

@DeleteTable("some_table")
class Version9 : AutoMigrationSpec

and adding declare it as a spec as follows:

@Database(
  version = 9,
  entities = [...],
  views = [...],
  exportSchema = true,
  autoMigrations = [
    ...
    AutoMigration(8, 9, Version9::class)
  ]
)
@TypeConverters(
  value = [...],
  builtInTypeConverters = BuiltInTypeConverters(enums = BuiltInTypeConverters.State.ENABLED)
)

I get the following same error.

If I use the spec as an interface instead of a class i.e.

@DeleteTable("some_table")
interface Version9 : AutoMigrationSpec

I get an additional error that, The AutoMigration spec type must be a class.

I've also tried using the following format while declaring the AutoMigrationSpec:

@DeleteTable.Entries(DeleteTable("some_table"))
class Version9 : AutoMigrationSpec

However, I get the initial error.

Answer 1

I can't see anything wrong from the code you have provided.

Based upon your code, assuming other code, coding to make running before and after relatively simple, using KAPT rather than KSP and using 2.5.2 Room libraries then the code works as expected as per the output from the log (see code below):-

Part 1 for the first version with 2 tables (T1 and T2):-

2024-01-02 11:29:24.237 D/DBINFO1SCHEMA: SQL FOR COMPONENT is 
        CREATE TABLE android_metadata (locale TEXT)
2024-01-02 11:29:24.237 D/DBINFO1SCHEMA: SQL FOR COMPONENT is 
        CREATE TABLE `T1` (`t1Id` INTEGER, `t1Name` TEXT NOT NULL, PRIMARY KEY(`t1Id`))
2024-01-02 11:29:24.237 D/DBINFO1SCHEMA: SQL FOR COMPONENT is 
        CREATE TABLE `T2` (`t2Id` INTEGER, `t2Name` TEXT NOT NULL, PRIMARY KEY(`t2Id`))
2024-01-02 11:29:24.237 D/DBINFO1SCHEMA: SQL FOR COMPONENT is 
        CREATE TABLE room_master_table (id INTEGER PRIMARY KEY,identity_hash TEXT)
2024-01-02 11:29:24.308 D/DBINFO1: logDBInfo function INVOKED and STARTED for DATABASE VERSION 1
2024-01-02 11:29:24.308 D/DBINFO1T1: T1 ID is 1 NAME is T1Test001_1
2024-01-02 11:29:24.308 D/DBINFO1T1: T1 ID is 2 NAME is T1Test002_1
2024-01-02 11:29:24.308 D/DBINFO1T1: T1 ID is 3 NAME is T1Test003_1
2024-01-02 11:29:24.308 D/DBINFO1T2: T2 ID is 1 NAME is T2Test001
2024-01-02 11:29:24.309 D/DBINFO1: logDBInfo function INVOKED and STARTED for DATABASE VERSION 1

Part 2 for the second version (the migration) with just 1 table (T1):-

2024-01-02 11:33:47.231 D/DBINFO2SCHEMA: SQL FOR COMPONENT is 
        CREATE TABLE android_metadata (locale TEXT)
2024-01-02 11:33:47.231 D/DBINFO2SCHEMA: SQL FOR COMPONENT is 
        CREATE TABLE `T1` (`t1Id` INTEGER, `t1Name` TEXT NOT NULL, PRIMARY KEY(`t1Id`))
2024-01-02 11:33:47.231 D/DBINFO2SCHEMA: SQL FOR COMPONENT is 
        CREATE TABLE room_master_table (id INTEGER PRIMARY KEY,identity_hash TEXT)
2024-01-02 11:33:47.237 D/DBINFO2: logDBInfo function INVOKED and STARTED for DATABASE VERSION 2
2024-01-02 11:33:47.237 D/DBINFO2T1: T1 ID is 1 NAME is T1Test001_1
2024-01-02 11:33:47.237 D/DBINFO2T1: T1 ID is 2 NAME is T1Test002_1
2024-01-02 11:33:47.237 D/DBINFO2T1: T1 ID is 3 NAME is T1Test003_1
2024-01-02 11:33:47.238 D/DBINFO2: logDBInfo function INVOKED and STARTED for DATABASE VERSION 2

• i.e. the schema has been changed as expected (T2 no longer exists) along with the data (only written if the DB Version is 1) remaining.

The full code being:-

All the DB Code (as per 2nd run (see comments for 1st run)):-

const val DBVERSION=2;
const val DBFILENAME = "the_dataabse.db"

@Entity
data class T1(
    @PrimaryKey
    var t1Id: Long?=null,
    var t1Name: String
)
@Entity
data class T2(
    @PrimaryKey
    var t2Id: Long?=null,
    var t2Name: String
)

@Dao
interface T1DAOs {
    @Insert
    fun insert(t1: T1): Long
    @Query("SELECT * FROM t1")
    fun getAllT1s(): List<T1>
}
@Dao
interface T2DAOs {
    @Insert
    fun insert(t2: T2): Long
    @Query("SELECT * FROM t2")
    fun getAllT2s(): List<T2>
}
@Database(entities = [T1::class/*,T2::class COMMENTED OUT FOR V2 */], version = DBVERSION, exportSchema = true
    ,autoMigrations = [AutoMigration(1,2,Version1To2::class)]
)
abstract class TheDatabase: RoomDatabase() {
    abstract fun getT1DAOs(): T1DAOs
    /*abstract fun getT2DAOs(): T2DAOs COMMENTED OUT FOR V2*/

    companion object {
        private var instance: TheDatabase?=null
        fun getInstance(context: Context): TheDatabase {
            if (instance==null) {
                instance = Room.databaseBuilder(context,TheDatabase::class.java, DBFILENAME)
                    .allowMainThreadQueries()
                    .build()
            }
            return instance as TheDatabase
        }
    }
}
@DeleteTable(tableName = "T2")
class Version1To2: AutoMigrationSpec

The Activity code (run on the main thread for brevity, again for the 2nd run, again refer to the comments for first version):-

const val TAG = "DBINFO"
class MainActivity : AppCompatActivity() {
    lateinit var db: TheDatabase
    lateinit var t1DAOs: T1DAOs
    lateinit var t2DAOs: T2DAOs
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

        db = TheDatabase.getInstance(this)
        db.openHelper.writableDatabase /* force database open and thus create/migration etc */
        logDBSchema()
        t1DAOs = db.getT1DAOs()
        if (DBVERSION==1) {
            t1DAOs.insert(T1(t1Name ="T1Test001_${DBVERSION}"))
            t1DAOs.insert(T1(t1Name = "T1Test002_${DBVERSION}"))
            t1DAOs.insert(T1(t1Name = "T1Test003_${DBVERSION}"))
            /* COMMENTED OUT FOR V2
            t2DAOs = db.getT2DAOs()
            t2DAOs.insert(T2(t2Name = "T2Test001"))
            logDBInfo(t1DAOs.getAllT1s(),t2DAOs.getAllT2s())

             */
        } else {
            logDBInfo(t1DAOs.getAllT1s())
        }
    }

    fun logDBInfo(rowsFromTable1: List<T1> = listOf(), rowsFromTable2: List<T2> = listOf()) {
        Log.d(TAG+ DBVERSION,"logDBInfo function INVOKED and STARTED for DATABASE VERSION ${DBVERSION}")
        for (t1 in rowsFromTable1) {
            Log.d(TAG+ DBVERSION+"T1","T1 ID is ${t1.t1Id} NAME is ${t1.t1Name}")
        }
        for (t2 in  rowsFromTable2) {
            Log.d(TAG+ DBVERSION+"T2","T2 ID is ${t2.t2Id} NAME is ${t2.t2Name}")
        }
        Log.d(TAG+ DBVERSION,"logDBInfo function INVOKED and STARTED for DATABASE VERSION ${DBVERSION}")
    }
    fun logDBSchema() {
        var cursor: Cursor? = null
        if(db != null && db.isOpen) {
            cursor=db.openHelper.writableDatabase.query("SELECT sql FROM sqlite_master")
        } else {
            cursor =SQLiteDatabase.openDatabase(this.getDatabasePath(
                DBFILENAME).path,null,0).rawQuery("SELECT sql FROM sqlite_master",null)
        }
        if (cursor != null) {
            while (cursor.moveToNext()) {
                Log.d(TAG+ DBVERSION+"SCHEMA","SQL FOR COMPONENT is \n\t${cursor.getString(0)}")
            }
        }
    }
}

Conclusion

Basically as originally stated there is no obvious issue with the code.

• It should be noted that 2.5.2 Room libraries were used. If this is not the version that you used then perhaps try using 2.5.2.
  • It is not unknown for bugs to appear e.g. this Q & A dealt with a bug in the libraries. The link also has a fix by using manual Migration.
    • using a manual Migration allows greater control over the migration process
• If using KSP then try using KAPT, fewer issues appear to be encountered when using KAPT.

Answer 2

After a long time trying to go through the bug and trying to understand the ins and outs of auto-migrations etc., I discovered that the issue happens if the schemas are erroneous during the migration into the new version.

A common mistake I run into is running the application without updating the db version. This way the current schema gets corrupted and can't be used for the actual version migration.

Point in case, let's say you're updating the database version from 1 to 2. However, during the first run, you forget to update the database version from 1 to 2 in your database class, the application runs and changes are made to your schema file, hence getting "corrupted". Then the application is launched and immediately crashes and oops, you didn't update the db version. So now you update the version and rerun the application. Bad news, the application can't be built because some migrations are not specified. Or so does the "new" version "think", because that version already exists as per the latest schema file.

TL;DR: Always ensure that you update your db version before running your application, or at least, save a copy of the schema file before running your application.