Managing an SQLite database on an Android Virtual Device

Mobile database development is often an unfamiliar when coming from the world of web development. Many database administrators are used to managing their databases through a management tool such as SQL Server Management Studio, phpMyAdmin or MySQL Workbench. They can feel a bit out of place when they find themselves creating SQLite databases by writing queries in Java or Objective-C code. Sometimes having a visual representation of the data just helps with understanding a system and can speed up development.

So, how can I directly manage an SQLite file?

When running an Android application on an actual device, we always have access to the file system over USB, so we can just download the SQLite database to our machine and manage it from there. In addition, there are many SQLite browser applications available for Android, such as SQLite Manager and SQLite Viewer, that allow you to browse the contents of an SQLite database directly from your device.

Note: The SQLite database for your application will always be found in the data -> data -> {application package name} -> databases folder on the Android file system.

The problem is that many developers do not develop on an actual Android device, at least not in the early stages. Instead, they run their application on the Android Virtual Device through the Android Developer Tools or Eclipse. While it is a bit more complicated, you can also download your application’s SQLite database from a virtual device. Here’s how it’s done:

Before following the steps below, make sure that your Android Virtual Device is running and connected to Android Developer Tools. Also, make sure that your application has successfully created an SQLite database on the file system.

1. Open the DDMS perspective in ADT by clicking the DDMS button in the top right hand corner of the application. You may only see the “Java” and “Debug” perspectives initially. If that is the case, you will need to click the open perspective button and choose “Other…” from the menu. From the next screen, choose DDMS and click OK.

perspective

The “Open Perspective” button in ADT.

The "Open Perspective" dialog in ADT with DDMS highlighted.

The “Open Perspective” dialog in ADT with DDMS highlighted.

2. You should see your AVD information in the left hand pane. In the right hand pane, choose the File Explorer tab.

3. From the file tree, expand to the data -> data -> {application package name} -> databases folder where you should see your SQLite database.

4. Select the database file and click the “Pull a file from the device” button in the top right hand corner of the File Explorer pane. A save dialog should appear.

The DDMS File Browser with the "Pull a file from the device" button shown.

The DDMS File Browser with the “Pull a file from the device” button shown.

5. Choose a location to save the file on your local machine.

Once the files is on your local machine, you can use any SQLite management tool to browse and modify your database. SQLite Explorer is a great free, open-source option for those looking for a recommendation.

There is also a plug-in for Eclipse available that will handle the process of downloading and opening the database for you. It allows you to manage the database directly from Eclipse. You can download it here.

If you need to make a change to the database, you can do it through your management software, and use the “Push a file onto the device” button from the File Explorer to push the updated database to the virtual device.

This is a great method of seeing exactly what is going on with your database every step of the way. While unit testing is still a crucial part of making sure your application functions correctly, visualizing the data can help to quickly resolve obvious bugs.

Thanks for reading. Feel free to leave any questions or comments.