Web developers already have localStorage, which is used for client side storage of simple key-value pairs. This alone doesn’t address the needs of many web applications for structured storage and indexed data. Mozilla is working on a structured storage API with indexing support called IndexedDB, and we will have some test builds in the next few weeks. This can be compared to the WebDatabase API implemented by several browsers that uses a subset of the allowable language of SQLite. Mozilla has chosen to not implement WebDatabase for various reasons discussed in this post.
In order to compare IndexedDB and WebDatabase, we are going to show four examples that use most parts of the asynchronous APIs of each specification. The differences between SQL storage with tables (WebDatabase) and JavaScript object storage with indexes (IndexedDB) becomes pretty clear after reading the examples. The synchronous versions of these APIs are only available on worker threads. Since not all browsers currently implement worker threads, the synchronous APIs will not be discussed at this time. The IndexedDB code is based off a proposal that Mozilla has submitted to the W3C WebApps working group that has gotten positive feedback so far. The code for both APIs does not include any error handling (for brevity), but production code should always have it!
These examples are for the storage of a candy store’s sale of candy to customers, which we’ll refer to as kids. Each entry in candySales represents a sale of a specified amount of candy to a kid, specified by an entry in candy and kids respectively.
Example 1 – Opening and Setting Up a Database
This first example demonstrates how to open a database connection and create the tables or object stores if the version number is not correct. Upon opening the database, both examples check the version and create the necessary tables or object stores and then set the correct version number. WebDatabase is a bit stricter in how it handles versions by giving an error if the database version is not what the caller expects (this is specified by the second argument to openDatabase). IndexedDB simply lets the caller handle versioning as they see fit. Note that there is active discussion about how IndexedDB should handle version changes in the working group.
WebDatabase
var db = window.openDatabase("CandyDB", "", "My candy store database", 1024); if (db.version != "1") { db.changeVersion(db.version, "1", function(tx) { // User's first visit. Initialize database. var tables = [ { name: "kids", columns: ["id INTEGER PRIMARY KEY", "name TEXT"]}, { name: "candy", columns: ["id INTEGER PRIMARY KEY", "name TEXT"]}, { name: "candySales", columns: ["kidId INTEGER", "candyId INTEGER", "date TEXT"]} ]; for (var index = 0; index < tables.length; index++) { var table = tables[index]; tx.executeSql("CREATE TABLE " + table.name + "(" + table.columns.join(", ") + ");"); } }, null, function() { loadData(db); }); } else { // User has been here before, no initialization required. loadData(db); }
IndexedDB
var request = window.indexedDB.open("CandyDB", "My candy store database"); request.onsuccess = function(event) { var db = event.result; if (db.version != "1") { // User's first visit, initialize database. var createdObjectStoreCount = 0; var objectStores = [ { name: "kids", keyPath: "id", autoIncrement: true }, { name: "candy", keyPath: "id", autoIncrement: true }, { name: "candySales", keyPath: "", autoIncrement: true } ]; function objectStoreCreated(event) { if (++createdObjectStoreCount == objectStores.length) { db.setVersion("1").onsuccess = function(event) { loadData(db); }; } } for (var index = 0; index < objectStores.length; index++) { var params = objectStores[index]; request = db.createObjectStore(params.name, params.keyPath, params.autoIncrement); request.onsuccess = objectStoreCreated; } } else { // User has been here before, no initialization required. loadData(db); } };
Example 2 – Storing Kids in the Database
This example stores several kids into the appropriate table or object store. This example demonstrates one of the risks that have to be dealt with when using WebDatabase: SQL injection attacks. In WebDatabase explicit transactions must be used, but in IndexedDB a transaction is provided automatically if only one object store is accessed. Transaction locking is per-object store in IndexedDB. Additionally, IndexedDB takes a JavaScript object to insert, whereas with WebDatabase callers must bind specific columns. In both cases you get the insertion id in the callback.
WebDatabase
var kids = [ { name: "Anna" }, { name: "Betty" }, { name: "Christine" } ]; var db = window.openDatabase("CandyDB", "1", "My candy store database", 1024); db.transaction(function(tx) { for (var index = 0; index < kids.length; index++) { var kid = kids[index]; tx.executeSql("INSERT INTO kids (name) VALUES (:name);", [kid], function(tx, results) { document.getElementById("display").textContent = "Saved record for " + kid.name + " with id " + results.insertId; }); } });
IndexedDB
var kids = [ { name: "Anna" }, { name: "Betty" }, { name: "Christine" } ]; var request = window.indexedDB.open("CandyDB", "My candy store database"); request.onsuccess = function(event) { var objectStore = event.result.objectStore("kids"); for (var index = 0; index < kids.length; index++) { var kid = kids[index]; objectStore.add(kid).onsuccess = function(event) { document.getElementById("display").textContent = "Saved record for " + kid.name + " with id " + event.result; }; } };
Example 3 – List All Kids
This example lists all of the kids stored in the kids table or the kids object store. WebDatabase uses a result set object which will be passed to the callback method provided after all rows have been retrieved. IndexedDB, on the other hand, passes a cursor to the event handler as results are retrieved. Results should come back faster, as a result. While not shown in this example, you can also stop iterating data with IndexedDB by simply not calling cursor.continue().
WebDatabase
var db = window.openDatabase("CandyDB", "1", "My candy store database", 1024); db.readTransaction(function(tx) { // Enumerate the entire table. tx.executeSql("SELECT * FROM kids", function(tx, results) { var rows = results.rows; for (var index = 0; index < rows.length; index++) { var item = rows.item(index); var element = document.createElement("div"); element.textContent = item.name; document.getElementById("kidList").appendChild(element); } }); });
IndexedDB
var request = window.indexedDB.open("CandyDB", "My candy store database"); request.onsuccess = function(event) { // Enumerate the entire object store. request = event.result.objectStore("kids").openCursor(); request.onsuccess = function(event) { var cursor = event.result; // If cursor is null then we've completed the enumeration. if (!cursor) { return; } var element = document.createElement("div"); element.textContent = cursor.value.name; document.getElementById("kidList").appendChild(element); cursor.continue(); }; };
Example 4 – List Kids Who Bought Candy
This example lists all the kids, and how much candy each kid purchased. WebDatabase simply uses a LEFT JOIN query which makes this example very simple. IndexedDB does not currently have an API specified for doing a join between different object stores. As a result, the example opens a cursor to the kids object store and an object cursor on the kidId index on the candySales object store and performs the join manually.
WebDatabase
var db = window.openDatabase("CandyDB", "1", "My candy store database", 1024); db.readTransaction(function(tx) { tx.executeSql("SELECT name, COUNT(candySales.kidId) " + "FROM kids " + "LEFT JOIN candySales " + "ON kids.id = candySales.kidId " + "GROUP BY kids.id;", function(tx, results) { var display = document.getElementById("purchaseList"); var rows = results.rows; for (var index = 0; index < rows.length; index++) { var item = rows.item(index); display.textContent += ", " + item.name + "bought " + item.count + "pieces"; } }); });
IndexedDB
candyEaters = []; function displayCandyEaters(event) { var display = document.getElementById("purchaseList"); for (var i in candyEaters) { display.textContent += ", " + candyEaters[i].name + "bought " + candyEaters[i].count + "pieces"; } }; var request = window.indexedDB.open("CandyDB", "My candy store database"); request.onsuccess = function(event) { var db = event.result; var transaction = db.transaction(["kids", "candySales"]); transaction.oncomplete = displayCandyEaters; var kidCursor; var saleCursor; var salesLoaded = false; var count; var kidsStore = transaction.objectStore("kids"); kidsStore.openCursor().onsuccess = function(event) { kidCursor = event.result; count = 0; attemptWalk(); } var salesStore = transaction.objectStore("candySales"); var kidIndex = salesStore.index("kidId"); kidIndex.openObjectCursor().onsuccess = function(event) { saleCursor = event.result; salesLoaded = true; attemptWalk(); } function attemptWalk() { if (!kidCursor || !salesLoaded) return; if (saleCursor && kidCursor.value.id == saleCursor.kidId) { count++; saleCursor.continue(); } else { candyEaters.push({ name: kidCursor.value.name, count: count }); kidCursor.continue(); } } }
IndexedDB generally simplifies the programming model for interacting with databases, and allows for a wide number of use cases. The working group is designing this API so it could be wrapped by JavaScript libraries; for instance, there’s plenty of room for a CouchDB-style API on top of our IndexedDB implementation. It would also be very possible to build a SQL-based API on top of IndexedDB (such as WebDatabase). Mozilla is eager to get developer feedback about IndexedDB, particularly since the specification has not been finalized yet. Feel free to leave a comment here expressing your thoughts or leave anonymous feedback through Rypple.
Pingback from Beyond HTML5: Database APIs and the Road to IndexedDB ✩ Mozilla Hacks – the Web developer blog on June 1st, 2010 at 1:24 pm: