Room: How to automigrate DB for new table?

Question

I want to do a simple change on my room database: adding a new table.

My room version is: 2.4.1

According to https://medium.com/androiddevelopers/room-auto-migrations-d5370b0ca6eb, this task should be easy using an auto-migration.

Here is what the relevant part of my DB class looks before the migration:

@Database(
    entities = [FlashCard::class, Pool::class],
    version = 2
)
abstract class DB : RoomDatabase() {
 ...

For the migration, I changed that part to:

@Database(
    entities = [FlashCard::class, Pool::class, FlashCardRunEvent::class],
    version = 3,
    autoMigrations = [
        AutoMigration (from = 2, to = 3)
    ]
)
abstract class DB : RoomDatabase() {
...

It all compiles fine. The app runs until the first interaction with the database.

Then, the app crashes and I get this in my log:

Caused by: java.lang.IllegalStateException: Migration didn't properly handle: FlashCardRunEvent(com.ravenala.flashy.room.FlashCardRunEvent).
     Expected:
    TableInfo{name='FlashCardRunEvent', columns={newBox=Column{name='newBox', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}, flashCardId=Column{name='flashCardId', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}, timeStampInSeconds=Column{name='timeStampInSeconds', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}, id=Column{name='id', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=1, defaultValue='null'}, oldBox=Column{name='oldBox', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}}, foreignKeys=[], indices=[]}
     Found:
    TableInfo{name='FlashCardRunEvent', columns={}, foreignKeys=[], indices=[]}

I do not know what to make out of this. What's up with that difference between "Expected" and "Found". I had hoped room would simply see the new table, generate a scheme out of it and create the table. Are the times of writing SQL statements for simple migrations as this one not over?

Answer 1

What's up with that difference between "Expected" and "Found"

Expected is the schema according to Room's processing of the classes defined via the entities argument in the @Database annotation.

Found is the schema found in the database when opening it, in your case after the auto migration.

Reporting that it found TableInfo{name='FlashCardRunEvent', columns={}, foreignKeys=[], indices=[]} indicates that the FlasCardRunEvent table does not exist after the AutoMigration.

I had hoped room would simply see the new table, generate a scheme out of it and create the table. Are the times of writing SQL statements for simple migrations as this one not over?

Why is not apparent from the code you have presented. Certainly AutoMigration has the ability to create new tables (have tested this with a simple scenario).

It all compiles fine.

Did you check the build log for any warnings? These, if any could be a clue as to what is happening.

Why Room is not creating the table

A Guess :-

You haven't inadvertently bypassed migration by forgetting something (such as not adding the FlashCardRunEvent entity to the entities defined in the list of entities in the @Database annotation) run and thus increased the version to version 3; then corrected the omission and run while still at version 3?

• Perhaps try adding an AutoMigration from 3-4 and and then running.

Otherwise I'm unsure. Look at the generated java (visible via Android View) e.g. :-

This should be something like :-

class AppDatabase_AutoMigration_2_3_Impl extends Migration {
  public AppDatabase_AutoMigration_2_3_Impl() {
    super(2, 3);
  }

  @Override
  public void migrate(@NonNull SupportSQLiteDatabase database) {
    database.execSQL("CREATE TABLE IF NOT EXISTS `FlashCardRunEvent` (`id` INTEGER, `newBox` INTEGER NOT NULL, `flashCardId` INTEGER NOT NULL, `timeStampInSeconds` INTEGER NOT NULL, `oldBox` INTEGER NOT NULL, PRIMARY KEY(`id`))");
  }
}

• you would expect ONLY the single execSQL as you are just adding the single table.

Additionally in the generated java you would expect in the class that is DB_Impl for the createAllTables method to also include a CREATE TABLE for the FlashCardRunEvent table.

As can be seen the above, code is derived from a test albeit it different initial tables but with migration 2-3 adding the FlashCardRunEvent table ( by building the entity class from the expected) i.e. :-

@Entity
data class FlashCardRunEvent(
    /*
        TableInfo{name='FlashCardRunEvent',
        columns={
            newBox=Column{name='newBox', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'},
            flashCardId=Column{name='flashCardId', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'},
            timeStampInSeconds=Column{name='timeStampInSeconds', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'},
            id=Column{name='id', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=1, defaultValue='null'},
            oldBox=Column{name='oldBox', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}},
            foreignKeys=[], indices=[]}
     */
    @PrimaryKey
    val id: Long?,
    val newBox: Long,
    val flashCardId: Long,
    val timeStampInSeconds: Long,
    val oldBox: Long
)

The test successfully creates the table. The one noticeable difference being that I have coded exportSchema = true in the @Database annotation rather than rely upon it to default to true.

Running the test and the FlasCardRunEvent table is created :-