Peak Techie: May 2012

The Web SQL database API isn’t actually part of the HTML5 specification, but it is part of the suite of specifications that allows us developers to build fully fledged web applications.
It is just like it says on the tin: an offline SQL database. Implementations today are based on SQLite, a general-purpose open-source SQL engine. It comes with all the pros and cons of traditional databases.

On the one hand, you have a fully relational structure, allowing you to rapidly query and manipulate data via joins, e.g. "delete all saved game where the level is less than 10 and the game hasn't been loaded in the past 30 days". You can express that in a line of SQL, whereas you'd have to manually wander through the equivalent corpus of Web Storage data. And if you have a strong need for performance, you can also lean on several decades of research on database optimisation (though tuning would have to be done separately for each possible SQL engine being used).

Furthermore, there's support for transactions, your database is protected from the kind of race conditions that can arise with Web Storage.

There are three core methods in the spec that I’m going to cover in this article:
openDatabase

transaction

executeSql

Support is a little patchy at the moment. Only Webkit (Safari, SafariMobile and Chrome) and Opera support web databases.

Creating and opening databases

If you try to open a database that doesn’t exist, the API will create it on the fly for you. You also don’t have to worry about closing databases.
To create and open a database, use the following code:
var db = openDatabase('mydb', '1.0', 'my first database', 2 *1024 * 1024);

We have passed four arguments to the openDatabase method. These are:
Database name

Version number

Text description

Estimated size of database

transaction
Now that we’ve opened our database, we can create transactions. Transactions give us the ability to rollback. This means that if a transaction — which could contain one or more SQL statements — fails (either the SQL or the code in the transaction), the updates to the database are never committed — i.e. it’s as if the transaction never happened.
There are also error and success callbacks on the transaction, so you can manage errors, but it’s important to understand that transactions have the ability to rollback changes.
The transaction is simply a function that contains some code:

var db = openDatabase('mydb', '1.0', 'my first database', 2 *1024 * 1024);
db.transaction(function (tx) {
// here be the transaction

// do SQL magic here using the tx object
});

executeSql
executeSql is used for both read and write statements, includes SQL injection projection, and provides a callback method to process the results of any queries you may have written.
Once we have a transaction object, we can call executeSql:

var db = openDatabase('mydb', '1.0', 'my first database', 2 *1024 * 1024);
db.transaction(function (tx) {
tx.executeSql('CREATE TABLE foo (id unique, text)');
});

This will now create a simple table called “foo” in our database called “mydb”. Note that if the database already exists the transaction will fail, so any successive SQL wouldn’t run. So we can either use another transaction, or we can only create the table if it doesn’t exist, which I’ll do now so I can insert a new row in the same transaction:

var db = openDatabase('mydb', '1.0', 'my first database', 2 *1024 * 1024);

db.transaction(function (tx) {

tx.executeSql('CREATE TABLE IF NOT EXISTS foo (id unique, text)');

tx.executeSql('INSERT INTO foo (id, text) VALUES (1, "synergies")');

});

Now our table has a single row inside it. What if we want to capture the text from the user or some external source? We’d want to ensure it can’t compromise the security of our database (using something nasty like SQL injection). The second argument to executeSql maps field data to the query, like so:

tx.executeSql('INSERT INTO foo (id, text) VALUES (?, ?)', [id,userValue]);

id and userValue are external variables, and executeSql maps each item in the array argument to the “?”s.

Finally, if we want to select values from the table, we use a callback to capture the results:

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);

}

});

(Notice that in this query, there are no fields being mapped, but in order to use the third argument, I need to pass in an empty array for the second argument.)

The callback receives the transaction object (again) and the results object. The results object contains a rows object, which is array-like but isn’t an array. It has a length, but to get to the individual rows, you need to use results.rows.item(i), where i is the index of the row. This will return an object representation of the row. For example, if your database has a name and an age field, the row will contain a name and an age property. The value of the age field could be accessed using results.rows.item(i).age.

Sample example to demonstrate the usage of WEB SQL api’s is as follows:

<html>
<head>
<script type="text/javascript">
var db;
try{
db=openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024);
}catch(err){
document.write("Your browser does not support WEB SQL");
}
function checkBrowser(){
if(!db){
document.getElementById('content').innerHTML="";
}
}
function insertToDb(){
var id=document.getElementById("id").value;
var message=document.getElementById("message").value;
db.transaction(function(tx){
var table=document.getElementById("table").value;
tx.executeSql('INSERT INTO '+table+' (id,message) VALUES (?, ?)',[id,message]);
});
displayDbContents();
}
function deleteRow(id){
db.transaction(function(tx){
var table=document.getElementById('table').value;
tx.executeSql('DELETE FROM '+table+' WHERE id=?',[id]);
displayDbContents();
});
}
function displayDbContents(){
db.transaction(function(tx){
var table=document.getElementById("table").value;
tx.executeSql('SELECT * FROM '+table,[],function(tx,results){
var i,len=results.rows.length;
var buffer="<table border='1px'><tr><th>ID</th><th>MESSAGE</th><th>DELETE</th></tr>";
for(i=0;i<len;i++){
buffer=buffer+"<tr><td>"+results.rows.item(i).id+"</td><td>"+results.rows.item(i).message+"</td><td><input id="+results.rows.item(i).id+" type='button' value='Delete' onclick='deleteRow(this.id)'/></td></tr>";
}
buffer=buffer+"</table>";
document.getElementById('result').innerHTML=buffer;
});
});
}
function createTable(){
document.getElementById('table').disabled=true;
document.getElementById('createid').disabled=true;
db.transaction(function (tx) {
var table=document.getElementById('table').value;
tx.executeSql('CREATE TABLE IF NOT EXISTS '+table+' (id unique, message)');
alert("Table created with 2 columns(id, message)");
document.getElementById('id').disabled=false;
document.getElementById('message').disabled=false;
document.getElementById('submit').disabled=false;
displayDbContents();
document.getElementById('drop').disabled=false;
});
}
function dropTable(){
db.transaction(function(tx){
var table=document.getElementById('table').value;
tx.executeSql('DROP TABLE '+table,[]);
clearAll();
document.getElementById('result').innerHTML="";
});
}
function clearAll(){
document.getElementById('table').disabled=false;
document.getElementById('table').value="";
document.getElementById('createid').disabled=false;
document.getElementById('id').disabled=true;
document.getElementById('id').value="";
document.getElementById('message').disabled=true;
document.getElementById('message').value="";
document.getElementById('submit').disabled=true;
}
</script>
</head>
<body onload="checkBrowser()">
<div id="content">
<table width="100%" border="1px">
<tr><td align="center">Create and Insert data</td><td align="center">Table Contents</td></tr>
<tr>
<td width="50%" align="center">
<table>
<tr><td>Create Table</td><td><input type="text" id="table" placeholder="Enter Table Name"/><input type="button" id="createid" value="Create Table" onclick="createTable()"/></td></tr>
<tr><td>ID</td><td><input type="text" id="id" placeholder="Enter ID" disabled/></td></tr>
<tr><td>Message</td><td><input type="text" id="message" placeholder="Enter Message" disabled/></td></tr>
<tr><td><input type="button" value="Submit" id="submit" onclick="insertToDb()" disabled/></td><td></td></tr>
</table>
</td>
<td width="50%" align="center">
<div id="result">
</div>
<input type="button" value="Drop table" onclick="dropTable()" id="drop" disabled/>
</td>
</tr>
</table>
</div>
</body>
</html>

Observations on different browsers:

Internet Explorer