Appcelerator Blog

The Leading Resource for All Things Mobile

At some point during your app development, you’ll have to store some values in a local database. This data could be data shipped with the app, data generated by your user while using your app, or data gathered from external sources like web services.

Both iOS and Android have native support for SQLite databases, and using the Titanium.Database object and its methods is really easy and straight-forward. However, I’ve seen that there’s a new generation of developers who have never used structured databases or don’t really get along that well with the SQL syntax.

If you’ve read my previous blog posts like this one and this one, you know I’m a fan of creating libraries and abstractions. They make the code more readable and easier to maintain. Besides, in the process you learn many things about the underlying technology and its behavior. With this in mind, I’d like to share with you a little library I call JSSQL.

JSSQL is simply a CommonJS library you drop into your Titanium project and allows you to access SQLite databases using a syntax that makes it feel like accessing JavaScript objects. The library treats tables and result sets as JSON strings and even has a method for converting a JSON string into a physical SQLite table, which you can them manipulate in any way you want.

To begin, download the library from the Github repo. If you’re using Alloy you’ll save the com.alcoapps.dbhelper.js file to your apps/lib folder. Then obtain an reference to the module and create a new instance pointing to your database. The constructor receives the path to the database file and name you’d like to assign to it.

At this point the variable db IS your database, and it’s ready for action. Let’s assume you have a database table called “events”.

Getting data from the database.

The GET method receives a JavaScript object with several properties to establish the criteria. You have two options to interact with this method: returning the result set, or sending a callback that will receive the result set. In either case, the result is a JSON String.

EXEC

The EXEC method will execute the given SQL String and returns a JSON string with the result set.

var myTable=db.exec('SELECT * FROM events where id > 5');

CREATE FROM JSON

The CREATEFROMJSON method receives a flat JSON string and a table name, and creates a table on the database with the given data.

db.createFromJSON(jsonString,'mytable');

Other self-explanatory methods are DROP to remove a table, TABLEEXISTS to check for existence of a table and CLOSE to close the database.

Finally, this is not a replacement for Alloy Models which are based on BackBone and have many additional features like synchronization adapters. If you require simple, plain-vanilla data access, then JSSQL could be an easy-to-implement solution. In any case, make sure you browse the source code and see how it works, and as usual, if you find some errors, omissions, or would like to make it better, feel free to send a pull request.