Web SQL Database

Client-side databases using SQL

The Core Methods:

There are following three core methods defined in the spec that I.m going to cover in this tutorial:

openDatabase: This method creates the database object either using existing database or creating new one.
transaction: This method give us the ability to control a transaction and performing either commit or rollback based on the situation.
executeSql: This method is used to execute actual SQL query.

Opening Database:

The openDatabase method takes care of opening a database if it already exists, this method will create it if it already does not exist.

To create and open a database, use the following code:

var database = openDatabase('msdb', '1.0', 'Web Sql Database', 2 * 1024 * 1024);

Above method took following five paramters:

Database name
Version number
Text description
Size of database
Creation callback

The last and 5th argument, creation callback will be called if the database is being created. Without this feature, however, the databases are still being created on the fly and correctly versioned.

Executing queries:

To execute a query you use the database.transaction() function. This function needs a single argument, which is a function that takes care of actually executing the query as follows:

database.transaction(function (t) {
     t.executeSql('CREATE TABLE IF NOT EXISTS Emp (ID UNIQUE,Name,Salary)');
});

INERT Operation:

To create enteries into the table we add simple SQL query in the above example as follows:

database.transaction(function (t) {
     t.executeSql('CREATE TABLE IF NOT EXISTS Emp (ID UNIQUE,Name,Salary)');

     t.executeSql('INSERT INTO Emp (ID,Name,Salary) VALUES (1,"A",40000)');
     t.executeSql('INSERT INTO Emp (ID,Name,Salary) VALUES (2,"B",50000)');
     t.executeSql('INSERT INTO Emp (ID,Name,Salary) VALUES (3,"C",20000)');
     t.executeSql('INSERT INTO Emp (ID,Name,Salary) VALUES (4,"D",8000)');
});

We can pass dynamic values while creating entering as follows:

database.transaction(function (t) {  
  t.executeSql('CREATE TABLE IF NOT EXISTS Emp (ID UNIQUE,Name,Salary)');
  t.executeSql('INSERT INTO Emp(ID,Name,Salary) VALUES (?, ?'), [5, "E",5000];
});

Here ID,Name and Salary are external variables, and executeSql maps each item in the array argument to the "?"s.

READ Operation:

To read already existing records we use a callback to capture the results as follows:

database.transaction(function (t) {
  t.executeSql('SELECT * FROM Emp', [], function (t, results) {
  var l = results.rows.length, i;
  msg = "<p>Records: " + l + "</p>";
  document.querySelector('#status').innerHTML += msg;
  for (i = 0; i < l; i++) {
     msg = "<p><b>" + results.rows.item(i).ID + "   " + results.rows.item(i).Name + "  " + results.rows.item(i).Salary + "</b></p>";
     document.querySelector('#status').innerHTML += msg;
   }
}, null);

Full Download Example

Full code

<!DOCTYPE>
<html>
<head>
    <title>Web SQL Database</title>
    <script type="text/javascript">
        var database = openDatabase('msdb', '1.0', 'Web Sql Database', 2 * 1024 * 1024);
        var msg;
        database.transaction(function (t) {
            //t.executeSql('DROP TABLE Emp');
            t.executeSql('CREATE TABLE IF NOT EXISTS Emp (ID UNIQUE,Name,Salary)');

            t.executeSql('INSERT INTO Emp (ID,Name,Salary) VALUES (1,"A",40000)');
            t.executeSql('INSERT INTO Emp (ID,Name,Salary) VALUES (2,"B",50000)');
            t.executeSql('INSERT INTO Emp (ID,Name,Salary) VALUES (3,"C",20000)');
            t.executeSql('INSERT INTO Emp (ID,Name,Salary) VALUES (4,"D",8000)');
            msg = '<p>Table created and record inserted.</p>';
            document.querySelector('#status').innerHTML = msg;
        });


        database.transaction(function (t) {
            t.executeSql('SELECT * FROM Emp', [], function (t, results) {
                var l = results.rows.length, i;
                msg = "<p>Records: " + l + "</p>";
                document.querySelector('#status').innerHTML += msg;
                for (i = 0; i < l; i++) {
                    msg = "<p><b>" + results.rows.item(i).ID + "   " + results.rows.item(i).Name + "  " + results.rows.item(i).Salary + "</b></p>";
                    document.querySelector('#status').innerHTML += msg;
                }
            }, null);
        });
    </script>
</head>
<body>
    <h4>
        openDatabase</h4>
    <code>var database = openDatabase('msdb', '1.0', 'HTML5 Database', 2 * 1024 * 1024);</code>
    <h4>
        Executing queries</h4>
    <code>database.transaction(function (t) {<br />
        t.executeSql('CREATE TABLE IF NOT EXISTS Emp (ID UNIQUE,Name,Salary)');<br />
        <br />
        t.executeSql('INSERT INTO Emp (ID,Name,Salary) VALUES (1,"A",40000)');<br />
        t.executeSql('INSERT INTO Emp (ID,Name,Salary) VALUES (2,"B",50000)');<br />
        t.executeSql('INSERT INTO Emp (ID,Name,Salary) VALUES (3,"C",20000)');<br />
        t.executeSql('INSERT INTO Emp (ID,Name,Salary) VALUES (4,"D",8000)');<br />
        }); </code>
    <div id="status">
        Status Message</div>
</body>
</html>