Getting Started with HTML5 Local Databases

Please note: This article was written in May 2010 and as of November 18, 2010, the Web Applications Working Group has reached an impasse and does not intend to maintain the Web SQL Database any longer. Read more here.

Starting with Safari 4, iOS OS3, Chrome 5, and Opera 10.5 (Desktop), HTML5 Local Databases (AKA Web SQL Databases) are now supported. I’ve been reading about local databases for quite some time and decided to do a write up with some basic examples on how to get started.

Setting up the Database

function initDatabase() {
	try {
	    if (!window.openDatabase) {
	        alert('Databases are not supported in this browser.');
	    } else {
	        var shortName = 'DEMODB';
	        var version = '1.0';
	        var displayName = 'DEMO Database';
	        var maxSize = 100000; //  bytes
	        DEMODB = openDatabase(shortName, version, displayName, maxSize);
			createTables();
			selectAll();
	    }
	} catch(e) {
 
	    if (e == 2) {
	        // Version number mismatch.
	        console.log("Invalid database version.");
	    } else {
	        console.log("Unknown error "+e+".");
	    }
	    return;
	}
}

First we check if the browser supports the openDatabase method, is so we continue and define the database parameters:

  • shortName is the DB name as it will be referred to by the browser and SQL
  • version openDatabase version. 1.0 for this (more on that here)
  • displayName The full display name / description of the database
  • maxSize This is max size in bytes is the size you expect the database to reach. This is essential for memory management purposes.

Next, we call the createTables(); function where the table is defined and pre-populated with initial data (optional).

Building the Table

function createTables(){
	DEMODB.transaction(
        function (transaction) {
        	transaction.executeSql('CREATE TABLE IF NOT EXISTS page_settings(id INTEGER NOT NULL PRIMARY KEY, fname TEXT NOT NULL,bgcolor TEXT NOT NULL, font TEXT, favcar TEXT);', [], nullDataHandler, errorHandler);
        }
    );
	prePopulate();
}

This basic function executes the executeSql method which runs as the CREATE TABLE query. The SQL syntax is based on SQLite so it should feel familiar to many Web & Mobile developers. For this demo, we are going to pre-populate the newly created page_settings table with some initial data:

function prePopulate(){
	DEMODB.transaction(
	    function (transaction) {
		//Optional Starter Data when page is initialized
		var data = ['1','none','#B3B4EF','Helvetica','Porsche 911 GT3'];
		transaction.executeSql("INSERT INTO page_settings(id, fname, bgcolor, font, favcar) VALUES (?, ?, ?, ?, ?)", [data[0], data[1], data[2], data[3], data[4]]);
	    }
	);
}

Note: although we don’t really need a PK for such a basic demo it will be there for future extensions and advancements.

Now the database is initialized, a table has been created and we have a row of default data inserted. Since the data is stored, you can now reload or page or close and reopen it and the data will remain. To visualize this in Safari, go to Develop > Show Web Inspector > Databases tab where you can view the database and its contents as they are added or changed.

Select the data

After the initial page load the data is stored and we run the selectAll() function to get the data:

function selectAll(){
	DEMODB.transaction(
	    function (transaction) {
	        transaction.executeSql("SELECT * FROM page_settings;", [],
                dataSelectHandler, errorHandler);
	    }
	);
}

The demo consists of only one row so we use * to grab everything from the database but please optimize your queries if you decide to use this example for your own projects.

function dataSelectHandler(transaction, results){
 
	// Handle the results
    for (var i=0; i<results.rows.length; i++) {
 
    	var row = results.rows.item(i);
        var newFeature = new Object();
 
    	newFeature.fname   = row['fname'];
        newFeature.bgcolor = row['bgcolor'];
        newFeature.font    = row['font'];
        newFeature.favcar  = row['favcar'];
 
        $('body').css('background-color',newFeature.bgcolor);
        $('body').css('font-family',newFeature.font);
        $('#content').html('<h4 id="your_car">Your Favorite Car is a '+ newFeature.favcar +'</h4>');
 
        if(newFeature.fname != 'none') {
       		$('#greeting').html('Howdy-ho, '+ newFeature.fname+'!');
       		$('#fname').val(newFeature.fname);
        }
 
       $('select#font_selection').find('option[value='+newFeature.font+']').attr('selected','selected');
       $('select#bg_color').find('option[value='+newFeature.bgcolor+']').attr('selected','selected');
       $('select#fav_car').find('option[value='+newFeature.favcar+']').attr('selected','selected');
    }
 
}

This function loops through our data and uses a bit of jQuery to apply the specific values to CSS properties on the DOM. Going further, we set the HTML form defaults based on data with the help of jQuery.

Making updates

This function reads the form values, validates the text input and updates the database

