For someone needing an iOS or Android specific local database CRUD solution, this may be helpful to you, but it should be noted that this article covers a deprecated standard.

Background

I had to provide a one page survey for a brick and mortar store to gather customer data at various off-site events they hosted. This survey would be loaded on iPads and needed to be able to work offline as internet or 3G service would not always be available.

This obviously puts us in the realm of localStorage, but localStorage itself only supports key / value pairings. Not ideal for this use-case.

After checking around the net, i realized that WebSQL was a viable option for my project, however asynchronous DB queries can be tricky, so I present a few thoughts on the matter in the form of a basic walkthrough.

Notes.

1. WebSQL is not fully supported, at time of writing browsers w/ support for WebSQl are

   • Safari (4.0+)
   • Chrome (4.0+)
   • Opera (10.5+)

2. IndexedDB is going to be awesome, and likely more widely supported than WebSQL, early comparisons here between the two.

3. Modernizr should often be used for detecting what features your client's browsers support, however for this example, I am assuming that the goal is to run this app on a mobile device (which implies offline support) so the less code the better and we certainly don't need a full library to handle support detection for localStorage and WebSQL.

Diving in

objects are a good way to avoid namespace pollution and general ugliness. So lets create a new object now.

var websql = {};

Now, let's set up our checks for support.

//returns true or false
var sqlsupport = !!window.openDatabase;

//check if we support local storage, if so, create a new localStorage object
websql.localstorage = function(){
    try {
        return 'localStorage' in window && window['localStorage'] !== null;
    } catch(e) {
        return false;
    }
};

If we indeed have support for websql, set up or DB as well as vars for our tables and colums.

if(sqlsupport){ 
    websql.livedb = openDatabase('th_example_db', '1.0', 'th example database', 2 * 1024 * 1024);
    websql.tables = 'people ';
    websql.dbcols = ['person_prefix', 'person_name'];
}

note The syntax for openDatabase is

window.openDatabase( DatabaseName, DatabaseVersion, DisplayName, EstimatedSize )

Be careful with the DatabaseVersion parameter. If it's set and it doesn't match, the operation will fail.

Now we move the the real crux of the biscut, the actual transaction wrapper.

//A wrapper method for our local DB related actions
websql.dbtransaction = function(action){
    var params = [],
        callback = null,
        bonusAction = null,
        actionBuffer = [],
        cols = (action == 'create') ? '('+'person_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, '+websql.dbcols.join(', ')+')' 
                                    : '('+websql.dbcols.join(', ')+')',
        args = Array.prototype.slice.call(arguments),
        placeholders = function(){
            var arr = [];
            for(var i=0; i < websql.dbcols.length; arr.push('?'), i++);
            return '('+arr.join(', ')+')';
        };

    //shift the first elem off, as it is already set as our action.
    args.shift();

    //lets evaluate our args and set params, bonusAction and callback appropriately
    for(var i=0; i < args.length; i++){
        if(typeof args[i] == 'object'){
            if(args[i].constructor == Array){ params = args[i]; }
        }
        if(typeof args[i] == 'string') { bonusAction = args[i]; }
        if(typeof args[i] == 'function') { callback = args[i]; }
    }

    websql.livedb.transaction(function(tx){
        switch(action){
            case 'create':
                actionBuffer.push('CREATE TABLE IF NOT EXISTS ');
                actionBuffer.push(websql.tables);
                actionBuffer.push(cols);
                break;
            case 'insert':
                actionBuffer.push('INSERT INTO ');
                actionBuffer.push(websql.tables);
                actionBuffer.push(cols);
                actionBuffer.push(' VALUES ');
                actionBuffer.push(placeholders());
                break;
            case 'update':
                actionBuffer.push('UPDATE ');
                actionBuffer.push(websql.tables);
                actionBuffer.push('SET ');
                break;
            case 'select':
                actionBuffer.push('SELECT * FROM ');
                actionBuffer.push(websql.tables);
                break;
            case 'drop':
                actionBuffer.push('DROP TABLE IF EXISTS ');
                actionBuffer.push(websql.tables);
                break;
        }

        try{
            //a final chance to modify the query
            //comes in handy for WHERE clauses,
            //is neccessary for UPDATEs
            if(bonusAction !== null) { actionBuffer.push(bonusAction.trim()+' '); }

            tx.executeSql(actionBuffer.join(' '), params, callback);

            //below is for reporting example only
            if(action == 'insert'){
                actionBuffer.splice(4,1);
                var paramsMod = [];
                for(var c=0; c < params.length; c++){
                    paramsMod[c] = "'"+params[c]+"'";
                }
                actionBuffer.push('('+paramsMod.join(', ')+')');
            }
            $('#sql-executed').prepend('<p>'+actionBuffer.join('')+'</p>');
            //end reporting example
        }
        catch(e){
            alert('Local database '+action+' failed');
        }

    });

};

