How do I completely recreate my database in Android Room?

Question

I have a situation where I want to be able to do a hard reset of my database using Android Room. Using SQLiteOpenHelper, I could do this by writing a method to drop all tables and indices, then manually call SQLiteOpenHelper.onCreate().

I'm aware that I can get access to a SupportSQLiteOpenHelper via room and drop all the tables myself, but there doesn't seem to be a good way to kick-off the recreation process.

Also, I'm aware that I could delete every item from each table without dropping it, but that's not what I want. That doesn't reset the auto-incrementing primary key, so the "id" field of new items won't reset back to 1.

Thanks!

EDIT:
This is something I want to be able to do arbitrarily at runtime.

EDIT 2:
The method should be maintainable, i.e. not involve hand-writing SQL that matches Room's behavior. Ideally there would be some way to retrieve the SQL that Room generates, or a SQLiteOpenHelper.onCreate() equivalent method. Or anything else that solves this problem! :)

Marquess

This is a feature request, not a problem somebody can solve right now except Room developers. I need this feature too! – Athapaskan

For "Also, I'm aware that I could delete every item from each table without dropping it, but that's not what I want. That doesn't reset the auto-incrementing primary key, so the "id" field of new items won't reset back to 1." you could review https://mcmap.net/q/428042/-how-to-recreate-sqlite-database-at-runtime-when-using-room-in-android – Florilegium

Answer 1

I found it easiest to do this via context.deleteDatabase(“name”) and then simply reinstantiating and repopulating the database via the Room.databaseBuilder().addCallback upon first access.

Whereby

And how do you remove the static INSTANCE of database then ? – Beaumarchais

Answer 2

Simple answer is to increment your @Database version number. Generally speaking you should do this for schema changes, however it will achieve your aims of clearing the database and resetting all primary key values.

@Database(entities = { Hello.class }}, version = 1) // <- you want to increase version by 1
@TypeConverters({})
public abstract class AppDatabase extends RoomDatabase {
    public abstract HelloDao helloTextDao();
}

EDIT: IF you want to do this at run time, I would clear all the data from your tables (to avoid FK issues), then call DROP TABLE table_name, on all of your respective tables. Then you will need to write queries for creating the tables ie : CREATE TABLE table_name (uid INTEGER PRIMARY KEY, name STRING);.

This will mean you have to maintain a list of create table queries up to date with your POJO's unfortunatly. Ideally you'd be able to use reflection to generate the query, howerever @ColumnInfo currently doesn't support reflection as it has its RetentionPolicy set to CLASS.

Hopefully this solves your problem, feel free to ask for further clarification in the comments. Happy hunting!

Jeneejenei

Yes, I believe this will accomplish the task in-between app updates. However, I wish to be able to do this arbitrarily at runtime (which I was able to do using the 'old' method I described of dropping all tables and calling onCreate()). I've updated my question to reflect this. – Marquess

Hey, I understand that this is one possible solution. Unfortunately, I don't consider it an acceptable one. Writing the SQL by hand to re-create what Room auto-generates for you is a recipe for disaster. Small differences could create terrible bugs. Using the "old" method, I can guarantee that calling onCreate() will re-create the tables exactly as they were. I can't guarantee that here. I guess I was hoping that we could somehow get access to Room's equivalent onCreate() method, or some other hard-reset method? – Marquess

The best solution I have come up with is a runtime solution (like you desire) however it will increase the version number on the database (not in your actual code), which will create issues when your db is on say, version 45, and your code says version 1. (You could just update the version number every time this issue comes up if that suits you though. so it isn't a game breaker and will allow you to recreate the database multiple times at runtime). is it worth me updating my answer for this case? – Jeneejenei

I don't think it'd be a good idea to have the database version mismatch the code version. I wanted to avoid talking about my specific use case, but essentially I have a database that acts as a search index. I want to reset the DB every time I recreate the index, which could be many times. Emptying the tables without resetting the primary keys is sufficient for now, but during development it's much nicer to analyze DB's that have single- and double-digit ID's than ones that are many digits. So yeah, I have a particular (and whiny) use case, but it was easy before I switched to Room :) – Marquess

Answer 3

For my case, deleting database with context.deleteDatabase(“name”) still caches the data of the old database.

I solved this by manually deleting the database file

File databasesDir = new File(context.getApplicationInfo().dataDir + "/databases");
new File(databasesDir, "MyDatabaseName.db").delete();

Enzymolysis

very simple solution – Pyrrha

If the database is not flushed before, the shm, wal and maybe journal files might zombie around still. – Halflight

Answer 4

This may help you.

1. upgrade your Database version

2. When you call DatabaseBuilder, remember add this to your code fallbackToDestructiveMigration

   Room.databaseBuilder(context, Database::class.java, DB_NAME) .allowMainThreadQueries() .fallbackToDestructiveMigration() .build()

More detail please refer this

Yuzik

fallbackToDestructiveMigration() will delete data of your users if you messed up with missing migrations. I can't imagine why it should ever be used by 99.9% of app developers. It's better to release a fix than let users lose data. – Radioluminescence

Answer 5

I was not able to find a way to do this programatically.

But, you can go to yourApp -> Long click -> App Info -> Storage & Cache -> clear both cache and Storage.

Clearing storage displays a verification dialog that indicates that databases will be destroyed.

Kristiankristiansand

Answer 6

As per the answers given here, and here, and here, I could do it as following:

public void deleteAndReset() {
    SQLiteDatabase database;
    database = SQLiteDatabase.openOrCreateDatabase(context.getDatabasePath(MY_DATABASE_NAME), null);
    String deleteTable = "DELETE FROM " + MY_TABLE_NAME;
    database.execSQL(deleteTable);
    String deleteSqliteSequence = "DELETE FROM sqlite_sequence WHERE name = '" + MY_TABLE_NAME + "'";
    database.execSQL(deleteSqliteSequence);
}

Cucullate

Too low level, assumes single table and assumes there is a db sequence defined for the table. – Midships

This just deletes data from the tables, it doesn't recreate the database. – Gilliangilliard