SQLite Clients

Sometimes, it would be nice to look at what is in our Room-powered database. For example, it might simplify tracking down a bug if we could peek inside our tables and see what data is inside of them.

For that, you will need some form of SQLite client.

Fortunately, SQLite is open source and has been around for a long time. There are quite a few clients available to you to choose from, to find one that meets your needs and fits your workflow.

Database Inspector

A leading candidate, starting with Android Studio 4.1, is Android Studio itself, through its Database Inspector tool.

Getting To Your Database

How you get to the Database Inspector varies by Android Studio version:

• For 4.1.x and 4.2.x, look for the “Database Inspector” tool, by default docked on the bottom edge
• For Arctic Fox, look for the “App Inspection” tool, by default docked on the bottom edge — “Database Inspector” is a tab in the resulting UI

In the strip just below the title, you will see the particular app that Database Inspector is offering to inspect, or “Select Process” if Database Inspector does not know of a suitable process (e.g., your phone is not plugged in). You can switch to something else by clicking on that entry and choosing the desired device (or emulator) and process from drop-down menus.

Note that loading the database details seems to take far longer than it should — be patient!

Database Inspector appears to look for databases in the stock location that Room and most other apps place them — in this case, it shows stuff.db in the tree on the left. Inside, it shows two tables:

• todos, akin to the ones in various samples in this book
• room_master_table, a table created by Room itself to help manage its work

Seeing the Schema

The tree goes beyond the database and the tables in that database. Folding open a table gives you the table’s schema:

Performing Operations

In the toolbar above that tree, the toolbar button that looks like a grid with a magnifying glass will open a tab for you to be able to execute queries against the selected database:

The drop-down controls the database, and the field above it is where you can enter a SQL expression. Clicking the “Run” button then executes your SQL expression, with a grid showing you the results:

Using Your DAO

The Database Inspector also ties into the Android source editor. For some queries on your DAO classes, you can use a gutter icon to trigger the same query to run in the Database Inspector:

Those icons will only appear while the Database Inspector is running and is inspecting your app’s database.

Updating the Output

A popular thing to do with databases is to change the data.

If you change the data within the app, you have two ways of seeing those results reflected in the Database Inspector output.

The low-impact approach is simply to click the “Refresh table” button above your query output (note: the “table” referred to in button caption is the UI table, not a database table). This will re-query the database and update the output to match.

The alternative is to check the “Live updates” checkbox:

This will hook into the same “invalidation tracker” that Room itself uses to know to deliver new updates to your app via LiveData, Flow, etc. The table showing the query results will update in near-real-time as your app makes changes to the data.

Updating the App

When “Live updates” is checked, a message appears below the output: “Results are read-only”. You will see the same message when running one of your DAO functions, or if you run a custom query yourself.

However, if you just double-click the table name in the tree on the left, that message does not appear. And, in that case, the UI table is itself live — any changes that you make there will be reflected, in near-real-time, in your app’s UI. Just double-click on a cell, type in the revised value, and press Enter or Return.

This too ties into Room’s invalidation tracker. The data change that you make triggers that tracker and in turn causes any of your reactive observers to get fresh data from Room, reflecting whatever change you make.

Note, though, that the Database Inspector does not perform much data validation. For example, a Boolean property maps to an INTEGER column in the table, with 0 meaning false and 1 meaning true… but Database Inspector will be happy to let you fill in 3 as the value. And, since in SQLite column types are hints, Database Inspector will be happy to let you fill in foo as the value of an INTEGER column. While Database Inspector is happy, Room might not be, so be careful when modifying your app’s data via the Inspector this way.