Exactly a year ago on November 18, 2010, the W3C announced that Web SQL database is a deprecated specification. Many major browsers including Chrome, Safari, Opera and nearly all Webkit based mobile devices support WebSQL, however, if you are going to start a new project and/or you wish to have your code running with the new version of client side database (that will receive updates and improvements) you should implement indexedDB as your client side database. In this short post we will see what are the main steps to refractor your WebSQL code to IndexedDB.

First lets create the databases

// WebSQL 
database = openDatabase('todos1', '1.0', 'todo list example db', 2*1024*1024);

// InxededDB
var todoDB = {};
var indexedDB = window.indexedDB || window.webkitIndexedDB || window.mozIndexedDB;
todoDB.indexedDB = {};
todoDB.indexedDB.db = null;

Create Table/ObjectStore

In both cases we need some ‘space’ to save our information

// WebSQL - creating a new table
 database.transaction(function(tx) {
     tx.executeSql("CREATE TABLE IF NOT EXISTS tasks (id REAL UNIQUE, text TEXT)", []);
   });

// IndexedDB - creating a new object store that will hold our data
todoDB.indexedDB.open = function() {
        var request = indexedDB.open("todos");

        request.onsuccess = function(e) {
          var v = "2.0 beta"; // yes! you can put strings in the version not just numbers
          todoDB.indexedDB.db = e.target.result;
          var db = todoDB.indexedDB.db;
          // We can only create Object stores in a setVersion transaction;
          if (v!= db.version) {
            var setVrequest = db.setVersion(v);

            // onsuccess is the only place we can create Object Stores
            setVrequest.onsuccess = function(e) {
              if(db.objectStoreNames.contains("todo")) {
                db.deleteObjectStore("todo");
              }
              var store = db.createObjectStore("todo",
              {keyPath: "timeStamp"});
              todoDB.indexedDB.getAllTodoItems();
            };
          }
          else {
            todoDB.indexedDB.getAllTodoItems();
          }
        };
        request.onfailure = todoDB.indexedDB.onerror;
      }

Add Item

Now it’s time to add some data to our database, no?

// WebSQL
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]);
        });
        // now let clean it to the next todo
        todo.value = "";
        showAll();
      }
      
// IndexedDB
todoDB.indexedDB.addTodo = function(todoText) {
        var db = todoDB.indexedDB.db;
        var trans = db.transaction(['todo'], IDBTransaction.READ_WRITE);
        var store = trans.objectStore("todo");

        var data = {
          "text": todoText,
          "timeStamp": new Date().getTime()
        };

        var request = store.put(data);

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

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

Fetch Items

After you have data it’s only make sense to show it to the world (and your dear friends)

// WebSQL
function showAll() {
        document.getElementById("ourList").innerHTML = "" ; 
        database.transaction(function(tx) {
          tx.executeSql('SELECT * FROM tasks', [], function (tx, results) {
            var len = results.rows.length, i;
            for (i = 0; i  Todo text: " + results.rows.item(i).text);
              
              var a = document.createElement("a");
              a.textContent = " [Delete]";
              a.setAttribute('data-key', results.rows.item(i).id);
              a.setAttribute('data-val', results.rows.item(i).text);
              a.addEventListener("click", function() {
                deleteTodo(this.getAttribute("data-key"),this.getAttribute("data-val") );
              }, false);
              li.appendChild(t);
              li.appendChild(a);
              document.getElementById("ourList").appendChild(li);
            }
          });        
        });
      }

// IndexedDB
function showAll() {
        document.getElementById("ourList").innerHTML = "" ;   
        var request = window.indexedDB.open("todos");
        request.onsuccess = function(event) {
          // Enumerate the entire object store.
          var db = todoDB.indexedDB.db;
          var trans = db.transaction(["todo"], IDBTransaction.READ_ONLY);
          var request = trans.objectStore("todo").openCursor();
  
          request.onsuccess = function(event) {
            var cursor = request.result || event.result;
            // If cursor is null then we've completed the enumeration.
            if (!cursor) {
              return;
            }
            var element = document.createElement("div");
            element.textContent = "key: " + cursor.key + " => Todo text: " + cursor.value.text;
            document.getElementById("ourList").appendChild(element);
            cursor.continue();
          }
        }                    
      }

Delete Item

In rare cases we wish to delete stuff… It’s easy.

// WebSQL
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
todoDB.indexedDB.deleteTodo = function(id) {
        var db = todoDB.indexedDB.db;
        var trans = db.transaction(["todo"], IDBTransaction.READ_WRITE);
        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);
        };
      };

As Oscar Wilde said: “…Consistency is the last refuge of the unimaginative…” – so in our case, let’s save data locally and have more performance in our web apps (with some consistency).

Live Example

All the code is on github – https://github.com/greenido/WebSQL-to-IndexedDB-example

and you can play with a live example.