Database Inspector

A live database tool we’ve been waiting for!

Creating and managing local databases is a core component of most mobile apps. But whether you’re using SQLite directly or through the Room persistence library, Android developers have been asking for a better way to inspect and debug databases in their running app. To learn more watch the video or read the blog below.

The latest version of Android Studio 4.1 (currently available in Canary) comes with a new tool called Database Inspector, which helps you inspect, query, and modify databases in your running app.

Database Inspector helps you to modify the data just like editing a spreadsheet.

With Database Inspector, modifying the data in your database is not much harder than editing a spreadsheet; if you are using Room and observing the query results, the changes will be reflected instantly in your app!

In this post, we will use the Database Inspector with the Sunflower app to test some edge cases. Sunflower is a gardening app that illustrates Android development best practices with Android Jetpack. I encourage you to clone the repo and follow along as you read this post.

Get started

The UI of the Sunflower app includes two tabs. To add some plants to my garden, let’s take a look at the Plant List tab, which lists available plants for my garden. On the top right, there is a filter button. When I press this button, the following list of plants is displayed:

Click to filter Plant List

Clearly, this button filters the plants based on some criteria. But let’s imagine I am not super familiar with this project and I want to figure out how the filtering works by using the Database Inspector.

To open the Database Inspector in Android Studio, I need to select View > Tool Windows > Database Inspector from the menu bar.

Select View > Tool Windows > Database Inspector from the menu bar to open Database Inspector.

This brings up the Database Inspector.

Database Inspector window

Now, I need to run the app to a device running API level 26 or higher and then select the app process from the dropdown menu.

Select the running app process from the dropdown menu.

After I select the app process I want to inspect, the database schemas appear in the panel below. To see Sunflower database tables, I need to expand the …/databases/sunflower-db schema.

Once you select a process, the app databases are listed.

Let’s go back to the app and take a look at the filtered plant list. The list contains Avocado, Grape, Orange and Tomato. If I could sort the plant table by the plant name, it wouldn’t be hard to find Avocado. Let’s see if the Database Inspector can do that!

First, I double-click on the plants table to display its data. The data is displayed with a default page size of 50, but you can reduce this number and page between a shorter list of results instead. Clicking on the name column sorts the plant table on the name of the entries. As expected, Avocado is not far down the list and I find it in the second row of the table.

Query your databases

Looking at the data entry of Avocado, the growZoneNumber is most likely the property which the app uses for filtering. To verify that, let’s run a query on the growZoneNumber, which is 9 for Avocado. Actually, this query already exists in PlantDao.kt and I can run the query directly from Room’s @Query annotations. Every @Query annotation has a small run icon next to its line number.

When I click the run icon for the query of getPlantsWithGrowZoneNumber() and select the correct database, a popup appears asking me the value for :growZoneNumber.

You can run queries directly from @Query annotation.

I can enter a value of 9 and click Run to see the query results.

Alternatively, I can type my own query and run it inside the tool window. This gives me more freedom, as I am not limited to the queries defined in DAO interfaces. To run my own query, I click Run SQL and select the plants database from the newly opened tab on the right.

Click Run SQL and select the app database.

Next, I type the following query into the box next to the database selection dropdown and click Run.

“Select * from plants where growZoneNumber=9”

You can execute SQL queries in Database Inspector

Here it is! We have the exact same list of plants as is shown when the filter is on.

Modify and debug your database

The Database Inspector makes debugging your app a breeze by allowing you to modify values in your app’s databases while it’s running on a device.

First, I want to test the app UI for really long plant names. Instead of changing the data source and refreshing the database with the new data, I’ll edit the values directly on the database using the Database Inspector.

You can edit your database in Database Inspector

Now that the cell is editable, I change the name from Apple to A really special type of Apple and press enter. If you are following along, you can type anything you like with the length you want to test in the app UI.

If your app uses Room and observes the query results, you don’t need to restart the app to see the changes.

Now let’s go back to the app. Notice that without us doing anything, the app is displaying the updated data! If your app uses Room and observes the query results (using LiveData/Flow) you do not need to trigger a database query to refresh the data. Otherwise, depending on how your app triggers a query, you may need to relaunch the app or just navigate to the related activity/fragment again. In order to use the full potential of the Database Inspector this might be a good excuse to migrate your app to use LiveData or Flow.

Looking back at our app, it looks like the card view is not designed to handle such long plant names. I’ll fix this later, but let’s move on to the next test.

The app doesn’t seem to handle long text well.

Each plant has a different watering interval and I want to see what happens when watering day is past due. To do this, I need to add some plants to my garden; but first, I select the Live updates checkbox in the Database Inspector. When Live updates is checked, the Database Inspector automatically displays any changes your app makes to its database.

Select Live Updates.

I go back to the My Garden tab and add some plants such as Avocado and Eggplant; but first, I go back to the Database Inspector and double click on garden_plantings to observe the table. Notice how the data in garden_plantings table is updated automatically as I add new plants.

Database Inspector can automatically display changes.

Both these plants have a watering interval of 3 days. I don’t really want to wait 3 days to see what happens so I’ll edit the database and change the last_watering_day. Once again I go back to the Database Inspector and double click on garden_plantings. The last_watering_date is the last column of the table. I’ll change the values of both records to something smaller to reflect sometime before today’s date.

Edits to your databases are visible instantly!

Ok, it looks like I went a little too far back in time, but that should still work for my test. The app UI seems fine displaying the due date. For future development, maybe we can suggest adding a warning for the user when watering day is passed.

Go ahead and give the new Database Inspector a try! Let us know what you think; and if you come across any issues, please don’t forget to file bugs!