function updateSetting(){
	DEMODB.transaction(
	    function (transaction) {
	    	if($('#fname').val() != '') {
	    		var fname = $('#fname').val();
	    	} else {
	    		var fname = 'none';
	    	}
			var bg    = $('#bg_color').val();
			var font  = $('#font_selection').val();
			var car   = $('#fav_car').val();
	    	transaction.executeSql("UPDATE page_settings SET fname=?, bgcolor=?, font=?, favcar=? WHERE id = 1", [fname, bg, font, car]);
	    }
	);
		selectAll();
}

Dropping the table

We could simply remove the row with a DELETE query but since we already check for the presence of data, it makes more sense to DROP the table:

function dropTables(){
	DEMODB.transaction(
	    function (transaction) {
	    	transaction.executeSql("DROP TABLE page_settings;", [], nullDataHandler, errorHandler);
	    }
	);
	location.reload();
}

After the table is dropped, the page is refreshed which triggers the initial database setup procedures defined earlier.

The demo:

View DemoDownload Source

Why should you use Local Databases?

They are fast, flexible and never expire. Developers can define the maximum size of the database and since all of the queries are run via JavaScript, no external server calls are required and unlike cookies and sessions, they persist until manually removed.
Unlike cookies and sessions, which are created on the server side and then stored in the browser, the information stored in a local database is truly local.
Safari and Opera’s local databases are based on SQLite and are threadsafe which prevents multiple instances of the data from being opened concurrently.
iPhone and iPad OS support local databases through their Mobile Safari browsers, making this storage platform ideal for mobile web apps.
From the UX perspective, the possibility of storing user and page settings and options locally, instantly and securely without the out the need to make external server or server side database calls is incredible. Since the HTML5 is widely supported in Webkit and Opera, there are nearly limitless possibilities for HTML5-based games and web apps.


Why should you NOT use Local Databases?

Local databases are a great way for you to simplify and improve your projects but they have a few obvious drawbacks:

Limited cross-browser support in the current form. At the time of this article, Safari 4, iPhone / iPad OS3, Chrome 5 and Opera 10.5 (Desktop) are the only mainstream browsers with support.
Not ideal for sensitive information. Sure, JavaScript can do MD5 and SHA1 hashing but if these guys can crack the code while listening to KoЯn, how safe do you feel?
Even though SQLite has been around for 10 years now, Webkit / Opera support is much newer and has consequently seen less vetting from those who would want to steal your data.


What about FireFox, Chrome and IE?

Firefox introduced support for localStorage in version 3.5 in the key-value pair format (similar to cookies and sessions) but has so far (and for the foreseeable future) stayed away from the SQL Database Storage model. More about that here, here and here
Internet Explorer has offered persistent storage support since IE5.5 which they’ve termed userData Behavior. This technique is very limited in features and has a maximum storage size of 128k per page. IE local storage improved in IE8 with the introduction of localStorage. Both IE local storage formats are key-value and there is currently no native local database support in IE—maybe in IE9?


Prior to version 4, local database storage support in Chrome was limited to implimentaiton via the Google Gears API. Gears offers a very similar SQLite-based local storage component that works across most browsers including IE6+, FireFox, Opera and Safari. The Gears platform was created to let developers create web applications that can be used offline and also offers support for other features such as Canvas, Desktop support and Geolocation. The downside is that users must install the Google Gears browser extension on their machine before they can access these features. Additionally, Gears requires developers to include a JavaScript file in their application in order to use Gears which makes it a less than ideal choice for the low-bandwidth mobile web application world. More on the Google Gears Database API here.

*UPDATE* As January 2010 Chrome now offers full local database storage support natively in effort to move to a more standards-based approach. Gears is still being supported in its current form (no new development or further platform compatibility, though) for the time being. More information on this change can be found here. (Thanks for the correction, Colin)

Resources



Please note: This article was written in May 2010 and as of November 18, 2010, the Web Applications Working Group has reached an impasse and does not intend to maintain the Web SQL Database any longer. Read more here.

The demo:

View DemoDownload Source



