One aspect of my current project, the Alloy MVC framework for building cross-platform mobile apps, is simple integration with local storage via SQLite databases. SQLite is a powerful and relatively simple way to store data for offline use, or just to cache remote data to speed up interactions. Unfortunately, the current data and structure of SQLite databases can sometimes be tricky to ascertain when housed on mobile devices, emulators, and simulators. This is especially true when developing for multiple platforms and having gone through multiple iterations of your data structure.
There’s some common things it would be great to know quickly about your deployed databases, like:
- What SQLite database files are installed?
- What tables are created on those databases?
- What is the structure of the tables? Primary keys?
- What records are present on the tables? This is critical in determining if you model layer is working as expected.
In order to make this a little less painful of an experience, let’s take a look at a very simple way to inspect SQLite databases, and where we can find those databases on various mobile platforms. First we’ll need a tool to actually interact with a SQLite database…
My weapon of choice is generally the command line. For that reason I tend to stick to the sqlite3 command line tool. It comes preloaded on Mac OSX and is available for all other major OSes. Interacting with a SQLite database becomes as easy as:
1 2 3 4 5 6 7 8
You can quickly get to know the rest of the commands by executing
.help in the sqlite3 prompt. Remember to include the
. before the name of the command.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51
The nice thing about the Android interface is that we can use a single command to find the SQLite databases on both emulators and devices. Assuming you have an open emulator and/or connected Android devices, execute
adb devices to get a listing of their serial numbers.
1 2 3 4
So now connecting to an emulator or device is easy. Just pick the target serial number and execute
adb -s MYSERIAL shell. We’ll use this shell for the duration of the :
All commands from here on in will assume you are already connected via
Now that we’re connected to the target Android device/emulator, let’s get a list of the SQLite databases installed on our target app. To find that, we’ll first need our target app’s ID. It’s typically a domain name format, like
com.somedomain.someapp. If you don’t know how to find your app ID, you can always list the installed apps on your device/emulator with the adb shell and search there:
1 2 3 4 5 6 7 8 9 10
In this case we’ll use
com.appcelerator.Harness as the target app, which happens to be the test harness app I used for testing Alloy. Now we just need to list the databases installed on this app:
1 2 3
In this case we find 2 installed databases,
Titanium. Congrats, you found your Android SQLite databases. To start inspecting, you just need to open up the database file with the
sqlite3 command while in the adb shell. That is, Android already has
sqlite3 available at its shell, so you can use it directly from the shell:
1 2 3 4 5
Now you’ve got yourself full access to the live SQLite database on your Android emulator/device. To take a look at a handful of useful ways to inspect your database from the sqite3 prompt, check out the inspecting your SQLite database section.
Unlike Android, you’ll have to take a different approach in locating your SQLite database(s) depending on whether you are inspecting a simulator or device. It’s actually pretty easy on the simulator. Devices, on the other hand, are a bit more of a pain in the ass to work with with respect to SQLite databases. So it goes with iOS.
To find a SQLite database on an iPhone/iPad simulator, let’s first track down a listing of all the installed applications. To find this you’ll need your current Mac username as well as the iOS version of the simulator you are running. For this example let’s assume the following:
- My username is tlukasavage
- iOS SDK version is 6.1
To list the applications we now execute the following. You can use Finder, but I prefer the CLI:
1 2 3 4 5 6 7 8
Now you need to select which app ID is the one that corresponds to your installed app. For this example, we’ll assume it’s
25D2F0D4-B225-4E19-A830-4EE6DB0093A0. Once you’ve determined that, we’ll dig a little bit deeper to find the actual location of that app’s SQLite databases. Bear in mind, if the following directories don’t exist in your app, you likely don’t actually have a SQLite database installed. We’ll continue from the previous code block:
1 2 3 4 5
Hey look, there’s our databases! Now that we have the location, opening up the databases is as easy as issuing the
1 2 3 4 5
You can now hop to the inspecting your SQLite database section to see what you can do with it.
It’s unfortunate, but on iOS we don’t really have a good CLI method for accessing the on-device SQLite database like we do with Android. For this, we’ll need to crack open Xcode, and more specifically Organizer.
Open up Xcode and then hit
SHIFT+CMD+2 or go to
Window -> Organizer to open up Organizer. In there we’ll find a listing of your currently configured iOS devices. Follow these steps to retrieve the SQLite database (and additional application data) on your target device:
- Select the device in the left panel, in my case Tony Lukasavage’s iPad.
- Expand the device and select Applications.
- In the top right panel select your target application, in this case Harness.
- In the “Data Files in Sandbox” panel, make sure your SQLite database file is present under the “Library/Private Documents” folder. In this case it’s _alloy_.sqlite.
- Download the application data in a “xcappdata” file by clicking on the Download button at the bottom of Organizer. It should save the file with a long file name that includes your app’s ID and a timestamp. In this case, mine is com.appcelerator.Harness 2013-04-07 18.104.22.1683.xcappdata.
Now that you have the app data downloaded, we just need to navigate to the embedded SQLite database and you’ll be free to use
sqlite3 on it. Assuming you downloaded the xcappdata file to your current path, this is how you would find it:
1 2 3 4 5 6
Though a bit more effort than the simulator, you now have full access to the SQLite database from your iOS device. Move on to the next section for some tips of what you can do with it to learn more about your app.
Inspecting the SQLite database
Now that you’ve found your app’s SQLite database, here’s a hadful of useful commands to inspect its state and contents. While it takes various methods to access these database files, interacting is exactly the same on all when using the
1 2 3
1 2 3
1 2 3 4 5 6
1 2 3 4 5 6 7
This is only a very small sampling of quick things you can check to find the current state of your SQLite databases. For more details on the full functionality of the sqlite3 command, be sure to check out the full documentation.
I’d highly suggest taking the time to learn the CLI and the syntax of SQLite itself. If, however, you prefer a GUI…
Honestly, I’m not a huge fan of Firefox, but it has one nice thing going for it: SQLite Manager. SQLite Manager is a Firefox add-on that provides a simple, clean interface for interacting with SQLite databases. Not much in the way of bells and whistles, but hey, this is SQLite, there’s not a lot of bells and whistles in the first place. The nice part about it is it’s free and it will work anywhere Firefox will.
You can choose and SQLite app you like, but I recommend this one for the sake of its price, availability, and simplicity. If you aren’t a command line warrior, this is a solid tool to lean on.