HTML5 IndexedDB, Web SQL Database and browser wars

Question

I'm starting the development of a web app with offline database storage requirements. Long story short, the app should be able to run on:

• One of the major desktop browsers, Chrome preferred
• Safari on iOS
• Android's native browser (based on V8 and WebKit)

So the question is which technology to choose: IndexedDB or Web SQL Database?

Regarding Web SQL Database, on one hand, it is ready to be used in any of the above scenarios. On the other, Mozilla has stated Firefox will never implement it, and acording to the HTML5 working draft the specification has reached an impasse:

This specification has reached an impasse: all interested implementors have used the same SQL backend (Sqlite), but we need multiple independent implementations to proceed along a standardisation path. Until another implementor is interested in implementing this spec, the description of the SQL dialect has been left as simply a reference to Sqlite, which isn't acceptable for a standard. Should you be an implementor interested in implementing an independent SQL backend, please contact the editor so that he can write a specification for the dialect, thus allowing this specification to move forward.

IndexedDB is the alternative advocated by Mozilla, but it will only come in Firefox 4. Microsoft is interested and Chrome will support it as well. I know nothing of Apple's plans regarding IndexedDB.

I'm personally inclined to choose Web SQL Database, but just because I'm used to SQLite, I like the power and expressiveness of SQL, and I understand the relational model. IndexedDB, for me, is an uncertainty.

That said, I'm afraid of betting on the wrong horse. Is it safe to assume support for Web SQL Database will continue to exist, even if IndexedDB becomes the standard?

(A note on CouchDB: do you also see it as an alternative?)

Solution 1

Considering only WebSQL supports all three requirements you have listed, shouldn't your choice be simple? You have no insight into the development roadmap for Safari or Android, so use what you have available.

Solution 2

Well, as with all things computing, the game is "abstraction".

If you can come up with an adequate layer that works on both a SQL store and a key/value store, then, ideally you're isolated from the problem and can support the appropriate implementation on the particular browser. If your data model and access patterns don't fit with the lowest common denominator (i.e. a k/v store), then that pretty much solves your problem right there.

If you can use either store, then work on a decent access layer and approach the problem from that direction.

Mind, just because you have a k/v store on the back end doesn't mean you have to model your data as only a k/v model. Essentially all a DB is on the backend is a k/v store. If you don't have an insane amount of data, you can do many things. With a large amount of data the hoops you might have to jump through may cost you in performance that you may well not see with a smaller amount of data. All depends.

Solution 3

I'm replying to this in 2016 (5 years after you asked this question) and everything concerning the deprecation of WebSQL still stands. IndexedDB on the other hand, enjoys the support of all of the major browser vendors.

So to anyone who may find themselves here faced with the same decision to make, go with IndexedDB.

As implied by others here, however, such a decision isn't one that necessarily has to be made; one can simply choose (or make) a library which utilizes whichever database is available on a client machine.

BakedGoods differs from such libraries already suggested here in several ways; most pertinently, it allows the storage type(s) that are to be utilized to be explicitly specified, in turn allowing the developer to introduce other factors (such as performance characteristics) in to the decision-making process.

With it, conducting storage operations in whichever of the database types is supported is a matter of...

... specifying the appropriate operation options and equivalent configs for both database types:

//If the operation is a set(), and the referenced structures 
//don't exist, they will be created automatically.

var webSQLOptionsObj = {
    databaseName: "Example_DB",
    databaseDisplayName: "Example DB",
    databaseVersion: "",
    estimatedDatabaseSize: 1024 * 1024,
    tableData: {
        name: "Main",
        keyColumnName: "lastName",
        columnDefinitions: "(lastName TEXT PRIMARY KEY, firstName TEXT)"
    }, 
    tableIndexDataArray: [name: "First_Name_Index", columnNames: "(firstName)"]
};

var indexedDBOptionsObj = {
    databaseName: "Example_DB",
    databaseVersion: 1,
    objectStoreData: {
        name: "Main",
        keyPath: lastName,
        autoIncrement: false
    },
    objectStoreIndexDataArray: [
        {name: "First_Name_Index", keyPath: "firstName", unique: false, multiEntry: false}
    ],
};

var optionsObj = {
    conductDisjointly: false, 
    webSQL: webSQLOptionsObj, 
    indexedDB: indexedDBOptionsObj
};

... and conducting the operation:

bakedGoods.set({
    data: [
        {value: {lastName: "Obama", firstName: "Barack"}}, 
        {value: {lastName: "Biden", firstName: "Joe"}}
    ],
    storageTypes: ["indexedDB", "webSQL"],
    options: optionsObj,
    complete: function(byStorageTypeStoredItemRangeDataObj, byStorageTypeErrorObj){}
});

Its simple interface and unmatched storage facility support comes at the cost of lack of support for some storage facility-specific configurations. For instance, it does not support the conduction of storage operations in WebSQL tables with multi-column primary keys.

So if you make heavy use of those types of features, you may want to look elsewhere.

Oh, and for the sake of complete transparency, BakedGoods is maintained by yours truly :) .

Solution 4

My recommendation is to go for IndexDB, because there is an IndexDB Polyfill available.

