Improving WebSql interaction using jQuery Deferred

I recently inherited a project that made heavy use of WebSql. Though it has been deprecated as a client side technology, this project was using it to create an offline web application that could be run on mobile devices. The immediate problem that I noticed with the project was that there was no consolidation of logic into a central JavaScript file. Each page included inline functions that essentially did the same thing, just using different queries. After profiling the site I realized that this "distribution" of logic also led to multiple instances of the web sql database being instantiated.... which, as you guessed, is a problem.

To address this issue I started by creating a wrapper for the Web SQL functions that would make it easier to work with the technology. The wrapper removes the work of instantiating the database object and managing the transaction, and it uses jQuery Deferred objects to make it easier to work with the results returned by the Web SQL calls.

/* Adding namespace objects to keep the code out of the global namespace */
var viaMacchina = viaMacchina || {};

viaMacchina.webSql = viaMacchina.webSql || {};

/* Database functions */
viaMacchina.webSql.Database = viaMacchina.webSql.Database || {};

viaMacchina.webSql.Database.open = function () {

    if (viaMacchina.webSql.Database.db === null || viaMacchina.webSql.Database.db === undefined) {
        var dbSize = 2 * 1024 * 1024;
        viaMacchina.webSql.Database.db = openDatabase("viaMacchinaDatabase", "1.0", "viaMacchina WebSql Database", dbSize);
    }

    return viaMacchina.webSql.Database.db;
};

viaMacchina.webSql.Database.onSuccess = function (results) {
    // Add default logging or anything else that may be appropriate.
};

viaMacchina.webSql.Database.onError = function (error) {
    alert(error.message);
};

viaMacchina.webSql.Database.executeCommand = function (commandText, data, onSuccess, onError) {

    var progress = $.Deferred();

    if (data === undefined || data === null) {
        data = [];
    }

    if (onSuccess === undefined || onSuccess === null) {
        onSuccess = viaMacchina.webSql.Database.onSuccess;
    }

    if (onError === undefined || onError === null) {
        onError = viaMacchina.webSql.Database.onError;
    }

    var db = viaMacchina.webSql.Database.open();

    db.transaction(function (tx) {

        tx.executeSql(commandText, data,

            function (tx, results) {

                progress.resolve(results);
                onSuccess(results);

            },

            function (tx, error) {

                progress.reject(error);
                onError(error);

            });
    });

    return progress;
};

Using the wrapper you can now call your Web SQL database like so:

var command = "SELECT * FROM Locations WHERE BuildingID=" + buildingId + " ORDER BY LocationId";
viaMacchina.webSql.Database.executeCommand(command).done(function (results) { 
    // process the results from the database call.
});

As simple as it is, this minor addition resolved the majority of issues that the project was suffering from. But sometimes, the simpleset solutions are the best solutions!

I hope this helps!