The Web SQL Database specification is a separate standard from HTML5, though its often discussed alongside it. This specification provides a set of APIs for manipulating client-side databases using SQL syntax. If you have experience with backend SQL operations, you will find these APIs familiar.

Currently, the Web SQL Database API is supported in Safari, Chrome, and Opera browsers.

Core Methods

The specification defines three fundamental methods for database operations:

1. openDatabase: Creates a new database or opens an existing one, returning a database object.
2. transaction: Provides control over database transactions, enabling commit or rollback operations.
3. executeSql: Executes actual SQL queries against the database.

Opening a Database

The openDatabase() method is used to access existing databases or create new ones. If the specified database does not exist, it will be created automatically:

var db = openDatabase('testdb', '1.0', 'Sample Database', 2 * 1024 * 1024);

The openDatabase() method accepts five parameters:

1. Database name
2. Version number
3. Description text
4. Database size in bytes
5. Creation callback function (optional)

The callback function is invoked after the database is successfully created.

Executing Queries

Database operations are performed using the database.transaction() function:

var db = openDatabase('testdb', '1.0', 'Sample Database', 2 * 1024 * 1024);
db.transaction(function (tx) {
    tx.executeSql('CREATE TABLE IF NOT EXISTS EVENTS (id unique, message)');
});

This creates a table named EVENTS in the testdb database.

Inserting Data

After creating a table, you can insert data using similar transaction logic:

var db = openDatabase('testdb', '1.0', 'Sample Database', 2 * 1024 * 1024);
db.transaction(function (tx) {
    tx.executeSql('CREATE TABLE IF NOT EXISTS EVENTS (id unique, message)');
    tx.executeSql('INSERT INTO EVENTS (id, message) VALUES (1, "First event")');
    tx.executeSql('INSERT INTO EVENTS (id, message) VALUES (2, "Second event")');
});

You can also use parameterized queries with dynamic values:

var db = openDatabase('testdb', '1.0', 'Sample Database', 2 * 1024 * 1024);
db.transaction(function (tx) {
    tx.executeSql('CREATE TABLE IF NOT EXISTS EVENTS (id unique, message)');
    tx.executeSql('INSERT INTO EVENTS (id, message) VALUES (?, ?)', [eventId, eventMessage]);
});

In this example, eventId and eventMessage are external variables. The executeSql method maps each array element to the corresponding placeholder.

Reading Data

The following example demonstrates how to retrieve existing data from the database:

var db = openDatabase('testdb', '1.0', 'Sample Database', 2 * 1024 * 1024);
db.transaction(function (tx) {
    tx.executeSql('CREATE TABLE IF NOT EXISTS EVENTS (id unique, message)');
    tx.executeSql('INSERT INTO EVENTS (id, message) VALUES (1, "First event")');
    tx.executeSql('INSERT INTO EVENTS (id, message) VALUES (2, "Second event")');
});

db.transaction(function (tx) {
    tx.executeSql('SELECT * FROM EVENTS', [], function (tx, results) {
        var count = results.rows.length;
        var output = "<p>Total records: " + count + "</p>";
        document.getElementById('result').innerHTML += output;
        for (var i = 0; i < count; i++) {
            alert(results.rows.item(i).message);
        }
    });
});

Complete Example

<html>
   <head>
      <meta charset="UTF-8">
      <title>Web SQL Demo</title>
      <script type="text/javascript">
         var db = openDatabase('testdb', '1.0', 'Sample Database', 2 * 1024 * 1024);
             
         db.transaction(function (tx) {
            tx.executeSql('CREATE TABLE IF NOT EXISTS EVENTS (id unique, message)');
            tx.executeSql('INSERT INTO EVENTS (id, message) VALUES (1, "First event")');
            tx.executeSql('INSERT INTO EVENTS (id, message) VALUES (2, "Second event")');
            var status = '<p>Table created with two records.</p>';
            document.getElementById('result').innerHTML = status;
         });

         db.transaction(function (tx) {
            tx.executeSql('SELECT * FROM EVENTS', [], function (tx, results) {
               var count = results.rows.length;
               var output = "<p>Total records: " + count + "</p>";
               document.getElementById('result').innerHTML += output;
                    
               for (var i = 0; i < count; i++) {
                  output = "<p><b>" + results.rows.item(i).message + "</b></p>";
                  document.getElementById('result').innerHTML += output;
               }
            });
         });
      </script>
   </head>
    
   <body>
      <div id="result"></div>
   </body>
</html>