Let’s burn the room

How To Reset Room DB Completely (Including Primary Keys) | Android

Photo by Anna Zakharova on Unsplash

When I was building my app using the Room persistence library, I wanted to give an option to the user to reset the whole app. What I mean by resetting is to completely delete all the tables including their primary keys and after that when a new entity is added, its primary key should start with 1. That day was very struggling because I managed to delete all the entries however when I insert a new entity, its primary key not resets and it just autoincrement from the last one.

So in this article, I will tell you what approaches I tried (however, those approaches didn’t go in vain. You’ll find how) and how I achieved this. And if you are not interested in my story, you can scroll to the last section (Approach 4 and 4.2) where you will get the solution.

I am using Kotlin coroutines and flow but you can use it in Java also because the main thing is the SQLite query (in the DAO class) that I’m gonna use which is independent of the Android language.

So let’s set up our Room DB

App Setup

1. Dependencies (Room + Kotlin coroutines). In app-level build.gradle file.

2. Define Entity

3. Define Dao

4. Create the Database

So now our database is ready let’s add some entities to it.

Now we are ready to burn the room. I mean deleting the entries.

Note: I am clearing the app's data from the app’s setting before each approach, so in each approach, the primary key will start from 1.

Approach 1: Drop the table

I tried to write a query to drop the table students but unfortunately, Room doesn’t support it.

Approach 2: Delete All the entries from the table

I wrote this query to delete the entries (remember this query, we will need it).

Let’s test this and see what we get.

Student(id=1, name=A)
Student(id=2, name=B)
Student(id=3, name=C)
Student(id=4, name=D)
Student(id=5, name=E)
No student in the room
Student(id=6, name=A)
Student(id=7, name=B)
Student(id=8, name=C
Student(id=9, name=D)
Student(id=10, name=E)

Have you observed the thing I was trying to tell you? It does delete all the entries but does not reset the primary keys. Now let’s get to the next approach because it didn’t work as expected.

Approach 3: Clear All the room table

After googling and reading the docs, I got this function of RoomDatabase that clears all the tables that Room DB contains (remember this function, we will need it). It is an abstract method of RoomDatabase, which means it will be available through our AppDatabase class (since it is a child).

@WorkerThread
public abstract void clearAllTables()

From the official docs:

Deletes all rows from all the tables that are registered to this database as Database.entities. This does NOT reset the auto-increment value generated by PrimaryKey.autoGenerate.

However, I tried it:

Results:

Student(id=1, name=A)
Student(id=2, name=B)
Student(id=3, name=C)
Student(id=4, name=D)
Student(id=5, name=E)
No student in the room
Student(id=6, name=A)
Student(id=7, name=B)
Student(id=8, name=C
Student(id=9, name=D)
Student(id=10, name=E)

Bad luck this time also.

Approach 4: Exploring SQLite (We’ll get the solution here :)

After googling and visiting numerous StackOverflow answers, I found that it is not Room’s fault. It is the behavior of SQLite.

Let’s know how primary keys are auto-generated.

SQLite maintains a table named sqlite_sequence where it stores all the table's names and its autoincrement column’s last generated value. SQLite creates it when we add the first table with a autoincrement column. Its schema is:

CREATE TABLE sqlite_sequence(name,seq);

Where:

• name — Name of the table which has a autoincrement column.
• seq — Last generated value for that table

SQLite uses this table to generate value for the next entry. More Info

Implementing Approach 4

I Introduced a DAO method and annotated it with this query. This query just deletes the row containing the last generated primary key of the students table.

Let’s test this:

Result:

Student(id=1, name=A)
Student(id=2, name=B)
Student(id=3, name=C)
Student(id=4, name=D)
Student(id=5, name=E)
No student in the room
Student(id=1, name=A)
Student(id=2, name=B)
Student(id=3, name=C)
Student(id=4, name=D)
Student(id=5, name=E)

Finally got the result we want it. That is after deleting all the entries, the primary key also resets and it starts from 1.

But wait didn’t I tell you I have to delete all the tables in my app to implement the app reset feature but it just rests a single table.

Approach 4.2: Let’s burn the Room completely

1. Create a DatabaseDao (You can introduce this function in any DAO but to keep things organize, I am creating a new DAO interface). This is a simple SQLite Delete query that deletes all the entries in the sqlite_sequence table.

2. Create a function to reset DB (usually in the app’s repository).

I am resetting the database in a transaction because:

From the docs:

runInTransaction executes the specified Runnable in a database transaction. The transaction will be marked as successful unless an exception is thrown in the Runnable.

So, if something goes wrong, all the modifications done inside the runInTransaction block will be undone. Whenever I have to do more than one operation with the database, I do it in a transaction and I also recommend you to do this.

Final test:

Result:

Student(id=1, name=A)
Student(id=2, name=B)
Student(id=3, name=C)
Student(id=4, name=D)
Student(id=5, name=E)
No student in the room
Student(id=1, name=A)
Student(id=2, name=B)
Student(id=3, name=C)
Student(id=4, name=D)
Student(id=5, name=E)

Hurray, we have successfully reset our Room DB completely.

Note: If you are prepopulating the database then you have to prepopulate it manually after the delete because it only deletes the tables.

If you want to try it but don’t want to write all this code (that you should), here is the link to the GitHub Repository. Just clone it, install and watch the logcat.

GitHub - sDevPrem/room-reset-demo

So that’s all about the Room DB and SQLite. Thank you for joining me on this exploration of Android development! If you found this article insightful and would like to stay connected for more content, I invite you to follow me on both Medium and LinkedIn.