Using a Local Database with Titanium

November 2, 2010

I’m going to go through a few of the key concepts required to get a database working in your Titanium app. I’ll first give a heads-up on the database environment, then give some code tips on how to pull things off.

The Database Environment

iPhone apps use SQLite databases. I have hitherto used MySQL, so there were a few things to learn about how they’re different. SQLite 3 doesn’t have a super-powerful query language. It will do all the usual selecting and inserting of stuff, but you can’t do advanced math or trigonometric stuff (which I care about since I use latitude and longitude stuff in my apps). Doing table maintenance, like deleting tables, is different. Just be prepared for that as you start learning the query language.

If you’re on a Mac, and you want a simple and clean tool to work on your SQLite databases, I recommend Base. I imagine there are more powerful SQL tools available that will do SQLite along with other database platforms.

Database Files

You typically put your database file (in this example, airports.db) in your Resources folder in your Titanium project. The database installation will reference that folder to install your database.
//Install Database
Ti.Database.install('airports.db', 'airports');

The installation process takes the .db file and turns it into a .sql file and places it into the Application Support section of your app. This is a location on your computer and the iPhone where the app references the data. On a Mac, it’s here:
~/Library/Application Support/iPhone Simulator/4.x/Applications/bunchStuffabcdef_1234567/Library/Application Support/database/airports.sql
Real easy to get to, right? Fortunately you never need to get in there since I learned a handy trick that I’ll explain later.

Your database only gets installed once, no matter how many times you include the above install code. Behind the scenes, I suspect Titanium is checking if the database file is present, then installing the database if it’s not.

Accessing your Database

Once you have installed your database, you’re now ready to plug in to it.
//Open DB
var db = Ti.Database.open('airports');

Opening the database makes it available for use in your app via the db variable. From there, we can start executing queries.
//Query
rows = db.execute('SELECT column FROM table');

If you need to walk through your result set, then you can use this fancy technique to process each row and access the data by column name:
//Walk through the results
while(rows.isValidRow()){
     Ti.API.info(rows.fieldByName('column'));
     rows.next();
}

Clean Up After Yourself

The last thing to do is close the result set and close the database. This will release the database resources from memory.
//Close result set
rows.close();
//Close database
db.close();

In a separate tutorial, I’ll describe how to build a table with multiple labels per row using the database, but hopefully this is enough information to get you walking through the data.

Database Updates

This last piece comes as a result of a lot of blood, sweat and tears on my part. I recently had an application with a database that users referenced when using my app. In an app update, I had new data added to the database and needed it to be part of the update. It turns out that app updates don’t automatically grab the new database. They’re cached on the user’s iPhone. So it turns out that you have to create code to delete the old database and grab the new one. Fortunately, the code for that is really simple.

//Check if the file is there (see the database path above)
var f = Ti.Filesystem.getFile(
Ti.Filesystem.applicationSupportDirectory+'/database', 'airports.sql');
//If it's there, delete it and reinstall the DB
if(f.exists() == true){
     f.deleteFile();
     Ti.Database.install('airports.db', 'airports');
}else{
     //Otherwise, install it for the first time
     Ti.Database.install('airports.db', 'airports');
}

This ensures the cached database always gets replaced with what you have in your Resources folder. Pretty important if your database is a source of data, and not one you create and write to on the fly.

All Done

So that’s an overview of using a database. There are more pieces to the API that you can look up, but this is just to go over some of the basics.

If you want to grab some code to see how to initialize and query a database, grab the Persistence project, import it into Titanium, and have a look through the code. Good luck and feel free to post any questions in the comments.