The Web SQL database API is not part of the HTML5 specification, but it is an independent specification that introduces a set of APIs that operate client database using SQL.

If you are a Web back-end programmer, it should be easy to understand the operation of SQL.

You can also refer to our SQL tutorial Learn more about database operations.

Web SQL databases can work in the latest versions of Safari, Chrome and Opera browsers.

Core method

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

1. openDatabase: This method creates a database object using an existing database or a newly created database.
2. Transaction: This approach allows us to control a transaction and perform commits or rollbacks based on this situation.
3. executeSql: This method is used to execute actual SQL queries.

Open the 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 five parameters of openDatabase() method are described as follows:

1. Database name
2. Version number
3. Descriptive text
4. Database size
5. Create callback

The fifth parameter, creating callbacks, is invoked after creating the database.

Perform query operations

The database.transaction() function is used to perform the operation:

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)');
});

When the above statement is executed, a table named LOGS is created in the'mydb'database.

insert data

After executing the above create table 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, "Rookie tutorial")');
   tx.executeSql('INSERT INTO LOGS (id, log) VALUES (2, "www.runoob.com")');
});

We can also use dynamic values to insert 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 (?, ?)', [e_id, e_log]);
});

The e_id and e_log in the instance are external variables, and executeSql maps each entry in the array parameter to "?".

Read data

The following example demonstrates how to read data that already exists in 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, "Rookie tutorial")');
   tx.executeSql('INSERT INTO LOGS (id, log) VALUES (2, "www.runoob.com")');
});
 
db.transaction(function (tx) {
   tx.executeSql('SELECT * FROM LOGS', [], function (tx, results) {
      var len = results.rows.length, i;
      msg = "<p>Number of query records: " + 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, "Rookie tutorial")');
    tx.executeSql('INSERT INTO LOGS (id, log) VALUES (2, "www.runoob.com")');
    msg = '<p>The data table has been created and two pieces of data have been 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>Number of query records: " + 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 results of the above example are shown in the following figure:

Delete record

The format used to delete records is as follows:

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

Deleting the specified data id can also be dynamic:

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

Update record

The format used for updating records is as follows:

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

Updating 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, "Rookie tutorial")');
    tx.executeSql('INSERT INTO LOGS (id, log) VALUES (2, "www.runoob.com")');
    msg = '<p>The data table has been created and two pieces of data have been inserted.</p>';
    document.querySelector('#status').innerHTML =  msg;
 });
 
 db.transaction(function (tx) {
      tx.executeSql('DELETE FROM LOGS  WHERE id=1');
      msg = '<p>delete id A record of 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>To update id 2 records.</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>Number of query records: " + 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 results of the above example are shown in the following figure: