Android SQLite Debugging

SQLite is one of the most popular databases when it comes to choosing persistence technology for Android. SQLite uses SQL and therefore allows the use of all its features such as inserting, updating, deleting data, joining tables, querying, etc. For Android, there is Android Room library, which is essentially a wrapper around SQLite.

In this post, we are going to learn how to use some of the debugging tools that SQLite/Android Room library provides in case standard tools are not helping.

Real-life story

When querying, there are a lot of adapters which can be used to return different types of data. The most popular would be RxJava Observable, Kotlin Coroutines Flow and PagingSource from Paging library.

Since we are strong Kotlin Coroutines supporters 💪, we mostly use Room in conjunction with Kotlin Coroutines. For example, here we have a method that returns Flow of a contact. If this contact will be changed in the database sometime in the future, new Flow emission will notify us about that.

@Query("SELECT \* FROM contacts WHERE id == :contactId")  
abstract fun getContactStream(contactId: Long): Flow

One sunny day, one of our testers reported a bug about wrong behaviour on one of our screens. After some time of debugging, I found out that our Flow doesn't emit some of the changes in the database. 🤨

That’s a tough one to debug, since you have to dig down into the Room library, creating a breakpoint hell, setting a lot of debugger watches and overall doing many unpleasant things. But is it the only way to do that? Maybe it’s possible to check all the queries Room is generating to identify some abnormalities? Spoiler: yes, it is possible.

Logging SQLite 

If we check the source code of SQLiteDebug class, we will find 3 possible log tags, which you can use to log different SQLite things:

• SQLiteLog - controls the printing of informational SQL log messages
• SQLiteStatements - controls the printing of SQL statements as they are executed
• SQLiteTime - controls the printing of wall-clock time taken to execute SQL statements as they are executed.

Cool, but how do we use it?

To enable logging aforementioned logs in logcat, you would need to fire up your command line interface and execute the following command.

adb shell setprop log.tag.%TAG% VERBOSE

Instead of %TAG%, put the tag you want to be logged. In our case, I wanted to see SQLite statements that Room is generating, so it will look like this. VERBOSE is the upper level of logging - everything that can be logged will be logged.

adb shell setprop log.tag.SQLiteStatements VERBOSE

To double-check, you can execute this command to see if the value was correctly set.

adb shell getprop log.tag.SQLiteStatements

If it printed “VERBOSE”, you’re good to go.

If we launch our application right now and do something that impacts the database, we will not see any logs in our logcat. Why so? Since SQLite service was already launched when we set our new log tag value, it was not taken into account, because we need to restart the service first.

You can easily do this using ADB commands, but your phone/emulator has to be rooted. If you use an emulator, execute the following command:

adb root

⚠️ Important: your emulator should be installed without Google Play services.
Since emulator with Google Play services is a production build, you will receive "adbd cannot run as root in production builds"

Adb should answer you with "restarting adbd as root” and you will be good to go. Now the only thing we need is to restart the shell, by executing these commands (emulator will restart):

adb shell stop  
  
adb shell start

Now you can finally launch your app and see the logs in the logcat. 🥳

It should look something like this:

2021-07-12 13:27:17.755 9094-9194/my.application V/SQLiteStatements: /data/user/0/my.application/databases/my\_database: "SELECT \* FROM contacts WHERE id == 4"  
2021-07-12 13:27:17.757 9094-9193/my.application V/SQLiteStatements: /data/user/0/my.application/databases/my\_database: "BEGIN EXCLUSIVE;"  
2021-07-12 13:27:17.760 9094-9193/my.application V/SQLiteStatements: /data/user/0/my.application/databases/my\_database: "UPDATE OR ABORT \`contacts\` SET \`id\` = 4,\`date\` = 1626089237608,\`name\` = \`John Doe\`, WHERE \`id\` = 4"

Here we have a simple SQL select, transaction begin and SQL update.

Solution on Real-life story

Using the above trick, we quickly identified the problem and fixed it. The reason was lying on how Room handles Kotlin Coroutines Flow return type by using SQL Triggers. You can read about that by checking an extensive article from Vasya Drobushkov. 

In our case, triggers were working incorrectly in conjunction with multiple transactions, so the fix was quite simple: wrap one of our repository update methods in one transaction. Easy peasy. 😎

Conclusion

That’s it! Hope it shed some light on SQLite debugging and made your Android journey a little more enjoyable. Don’t hesitate to dig down into libraries’ code and try different approaches.

See ya and stay healthy!