Experimenting with Web SQL databases

&

What is Web SQL?

Web SQL (http://www.w3.org/TR/webdatabase/) is proposed technology that allows a small SQL database to reside inside a browser. Instead of making AJAX calls back and forth from server to client whenever any information is needed, the data is stored locally in the browser so it can be quickly queried and used. Web SQL is not technically part of HTML5, but it is part of a group of technology suggestions for the future of the web.

Currently, Web SQL is only implemented in WebKit based browsers (Safari, Chrome, iPhone) and beta versions of Opera. Google Gears also had a local database implementation, but Google Gears is being discontinued in favor of HTML5.

How Does Web SQL Work?

HTML5 Doctor has a great article on using Web SQL, so I’ll only repeat a small code sample.

// create/open database
var db = openDatabase(‘mydb’, ‘1.0’, ‘my first database’, 2 * 1024 * 1024);

// start transaction, create table, insert data
db.transaction(function (tx) {

amp;nbsp; tx.executeSql(‘CREATE TABLE IF NOT EXISTS foo (id unique, text)’);
  tx.executeSql(‘INSERT INTO foo (id, text) VALUES (1, “synergies”)’);
});

// query the data
db.transaction(function (tx) {
  tx.executeSql(‘SELECT * FROM foo’, [], function (tx, results) {
    var len = results.rows.length, i;
    for (i = 0; i < len; i++) {
      alert(results.rows.item(i).text);
    }
  });
});

A Giant Web SQL example

Web SQL is great for small amounts of data. The Webkit team has a sample which creates and stores notes (http://webkit.org/demos/sticky-notes/index.html), while HTML5 doctor stores recent tweets (http://html5demos.com/database).

I wanted to load a lot more data to see what the performance would be, so I decided to load the entire Greek New Testament which is around 170,000 words along with its morphology information (part of speech, declension, etc.). It attempts to load entire books at a time and then allow you to pull up a chapter. You can also click on a word and see every time it is use in the Greek Bible. Once the database is loaded, you can come back to the site at any time and never have to reload the database. It will always be there in your browser. 

Try it out:

The Greek New Testament in Web SQL

Gotchas: The database is a little over 5MB which breaks the limit in Google Chrome. Safari will ask you if you want to increase the storage, but it will also stop the loading processing while it waits for the user to respond. If you use Safari, you can click the “Toggle DB Admin” then click “Delete all” and then “Load all” to clear out and then reload the entire database.