HTML 5 Web SQL Database
HTML 5 has a torrent of new and exciting APIs that will eventually allow web developers to create richer and more compelling applications. Perhaps the most useful is HTML 5’s new “Web Storage” API. We’ve covered local and session storage in the past, going as far as to simple object persistence using these APIs.
Local and session storage is great for storing anything from simple key value airs (such as application settings) or simple objects (like application state) but isn’t sorted for anything other than trivial amounts of relational data. This is where HTML 5’s “Web SQL Database” API comes in to play.
A word of warning, the rest of this document requires an healthy understanding of JavaScript and OOP (particularly anonymous inner functions) and SQL.
Opening a connection
To open a connection we execute the following:
db = openDatabase("ToDo", "0.1", "A list of to do items.", 200000);
This creates the database object db with the title ToDo, a version number of 0.1. db has a description and approximate size also. The description could can be used by the user agent to communicate the purpose of the database with the user. The size allows the user agent to attempt to set aside enough storage for your content. This space is flexible when needed, so there’s no need to attempt to estimate how much a user could be allowed to use.
To test the connection was created successfully you can attempt to check if the database object if null:
if(!db)alert("Failed to connect to database.");
Never assume the connection was successful, even if it has been with that particular user in the past. There are a number of reasons why a connection could fail. Perhaps the user agent denied you access to the API for security reasons, perhaps storage on the device is limited. With the vibrant and fast evolving landscape of potential user agents it’s simply unwise to make assumptions of your user’s machine, software or their capabilities. For example your user may have just megabytes of data at their disposal if, say, they are using a hand held device.
Executing queries
To execute a query you use the database.transaction() function. This function has a single argument, a function that takes care of actually executing the query.
This function (often anonymous) has a single argument of the type transation.
db.transaction(
function(tx) {
The transaction has a function, executeSql. This function takes four arguments, a string denoting the query, an array of strings to insert in place of question marks in the query (much like Java’s prepared statement), a function to execute on success and a function to complete on failure.
tx.executeSql("SELECT COUNT(*) FROM ToDo", [], function(result){},
function(tx, error){});
On success
When the query is successfully executed the application can jump in to a query with a pair of arguments, the transaction and the results it collected. This would be perfect for actually relaying your data to the user, for example, displaying a list of ToDos. More about this later.
On failure
This is called if the query doesn’t execute. Because you’re passed the transaction object as the fucntion’s first argument you can execute further queries in the case of error. For example, if the query can’t run because the table is missing, this is the ideal time to actually build the table and attempt to re-execute the statement. You can get information (including a description) about the error from the function’s second argument.
An example
Let’s say that we wanted to use the above example. We wanted to poll the database for a particular table, if that table doesn’t exist we create it.
In this example we’d have to call db.transaction() with it’s one function argument. Within this argument we want to call tx.executeSql(). If this succedes we’ll do nothing (thus a null argument). Otherwise we want to take the transaction passed with the fail function and call tx.executeSql() once again. This time with the creation query.
db.transaction(
function(tx) {
tx.executeSql("SELECT COUNT(*) FROM ToDo", [], null,
function(tx, error) {
tx.executeSql("CREATE TABLE ToDo (id REAL UNIQUE, label TEXT, timestamp REAL)", [], null, null);
}
);
}
);
It may be tough to juggle all of these inner methods so you may want to create functions externally of the db.transaction() call. For example we could make the error function self contained and call it “createToDoTable()“.
Inserting
To allow for clearer and safer code the Web SQL Database API allows you to supply an array of strings to the transaction.executeSql() function to denote variance in the SQL statement called. This is demonstrated in the following code:
db.transaction(
function(tx) {
tx.executeSql("INSERT INTO ToDo (label, timestamp) values(?, ?)", [label, new Date().getTime()], null, null);
}
);
In this example the two question marks in the first argument are replaced by their respective counterparts in the array that follows. The first being the label to attribute to the task (perhaps a variable we defined earlier in code) and a timestamps generated as the function is called.
You could emagine as the query is executed it looks more like this:
INSERT INTO ToDo (label, timestamp) values ("Test", 1265925077487)
Working with results
The result object of the success function contains a collection or rows. Each row represents a result. This result has a collection of values assigned to it representing values for each column in the database for that particular result. You can access a row by calling result.rows.item(i) where i is the index of the row you’d like to inspect. To select a value from a row you pass the row an array style string index denoting the column you’d like to inspect. For example if we wanted the column label we’d call row['label'].
The following outlines printing the results of a query using the result object:
db.transaction(
function(tx) {
tx.executeSql("SELECT * FROM ToDo", [],
function(tx, result) {
for(var i = 0; i < result.rows.length; i++) {
document.write('<b>' + result.rows.item(i)['label'] + '</b><br />');
}
}, null);
}
);
Conclusion
And so ends our whistle stop tour of, what could be, an incredibly useful and flexible API. Keep in mind that Web SQL Databases ought only be used if strictly necessary. Not just because of their technical overhead but because they introduce a good deal of complexity to your code. In most cases local storage or session storage can do, especially when you can persist object states.
That having been said you can get a great deal of functionlity from these APIs. I’m sure that, over years, some incredible applications can be built around this API. After all, Google’s mobile Mail client already uses similar technology to reduce calls to high latency cellular networks.
Fantastic article! Concise, helpful and well written.
persistence.js is a great ORM wrapper around Web SQL, and it works with Gears, too, for folks not using HTML5 browsers (e.g., IE).
Great post. Thanks
Oh man, with all the new stuff in HTML, I can’t wait. Thanks for the article!
Almost all of your code samples are cut off on the right hand side (Win Firefox 3.6).
Thanks for the good overview!
Nice article and maybe my concerns should not be articulated here, but I would worry about my database server if each and every client of my webapp opened a new database connection (and cursors, etc).
Wow, fantastic article, very concise as well =)
And thanks for the link to persistence Colin
There is an issue with the large gap between browsers that support this, browser that kinda support this and browsers that don’t.
So we’re either stuck with building two versions of the same thing, or just dedicated apps, like addons and widgets. The funny thing is that there already are other datastores for those.
Still, I think it’s just a matter of time. In the meanwhile, we can use this cautiously to reward those nice people that use those nice browsers
HTML5 will change the web alot. But this database is more exciting and will lead to very flexiable applications on the web side.