All browsers supporting WebSQL can support the IndexDB API this way. The other way round would be very difficult to implement, so if you want to reach all browsers which know about some DB API, IndexDB is the best choice today.

---

Note: Even that this question is old it still is relevant, so I think the answers to this question deserve an update. And sorry for the link-only solution, so I added only links to usually long lasting destinations: W3C and GitHub

Solution 5

With your given requirement of Safari on iOS, there is no alternative but WebSQL. WebSQL is supported in other mobile browsers like Opera and Blackberry. I don't think they will remove WebSQL support even if they have IndexedDB. Somehow they are complementary.

On the other hand, in the browser storage war, IndexedDB has won for good. IE and FF will only have IndexedDB. An ironic fact is that FF implements IndexedDB on top of Sqlite.

What I would like to say is IndexedDB is more than just a key value store. It has indexes and transactions. These two alone provide almost all features of SQL query including join, conditional and sorting. It is not obvious at first because of its asynchronous API.

Performance of IndexedDB is better than WebSQL. It is more secure. It is more flexible for javascript use case. Lastly it is easier to use.

To illustrate the case, I will use pseudo code from my library, but you can use IndexedDB API directly:

The 'people' store has index field 'name' and list indexed field 'hobby'. In JSON,

people = {
  name: 'Foo Bar',
  email: '[email protected]'
  hobby: ['camping', 'swimming']
};

To retrieve name from 'people' whose hobby is 'camping'.

var req = db.keys('people', 'hobby', IDBKeyRange.only('camping'));
req.done(function(campers) {
  db.keys('people', campers, 'name').done(function(names) {
     console.log(names);
  });
});

Interesting thing about this code is there is no serialisation involved. Hence it is very fast.

The following example illustrate friendship graph query. friendship object store has only one listed indexed field friend_list. It uses people object store key as out-of-line primary key. people object store has many attributes, among them is location field. The query is to find list of friends who know me and other_guy and located in 'Singapore'.

var q1 = new ydn.db.Iterator('friendship', 'friend_list', IDBKeyRange.only(me));
var q2 = new dn.db.Iterator('friendship', 'friend_list', IDBKeyRange.only(other_guy));
// if location is not indexed, a filtered value query is used.
var q3 = new ydn.db.Iterator('people', new ydn.db.Expression(['"location"', "'Singapore'", '=']));
// if location is indexed, an index query is used.
// var q3 = new ydn.db.Iterator('people', 'location', IDBKeyRange.only('Singapore'));
var current_loop = 2; // start from inner loop
var join_algo = function(keys, index_keys) {
  var advancement = [];
  advancement[keys.length - 1] = null;
  var has_adv = false;
  for (var i = 0; i < keys.length; i++) {
    if (!goog.isDef(keys[i])) {
      // completed iterator
      if (i != 0) {
        advancement[i] = false; // request to restart the iteration
        advancement[i - 1] = true; // advance outer iterator
        current_loop = i - 1;
      } // i == 0 means we are done.
    has_adv = true;
    break;
    }
  }
  if (!has_adv) {
    // continue looping current
    advancement[current_loop] = true;
  }
  return advancement;
}
var result = db.scan([q3, q1, q2], join_algo);
result.done(function(keys, index_keys, values) {
  console.log(values); // should get desire list of friends 
});

Again this join query is just key scanning and hence very fast. By default scan use a sorted-merge algorithm to find matching keys, but here shows a naive nested-loop join algorithm. So table joining is possible, but you have to code the join algorithm. But newer algorithms like zigzag merge are faster than possible with Sqlite because all inputs are sorted, cursors can advance as well and more importantly the join process can exploit external knowledge that is not in the database. With SQL, the join operation is opaque.

Other than that IndexedDB can be used techniques like streaming and map/reduce processing.

View more solutions

Comments

[ivo]

Yeah, the problem lies in the risk I will be taking. Will WebSQL be dropped out eventually? Is it a matter of time until it is dropped out, or can I safely assume it will remain being supported although maybe not in active feature development? Is there any information I'm not considering that would allow me to make a more knowledgeable choice? Thanks.

[codelark]

There haven't really been any concrete plans for long-term support of either Web SQL or IndexedDB. If you really want to mitigate the risk, you can always bypass the SQL alternatives and use a JSON serialization library to store things in HTML5 localStorage or sessionStorage.

[Wytze]

Just found out that webSQL (and localStorage) is no longer persistent in iOS 5.0.1. The location where webSQL data are stored is now regularly cleaned by the OS. If you're using Phonegap/Cordova, a plugin workaround is being developed. issues.apache.org/jira/browse/CB-330

[Tino]

FYI: Apache/Cordova/PhoneGap fixed the problem(s) in the meanwhile

[Chepech]

Just a note on support: WebSQL is supported on all browsers on all mayor OS versions (iOS7, Android KiKat) as for today, so it is and still be an option for while.

[Vladislav Rastrusny]

@Chepech WebSQL is not supported natively on FireFox. You have to use addons: github.com/Sean-Der/WebSQL-For-FireFox

[Chepech]

@FractalizeR - True, the question actually refers to 2 browsers, Safari for iOS and Native Android, that's what I meant by "all". Sorry for the confusion. You can find all supported Browsers here: caniuse.com/#search=websql