More local Web SQL

In my last post on this topic, we looked at how one might use Web SQL in a local web application, and I supplied some code which initialised a local database and pulled data from its lone table. All well and good, but what about the realities of using this data? Specifically, let us look at how one re-loads, edits and saves the data.

“Eh?” I hear you ask. “Surely you just load up data in the normal way, and then, um, well put it somewhere…” (reader tails off quietly, and there is a momentary embarrassed silence). Yes, in my last post the provided code shows how to do a basic save and bulk load from the fabled winkles table, I grant you, but that in and of itself isn’t that much use. Sure, you can iterate query results and present them in a page, but what about editing said data? This is where the fun starts. You’re running a local web app remember: no web server, no box to which you may post forms for processing! But that won’t stop us, dear me no! I will show you how you can save a form within a web page to your database table, and then how you can load that data back into the same web page for editing and more saving. No server tricks! Splendid.

First up, grab a shiny new Javascript source file from a new gist I created here: Web SQL javascript (it’s the JS we used in my previous post, broken out into its own source file, with some added goodies). The gist tells you what to call the file. Then, in the same directory, create a new HTML page and whack the following mark-up into it. It’s a simple HTML5 page, with a wee bit of Javascript injection, a tiny form, and that’s about it (note: as with my other examples, this page requires jQuery in addition to the supplied Javascript).

  <meta charset="UTF-8" />
  <title>Winkles! Form a line!</title>
  <style type="text/css">
   body {font-family: Helvetica, sans-serif; font-size: 100%; line-height: 1.4em}
   input, button {width: 25em; font-size: 100%; padding: .25em}
   label {display: block; margin-top: 1em}
  <script type="text/javascript" src="jquery.js"></script>
  <script type="text/javascript" src="winkling.js"></script>
  <script type="text/javascript">
   var form = $("#winkle-form");
    var values = form.serializeJSON();
    saveWinkle(values, function(){
     alert($('#winklename').val() + "'s data has been saved, thanks.");

   loadWinkles("", updatePageWithLinks);

  <h1>Winkles! Form a line!</h1>
  <p>Here’s where you enter some winkle-related data. And it gets saved. And you can load stuff too.</p>
  <form id="winkle-form" action="" method="POST">
   <label for="winklename">Your winkle’s name</label>
   <input name="winklename" id="winklename" placeholder="Winkle's full name, e.g. 'Pansy Trueshell'"/>
   <label for="location">Your winkle’s location</label>
   <input name="location" id="location" placeholder="Type in a location here, e.g. 'Ayrshire'" />
   <input type="hidden" name="id" id="id" />
   <p><input type="submit" value="Save Winkle" /><input type="button" id="clear-btn" value="Clear form" /></p>
  <h2>Previously-saved winkles</h2>
  <p>Tap or click on a link below to load that winkle’s data into the form above:</p>
  <ul id="winkle-list"></ul>

All done. Now for some testing in your browser! As with my previous local SQL example, there are a couple of gotchas to bear in mind when previewing this stuff locally:

  1. Don’t preview with the file:// protocol, you will hit strange security exceptions in your browser. Use a proper web server, something that can serve up as http://. (I use OS X, so I preview in the bundled local Apache 2 installation, which is handy).
  2. I have only tested this code in Chrome and Safari. It should work in Opera, but I make no warranties for it working anywhere (and it most certainly will not run in Internet Explorer ;-)).
  3. There’s no validation: it is entirely possible for you to save empty winkle data (shocker).

Let’s look at the code in this web page. First, we override the form’s default onSubmit action so that it doesn’t actually try to POST anywhere, it just triggers saveWinkle(), a function which grabs all the data in the form (yes, all two fields of it. Well, OK, all three fields, what with the hidden one), saves it to the winkles table, and then kicks off a callback function (a grand term for a simple alert call, I think you will agree). Easy-peasy.

Where things get a little more interesting is with the other bits of Javascript you see triggered when the page is ready. The first function, loadWinkles() comprises some Web SQL Javascript which SELECTs every winkle in the table, and then passes the SQLResultSet over to a callback, updatePageWithLinks. That function iterates the resultset, and writes the data out to the bottom of the web page (the winkle-list unordered list to be precise). Now, this code is all familiar from my previous post. Where things diverge is in what that function writes out: it’s not plain text as before, it’s a list of links.

(Oh yes, I also add a click event to a form reset button, but that’s not very interesting, so I shan’t talk about it).

If you take a look at updatePageWithLinks in the Javascript source provided, you will see that the links are written out to the page with reference to a new function, loadMe. This takes a single parameter, which is the relevant winkle’s database record “id”, so that loadMe knows which winkle’s details to load up.

loadMe, surprise surprise, is callback-mungous, like a lot of the other code here. First, it triggers a routine to perform the SELECT and pull back the relevant record. Then this SQL result is passed over to a small function called loadFormWithData… and you can guess what that does.

There you have it. Loading and saving data to and from a web page, no server required. Now, I grant you, this form injection stuff all seems a bit fiddly. Why not use querystring parameters and the like to pass round the record ID required? Well, that’s where you hit one of the limitations of local web applications: as soon as you start involving things like href hashes, or querystrings, your web client will assume a server is involved. And if you’re running off-line (remember how this series of posts started!), that’s a no-no, and your browser will forget all about appcaches and the like, and will attempt to contact the (potentially unreachable) server. Doh!

Comments on this post are now closed.


I’m a software architect / developer / general IT wrangler specialising in web, mobile web and middleware using things like node.js, Java, C#, PHP, HTML5 and more.

Best described as a simpleton, but kindly. You can read more here.