Tutorial: Web SQL Database

I previously discussed in the last tutorial the Web Storage API which offers a really nice way to store key/value pairs data information without the hassle of using cookies.

It is very convenient but what if you need to store complex relational data and perform simple or complex queries on these data? Well, Web Storage does not allow that but Web SQL Database does.

Although it tends to be labelled "HTML5" feature, Web SQL Database is not part of the HTML5 specification. Nonetheless, it is important to know how it works since it is an essential feature that will help you to build great web applications.

Before moving on, you need to know that since November 18th, 2010, the W3C stopped supporting the Web SQL Database specification. This means that you as a developer have to use it at your own risk. However, since it is still implemented in some desktop browsers and some mobile versions of Safari in Apple's iOS, it is still interesting to know how to use it.

In this tutorial, I am going to discuss three main methods of the API: openDatabase, transaction and executeSql. I am going to code a simple contact manager application that makes usage of these three core methods. The link to download the code will be available at the end of the tutorial.

First thing first, how to create and/or open a database?

var db=openDatabase('contacts','1.0','my contacts app', 2 * 1024 * 1024);

The openDatabase method has the following arguments in their respective order:

• Name of the database you want to create and open. If the database has already been created, the openDatabase method just open the database
• Version of the database to open. The version number is required to open the database and this number has to match the database version that the client use.
• Textual description of the database
• Size of the database to create. The current specification recommends 5MB of data per origin per user agent. Unlike Web Storage, you can use over 5MB if needed. Some browsers will ask the user the authorization for more space if more than 5MB is needed though.

Once the database is created and opened, we want to create some tables to work on. Any SQL operations have to happen in a transaction. A transaction is a set of operations considered as one single operation, isolated from other transactions. it ensures consistency because it brings the database from a consistent state to another one, that means that if anything fails during the transaction, the database is rollbacked to the previous state before the transaction.

Since we got our Database object, db, thanks to the openDatabase method, we can perform a transaction using the Database's method: transaction

//Perform a transaction
db.transaction(function(tx) {
  //This is where SQL statements will be executed
});

The transaction method can take up to three arguments. The first one is the transaction callback, the second one is the error callback and the last one is the success callback. These arguments are optional, so here I just use the transaction callback.

In the transaction callback I have an attribute, tx, which is a SQLTransaction object. I will now use the executeSql method of this object to create the "contacts" table:

//Create the contacts table
db.transaction(function(tx) {
  tx.executeSql('CREATE TABLE IF NOT EXISTS contacts(id integer primary key autoincrement, firstname, lastname, phonenumber)');
});

The executeSql method is the following: executeSql(sqlStatement, arguments, callback, errorCallback)

• sqlStatement: is the SQL statement to perform on the database. This argument is mandatory, all the following is optional.
• arguments: The sqlStatement argument can contain SQL arguments represented by question marks '?'. These question marks are then mapped to values contained in the arguments argument which is an array of values.
• callback: success callback function
• errorCallback: error callback function

As we previously seen, the executeSql method allows you to perform SQL prepared statements in order to avoid SQL injection using question marks as arguments in the query. Let us see how to apply that when I add a new contact:

db.transaction(function(tx) {
  tx.executeSql('INSERT INTO contacts(firstname,lastname,phonenumber) VALUES (?,?,?)',[inputFirstName,inputLastName,inputPhoneNumber], function(tx) {
    //Success callback get executed if the INSERT statement worked well
    //Get the last contact we just added and dynamically add it to the table displaying the contacts
  });
});

We have not seen yet how to access results from a query (using a SELECT statement for instance). Reading the results of the request if this one went well, happens in the success callback function. This success callback takes two arguments: a SQLTransaction object and a SQLResultSet object.

According to the specification, the SQLResultSet object is defined like this:

interface SQLResultSet {
  readonly attribute long insertId;
  readonly attribute long rowsAffected;
  readonly attribute SQLResultSetRowList rows;
};

• insertId returns the row ID of the row that the SQL statement inserted into the database (or the last one if more than one has been inserted).
• rowsAffected returns the number of rows that were modified by the SQL statement.
• rows is a SQLResultSetRowList object. it contains the rows returned by a SELECT statement.

The SQLResultSetRowList is described like this in the specification:

interface SQLResultSetRowList {
  readonly attribute unsigned long length;
  getter any item(in unsigned long index);
};

• length is the number of rows returned by the database
• The item(index) method returns the row with the given index number. If there is no such row, then this method returns null. You can then access the different fields by doing for instance item(index).fieldname

So finally, here is the example where I get all the contacts from the database:

function listContacts() {
  db.transaction(function(tx) {
    tx.executeSql('SELECT * FROM contacts', [], function(tx, results) {
      var len=results.rows.length;
      var i;
      for(i=0; i<len; i++) {
        //Set values coming from the database
        alert("Id: " + results.rows.item(i).id +
               " Firstname: " + results.rows.item(i).firstname +
               " Lastname: " + results.rows.item(i).lastname +
               " Phone number: " + results.rows.item(i).phonenumber);
      }
    });
  });
}

You can download the sample project here