Photo by Shane Aldendorff on Unsplash

Debugging SQLite Databases in Xamarin-based Android Apps

We recently got an email asking if it is possible to use SQLScout to debug SQLite databases in Xamarin-based Android applications. I was happy to find out that it is actually possible, and better yet, fairly straightforward.

What is SQLScout?

SQLScout is a plugin for Android Studio and IntelliJ IDEA that provides first-class support for debugging and managing SQLite databases. Its features include:

When working with databases in Android applications, SQLScout offers 2 options:

  1. Download the database to your computer, for offline use. Any record changes are local to the downloaded copy, but can be easily uploaded back to the device.
  2. Live Device Connection” where you can manage and update database records directly from the Android device, without downloading any files.

Step-by-step Guide

To debug and manage databases in Xamarin-based Android application using SQLScout, we only need to follow a few steps:

1. Build and import the APK into Android Studio

We first need to build the Android application and its APK in Visual Studio. Once we have the APK file, we can import it in Android Studio.

SQLScout is a plugin for Android Studio and it doesn’t work with Visual Studio (the IDE of choice when working with Xamarin). We can work around this limitation by using Android Studio’s ability to import pre-built APKs for debugging.

Importing the APK into Android Studio is as simple as clicking the “Profile or import APK” action from the IDE’s “Welcome” page, and then selecting the APK file from the file system.

Figure 1. Android Studio’s support for debugging pre-built APKs.

Android Studio will create a project for the imported APK. The created project, like any Android project, will have a Run Configuration to run or debug the application in an Android device or emulator.

2. Configure the project’s Android SDK

Before we run the application, we will need to set the Android SDK to use in the project. This seems like a bug in Android Studio, since the APK’s manifest already specifies the platform level to use, and the IDE should be able to pick this up to automatically set up the project’s SDK.

To me, the easiest way to set up the project’s SDK is to open the file AndroidManifest.xml and select the “Open Project Structure” link on the editor’s notification header (figure 2). Then, in the “Project Structure” dialog, select an Android SDK from the drop-down or create a new one(figure 3).

Figure 2. Use the “Open Project Structure” link to set up the project’s SDK.
Figure 3. Selecting an Android SDK in the “Project Structure” dialog.

3. Run the application

Now we can run our application (for debugging the app’s database we don’t need to execute the app in debug mode).

4. Locate the application’s database file in the Device File Explorer

Once the application is up and running, we will locate the application’s database file in Android Studio’s Device File Explorer. In Android applications, database files have the extension “db” and are under the path /data/data/<package>/databases (where <package> is the value in the “package” attribute in AndroidManifest.xml). In the case of Xamarin-based applications, the path for database files is /data/data/<package>/files (at least this is the path in the samples I found).

Figure 4. Locating the app’s database file in Android Studio’s Device File Explorer.

5. Open the selected database file

SQLScout’s tight integration with Android Studio allows us to connect to a database file by simply double-clicking it or selected the “Open” action in the context menu.

Figure 5. Using SQLScout to connect to a database by simply opening a database file.

SQLScout will automatically download the database file to the local file system and connect to it. After the connection to the database is established, we will be able to browse the database’s schema in SQLScout’s SQLite Explorer.

Figure 6. Database schema displayed in SQLScout’s SQLite Explorer

6. Viewing and updating database records

From this point on, we can view and update records in the database. SQLScout provides 2 ways to do this:

  1. SQLite Console. The console lets us visualize all the records in a table. It also supports adding, updating and removing records through its UI, without using SQL statements (figure 6).
  2. SQL Editor. SQLScout’s SQL editor supports SQLite’s SQL dialect, and provides all the functionality we would expect from an IDE editor: syntax highlighting, code completion (including SQLite-specific functions) and symbol navigation. We can also execute SQL statements from the editor with a single click (figure 7).
Figure 6. SQLScout’s UI for viewing and editing database records, without SQL.
Figure 7. SQLScout’s SQL editor.

To update our application’s database with the changes we made to the local copy, we simply need to click the “Upload” action in SQLScout’s SQLite Explorer.

Figure 8. Uploading database changes to the Android device or emulator.

Conclusion

In this article, we have explored how to easy it is to use SQLScout to debug SQLite databases in Xamarin-based Android applications:

  1. Build the APK using your favorite IDE/build system (Visual Studio, in the case of Xamarin).
  2. Import the built the APK into Android Studio using its “Profile or import APK” feature.
  3. Set the project’s Android SDK (hopefully future versions of Android Studio will do this automatically).
  4. Run the application in an Android device or emulator.
  5. Locate the database file in Android Studio’s Device File Explorer and open it.
  6. From this point you can use SQLScout to view and update the records in the database.

SQLScout for Visual Studio?

SQLScout is a plugin for Android Studio and it doesn’t work with Visual Studio. The person who asked us whether it is possible to use SQLScout to debug databases in Xamarin also mentioned that he would like to use SQLScout in Visual Studio, instead of using two IDEs to develop applications and debug databases.

I’d like to kindly ask you, dear reader, to please leave a comment or send us an email to contact at idescout.com if you would consider using a Visual Studio-compatible version of SQLScout (you can see all its features in action here).