Since there is a lot happening let's take a look at the above method piece by piece.

websql.dbtransaction requires an 'action' arg. This would be one of our DB actions, create, insert, select, update, or delete.

we declare our vars, noting for now that

• 'actionBuffer' is an array that we will push items onto in order to craft our SQL statement
• 'cols' is essentially our websql.dbcols array joined which will later be joined to 'actionBuffer'
• 'args' we hijack the Array.splice function to make it work on the arguments faux-array and set the result to our 'args' array
• 'placeholders' a function we call to create a string that should look something like ('?', '?') for our db.transactions.

next we evaluate our args array so we can optionally set 'callback', 'params' for our SQL statement or 'bonusActions' (like a WHERE clause). then we enter a switch statement and push items onto our 'actionBuffer' array accordingly. after that we get one last chance to push items onto 'actionBuffer' before it is passed to tx.executeSql and joined.

note the syntax for executeSQL is

transaction.executeSQL( SQLStatement, SQLParameters, ResultsetCallback, ErrorCallback )

we skip ErrorCallback by wrapping this method call in a try/catch block and call it like so

tx.executeSql(actionBuffer.join(' '), params, callback);

The the real magic will often lie in what you choose to do in your callback functions.

After that it's all pretty straightforward. We have our binds / eventHandlers. Note how we are using websql.dbtransaction

websql.binds = function(){
    $("#websql-insert-names").submit(websql.insertSubmitHandler);
    $("#websql-update-names").submit(websql.updateSubmitHandler);
    $("#websql-drop-names").submit(websql.dropSubmitHandler);
};


websql.insertSubmitHandler = function(e){    
    var person_name = $("#name").val().trim(),
        params = [
                    '',                 //prefix
                    person_name         //name
                ];

    if(person_name !== ''){
        websql.dbtransaction('insert', params, function(){
                websql.displayPeeps();
                websql.form_reset();
         });
    }

    return false;
};

websql.updateSubmitHandler = function(e){
    var person_id = $("input[name='update-name']:checked").val(),
        newPrefix = $('#name-prefix').val();

    if($("input[name='update-name']:checked").val() !== undefined){
        websql.dbtransaction('update', 'person_prefix = \''+newPrefix+'\' WHERE person_id = \''+person_id+'\'', function(){
            websql.displayPeeps();
        });
    }

    return false;
};

websql.dropSubmitHandler = function(e){
    websql.dbtransaction('drop', function(){
        websql.dbtransaction('create', function(){
            websql.displayPeeps();
        });
    });
    return false;
};

A few utility functions

//resets all the form elements on the 'main' form page
websql.form_reset = function(){
    $("input#name").val('').focus();
};

websql.hide = function(){
    $('#websql-update-names, #websql-drop-names, #results').fadeOut(function(){ $(this).addClass('hidden'); });
};

websql.show = function(){
    $('.hidden').fadeIn(function(){ $(this).removeClass('hidden'); });
};


websql.displayPeeps = function(){
    websql.dbtransaction('select', function(tx, res){
        var htmlRadios = [],
            htmlList = [],
            l = res.rows.length,
            i;

            if(l>0){
                for(i = 0; i<l; i++){
                  htmlRadios.push('<li><input type="radio" name="update-name" value="'+res.rows.item(i).person_id+'"/>'+res.rows.item(i).person_name+'<\/li>');
                  htmlList.push('<li><strong>'+res.rows.item(i).person_prefix+'</strong> '+res.rows.item(i).person_name+'<\/li>');
                }

                $('#results ul').html(htmlList.join(''));
                $('#update-list').html(htmlRadios.join(''));

                websql.show();
            } else {
                websql.hide();
            }

    });
};

and, our init function, which will be called on $(document).ready()

//called in document.ready and starts the websql ball rolling
websql.init = function(){
    //Similar to other checks for 'Safari', this conditional is unecessary if this is only used on iPads/Safari 
    if(websql.livedb && websql.localstorage()){
        //Bind everything
        websql.binds();

        //reset the form
        websql.form_reset();

        // ensure our tables exist
        websql.dbtransaction('create');

        //Get our current DB counts
        websql.displayPeeps();

    } else {
        alert('Either Web SQL Databases or Local Storage are not supported on this device. Please view this page in Safari, Chrome or Opera.');
    }
};

Conclusion

Hopefully this simple example is helpful. My goal was that websql.dbtransaction method outlined above would make it easy to access local WebSQL DBs. It has for me, so hopefully it makes someone else's life a bit easier.