A while back, I wrote up a quick post about some of the new client storage options that will be available in HTML5, particularly localStorage and sessionStorage.  While they are both pretty nice alternatives to the clunkiness of managing client cookies, they are definitely limited.  The biggest limitation is the data structure that each supports (they’re exactly the same, after all, except for lifetime of the data).  As I pointed out, while their storage capacity is much greater than that of the old cookie, all data is still managed as key/value pairs.  Of course, you can always use JSON encoding to store complex data structures as strings in these options…however, if you need to get at deeper levels of data relationships, these will quickly get VERY clunky and unusable.

Enter Web SQL databases.  As the name implies, Web SQL databases are, well, client-based SQL databases (SQL Lite, to be exact) that can be levered through JavaScript.  Each “origin” can have a number of databases, each of which has a unique name and version (more about this later).  If you use something like the Developer Tools in Chrome, you can see these databases in action–including the databases themselves, child tables, and any data that has been added to the tables.

Working with Web SQL

The doorway to creating and using Web SQL databases is the openDatabase() method.  It doesn’t matter if you’re creating a new database, or creating a connection to an existing one: this is the first thing you’ll do, and here’s what it looks like:

this.database = openDatabase('Blog',"",'A Blog to Rule the World',2000000);

The openDatabase() method takes 4 required arguments:

  1. Name: Any valid string, including the empty string, is acceptable.  Database names must be unique, however, and should be evaluated in a case-sensitive manner
  2. Version: The version of the database to open
  3. Display Name: A friendly name that the browser can use (in prompts, etc.)
  4. Estimated Size: Each database has a default  maximum size, and users will be prompted to allow this to increase if the data exceeds the default quota.  Specifying a particular value here can avoid this annoyance by telling the browser up front what the total size might eventually be, short-circuiting the otherwise annoying prompts at each incremental increase (e.g., at 5, 10, 15, 20MB…)

Additionally, there is an optional 5th argument– callback method.  This callback method will be invoked if the database is created (not opened), and per the spec should be used to invoke the changeVersion() method to set the appropriate version of the database.  If no callback method is specified, the database will be created with whatever version is specified in the openDatabase() method invocation.

Now that we’ve got an open connection to our database, let’s do some stuff to it!

SQL Transactions

The core of working with your now-open connection is the Web SQL transaction() method.  [There’s also the “readTransaction()” method, which allows for read-only access].  This method takes 1-3 arguments, including the following:

  1. SQLTransactionCallback: This is required, and is the executeSql method that will actually execute the sql command (more on this later)
  2. SQLTransactionCallback: Optional callback method to handle transaction errors.  This method passes both the transaction, and an error object which contains, among other things, a message describing the error
  3. SQLVoidCallback: Optional callback method executed upon a successful transaction

A really cool part about the transaction() method is that it provides the ability for rollback.  This is why, in short, the actual execution of the SQL is wrapped in a transaction.  It’s kind of a pain if you don’t care about rollbacks, but if you don’t care about rollbacks, maybe you don’t care about data integrity 🙂

Here’s what a transaction might look like:

database.transaction(
     function(t){
        //execute sql here...
     }
);

Executing SQL

Now that we’ve created a transaction() method wrapper, we can actually get down to running some SQL.  This is accomplished by the aptly named executeSQL() method.  This method takes up to 4 arguments:

  1. SQLStatement: A valid string representation of the SQL to be executed (this, obviously, is required)
  2. Argument Array: An array of arguments to be matched in SQL statement (more about this later)
  3. SQLStatementCallback: An optional callback method to be invoked on successful execution of the SQL statement–this method passes the transaction and result set from the query…so it’s pretty important to specify if you want to return data from the database…
  4. SQLStatementErrorCallback: An optional callback method to be invoked when the executeSql() method fails–this method passes the transaction and error object

So let’s say we want to create a new table called “posts” with a few columns.  Our full transaction/executeSql() statement might look like this:

