Android: SQLite database created with room shows no tables when opening with sqlte-browser

Question

I am using Room Persistence Library 1.1.0. I could find the database file at /data/data/<package_name>/databases/ using Android Studio's Device File Explorer.

It contains multiple tables and I can access contents of that tables without any problem using room-DAOs. However when opening with sqlite-browser, is shows no table.

What might be the reason? Is it possible to resolve the issue without switching back to old SQLiteOpenHelper from room?

Answer 1

Copy all three files from Device File Explorer in AndroidStudio to your PC directory and open the db file in Db Browser for SQLite (http://sqlitebrowser.org). Make sure all three files are in the same folder.

Answer 2

Solution

To open such databases* with sqlite-browser, you need to copy all three files. All must be in the same directory.

^{* Databases stored in multiple files as stated in the question.}

Why three files?

As per docs, Starting from version 1.1.0, Room uses write-ahead logging as default journal mode for devices which has sufficient RAM and running on API Level 16 or higher. It was Truncate for all devices until this version. write-ahead logging has different internal structure compared to Truncate.

Take a look at the files temporary files used by SQLite now and then :

Until version 1.1.0

From version 1.1.0

If you want to change the journal mode explicitly to Truncate, you can do it this way. But, it is not recommended because WAL is much better compared to Truncate.

public static void initialize(Context context) {
    sAppDatabase = Room.databaseBuilder(
            context,
            AppDatabase.class,
            DATABASE_NAME)
        .setJournalMode(JournalMode.TRUNCATE).build();
}

Is it possible to move it to single file without changing to Truncate ?

Yes, it is. Query the following statement against the database.

pragma wal_checkpoint(full)

It is discussed in detail here here.