Migrating your WebSQL DB to IndexedDB

HTML5 Rocks

Introduction

Because web databases let you store data and assets in users' browsers and provide you with rich query abilities, they enable you to create a new breed of web applications that can work online, offline, and states in between where the connection is flaky. Here are the main differences between the two databases.

Category WebSQL IndexedDB
Advantages A real, relational database implementation on the client (SQLite).
  • Allows fast indexing and searching of objects, so in a web application scenario, you can manage your data and read/write it fast.
  • A NoSQL database that let you work with your JavaScript objects and indexing them based on your application needs.
  • Works in asynchronous mode with moderately granular locking per transaction. This allows you to work inside the event-driven module of JavaScript.
Disadvantages
  • The spec is deprecated
  • Overhead of SQL language you need to master and transform your JavaScript objects into relational schema
  • Not object driven
Harder to understand if you are coming from the world of relational databases.
Location Tables contain columns and rows objectStore contains Javascript objects and keys
Query Mechanism SQL Cursor APIs, Key Range APIs, and Application Code
Transaction Lock can happen on databases, tables, or rows on 'readwrite' transactions Lock can happen on database 'versionchange' transaction, on an objectStore 'readonly' and 'readwrite' transactions.
Transaction Commits Transaction creation is explicit. Default is to rollback unless we call commit. Transaction creation is explicit. Default is to commit unless we call abort or there is an error that is not caught.

Since November 18, 2010, the W3C announced that Web SQL database is a deprecated specification. This is a recommendation for web developers to no longer use the technology as effectively, the spec will receive no new updates and browser vendors aren't encouraged to support this technology. The new alternative is IndexedDB which is already available on Chrome 12+, Firefox 5+ and in IE 10+ as well.

In this tutorial, we will take a simple example of a todo list and move our WebSQL code to indexedDB one.

Initializing the database

In the majority of cases where you are using web database you will be using the asynchronous API. The asynchronous API is a non-blocking system and, as such, does not get data through return values, but rather will gets data delivered to a defined callback function. We are going to use the asynchronous APIs in both webSQL and IndexedDB.

Let's see how we are creating the database in each case.

WebSQL - Creating the database

You can only create a table by executing a CREATE TABLE SQL statement inside a transaction. We have defined a function that create a table in the body onload event. If the table doesn't already exist, a table is created. In our case, let's have 2MB of storage allocated to our todo list.

var db = openDatabase('todos1', '1', 'todo list example db', 2 * 1024 * 1024);

IndexedDB - Creating the database

In IndexedDB, we need a bit more code at the initialization phase because we wish to handle the prefix per browser (webkit, moz, etc). This enable us to have cleaner code during our application. Moreover, when more browsers implement this feature, we could use the window object without any prefix so our code is ready for that.

window.indexedDB = window.indexedDB || window.webkitIndexedDB || window.mozIndexedDB || window.msIndexedDB;

// Handle the prefix of Chrome to IDBTransaction/IDBKeyRange.
if ('webkitIndexedDB' in window) {
  window.IDBTransaction = window.webkitIDBTransaction;
  window.IDBKeyRange = window.webkitIDBKeyRange;
}

indexedDB.db = null;
// Hook up the errors to the console so we could see it.
// In the future, we need to push these messages to the user.
indexedDB.onerror = function(e) {
  console.log(e);
};

Create a table

In WebSQL, we want to create a table. This is the code that define it for us:

if (database) {
  database.transaction(function(tx) {
    tx.executeSql("CREATE TABLE IF NOT EXISTS tasks (id REAL UNIQUE, text TEXT)", []);
  });
}

In IndexedDB, we create an object store inside a 'SetVersion' transaction. SetVersion is the only place in our code that we can alter the structure of the database. In it, we can create and delete object stores and build and remove indexes. Object stores contain your JavaScript objects and you can reach your data by key or by setting indexes. A call to setVersion returns an IDBRequest object where we can attach our callbacks. When successful, we start to create our object stores.

indexedDB.open = function() {
  var v = "2.0 beta"; // yes! you can put strings in the version not just numbers
  var request = indexedDB.open("todos", v);
  
  request.onupgradeneeded = function(e) {
    var db = request.result;
    var store = db.createObjectStore("todo", {keyPath: "timeStamp"});  
  };

  request.onsuccess = function(e) {    
    todoDB.indexedDB.db = e.target.result;
    todoDB.indexedDB.getAllTodoItems();
  };

  request.onfailure = todoDB.indexedDB.onerror;
};

Object Stores are created with a single call to createObjectStore(). The method takes a name of the store and an parameter object. The parameter object is very important as it lets you define important optional properties. In our case, we define a keyPath that is the property that makes an individual object in the store unique. That property in this example is "timeStamp". "timeStamp" must be present on every object that is stored in the objectStore.

Note: according to latest IndexedDB spec: http://dvcs.w3.org/hg/IndexedDB/raw-file/tip/Overview.html setVersion() will be taken out. Thus, our code snippets here is going to change and the version setting will be part of the open() function of the database.

Adding data

Let's have a look at the most basic ability: adding data to our database. In both cases, we are using the location (table or object store) and then adding some data into it.

WebSQL - Adding item

