Android Room Migration from version 2 - 4?

Question

I'm trying to migrate a Room database from versions 2 and 3 to 4 like so:

private static final Migration MIGRATION_2_4 = new Migration(2, 4) {
    @Override
    public void migrate(@NonNull @NotNull SupportSQLiteDatabase database) {

        database.execSQL(
                "ALTER TABLE 'market_data' ADD COLUMN 'watch_list_boolean' TEXT NOT NULL DEFAULT '0'");

        database.execSQL("DROP TABLE 'developer_data'");
    }
};

but it's not working, what is wrong here?

Answer 1

The problem, most likely (post your stack trace to help future readers), is that your DB won't be able to perform migrations 2->3 and 3->4

So, your code will only work if your db is upgraded from 2 directly to 4 and will throw an exception (that indicates what migration is missing) if db is upgraded from 2 to 3 or from 3 to 4.

Best practice is to create separate migrations - 2 to 3, and 3 to 4.

Room will know to execute the correct migrations and in the right order (2->3 or 3->4 or 2->3->4):

private static final Migration MIGRATION_2_3 = new Migration(2, 3) {
    @Override
    public void migrate(@NonNull @NotNull SupportSQLiteDatabase database) {
        database.execSQL(
                "ALTER TABLE 'market_data' ADD COLUMN 'watch_list_boolean' TEXT NOT NULL DEFAULT '0'");
    }
};

private static final Migration MIGRATION_3_4 = new Migration(3, 4) {
    @Override
    public void migrate(@NonNull @NotNull SupportSQLiteDatabase database) {
        database.execSQL("DROP TABLE 'developer_data'");
    }
};

Don't forget to update DB version :)