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:

• Reset auto-increment in Android's Room library
• Android Room - reset auto generated key on each app run

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

Answer 1

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

1. First increase the version of the database: update the version parameter in the @Database annotation.
2. 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), ...)")
        }
    };

3. Add the migration when building the database:

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

4. 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.

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

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

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