Experimenting with Web SQL databases

February 27, 2010 | JavaScript | 3 Comments

&

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.

3 responses to “Experimenting with Web SQL databases”

  1. chovy says:

    Very cool intro to Web SQL — I just recently discovered it when building a prototype iPhone app in Safari. SQLite is the db engine.

    I see it having value in storing local copies, perhaps doing an occassional sync with a main database, or storing local session information. If you want to actually save this data then there needs to be a means of uploading it back to a main database. It’s much like saved passwords and cookies — occassionaly those get cleared out for whatever reason.

  2. I think you’ve hit the "prime divide" right there in your example of loading the Greek NT. Is giving a user a 5MB "database" really a good use of the client/server relationship for something like this? I can definitely see the benefits, but if I stumble upon the web app on my iPod Touch in a really slow connection area, it would be nuts to have to wait for the file to download, when all I wanted was a quick ref to a verse in greek.

    Don’t get me wrong, I’m really excited with stuff HTML5 is bringing to the table, but I’m wondering if large files such as this is reaching that point where the web app is trying to become too much like a desktop app with local storage.

    Thoughts?

  3. chovy says:

    I made a localStorage() example after reading this article (a todo list), biggest problem I have is that I can only modify it from that particular browser.

    is cloudStorage() going to be available — HTML7? :-)

Leave a Reply

Hi, I'm John Dyer. In my day job, I build websites and create online seminary software for a seminary in Dallas. I also like to release open source tools including a pretty popular HTML5 video player and build tools that help people find best bible commentaries and do bible study. And just for fun, I also wrote a book on the theology of technology and media.

Fork me on GitHub

Social Widgets powered by AB-WebLog.com.