HTML5, JavaScript, webdev

Convert WebSQL To IndexedDB Tutorial

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.

Standard

4 thoughts on “Convert WebSQL To IndexedDB Tutorial

  1. It appears I can choose to write to a browser store that works today or may work tomorrow. Your webSQL example functions on mobile safari (latest), but indexedDB does not.

    Is there any relation to theses browser DBs and the manifest?

  2. greenido says:

    If you are going to write a mobile web app – your choice today should be WebSQL because the mobile safari support it but not indexedDB (check: http://caniuse.com/#search=indexedDB on the right side you have all the mobile browsers).
    But, in case you are developing a web app both Chrome and FF support indexedDB today and all the major browsers will join them in the future.
    This is the place to see which browser support it: http://caniuse.com/#search=indexedDB

    Good luck.

  3. Jonas Sicking says:

    there is no onfailure property on requests. This isn’t because requests can’t fail, it’s because the property is called onerror 🙂

    Yes, on mobile IndexedDB still has barely any marketshare, but on desktop it has better marketshare than WebSQL according to caniuse.com

    IndexedDB is going to be the way forward on mobile too though, so makes sense to start looking at it there too.

  4. greenido says:

    @Jonas – thanks for the catch 🙂 It’s fixed it asap.
    As for the market share and the direction – you are 100% right… I would go with indexedDB.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s