In WebSQL, we use a SQL statement that add our data to the 'tasks' table. Before this action, we are constructing an object that will hold our to-do item and the time as key.
function addTodo() {
  var todo = document.getElementById("todo");
  var task = {
    "id": new Date().getTime(),
    "text": todo.value
  };

  database.transaction(function(tx) {
    tx.executeSql('INSERT INTO tasks (id, text) values (?, ?)', [task.id, task.text]);
  });

  todo.value = ''; // Clean for the next todo.
  showAll();
}

IndexedDB - Adding item

We first get a quick reference to the database object todoDB.indexedDB.db, initiate a 'readwrite' transaction and get a reference to our object store. There are three types of transactions:

  • 'readwrite' - Allows records contained in object stores to be added, read, modified, and removed.
  • 'readonly' - Allows records contained in object stores to be read.
  • 'versionchange' - Used to create or update object store and indexes.
Now that the application has access to the object store, we can issue a simple put command with a basic JSON object. Notice that there is a timeStamp property. That is our unique key for the object and is used as the "keyPath". When the call to put is successful, our onsuccess event is triggered, and we are able to render the contents on the screen.

indexedDB.addTodo = function() {
  var db = todoDB.indexedDB.db;
  var trans = db.transaction('todo', 'readwrite');
  var store = trans.objectStore('todo');

  var data = {
    "text": todoText, // todoText should be visible here
    "timeStamp": new Date().getTime()
  };

  var request = store.put(data);

  request.oncomplete = function(e) {
    todoDB.indexedDB.getAllTodoItems();
  };

  request.onerror = function(e) {
    console.log("Error Adding: ", e);
  };
};

Retrieving data

After we put 'huge' amount of data in our database we wish to get it out.

WebSQL - retrieving data

In webSQL we will use the familiar syntax of 'Select'.

function showAll() {
  var ourList = document.getElementById('ourList');
  ourList.innerHTML = '';

  database.transaction(function(tx) {
    tx.executeSql('SELECT * FROM tasks', [], function (tx, results) {
      var len = results.rows.length;
      var ul = document.createElement("ul");
      for (var i = 0; i < len; i++) {
        var item = results.rows.item(i);

        var li = document.createElement("li");
        var t = document.createTextNode(i + ") key: " + item.id +
                                        " => Todo text: " + item.text);
        // Have the ability to delete the item using data attributes and a link.
        var a = document.createElement("a");
        a.textContent = " [Delete]";
        a.dataset.key = item.id;
        a.dataset.value = item.text;

        a.addEventListener("click", function() {
          deleteTodo(this.dataset.key, this.dataset.val );
        }, false);

        li.appendChild(t);
        li.appendChild(a);
        ul.appendChild(li);
      }

      // Update the DOM only after we have ALL items in one element (performance baby...)
      ourList.appendChild(li);
    });
  });
}

IndexedDB - Retrieving data

We open a transaction on our object store. This is set to 'readonly', because we only wish to retrieve data. Next, we open a cursor and iterate with it on our list of todos. All of these commands used in this sample are asynchronous and, as such, the data is not returned from inside the transaction.

function showAll() {
  document.getElementById("ourList").innerHTML = "";

  var request = window.indexedDB.open("todos");
  request.onsuccess = function(event) {
    // Enumerate the entire object store.
    var ul = document.createElement("ul");
    var db = todoDB.indexedDB.db;
    var trans = db.transaction("todo", 'readonly');
    var request = trans.objectStore("todo").openCursor();

    request.onsuccess = function(event) {
      var cursor = request.result;

      // If cursor is null then we've completed the enumeration - so update the DOM
      if (cursor) {
        var li = document.createElement("div");
        li.textContent = "key: " + cursor.key + " => Todo text: " + cursor.value.text;
        ul.appendChild(li);
        cursor.continue();
      }
      else {
        document.getElementById("ourList").appendChild(ul);
      }
    }
  }
}

Deleting data

WebSQL - delete data

We use a simple SQL to delete a todo item base on its ID. We took the extra mile here, and we even, warn the user. Because that's how we like to roll...

function deleteTodo(id, text) {
  if (confirm("Are you sure you want to Delete "+ text +"?")) {
    database.transaction(function(tx) {
      tx.executeSql('DELETE FROM tasks WHERE id=?', [id]);
    });
    showAll();
  }
}

IndexedDB - delete data

Start a transaction, reference the Object Store with your object in and issue a delete command with the unique ID of your object.

indexedDB.deleteTodo = function(id, text) {
  if (confirm("Are you sure you want to Delete " + text + "?")) {
    var db = todoDB.indexedDB.db;
    var trans = db.transaction("todo", 'readwrite');
    var store = trans.objectStore("todo");

    var request = store.delete(id);

    request.onsuccess = function(e) {
      todoDB.indexedDB.getAllTodoItems();
    };

    request.onerror = function(e) {
      console.log("Error Adding: ", e);
    };
  }
};

The Full Code

you can find the full code on github: https://github.com/greenido/WebSQL-to-IndexedDB-example and here is a live example.

Epilogue

As WebSQL is deprecated, we recommend web developers to stop using the technology in new projects, as, effectively, the spec will receive no new updates and browser vendors aren't encouraged to support this technology. The replacement is IndexedDB. As a 'NoSQL' database, IndexedDB is very different from relational databases, and it give us lots of power. IndexedDB let us create an Object Store for a type of data and simply persist Javascript Objects to that store. Each Object Store can have a collection of Indexes that make it efficient to query and iterate across.

References

Comments

0