database.transaction(
     function(t) {
          t.executeSql("create table posts(postid integer primary key autoincrement,title,publishdate,author,content);";
     }
)

One thing to note, especially relating to schema creation, is that in SQL Lite, only one SQL statement can be executed per request.  So for example, if you’re trying to create multiple tables at one time (like when you’re building the schema for your database), you’ll have to pass the SQL statement for each table creation to an individual executeSql() method.  To streamline this, I simply create an array of SQL statements, and then iterate over them in a loop.  I’m sure there are other fine ways to accomplish this as well, but this works for me.

Preventing SQL Injection

If you’re a web developer, you should always be aware of the possibility of SQL injection, and do all that you can to prevent it.  Because of the way that Web Sql is structured, it’s probably a pretty big temptation to build SQL statements on the fly.  DON’T DO IT. This just opens the door to SQL injection, and because this is all happening in JavaScript, it would not be hard to identify where SQL is being dynamically generated and exploit it.

To help prevent SQL injection, the 2nd argument of the executeSQL() method, the Arguments array, allows you to specify “placeholders” in your SQL that can be substituted at execution time with scrubbed and validated data.  It works like this:

var sql = "insert into posts (title,content,publishdate,author) values (?,?,?,?,?)";
var val = new Array(t,c,p,a);
var executeSql(sql,val);

As you can see, in the actual SQL statement, I used “?” to specify my argument’s placeholders.  Then, I created a new array containing my scrubbed values coming from the client.  Then, I simply passed both to my executeSql method, and it works brilliantly.  Simple.  Of course, since I’m passing an array and not a key/value pair object, the order of the arguments compared to the placeholders is EXTREMELY important.  Just keep that in mind 🙂

Finally…Getting Some Data

So far, we’ve seen how to establish a database connection, create transactions, and then execute SQL against the database.  Now let’s talk about what to do with the data once we get it.

If you’ll remember, the executeSql() method provides an optional (although recommended) callback method that returns a results object from the executed SQL.  This object contains the following:

  • insertedId:  This is the id of the row that a SQL execution inserted, if any.  If multiple rows were inserted, it returns the id of the last inserted row.  Very handy.
  • rowsAffected:  This is the number of rows affected by the SQL execution.
  • rows:  An object containing any rows returned from the SQL execution

Once you’ve got the “rows” object to work with, you can retrieve an individual row’s data like so:

var row     = results.rows.item(num);
var title   = row.title;
var author  = row.author;
var content = row.content;

As you can see, each “column” in the row can simply be accessed by its name in the object.  Pretty simple, huh?

So What’s the Point?

As we’ve seen, working with Web SQL databases is pretty straightforward.  If you’re familiar with anonymous functions, and have a good grasp of general SQL, this is easy stuff.   Plus, because it is SQL based, the concepts involved with managing the data and database structure are no big deal, and you can concentrate more on developing the connections, error-handling, etc.

But on a more conceptual level, what’s the point of this?  After all, this is still a client-based solution.  If the user goes nuclear on their client history settings, any databases will be wiped out.  If this data, in that sense, is still so “temporary,” why bother with it?

Obviously, Web SQL databases are not intended to be replacements for server-based solutions.  On some level, data does need to be managed in a controlled, secure, and “permanent” way.  Web SQL is not going to change that.  What it will change, however, is how application development happens.

For example, consider the present situation.  If you’re developing an application that needs to regularly (let’s say once a minute) save data states for your users, the way to do this is through either clunky cookies (blah) or regular server-side hits to save records to a managed database.  While this works, it’s expensive.  Depending on the size of the data and amount of traffic, this could result in thousands and thousands of remote requests hitting every couple seconds.  If you have the hardware to handle this, it’s not a big deal.  But if you don’t, it can take it’s toll.

So what if you could reduce this by a quarter? Or half?  Or a 75%?  Imagine this:  instead of hitting the server every minute to save a data state, you could instead change the schedule to once every 5 minutes.  During those 5 minutes you could still have the same routine running, only in this scenario it would be saving the data state to the client Web SQL database.  Then, after 5 minutes, a protocol could transfer the saved datastates from client to server.  This will be entirely seemless to the user, and will save load on the server by making the requests less frequent.

Or imagine making your applications available offline.  When connected, the client machine could maintain a synchronized set of data with the remote server.  Then, when the client goes offline, they could continue working with the application, utilizing the data from their machine’s synched data from the server.  Then, when they are available to get online again, the client data repository could be resynched with the remote server, pushing any new or modified data.  While the experience between offline and online states would be different in some ways, the very ability to be able to work online and offline in a more or less seamless manner is a radical paradigm shift for web-based applications, and opens up the doors of some very exciting possibilities.

The Demo

Ok, enough of this post.  If you’re interested in learning more, be sure to check out the W3C spec of this.

Also, be sure to check out my demo (use Chrome…).  It’s a simple blog manager that allows you to create, edit and delete posts.  Check it out!!

if(!history.iscurrent) {
switch(ver) {
case ‘1.00’:
sql[0]    =    “create table comments(commentid,content,author,publishdate,postid);”;
break;
case ‘2.00’:
sql[0]    =     “create table categories(categoryid,category)”;
sql[1]    =    “create table tags(tagid,tag)”;
break;
case ‘3.00’:
sql[0]     =     “alter table posts add created”;
sql[1]    =    “alter table posts add updated”;
break;
// default will be a snapshot of the current version of the schema;
default:
sql[0]    =     “create table posts(postid integer primary key autoincrement,title,publishdate,author,content,created,updated);”;
sql[1]    =     “create table comments(commentid,content,author,publishdate,postid);”;
sql[2]    =     “create table categories(categoryid,category)”;
sql[3]    =    “create table tags(tagid,tag)”;
sql[4]    =    “create table bloginfo(name,owner,tagline)”;
}
this.database.transaction(function (t) {
for(var i=0;i<sql.length;i++) {
t.executeSql(sql[i]);
}
});
this.setVersion();
}