Web SQL

Hello everyone and welcome back to fancy hints.

Today, let’s store some data in web SQL, yes SQL in the web. We will be using a local method to cache data from the users client side. Let’s consider any type of data you would like to keep on the client’s machine. Usually this is used in Cordova applications where you can store data in a local indexed DB.

Open Database

First of all, we need to create an instance to connect to the database. The “openDatabase” method can open a specific database by providing the database name, version number, text description, size of database and creation callback. The callback will be called if the database is being created.

function init(tx) {
	tx.executeSql('DROP TABLE IF EXISTS USERS');
	tx.executeSql('CREATE TABLE IF NOT EXISTS USERS (id unique, name, phone)');
}

var db = window.openDatabase("Database", "1.0", "Cordova Demo", 100000, init);

Executing Some Queries

The “transaction” method will allow you to execute any query to the database, such as insert, update, select and/or delete. Here, we are inserting one record into the “Users” table. It’s just a simple table with 3 columns.

db.transaction(function (tx) {  
   tx.executeSql('your query takes place here');
   tx.executeSql('INSERT INTO USERS (id, name, phone) VALUES (1, "Hadi", 098765456)');
});

You can also pass dynamic variables to your query like the below.

db.transaction(function (tx) {  
   tx.executeSql('INSERT INTO USERS (id, name, phone) VALUES (?, ?, ?)', [i++, newName, newPhone]);
});

Selecting Some Rows

Selecting rows is also easy which just requires that you type your select query. You can also add some conditions as dynamic params. The example will map the ‘?’ to the values in the array.

db.transaction(function (tx) {
   tx.executeSql('SELECT * FROM USERS', [], function (tx, results) {
      var len = results.rows.length, i;
      
      for (i = 0; i < len; i++){
         console.log(results.rows.item(i).name );
      }
	
   }, null);
});

db.transaction(function (tx) {
   tx.executeSql('SELECT * FROM USERS where id =?', [2], function (tx, results) {
      var len = results.rows.length, i;
      
      for (i = 0; i < len; i++){
         console.log(results.rows.item(i).name );
      }
	
   }, null);
});

The rest is very similar like using the update or delete methods. It’s just simple SQL queries; however, they are all executed against the web 🙂