Client-side sql databases in Javascript


It occurred to me that I didn't really go into much detail about the local storage part of my 5K TODO app. It's quite a neat piece of tech and because it works in Safari it also works on the iPhone. That obviously means you can use javascript+local storage to create apps for your iPhone without needing to touch the iPhone SDK or learn objective-c. As everything is client-side you also don't need server-side code to store your app state. The only downside is that local storage is at the mercy of the user. If they decide to reset Safari or generally clear out their user data the local storage goes too! Then again this would be true of files on a Mac's file-system anyway...

So for any "serious" stuff you might want to look elsewhere, but in the meantime you can still build interesting client-side apps that use local-storage and run on your iPhone.

At the time of writing database storage in html5 is still a work in progress and is only available in Safari/Webkit. In the future it should appear in Firefox etc. Though there's a HTML5 Wrapper for Google Gears that uses Gears to provide the html5 local storage functions (e.g. openDatabase).

Opening the database

The main function we're concerned with is openDatabase which let's us open/create a database that can only be accessed by the current domain (in a similar manner to cookies). In the TODO app I use this code to see if openDatabase is available and output a message if not and stop any further processing:

if ( !window.openDatabase ) {
    $('add_note').innerHTML = "Browser does not support local storage."
    return;
}

(note $ is a shortcut to document.getElementById in the TODO app code)

Assuming we find the openDatabase function we can then get a database object thus:

var dbName = '5KTodo',
     db = openDatabase(dbName, '1.0' /*version*/, dbName, 65536 /*max size*/);

In this case we create/open a database called 5KTodo. We provide a version number (not really used at this stage, but is intended for allowing upgrading database schemas) and a max size. The max size is provided as a hint to the browser as to how big the database will grow. The upper limit for this is defined by the browser (and is about 5M in Safari).

Now once we've got our database object we can start running some SQL. We do this using the database object's transaction method and the transaction's (tx) executeSql function:

db.transaction(
    function(tx) {
        tx.executeSql(sql, params, callback);
    }
);

That will run the sql using the given params (replacing ? in the sql string with the param values to avoid sql-injection attacks) and then calls the callback with the results (if it succeeded).

Creating the tables

The first time we run the app the database will not exist and will contains no database tables, so we need to run some SQL to create the tables:

function dbTransaction(fn) {
    db.transaction(fn);
}

/*create todo table*/
dbTransaction(
    function (tx) {
        tx.executeSql('CREATE TABLE todo (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, description TEXT NOT NULL DEFAULT "", todo_order INTEGER NOT NULL DEFAULT 0, done INTEGER NOT NULL DEFAULT 0 );');
    }
);

On the next run of the application that will fail silently, which is fine as it only needs to run successfully once (to create the tables). It's key that the create table statement runs in it's own transaction though, so when it fails it doesn't stop anything else running. Note that dbTransaction used here is only so that YUICompressor can better compress the code, as this code is from an example for the 5K App.

Reading from the database

After creating the tables (or doing nothing if they are already there) the next step is to see what's already in the database. Unlike the create table statement we expect to get some data back, so we provide a callback to get the results:

dbTransaction(
    function (tx) {
        listTODOs(tx, 'SELECT * FROM todo ORDER BY todo_order ASC', []);
    }
);

/*run the sql to read the notes and insert them into the DOM*/
function listTODOs(tx, sql, params) {
    tx.executeSql(
        sql, params,
        function(tx, results) {
            var rows = results.rows;
            for ( var i = 0; i < rows.length; i++ ) {
                var row = rows.item(i),
                    li = createElement('li'),
                    checkbox = createElement('input'),
                    label = createElement('label');
                setAttribute(checkbox, 'type','checkbox');
                setAttribute(checkbox, 'id', 'checkbox_'+row['id']);
                setAttribute(label, 'for', 'checkbox_'+row['id']);
                appendChild(label, createTextNode(row['description']));
                appendChild(li,checkbox);
                appendChild(li,label);
                setAttribute(li,'id', 'note_'+row['id']);

                if ( !row['done'] ) {
                    // not done, so insert above text field
                    notesList.insertBefore(li,$('add_note'));
                }
                else {
                    // done so put below text field
                    setAttribute(checkbox,'checked','checked');
                    li.className = 'done';
                    appendChild(notesList, li);
                }

                attachTODOHandlers(row['id'],li,checkbox);
            }
        });
}

(note there are a lot of shortcut functions in there, but hopefully you should get the idea)

What that basically does is execute 'SELECT * FROM todo ORDER BY todo_order ASC' on the database in a transaction (tx) and then takes the results and inserts them into the DOM.

Inserting into the database

Inserting into the database is pretty straightforward - we just use an SQL insert statement. Though in the case of the 5K TODO app we need to do a couple of other things. The first is figuring out the next highest todo_order, then we insert the TODO item and next we re-select the TODO items from the database (with the id of the newly inserted item - results.insertId). The 2nd part is a bit overly obtuse in some respects, but was done so as to help cut down on code size (to fit under the 5K limit):

function insertTODO(description) {
    dbTransaction(
        function(tx) {
            // set order to be one after items that aren't done
            tx.executeSql('SELECT MAX(todo_order) AS next_order FROM todo WHERE done=0', [],
                function(tx,results) {
                    var order = results.rows.item(0)['next_order'] + 1;
                    tx.executeSql('INSERT INTO todo (description,todo_order) VALUES(?,?)', [description,order],
                        function(tx,results) {
                            listTODOs(tx, 'SELECT * FROM todo WHERE id=?', [results.insertId]);
                            updateTODOOrder(tx);
                        }
                    );
                }
            );
        }
    );
}

As the later SQL statements depend on results from the earlier statement(s) they actually get run in the statement callback functions.

It's fairly straightforward to access a database this way. For those familiar with databases it should all be pretty familiar territory. There's plenty of more info on Apple's site about using the Javascript database. You may also want to look at the source code the the 5K TODO app, though be warned a little bit of readability has been sacrificed for code size.