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

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.

Answer 2

Room database Export and Import Solution

Im facing same problem in one of my project, i spend two days to resolve this issue.

Solution

Don't create multiple instance for Room library. Multiple instance creating all the problems.

MyApplication

class MyApplication: Application() 
{

companion object {
    lateinit var mInstanceDB: AppDatabase
}

override fun onCreate() {
    super.onCreate()
    mInstanceDB = AppDatabase.getInstance(this)
}
}

AppDatabase

fun getInstance(context: Context): AppDatabase 
{

if (sInstance == null) {

    sInstance = Room.databaseBuilder(context.applicationContext,AppDatabase::class.java, "database").allowMainThreadQueries().build() 

            return sInstance!!
}
}

Now use this instance in any number of activity or fragment just like that

{
    var allcustomer = MyApplication.mInstanceDB.customerDao.getAll()
}

Export and Import use this library

implementation 'com.ajts.androidmads.sqliteimpex:library:1.0.0'

Github link

Answer 3

You can use the wal_checkpoint pragma to trigger a checkpoint which will move the WAL file transactions back into the database.

        theRoomDb.query("pragma wal_checkpoint(full)", null)

or

        // the result
        // contains 1 row with 3 columns
        // busy, log, checkpointed
        Cursor cursor = theRoomDb.query("pragma wal_checkpoint(full)", null)

See PRAGMA Statements for more details about the pragma parameter values and results.

If the WAL is not enabled the pragma does nothing. By the way, I tested with Room 1.1.1, and the WAL mode was not used by default, I had to enable it.

Answer 4

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.