❮ Html Url Html Object ❯

HTML5 Web SQL Database

The Web SQL Database API is not part of the HTML5 specification, but it is a standalone specification that introduces a set of APIs using SQL to manipulate client-side databases.

If you are a web backend programmer, you should find it easy to understand SQL operations.

You can also refer to our SQL Tutorial to learn more about database operations.

Web SQL Database works in the latest versions of Safari, Chrome, and Opera browsers.

Core Methods

The following are the three core methods defined in the specification:

openDatabase: This method creates a database object using an existing database or a new database.
transaction: This method allows us to control a transaction and perform commit or rollback based on the situation.
executeSql: This method is used to execute actual SQL queries.

Opening a Database

We can use the openDatabase() method to open an existing database. If the database does not exist, a new database will be created. The code is as follows:

var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024);

The openDatabase() method has five parameters:

Database name
Version number
Description text
Database size
Creation callback

The fifth parameter, the creation callback, is called after the database is created.

Executing Query Operations

Operations are executed using the database.transaction() function:

var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024);
db.transaction(function (tx) {  
   tx.executeSql('CREATE TABLE IF NOT EXISTS LOGS (id unique, log)');
});

The above statement will create a table named LOGS in the 'mydb' database.

Inserting Data

After executing the table creation statement, we can insert some data:

var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024);
db.transaction(function (tx) {
   tx.executeSql('CREATE TABLE IF NOT EXISTS LOGS (id unique, log)');
   tx.executeSql('INSERT INTO LOGS (id, log) VALUES (1, "tutorialpro.org")');
   tx.executeSql('INSERT INTO LOGS (id, log) VALUES (2, "www.tutorialpro.org")');
});

We can also insert data using dynamic values:

var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024);
db.transaction(function (tx) {  
  tx.executeSql('CREATE TABLE IF NOT EXISTS LOGS (id unique, log)');
  tx.executeSql('INSERT INTO LOGS (id,log) VALUES (?, ?)', [e_id, e_log]);
});

In this example, e_id and e_log are external variables, and executeSql maps each entry in the array parameter to "?".

Reading Data

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

var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024);

db.transaction(function (tx) {
   tx.executeSql('CREATE TABLE IF NOT EXISTS LOGS (id unique, log)');
   tx.executeSql('INSERT INTO LOGS (id, log) VALUES (1, "tutorialpro.org")');
   tx.executeSql('INSERT INTO LOGS (id, log) VALUES (2, "www.tutorialpro.org")');
});

db.transaction(function (tx) {
   tx.executeSql('SELECT * FROM LOGS', [], function (tx, results) {
      var len = results.rows.length, i;
      msg = "<p>Query record count: " + len + "</p>";
      document.querySelector('#status').innerHTML +=  msg;

      for (i = 0; i < len; i++){
         alert(results.rows.item(i).log );
      }

   }, null);
});

Complete Example

Example

var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024);
var msg;

db.transaction(function (tx) {
    tx.executeSql('CREATE TABLE IF NOT EXISTS LOGS (id unique, log)');
    tx.executeSql('INSERT INTO LOGS (id, log) VALUES (1, "tutorialpro.org")');
    tx.executeSql('INSERT INTO LOGS (id, log) VALUES (2, "www.tutorialpro.org")');
    msg = '<p>Table created and two records inserted.</p>';
    document.querySelector('#status').innerHTML =  msg;
});

db.transaction(function (tx) {
tx.executeSql('SELECT * FROM LOGS', [], function (tx, results) {
    var len = results.rows.length, i;
    msg = "<p>Query record count: " + len + "</p>";
    document.querySelector('#status').innerHTML +=  msg;

    for (i = 0; i < len; i++){
        msg = "<p><b>" + results.rows.item(i).log + "</b></p>";
        document.querySelector('#status').innerHTML +=  msg;
    }
}, null);
});

The above example results are shown as follows:

Deleting Records

The format for deleting records is as follows:

db.transaction(function (tx) {
    tx.executeSql('DELETE FROM LOGS  WHERE id=1');
});

The specified data id can also be dynamic:

db.transaction(function(tx) {
    tx.executeSql('DELETE FROM LOGS WHERE id=?', [id]);
});

Updating Records

The format for updating records is as follows:

db.transaction(function (tx) {
    tx.executeSql('UPDATE LOGS SET log=\'www.w3cschool.cc\' WHERE id=2');
});

The specified data id can also be dynamic:

db.transaction(function(tx) {
    tx.executeSql('UPDATE LOGS SET log=\'www.w3cschool.cc\' WHERE id=?', [id]);
});

Complete Example

Example

var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024);
var msg;

 db.transaction(function (tx) {
    tx.executeSql('CREATE TABLE IF NOT EXISTS LOGS (id unique, log)');
    tx.executeSql('INSERT INTO LOGS (id, log) VALUES (1, "tutorialpro.org")');
    tx.executeSql('INSERT INTO LOGS (id, log) VALUES (2, "www.tutorialpro.org")');
    msg = '<p>Table created and two records inserted.</p>';
    document.querySelector('#status').innerHTML =  msg;
 });

 db.transaction(function (tx) {
      tx.executeSql('DELETE FROM LOGS  WHERE id=1');
      msg = '<p>Deleted record with id 1.</p>';
      document.querySelector('#status').innerHTML =  msg;
 });

 db.transaction(function (tx) {
     tx.executeSql('UPDATE LOGS SET log=\'www.w3cschool.cc\' WHERE id=2');
      msg = '<p>Updated record with id 2.</p>';
      document.querySelector('#status').innerHTML =  msg;
 });

 db.transaction(function (tx) {
    tx.executeSql('SELECT * FROM LOGS', [], function (tx, results) {
       var len = results.rows.length, i;
       msg = "<p>Query record count: " + len + "</p>";
       document.querySelector('#status').innerHTML +=  msg;

       for (i = 0; i < len; i++){
          msg = "<p><b>" + results.rows.item(i).log + "</b></p>";
          document.querySelector('#status').innerHTML +=  msg;
       }
    }, null);
 });

The above example results are shown as follows:

❮ Html Url Html Object ❯