PreviousNext…

Off-line web apps: local Web SQL & Javascript

So, you know how to take our site off-line: events are triggered, caches are swapped, and even though you still have that (apparent) server-based URL going on in your address bar, you are off-line! Yes! Stick your device in airplane mode and revel in how your website is still doing its thing. Clever.

However, your world is incomplete. You have some pages going on, some links between them, stylesheets—maybe some whacked-out Javascript doing wondrous things—but something is still missing. And that something is persistence, a proper local data store that goes beyond the capabilities of the humble, fragile wee cookie.

Until the newer browser technology started to become mainstream (arguably aided by the early efforts of things like Google Gears), this was a big sticking point for off-line sites and apps, because there was nowhere to store one’s data. Now though? Sea-change, oh yes. Depending on your browser of choice, you have two options: 1) an embedded local relational database (the generic “Web SQL” standard, that in practice actually means SQLite) or Web Storage, which is a simple key-value store.

IndexedDB is one for another day. For now, we’re thinking about a local instance of SQLite with a Javascript framework to access it, which can be found in all up-to-date WebKit-based browsers, and also in Opera. “Web SQL” works well, and is surprisingly well-featured, but we hit the main hiccup with its uptake when we consider the supporting web clients: Chrome and Safari (including mobile variants thereof) are golden of course. Opera is popular, and includes a mobile instance too—again, good—but Firefox and Internet Explorer are out in the cold, with no Web SQL support (in fairness to Mozilla, Firefox instead supports IndexedDB which appears to be the preferred local data store going forward anyway—see “Further reading” section). Only you can decide whether this is an issue, but seeing as I’m been coming from the mobile web dev. angle, I’m OK with it :-)

On to the code

The Javascript API that overlays all this SQLite goodness is pretty simple to use. It is callback-mungous though, so if you’re not used to coding your JS that way, you soon will be. Here is a snippet of Javascript which opens a database for logging periwinkles. The code creates the main table (if it doesn’t already exist), which is a simple three column affair: a generated id, plus two text values for a given winkle’s name and location:

/* Open & initialise our database. Note the required parameters, 
   which comprise a short name, a version, a display name 
   & expected maximum size (in bytes)
*/
var db = openDatabase("winkles", "1.0", "Winkles Of The World", 32678);

/* Create the winkles table (if it doesn't already exist), with 
   an automatic id key, plus two columns for name & location
*/
db.transaction(function(transaction){
  transaction.executeSql("CREATE TABLE IF NOT EXISTS winkles (" +
    "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," + 
      "winklename TEXT NOT NULL, location TEXT NOT NULL);");
});

So the database and table exist (and will still be there when you close your browser session of course), and now we want to do stuff with the table. Assuming we’ve magically generated some winkle data, let’s say I now want to see the names of all winkles found in Ayrshire. Here’s how it’s done:

var showWinkles = function(location, successCallback){
  db.transaction(function(transaction){
    transaction.executeSql(("SELECT * FROM winkles WHERE location=?"), [location],
      function(transaction, results){successCallback(results);}, errCallback);
    });
  };

A few things to note here: the SQL statement is wrapped in a transaction instance, which is asynchronous. As such, the call to showWinkles is expected to pass over two things. One is the location we want data for (“Ayrshire”), and the other is a callback (successCallback). This should be a function that the SQL results get passed to for onward processing. Finally, you can see reference to some random thing called errCallback. This is optional, but recommended, and in this instance it’s a reference to an error state callback function. You can define this in your main Javascript code, something along these lines perhaps:

// Generic error callback
var errCallback = function(){
  alert("Oh noes! There haz bin a datamabase error!");
}

Well you get the idea.

Is this thing on?

A quick aside: let’s fire up the Web Inspector found in all WebKit browsers, and see just what we’ve got there:

Screenshot of Safari Web Inspector showing our winkles database

Not bad eh? There’s our database, our winkles table, and some data, right there. In fact, the Web Inspector window is quite powerful: highlight the databases icon, and the pane to the right becomes a console into which you can type SQL directly—perform updates, queries, drops, anything you want (you even get some auto-complete on your SQL).

Success

Back to the code. Let’s look at a typical “success” callback in more depth. How might one usefully call showWinkles within a web page? How about a button (with the id show-me), that when clicked, retrieves all the winkle data for a given area and presents it in a list? Anyone familiar with the “subversive injections” presentation I did with the Wookiee at UKLUG 2009 will know I like to inject code into web pages willy-nilly (dirty devil). So, when your document’s ready, bind some code to our button like so (note: all the following snippets use jQuery for brevity, selectors, and a couple of other things):

$('#show-me').click(function(){showWinkles('Ayrshire', updatePage);});

