Appcelerator Blog

The Leading Resource for All Things Mobile

Make your SQLite database feel more JavaScript-like

38 Flares 38 Flares ×

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.

var DBH=require('com.alcoapps.dbhelper');
var db=new DBH.dbhelper('/alco.sqlite','alco');

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.

Option 1

var myTable=db.get({
    fields  : '*',
    table   : 'events',
    where   : 'country like "U%"',
    order   : 'id DESC'
});

Option 2

db.get({
    fields  : '*',
    table   : 'events',
    where   : 'country="US"',
    order   : 'id DESC'
},function(rs){
    console.log(rs);
});

Adding records to the database

The SET method receives the table to insert to and the data to add, and returns the id of the record that was inserted.

var rowId=db.set({
    table: 'events',
    data : {
        country     : 'France',
        name        : 'TiConf FR'
    }
});

Modifying existing records

The EDIT method receives the table name, the data to modify and the condition that will find the record you wish to edit, and returns the amount of records that were affected.

var rowsAffected=db.edit({
    table   : 'events',
    data    :{
        name : 'TiConf PR',
        country : 'PR'
    },
    where   : 'id = 1'
});

SQL DELETE

The DELETE method receives the table name and the condition that will get you the record to delete, and returns the amount of rows that were deleted.

var rowsAffected=db.delete({
    table   : "events",
    where   : 'name="TiConf PR"'
});

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.

38 Flares Twitter 0 Facebook 0 Google+ 25 LinkedIn 13 Email -- 38 Flares ×

4 Comments

  1. But how would I join tables?

  2. Looking at the code it should be pretty easy to hack the get method to do something like:
    db.get({
    “fields”: “*”,
    “from”: “events,stats”,
    “where”: “events.id=stats.id”
    })

    • Ricardo Alcocer

      Feel free to open an issue or submit a PR on Github. I agree, it should be easy to add.

  3. Janis P

    Can this be used outside titanium?

Comments are closed.

Sign up for updates!

Become a mobile leader. Take the first step to scale mobile innovation throughout your enterprise.
Get in touch
computer and tablet showing Appcelerator software
Start free, grow from there.
38 Flares Twitter 0 Facebook 0 Google+ 25 LinkedIn 13 Email -- 38 Flares ×