HTML5 in-browser SQL database storage

I’ve been working on a new presentation to add to my Advanced Database Structures course: HTML5 Storage. It covers both the Web Storage and Web Database APIs. The former is a key-value store most likely to be used like beefed-up cookies or for form data persistence. The latter one is the more interesting of the two.

The Web SQL Database API is the logical progression from Google Gears: a programmatic way of accessing a SQL database that’s built into your web browser. The current crop of browsers also have this all nice and sandboxed, so we shouldn’t end up suffering from some of the cross-site data access issues that were a worry in the previous generation.

So far, I think the best user-space access to the databases has to go to Safari. You can get at them in the Security tab of the Preferences window:

But, even better than that, the Developer tools have an actual browser and query console—with smart SQL tab-completion!

(I didn’t do anything magic to hide that rs_hints object—it’s a view. I guess the Safari browser doesn’t show views.)

All of the current browser implementations use SQLite as their storage engine. SQLite has robust SQL support—it’s not meant to be a data warehouse, but it’ll probably do pretty much anything you’d think was sane to do inside a web browser. I’ve successfully been able to use views, unions, several types of joins, indexes, and primary and foreign keys.

I’ve whipped up a quick app that shows off some of the technology: Sudoku + HTML5. The storage layer for the app is done using the SQL engine, as is the business logic (game rules) of the Sudoku game itself. The presentation layer is, of course, done in JavaScript using the HTML5 canvas API. I’ve tested it in Safari, Opera, and Chrome. Firefox, as of 3.5, does not yet support the SQL Database API. I don’t have access to Internet Explorer to test it, but 8 shouldn’t work due to the use of the canvas element.

Here’s a really quick overview of the Web SQL Database API:

var DB_NAME    = "ricko-sudoku";
var DB_VERSION = "1.0";
var DB_TITLE   = "Rick O's SQL Sudoku";
var DB_BYTES   = 500000;
var db = window.openDatabase(DB_NAME, DB_VERSION, DB_TITLE, DB_BYTES);

The first part is very straightforward: I define a few constants and make the openDatabase call. I’m using the constants to illustrate the variable placement—your app may just use literals in the function call.

The openDatabase call takes one additional optional argument that I have elided: a function callback. If the database needs to be created, this being the first time you’ve ever tried to open this database, the extra function will be called. The theory is that you could write all of your table creation and other DDL queries in this callback. However, since SQLite supports the IF EXISTS syntax, I find it easier to run my schema setups immediately no matter if it’s the first time or not.

Before we run any queries, though, it would be helpful to have debugging functions for when our queries go pear-shaped:

function sqlFail(err) { alert("SQL failed: " + err.message); }
function txFail(err) { alert("TX failed: " + err.message); }

Again, nothing tricky here: we’ll use these functions as callback error handler in future queries. For illustrative purposes, we could also have a logger for when our queries succeed:

function sqlWin(tx, response) { console.log("SQL succeeded: " + response.rows.length + " rows."); }
function txWin(tx) { console.log("TX succeeded."); }

A little trickier, but we’ll go into more detail on result sets in a bit. On with the database setup:

db.transaction( function(tx) {
    var queryParams = [];
    tx.executeSql(
        "CREATE TABLE IF NOT EXISTS animals (id INT NOT NULL PRIMARY KEY AUTOINCREMENT, name VARCHAR(20) NOT NULL UNIQUE);",
        queryParams,
        sqlWin,
        sqlFail
    );
}, txFail, txWin);

Again, I’m a little verbose and heavy with the variables for illustrative purposes—you’d use a literal [] instead of a queryParams local variable. Inside of a transaction function, your queries are executed atomically and serially, but also asynchronously so be careful—hence all of the callback functions. The transaction function even has optional failure/success callbacks of its own, but again be careful: they’re in the opposite order of the query functions.

If we insert a row, we can get back the ID of an autogenerated number:

db.transaction( function(tx) {
    var animalName = "Cheetah";
    tx.executeSql("INSERT INTO animals (name) VALUES (?);",
        [ animalName ],
        function(tx, results) {
            var animalId     = results.insertId;
            var animalsAdded = results.rowsAffected;
            // for this simple insert, we should only see 1 row
        },
        sqlFail
    );
});

Using SELECT queries is just as easy:

db.readTransaction( function(tx) {
    var animalName = "%Lion%";
    tx.executeSql("SELECT id, name FROM animals WHERE (name LIKE ?);",
        [ animalName ],
        function(tx, results) {
            for(var i = 0; i < results.rows.length; i++) {
                var row = results.rows.item(i);
                console.log("Animal[" + row.id + "] = " + row.name);
            } // for i
        },
        sqlFail
});

Still nothing too tricky here. Query parameters work just like you’d expect: replace the parameter with a question mark in the query, then pass it in the array afterward. The callback here iterates over the records, each of which is a simple object with column names for keys, and prints them on the debugging console.

There’s not much more to it than that—it’s surprisingly easy for something designed by committee!