51 comments on “Getting Started with HTML5 Local Databases

  1. Hi,

    Thanks for such nice tutorial :) .
    I have one problem, When I am trying to run it on iPad 5.0 nothing will happen. like no change in font, bg color etc. It will simply display that rectangle with text field & drop down options. More over, no error message is shown. Plz help.

  2. openDatabase(‘xxxxxx’, ‘xxxxxx’, dbsize=3M)

    In the example above, The database maxium size is defined.
    If the maxium database size is exceeded, What happened ?

    Anybody help ?

  3. Hi Ben. Great info, this is really useful.
    However, there is one difference when I use your hosted demo compared to when I download the static html page, and that is resetting the preferences (dropping the table in this case). I cannot get chrome to do this from the static html page, despite the database being truly and completely local, (unlike cookies which I understand chrome does not implement for static html).
    Is this difference something I can easily resolve to allow the dropping of tables from a live site as well as a static file simultaneously? Thanks!

    • Local database can only be accessed (and modified) by the domain they are created on so I think that is why you’re probably seeing this happen. If you need to sync a local and web version for your app, your best best is probably to create a server-side db and poll for a hash value/ write to it asynchronously on page load and on db modification.

  4. Hi,
    Wonderful tutorial.. probably the best on internet right now :)

    I need a help/suggestion. The code you provided works great but somehow on closing and reopening the browser the data vanishes. What am i doing wrong? I guess its working like a session stuff..

    • I am a few months late on this one but: My guess is that the browser is set to remove all cookie/login/session data on close if this happening. Otherwise the only thing I can think of is the page is being viewed on different domains which would contain different versions of the db. e.g. the same version on your-site-name.com’s version would not be reflected on your-site-name2.com.

  5. well that’s pretty helpful.. I am developing my first web app with html5 … The major issue I am facing right now is the local storage…

    That’s a great example for storing local databases.. well I have been reading a lot about cache manifest which everyone seems to be recommending …

  6. hi..

    Is it possible to delete the database or retrieve a list of all databases, including their sizes.

    • Ferengi,

      you can view databases from the current domain in Safari by going to Develop > Show Web Inspector > Databases, not sure if you can get the actual DB size in the inspector but you can view the data that is being stored which should give you a pretty good idea. As for deleting the database, you can do a simple SQL drop statement e.g. DROP TABLE page_settings.

  7. Along with every little thing which seems to be developing throughout this particular area, a significant percentage of opinions are actually very refreshing. Having said that, I appologize, but I do not give credence to your entire theory, all be it stimulating none the less. It would seem to everyone that your commentary are actually not totally justified and in fact you are your self not even wholly certain of your assertion. In any event I did appreciate examining it.

  8. Howdy just wanted to give you a brief heads up and let you know a few of the pictures aren’t loading correctly. I’m not sure why but I think its a linking issue. I’ve tried it in two different browsers and both show the same results.

  9. Hi there,
    I’m pretty new to html5 and databases and I found this really helpful. At the moment though it just replaces the first row in the database when you click update, how would I go about making it so it adds a row with a unique key for each time you submit?

    Thanks in advance,
    Sam.

  10. Hi There,
    I have a great interest in getting asychronous web apps happening on the iphone/ipad mainly, but that’s limiting the potential. This is ideal for a number of reasons, thankyou for providing this information. What I would really really like is a way of synching it back into a core system. Whata do you do? Submit the data? wait until you hit the lan and the respective resources become available? Thanks again, I see this becoming invaluable to remote resources, ie surveyors or devices/data feeds on unreliable links.

    Cheers

    • That is a great idea–you could do a simple XMLHttpRequest to check for connectivity and then if successful, save the data to the DB on your sever.

    • You can do anything you want with the data after it’s saved. Just run a SELECT query and then manipulate the result as needed.

  11. Let me first say that i’m a noob when it comes to programming. Second, i don’t see any HTML5 involved in this whole demo. can someone point out for me?
    And can i use HTML5 to help Sqlite store/insert data into local database?

    • Katherine,

      Client-side databases are part of the current HTML5 working spec. Your confusion might be due to the fact that the databases are actually created and accessed through a JavaScript API. Also, In order to see the demo you must use one of the supported browsers mentioned in the article. Feel free to download the demo files and experiment / hack / re-write the code as you want.

  12. Thanks a lot for the tutorial, very helpful!
    There aren’t many good simple html5 database tuts around, so thanks again.

  13. Hi Author,

    Really a nice article. Learned a lot. One question though, I run the demo using Safari browser. How can I check where my data is storing in local machine?

    Thanks in advance,
    Pallav

    • Pallav,

      You can view local databases and local storage by going to Devlop > Show Web Inspector (if you don’t see the develop menu at the top you might need to enable it first)

  14. I would say that local databases are not a good place to store most user preferences because they are machine specific. If I go to gmail on my laptop and set preferences I should see the result of that when I log into gmail from my desktop.

    Local databases are awesome for dealing with large datasets though, since they can be pulled down from the server and stored locally so the client doesn’t have to hit the server to move from one page of data to the next (and other operations like searching, sorting, etc..).

  15. Hey, thanks for this tutorial. You might want to fix the formatting in the walk-through above, where you declare dataSelectHandler function. It’s fine in the downloaded code though. Cheers again for putting this together.
    Ade

  16. @Ben Dude, that’s hot! I just checked my Safari’s default value, and it’s 5 megs. I sure hope that’s per sub-domain vs. the entire browser.

  17. Hey, thanks for this awesome post! Do you know the maximum size you can make a database?

    Reason I ask is that I’ll often utilize local data caching engines utilizing Flash Player’s SharedObject. The issue is, it defaults to 100k per sub-domain, and that’s just not enough space a lot of times. You can ask the user for more space, but the UX is horrible. I was hoping that local databases could perhaps give me more space?

  18. Now the database is initialized, a table has been created and we have a row of default data inserted. Since the data is stored, you can now reload or page or close and reopen it and the data will remain. To visualize this in Safari, go to Develop > Show Web Inspector > Databases tab where you can view the database and its contents as they are added or changed.
    +1