Now, let’s write the routine mentioned above as the callback we pass to showWinkles—we’re calling it updatePage. This function needs to get a handle on our HTML list (to which I have granted the imaginative epithet winkle-list), iterate our SQLResultSet, and write out a list item for each row in said results (or not, if there’s no data). Again, I’m using jQuery here, both for selectors and for $.each (which I like very much):

var updatePage = function(results){
  var winkles = $("#winkle-list");
  winkles.empty();  // Clear down before re-creating
  if (results.rows.length==0){
    // Nothing in the table
    alert("Alas, there be no winkles here.");
  } else {
    // Iterate the SQLResultSet
    $.each(results.rows, function(i){
      var row = results.rows.item(i);
      // Write out a list item for each row
      winkles.append("<li>" + row.winklename + " who resides in " + 
        row.location + "</li>");
    });
  }
};

All done! So there, in a nutshell, is how to query data in a SQLite database running in your WebKit browser. Now, the more astute amongst you will have noted that this isn’t the whole story—I’ve not listed any code to save my winkles for a start, nor is the mark-up I’m tinkering with detailed. Do not fear, the complete source code for this post (a single HTML page with all the required Javascript) is available on GitHub, in a thing called a “gist”:

gist - Javascript & Web SQL example

Now, this code is a simple introduction to Javascript and SQLite in a local web page. It is not production-ready for a number of reasons, not least that it’s all just straight Javascript function calls and wot-not. Any developer worth his or her salt would rip this apart and create a nice DAO which can then be used as a “service class” of sorts, tucking away all those nasty brackets and SQL statements (in fact, that’s just what I did for a client project recently ;-)), so there’s some homework for you right there!

Further reading

Comments

  1. that is a damn good blog entry, more an article that a blog!

    how does the EU directives on cookies relate to this offline stuff?stickfight#
  2. Very good article Ben.

    How does the security angle play out here? I assume you have to be very careful what data you store locally e.g no bank balances ;-) as it looks like it could be easily manipulated by the end user - bypassing any code based validation. I appreciate your target device was mobile so that maybe its not such a problem.

    I bet a partially connected device must give you headaches when trying to replicate any local end user driven data back to the cloud ;-) One row at a time with a success callback I assume?Mark Barton#
  3. Good article Ben. Strange data!

    Question: if you're using a browser with an embedded database like SQLite in a mobile device that also contains SQLite as a native application, can the HTML5 app store data in the native database or is this restricted for security reasons? It would be an interesting way of mixing between web and native applications.Rob Wills#
  4. Good points re security. Ultimately you have to make a call as to what data you want to actually store in a local database, yes. As I mentioned, you can query your tables independently of the web pages using SQL (albeit not readily on a device).

    Local databases are stored per domain. If you play around with local storage code, you will see such databases listed in your browser settings, and you can delete them from there (both desktop and mobile—take a look at the Safari prefs in iOS for example, once you have a database stored locally). Web Inspector will also show you relevant databases when you have a site’s page open, which has initialised the database. For example, in the screenshot above, Web Inspector will only show me the “winkles” database when I’m on a web page associated with winkles. Otherwise, nada.

    @Mark re sync. Heh, always good fun. This can be achieved a few different ways. For a recently-completed project, I push completed data from the device up to a SOAP web service (look for a post on this soon).

    As for callbacks if you look at the gist, the entire SQLResultSet is available to a callback, and you can then iterate it as you see fit (for example, using $.each as I do in the example).

    @Rob: good question, and one that’s come up a few times on Stack Overflow etc. I don’t imagine that the browser can store data in anything other than its own “sand-boxed” instance of SQLite. But it may be possible the other way round (i.e. a native app talking to SQLite in the browser, perhaps via something like UIWebView in iOS).Ben Poole#
  5. I am glad someone else is doing this as well, i really enjoy the power of sql offline data. Being able to get results and do an 'order by' and things like that are very nice.

    I personally use a webservice for syncing both ways, and yes it is painful, especially figuring out which entry is newer not being dependent on time zones etc.

    Works great on mobile phones, and is super fast.Mark Hughes#
  6. Thanks for this, really good work on the simplification...Mwathi#
  7. This is a fantastic find for me - just what I need to do. Thanks.

    How would you recommend extracting the data and transferring to a server at a later date?Graham#
  8. Graham: in the app I did, all data is pushed to a web service via a simple home-grown JavaScript routine that turns JSON into a simple SOAP structure. A RESTful web service would be simpler I suppose, but you work with what you have!Ben Poole#

Comments on this post are now closed.

About

I’m a developer / general IT wrangler, specialising in web apps, the mobile web, enterprise Java and the odd Domino system.

Best described as a simpleton, but kindly. Read more…