Android Room Database DAO debug log

Question

Given a Room database DAO like this:

import android.arch.persistence.room.Dao;
import android.arch.persistence.room.Query;

import java.util.Date;
import java.util.List;

@Dao
public interface MyDao {

    @Query("SELECT * FROM MyTable")
    List<MyItem> all();

    @Query("SELECT * FROM MyTable WHERE date = :date AND language = :language")
    MyItem byDate(Date date, String language);


}

Is there a way to have a Logger or something like that added to MyDao so that I could see which statements are being performed. This would be really helpful during development, because I could immediately check if the functions are transformed correctly to the expected SQL statement or not.

Answer 1

Assuming that Room uses framework's Sqlite as underlying database, the statements can be quite simply logged. The only limitation: this can be done only on emulator.

From SQLiteDebug.java:

/**
 * Controls the printing of SQL statements as they are executed.
 *
 * Enable using "adb shell setprop log.tag.SQLiteStatements VERBOSE".
 */
public static final boolean DEBUG_SQL_STATEMENTS =
        Log.isLoggable("SQLiteStatements", Log.VERBOSE);  

---

By default, the log.tag.SQLiteStatements's value is not set:

alex@mbpro:~$ adb shell getprop log.tag.SQLiteStatements
<-- BLANK LINE -->

According to the above documentation, to set the property we have to use:

alex@mbpro:~$ adb shell setprop log.tag.SQLiteStatements VERBOSE
alex@mbpro:~$ adb shell getprop log.tag.SQLiteStatements
VERBOSE

As we can see, the VERBOSE value was successfully set. However, if we'll re-run our application - we won't see those statements printed. To make it work, we'll have to restart all the services using adb shell stop and then adb shell start.
If you'll try to do that with a regular device, you'll receive the following error (tried with Pixel XL / stock Android 9):

alex@mbpro:~$ adb shell start
start: must be root
alex@mbpro:~$ adb root
adbd cannot run as root in production builds

This is why we have to use the emulator:

alex@mbpro:~$ adb root
restarting adbd as root
alex@mbpro:~$ adb shell stop
alex@mbpro:~$ adb shell start

The emulator will restart.
Run your application and you'll see similar Sqlite statements in logcat:

<redacted..>
V/SQLiteStatements: <redacted>/my_db: "BEGIN EXCLUSIVE;"
V/SQLiteStatements: <redacted>/my_db: "CREATE TABLE IF NOT EXISTS room_master_table (id INTEGER PRIMARY KEY,identity_hash TEXT)"
V/SQLiteStatements: <redacted>/my_db: "INSERT OR REPLACE INTO room_master_table (id,identity_hash) VALUES(42, "3cb5664b6da264c13388292d98141843")"
V/SQLiteStatements: <redacted>/my_db: "CREATE TABLE IF NOT EXISTS `MyTable` (`id` TEXT NOT NULL, `date` INTEGER, `language` TEXT, PRIMARY KEY(`id`))"
<redacted..>
V/SQLiteStatements: <redacted>/my_db: "BEGIN EXCLUSIVE;"
V/SQLiteStatements: <redacted>/my_db: "PRAGMA temp_store = MEMORY;"
V/SQLiteStatements: <redacted>/my_db: "PRAGMA recursive_triggers='ON';"
V/SQLiteStatements: <redacted>/my_db: "CREATE TEMP TABLE room_table_modification_log(version INTEGER PRIMARY KEY AUTOINCREMENT, table_id INTEGER)"
V/SQLiteStatements: <redacted>/my_db: "COMMIT;"
<redacted..>
V/SQLiteStatements: <redacted>/my_db: "SELECT * FROM MyTable"
V/SQLiteStatements: <redacted>/my_db: "SELECT * FROM MyTable WHERE date = 1551562171387 AND language = 'en'"  

---

To undo the changes, use these commands:

alex@mbpro:~$ adb shell setprop log.tag.SQLiteStatements ""
alex@mbpro:~$ adb shell getprop log.tag.SQLiteStatements
<-- BLANK LINE -->
alex@mbpro:~$ adb shell stop
alex@mbpro:~$ adb shell start
alex@mbpro:~$ adb unroot
restarting adbd as non root

Answer 2

