How to work with Web SQL

Web SQL is one of the several technologies introduced with HTML5 although sill not be a standard. In fact Web SQL as Web Storage appears because nowadays our web pages need to be more flexible that means they should work without a permanent server or store locally permanent data.

Web SQL is a API that provides a simple simple interface to access and manipulate the data through SQL language. Data can be manipulated on client-side similar with what happens on server-side using databases like MySQL or MS SQL.

There are three simple functions to manipulate a database using Web SQL API.

  • openDatabase: method provides you to create a database or open a connection if it already exist.
  • transaction: method provides a way to run a transaction. Means all queries into a transaction are finished with success or not.
  • executeSql: method is used to execute SQL query into transaction.

The line below creates or opens a database.

var db = openDatabase('myfirstwebdb', '1.0', 'My First Web Database', 2 * 1024 * 1024)

openDatabase() parameters:

  • Database name;
  • Version number;
  • Database description;
  • Size in bytes (in this case 2MB)

To execute queries you might use the transaction function which receives a callback function with a single argument to execute queries. This argument basically represents the transaction that will be share by every executions into transaction. In the example below we are creating a new table called user with two field id and name and then insert one record.

var db = openDatabase('myfirstwebdb', '1.0', 'My First Web Database', 2 * 1024 * 1024);
 db.transaction(function(tx) {
     tx.executeSql("CREATE TABLE IF NOT EXIST user ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT(100 )" );
     tx.executeSql("INSERT INTO user (name) VALUES ('Miguel Quintal')");
 });

Now other example using a select query to get the records that we just insert.

db.transaction(function(tx) {
    tx.executeSql("SELECT * FROM user ", [], function(tx, results) {
        var n_row = results.rows.length;
        console.log("Nº rows: "+n_row);
        var row = null;
        for( var i=1; i< n_rows; i++) {
            row = results.rows.item(i);
            console.log("User: "+row.id+" - "+row.name);
        }
    });
 });

In video below you can see how to use a Web SQL in Google Chrome with a simple example.

 

Conclusion

Although Web SQL isn’t supported in all browser and event isn’t a standard Web SQL could be good to develop Google chrome extensions or mobile web applications. If you are interested about mobile development you can try a great platform called PhoneGap which use Web SQL too.