Android Room persistance library. Drop Table

Question

I need to know how to "DROP FROM Table" using Room Persistence Library.

I already know that we can delete all the rows using the method:

@Query("DELETE FROM table")
void deleteAll();

However, what I need is deleting the whole table. This is because of my primary_key is an autoincrement column, so using the previous code, it is not rested.

I already read answers about this topic in the next links:

However, I can't believe that this library doesn't provide an easier way to do this, regardless of the reason or use.

score 19 · Accepted Answer · answered Mar 18 '19 at 21:14

Could use Migrations that Room provides for updating the database via our own queries. As we want to make changes to the database which Room cannot resolve (yet) from the code. We could delete the table, recreate it or update it. Depending on what is needed.

Option 1: Migrate with keeping other data

First increase the version of the database: update the version parameter in the @Database annotation.
Create a migration like this:

    static final Migration MIGRATION_1_2 = new Migration(1, 2) { // From version 1 to version 2
        @Override
        public void migrate(SupportSQLiteDatabase database) {
            // Remove the table
            database.execSQL("DROP TABLE my_table"); // Use the right table name

            // OR: We could update it, by using an ALTER query

            // OR: If needed, we can create the table again with the required settings
            // database.execSQL("CREATE TABLE IF NOT EXISTS my_table (id INTEGER, PRIMARY KEY(id), ...)")
        }
    };

Add the migration when building the database:

    Room.databaseBuilder(context, MyDatabase.class, "mydatabase")
        .addMigration(MIGRATION_1_2) // Add the migration
        .build();

Run the app again. If the queries were correct, the migration is done

Option 2: Migrate with losing data

There is also a fast option, but all data in the database will be cleared!

This is because the database gets recreated when using the method below.

Like option one, increment the version of the database
Use .fallbackToDestructiveMigration() when creating the database, like so:

    Room.databaseBuilder(context, MyDatabase.class, "mydatabase")
        .fallbackToDestructiveMigration()
        .build();

Run the app. It will remove the old database and recreate it. (All earlier data is wiped)

I tried the second option. However, I added an extra column named ID, now my app crashes saying column 'id' does not exist. Could you please help me to resolve this? Thank you. — Junia Montana, Mar 18 '20 at 00:04

score 0 · Answer 2 · answered Jan 06 '22 at 19:41

If you want to do this with auto migration then need to use spec as autoMigrations value for @Database annotation. Look like this

autoMigrations = {@AutoMigration(from = 1, to = 2, spec = AppDatabase.MyAutoMigration.class)}

Example like you want to delete a table(YourTableName) from database version 1 and then migrate to version 2 then the full code looks like this

@Database(
    version = 2,
    entities = {Entity1.class, Entity2.class},
    autoMigrations = {@AutoMigration(from = 1, to = 2, spec = AppDatabase.MyAutoMigration.class)},
    exportSchema = true)
@TypeConverters({Converters.class})
public abstract class AppDatabase extends RoomDatabase {

   @DeleteTable.Entries(value = @DeleteTable(tableName = "YourTableName"))
   public static  class MyAutoMigration implements AutoMigrationSpec {
   }
   // Your DAO 1
   // Your DAO 2
}

Android Room persistance library. Drop Table

2 Answers2