Browsing Your Android App’s Database
--
As Android developers we often work with SQLite or libraries built on top of it such as Realm or Room. When doing so, I often find myself wanting to check what is actually stored in the database. Looking at the actual data in the database is also a good way to help debugging when our app is behaving strangely.
The database lives within our app’s folder on our device and is by default only accessible by our app. It is usually a file with a .db extension and its path looks something like this:
/data/data/com.example.app/app.db
To look at the data stored in our database we must first get hold of this file. Then we need to open it in a program capable of browsing its content.
Extracting the Database File
To get hold of the database file we need to be running a debug version of the app or have root access to the device.
Below API 24, we can extract the database file using adb pull.
On API 24 and above adb pull /data/data/com.example.app/app.db leads to Permission denied. This is due to the Permissions Changes in Android 7.0 mentioned here.
Instead we can use adb exec-out and run the command as our app:adb exec-out run-as com.example.app cat databases/app.db > app.db
When running the command as our app, we have access to the app’s folder and we can simply copy the contents of the database to our computer’s file system.
Browsing the Data
Once we have extracted the database file, we can look at the data within by using a SQLite browser. Personally I like the aptly named DB Browser for SQLite.
Write-Ahead Logging
In most cases the above is enough, but recently my script “stopped working”.
I could see that my app was still working normally since the data stored was shown on the next launch, but when extracting the database like I was used to, I just got an empty file of exactly 4096 bytes, which is the size of one page.
When opening this file I saw that it had no tables or data to look at.
After some digging I found out that this was due to write-ahead logging. I guess that Room has started using this in one of their newer versions.
The link explains it thoroughly, but in short write-ahead logging or WAL writes changes to a separate file app.db-wal before applying them to the database file at certain checkpoints. In my case the database file was empty because there had been no checkpoints yet. So the changes were still in the WAL file.
The fix is to extract the WAL file together with the database file and force a checkpoint to occur.
Our final script looks something like this:
# extractDatabase.sh# Extract database file, WAL file and shared memory file
adb exec-out run-as com.example.app cat databases/app.db > app.db
adb exec-out run-as com.example.app cat databases/app.db-wal > app.db-wal
adb exec-out run-as com.example.app cat databases/app.db-shm > app.db-shm# Commit changes from WAL file to database file
sqlite3 app.db "PRAGMA wal_checkpoint"# Delete files that are not needed anymore
rm app.db-wal
rm app.db-shm# Open the database in a browser
open -a "DB Browser For SQLite" app.db
Hope this is helpful to some of you. Feel free to ask questions or share some knowledge in the comments if I can improve something.
