pmuellr is Patrick Mueller

other pmuellr thangs: home page, twitter, flickr, github

Thursday, January 12, 2012

making Web SQL easier to use

One HTML5-ish area I've not yet played much with, but am starting to now, is Web SQL. Web SQL is the W3 standard for "SQL in the browser", which has been orphaned by the W3C. Here are some warnings from the Web SQL Database spec at

  • "Beware. This specification is no longer in active maintenance and the Web Applications Working Group does not intend to maintain it further."

  • "This document was on the W3C Recommendation track but specification work has stopped. The specification reached an impasse: all interested implementors have used the same SQL backend (Sqlite), but we need multiple independent implementations to proceed along a standardisation path."

Despite the fact that the W3C no longer endorses Web SQL and will never 'standardize' it, implementations of Web SQL are shipping in browsers, including some mobile platforms. It's not dead yet.

what's the api like?

Here's an example right out of the spec, to count the number of rows in a table:

A function showDocCount()that calls a function db.readTransaction() that takes two anonymous functions as callbacks. One of those callbacks calls t.executeSql() passing it yet another callback, which finally, mercifully, gets the row count.

It's executing a single SQL statement.

But wait, it gets better.

If you need to run multiple SQL statements within a single transaction, you'll have to chain those executeSql() invocations within increasingly nested callbacks.

It's basically a horror show. At least as far as I'm concerned. Nested callbacks are things I hate to have to write, and things I really hate seeing other people have to write. The result is almost always unintelligible.

can we make this easier to understand?

For weinre, I implemented a stepper module used by the inspector's database interface. Not very clear what's going on there though, sorry.

The basic idea is that you provide an array of functions - the steps - that the stepper will arrange to run in order, by managing the callbacks itself. Double bonus good - you get to linearize your database transaction steps and you don't have to deal with callbacks!

I found another instance of this kind of processing in Caolan McMahon's async library - specifically the waterfall() function. Not really appropriate for Web SQL, but at least it seems to validate the approach.

I decided to implement a clean implementation of the stepper, trying to make it easy to use and understand. The result is available as WebSQLStepper at GitHub. As a use case, I took the Todos sample from Backbone, and modified it to use this library to persist the data to a Web SQL database. The complete sample is included in the GitHub repo, and here are the steps used to read all the rows of a database: