Taking your web apps offline: A tale of Web Storage, Application Cache and WebSQL

By Shwetank Dixit

Introduction

To make applications run fully offline, we should make ourselves familiar with three very interesting technologies: The HTML5 Application Cache, Web Storage and WebSQL Databases.

I’ve already written introductory articles covering Web Storage and HTML5 Application Cache: you should read these first if you are not already familiar with the basic concepts. This article will revisit these technologies, introduce WebSQL, and show how to effectively use all three in context. All three of these technologies are now supported by Opera desktop version 11.10, Opera Mobile 11, and Webkit-based browsers such as the iOS and Google Android browsers.

Note: All the examples discussed below can be found in our offline web apps demo package. You might want to download this and look through it while you read through the article (unless you are offline, of course!)

Why do web apps need to work offline?

Nowadays we don’t just have simple web pages. The internet is becoming increasingly ubiquitous, and we are seeing ourselves consume web applications more and more. Our reliance on them is increasing, and they are also becoming more complicated and sophisticated. we are seeing more instances where web apps are successfully replacing traditional desktop applications.

One major advantage desktop applications have always had over web applications is the ability to work offline. With web applications, we simply did not have the proper tools and technologies to make this happen.

But this is no longer a problem! With the advent of HTML5 Application Cache, Web Storage and WebSQL we finally have a great way to make web applications work offline:

  • Application Cache allows us to store a copy of our web app's HTML, CSS and other assets off line, to be used when the network is not available.
  • Web Storage takes the principles of older storage mechanisms such as cookies, and makes them more powerful and flexible.
  • WebSQL provides a fully-operational SQL database inside the browser, which can store a copy of our web app's data offline, allowing users to continue working with their data when they have no connection available. The data is then synched back up to the server when the network is available again.

there are many reasons why we might lose network connection: power failure, router troubles, having a bad (or non-existent) signal when accessing the site outside on a mobile phone. As developers, we would like to ensure that even in these situations, people can use our applications properly (or at least to some degree).

Application Cache: Loading our app's files when there is no network connection

Generally speaking, if we are offline and try to load or reload a page, we will get an error. So the first thing to do is to make sure our users can see and use our app even when they try to load the page offline. This means all the images, CSS files, JavaScript files, and the actual HTML page itself should all load properly when offline.

This is achieved using HTML5 Application Cache (also known as AppCache). To use this we first define a manifest file containing the references to the files needed to run that app offline.

Lets look at an example: demo.manifest.

CACHE MANIFEST

CACHE:
logo.png
style.css
script.js
jquery.js
index.htm

Whichever HTML file references this manifest file will now have an application cache associated with it, with these resources available in it. Referencing the manifest file from an html file is easy — you use a manifest attribute on the <html> element:

<html manifest="demo.manifest">

All the files listed here will be cached in the browser’s application cache. Even if a person is offline and tries to load the page, all of the resources mentioned in the linked manifest file will be loaded by the browser.

Full details about this can be found in our full featured article about HTML5 application cache.

What about the data?

AppCache sorts us out in terms of having the site assets available offline, but sometimes we'll want to store small amounts of data like a user’s preferences, or the last saved search item of a user. Other times you will want to store more structured application data. Depending on the use case, either Web Storage or WebSQL would be the best way to go.

Going offline with Web Storage

Web Storage is perfect for storing small chunks of information rather than large tables of organized information. We only give a brief overview in this article, and look at some examples. You can learn more of the basics in our dedicated article on Web Storage.

There are a lot of places in the world where electricity goes off frequently, and people are offline for quite a while before the power comes back on again. Imagine if someone living in such a region is filling out a multi-page form, or writing a long blog post, or composing an important email? If the power suddenly goes off (or their computer runs out of battery), they would lose all that data. Wouldn’t it be nice if the next time they are online, they could go to the URL and have that information available again, to carry on with?

Lets look at how we can do this with a simple page containing a <textarea>. This page should save whatever we type into local storage every few seconds so if it is closed and reopened, it should load up the last saved text.

Our page contains a <textarea> with an id of draft:

...
<textarea id="draft" rows="10" cols="30"></textarea>
...

We’ll create a simple function to save into localStorage whatever is typed in the textbox.

function saveMessage(){
	var message = document.getElementById("draft");
	localStorage.setItem("message", message.value)
}

We’ll make it save whatever is typed into localStorage every half a second:

setInterval(saveMessage, 500);

For the sake of simplicity, we have used setInterval() to save the message to local storage every half a second. (You could further improve on it by having it save the message to local storage only when you detect that the user has inputted something into the text box).

We also have to make sure that, whenever the page is opened or reloaded, the last saved value from localStorage is reflected inside the textbox.

window.addEventListener("DOMContentLoaded", loadMessage, false);

function loadMessage(){
	var textbox = document.getElementById("draft");
	var message = localStorage.getItem("message");
	 
	 if (!message) {
	 	textbox.value = "";
	 }else {
		textbox.value = message;
	}
}

Try out the Web Storage demo. Web Storage is a great thing to use when you have to store small pieces of information like this.

Working in offline mode

Users that want to enable working in offline mode, can do so by enabling "Work Offline" mode (In Opera it is available under Menu>Settings>Work Offline or File>Work Offline). The navigator.onLine property is set to false whenever the user is working in offline mode, and is true otherwise. In many cases, however, it might be better to use events to do the same thing. When the user is in offline mode, the offline event is fired, and when they switch back the online event is fired. We can use this to display a small notification whenever the user switches to offline mode.

Something like this will work fine:

...
window.addEventListener( "offline", function(){showWarningDiv("on")}, false);
window.addEventListener( "online", function(){showWarningDiv("off")}, false);
...
function showWarningDiv(status){
var warningdiv = document.getElementById("warning");
if (status == "on"){
warningdiv.innerHTML = "<p style=\"padding:3px;\">Right now you are in offline mode. This message is saved and will be sent to the server the next time you are online.</p>";
} else {
warningdiv.innerHTML = "";
	}
}

Right now, the "Work Offline" mode is available only in Opera and Firefox.

It might make sense to make sure forms are not attempting to submit information while a user is working in Offline Mode. To check for that, we can do the following:

...
window.addEventListener( "submit", submitForm, false);
...
function submitForm(){
	saveMessage();
	if (!navigator.onLine){
		return false;
	} 
	
}

Whenever the form is submitted, a submit event is fired, which calls the submitForm() function. This function will save the message locally first, and then see if the user is in offline mode. If so, then it will not submit the form.

You can take this example further and have it save the typed text to a server every few seconds, so that a copy of the draft is available on the site in case the user deletes it. This is especially relevant in cases where sensitive information is involved: you would not for example want to store credit card details locally in a persistent way via localStorage.

Try out our Advanced Web Storage demo, which stores information in sessionStorage. As long as you do not close the page, the information typed will remain in the textbox, even if you reload the page. The page will also send the information to a server side script every few seconds and update the time it was last saved. An approach like this could be used in blogging or email systems to make sure that data is periodically saved to the server and that the user can continue working during network outages.

WebSQL: Going further with offline data

Web Storage is great for storing small blobs of information, but what if want to store a whole database offline? What if your application demanded querying tables within the database, doing joins, searches, etc.?

Using Web Storage will not cut it in such cases: you need something more robust. This is where WebSQL comes in. WebSQL is a local SQLite database that you can save application data to, using a combination of simple JavaScript and SQL.

Working with WebSQL databases

The first thing to do is to detect whether the browser actually has support for WebSQL or not. We can do this using the window.openDatabase property, like so:

if (window.openDatabase){
	//rest of your code
} else{
	alert("It seems your browser does not have support for WebSQL. Please use a browser which does, otherwise parts of this application may not run as intended."); //or any other similar message
}

Creating and opening a database

You can create and open a database using the openDatabase command.

var db = openDatabase("food_db", "1.0", "Web SQL Storage Demo Database", 1*1024*1024); // creates a database called 'food_db' with version number 1.0, description as 'Web SQL Demo Database' and a size of 1MB.

This creates a database called food_db with version number 1.0, a description of Web SQL Demo Database, and a size of 1MB. The variable db contains a reference to this database object, which we will use further down in the code.

The size of the database has to be set in bytes. That is why we have defined the size as 1*1024*1024, which equals 1Mb. If we want the size to be 4Mb, we would define the size as 4*1024*1024.

Executing transactions on the database

Now that we have created and opened a database, we can execute transactions on it using SQL commands. We create transactions by calling the transaction() function on the database object (db in our case). The callback to it will return a transaction object as a reference, on which we will be executing SQL commands using the executeSQL() command. The syntax for the command is like so: executeSql(sqlStatement, arguments, callback, errorCallback). Out of these only the SQL statement is required: the rest of the parameters are optional.

So for example, if we wanted to create a table within the database, we would write the following:

...
db.transaction(
 function(t){ // This is the callback with "t" as the transaction object
  t.executeSql("CREATE TABLE IF NOT EXISTS cal_list (food_name TEXT PRIMARY KEY, calories REAL, servings TEXT)");
}
);
...

The above code will create a table (if it does not already exist) called cal_list with the columns food_name, calories and servings.

Inserting values into the tables

Tasks like inserting and querying data within tables is pretty straight forward with WebSQL. Lets take the following example:

var food_name = "pizza";
var amount_of_calories = 320;
var serving_size = "one slice";

db.transaction(
 function(t){
  t.executeSql("INSERT INTO cal_list VALUES (?, ?, ?)", [food_name, amount_of_calories, serving_size]);
		}
);

Here, the first ? will map to food_name, the second one to amount_of_calories and the third to serving_size. This code will create a new row in the cal_list table with values of pizza, 320 and one slice in each respective column.

Lets take another example, this time querying a table:

var min_cal_amount = 300;
...
t.executeSql("SELECT * FROM cal_list WHERE calories > ?", [min_cal_amount]);

This code will execute a SQL statement to select all rows where the calories field is greater than 300: the question mark ('?') is mapped to min_cal_amount.

Looping through result sets

So we’ve created our database and tables, inserted information into them, and now we want to query the database and display the information. Usually we’ll have a bunch of results for one single SQL query, and we’ll want to loop through them and display the data in a table or some other structure on the page. The third parameter of the executeSql function is the success callback. Its here that we will loop through the result set.

var list = document.getElementById("thelist");
var food;
var min_cal_amount = 400;
var serving_size;

db.transaction(
 function(t){
  t.executeSql("SELECT food_name AS food, calories AS amount_of_calories, servings as serving_size FROM cal_list where calories > ?" ,[min_cal_amount], function(t,r){
	 for (var i=0; i < r.rows.length; i++){
	  food = r.rows.item(i).food;
	  amount_of_calories = r.rows.item(i).amount_of_calories;
	  serving_size = r.rows.item(i).serving_size;			
		
	  list.innerHTML +="<li>"+food+" has "+amount_of_calories+" KCAL worth of calories.</li>";
					
		}
	}, 
			
	function(t,e){alert(e.message);})
}
);

We first determine the length of the result with r.rows.length and then loop from 0 to that value. Each row's results are accessible by r.rows.item(i) where i is the row number. The column names are accessible through it as well. So to determine the food column of the row, we use r.rows.item(i).food, and so on for the other columns.

Using all three technologies at once

There are some use cases where you'll want to use AppCache, Web Storage and WebSQL all at once. This is certainly possible, as long you are aware of which technologies to use for which things. For example, if you just want to store some user preferences, it would be overkill to use WebSQL. You are much better off using Web Storage for that.

However, if you have a lot of data to use, and you also want to do queries on it then it would be better to use WebSQL.

Take a look at our Calorie Finder demo page, which uses all three technologies together. It uses Web Storage to store your search terms, so that the search term will persist in the search box even if you refresh or close and open the page. It also uses AppCache and WebSQL so that you can still load the page and search the data even when offline.

Note: you can get information on what web databases Opera is currently storing, and administrate the databases stored for different domains by going to opera:webdatabases. You can also find out the current domains storing data in your browser using Web Storage, and administrate said data, using opera:webstorage.

Wait ... isn’t the WebSQL specification in impasse?

Yes, but you can use it right now, on a number of browsers and devices. At the time of this writing, the same can’t be said of IndexedDB, which doesn’t have as much cross browser and cross device support. As that specification matures, that will no longer be the case, but it will take quite some time for that to happen.

It is important to note that certain desktop browsers like Firefox and Internet Explorer will likely never support WebSQL and will instead go on to support IndexedDB. So the primary use case of WebSQL is offline data storage for smart phones: it is supported on Opera Mobile since version 11, as well as prominent Webkit-based browsers on Android and iPhone.

The good thing about WebSQL is that it provides a nice way to have an offline database working in a browser. It's simple to learn and use, and you can quickly make an offline web app working across quite a few different browsers and devices right now.

Summary

Web applications can now run offline with the help of Application Cache, Web Storage and WebSQL Databases. Application Cache takes care of files being cached for offline usage, Web Storage takes care of small amounts of data and WebSQL Databases deals with handling large and complicated data. Developers, according to their needs, can use any combination of these technologies to make their applications run offline.

Read more...

This article is licensed under a Creative Commons Attribution 3.0 Unported license.

Comments

The forum archive of this article is still available on My Opera.

  • photo

    aexelm

    Wednesday, November 6, 2013

    Hi. Excuse me if this article is closed or is "antique" for comments, but I have a question and I will thank any answer.
    I am trying to make a web app using all these wonderful features. I can see that LocalSotrage keys may exists for ever even closing browser. But what happen to WebSQL? Is there a way to access into a DB after closing the browser? I would try to be more explicit:
    1. A webpage that creaes a WEBsql Database ( ie. name=testDB )
    2. I close this web page
    3. I open another web page that try to open the WEBSQL Databse named testDB

    Could this work?

    Thnks.

    Atte EXEL
  • photo

    Ramasamy Kanna

    Monday, November 18, 2013

    Hi,
    I want create endless scroll using websql.Is that possible with that.Please suggest me if possible.
No new comments accepted.