As of Room 2.3.0-alpha04 (released December 16 2020, could be stable by the time you're reading this), there's direct support in Room for logging SQL queries with the new RoomDatabase.QueryCallback

You set this callback on the RoomDatabase.Builder

    fun getDatabase(context: Context): MyDatabase {
        val dbBuilder = Room.databaseBuilder(
            context.applicationContext,
            MyDatabase::class.java, "mydatabase.db"
        )
        dbBuilder.setQueryCallback(RoomDatabase.QueryCallback { sqlQuery, bindArgs ->
            println("SQL Query: $sqlQuery SQL Args: $bindArgs")
        }, Executors.newSingleThreadExecutor())
        return dbBuilder.build()
    }

Note this is just example code and you should probably ensure MyDatabase is a singleton in your app. Another tip is only log queries when the app is DEBUG: if (BuildConfig.DEBUG) dbBuilder.setQueryCallback(... and the rest of the code from above.

Comment if anybody wants the example code in Java

Answer 3

As per document of Room it performs compile time check so if your SQL statement is not valid compilation itself failed and proper error message is displayed in the log.

Also generated code is debuggable by default and can be found under below mentioned path.

build > generated > source > apt > your Package > yourDao_Impl.java

This class contains implementation of your DAO you can debug this class as you debug other classes in your project. :-)

Example :

Answer 4

There does not appear to be any hooks for that at the DAO level. There are callbacks related to database opens and upgrades, but not arbitrary stuff.

You could file a feature request, though. I agree that it could be useful. Even better would be an OkHttp-style generic interceptor framework.

Answer 5

When I have got some unknown error while inserting or updating row in room db Android does not show any error in debug console. One thing I found how to check whats happen while debug is:

try { someSource.update(someRow) } catch (e: Throwable) { println(e.message) }

Output is:

UNIQUE constraint failed: quiz.theme (code 2067)

Answer 6

I have been able to achieve it via a hack for Select queries. This wont work for insert/update/delete operations :)

Create a separate class RoomLoggingHelper as follows

import android.annotation.SuppressLint
import androidx.room.RoomSQLiteQuery

private const val NULL = 1
private const val LONG = 2
private const val DOUBLE = 3
private const val STRING = 4
private const val BLOB = 5
private const val NULL_QUERY = "NULL"

const val ROOM_LOGGING_TAG = "roomQueryLog"

object RoomLoggingHelper {

    @SuppressLint("RestrictedApi")
    fun getStringSql(query: RoomSQLiteQuery): String {
        val argList = arrayListOf<String>()
        val bindingTypes = query.getBindingTypes()
        var i = 0

        while (i < bindingTypes.size) {
            val bindingType = bindingTypes[i]

            when (bindingType) {
                NULL -> argList.add(NULL_QUERY)
                LONG -> argList.add(query.getLongBindings()[i].toString())
                DOUBLE -> argList.add(query.getDoubleBindings()[i].toString())
                STRING -> argList.add(query.getStringBindings()[i].toString())
            }
            i++
        }

        return String.format(query.sql.replace("?", "%s"), *argList.toArray())
    }

    fun getStringSql(query: String?, args: Array<out Any>?): String? {
        return if (query != null && args != null) {
            String.format(query.replace("?", "%s"), *args)
        } else
            ""
    }
}

private fun RoomSQLiteQuery.getBindingTypes(): IntArray {

    return javaClass.getDeclaredField("mBindingTypes").let { field ->
        field.isAccessible = true
        return@let field.get(this) as IntArray
    }
}

private fun RoomSQLiteQuery.getLongBindings(): LongArray {

    return javaClass.getDeclaredField("mLongBindings").let { field ->
        field.isAccessible = true
        return@let field.get(this) as LongArray
    }
}

private fun RoomSQLiteQuery.getStringBindings(): Array<String> {

    return javaClass.getDeclaredField("mStringBindings").let { field ->
        field.isAccessible = true
        return@let field.get(this) as Array<String>
    }
}

private fun RoomSQLiteQuery.getDoubleBindings(): DoubleArray {

    return javaClass.getDeclaredField("mDoubleBindings").let { field ->
        field.isAccessible = true
        return@let field.get(this) as DoubleArray
    }
}

private fun RoomSQLiteQuery.getIntBindings(): IntArray {

    return javaClass.getDeclaredField("mBindingTypes").let { field ->
        field.isAccessible = true
        return@let field.get(this) as IntArray
    }
}

Or, you can download this file from here

Add this file to your Project and call it from your Room Database class as follows: Override both the query methods like this

override fun query(query: SupportSQLiteQuery?): Cursor {
        //This will give you the SQL String
        val queryString = RoomLoggingHelper.getStringSql(query as RoomSQLiteQuery)
        //You can log it in a way you like, I am using Timber
        Timber.d("$ROOM_LOGGING_TAG $queryString")
        return super.query(query)
    }

    override fun query(query: String?, args: Array<out Any>?): Cursor {
        //This will give you the SQL String
        val queryString = RoomLoggingHelper.getStringSql(query, args)
        //You can log it in a way you like, I am using Timber
        Timber.d("$ROOM_LOGGING_TAG $queryString")
        return super.query(query, args)
    }

Disclaimers:

• I am using Reflection to get string SQL, hence use this ONLY in DEBUG mode
• This is written in haste and may contain errors, it would be wise to keep it in try-catch block
• Also, I have tested it for string args, should work for long and double as well, will not work for Blobs