Wednesday, March 30, 2011

On Web SQL Database and IndexedDB

One of the trickier aspects of web development is dealing with storage. When I started out, the only thing you could store on the user’s web browser were cookies, which generally didn’t give you a lot of room to store anything complex.

Time moves on and so does web technology. The major, major, issue that web developers wanted addressed was the fact they could develop really beautifully advanced applications in Javascript, but could only store data by sending it over the network to a separate server. With HTML5, there was also a desire to develop applications so that they’d run off-line. Where could they store data?

There are currently three ways to store data in a web browser. Only one is a real (both de-facto and de-jure) standard, it’s called Web Storage, and most mainstream web browsers, with the exception of Internet Explorer versions 1-7, support it, to a certain extent. The system is crude, allowing applications to store key/value pairs (ie “USERNAME=paulh”) but nothing more than that.

To advance beyond this, Apple came up with a system called Web SQL Database.

Web SQL Database is based on a free database product called SQLite. SQLite is a database system programmable in a large subset of the SQL programming language. The system is more freeform than most SQL databases like Oracle or PostgreSQL, and the code is much simpler and easy to integrate into existing applications, which means a lot of the popular applications you use every day actually have SQLite built into them.

Apple’s solution is popular, it works, and it’s also been adopted by Google for Chrome and Android. However, there are detractors, notably Microsoft and Mozilla. They argue that Web SQL Database has a number of shortcomings:
  • SQL isn't really a standard. There are differences between the official standards, and virtually every implementation has non-standard extensions to deal with shortcomings in the language.
  • Web SQL Database's detractors also argue that SQL is too high level. If a programmer merely wants to insert and remove things from a table, why force them to use a complex programming language?
  • Web SQL Database is based on SQLite, but SQLite doesn't implement a specific standard, and it would be hard, it's been argued, to create a standard "SQL" for Web SQL Database.
Many detractors also complain that SQL itself has flaws, and thus building a persistent storage system based on SQL is not a good idea. This movement is called the "NoSQL" movement, and they have many reasons for being unhappy with SQL.
  • SQL is an entirely different system, unrelated to the languages its usually embedded within.
  • It's easy for inexperienced programmers to embed security flaws in their SQL statements
  • It's easy for inexperienced programmers to develop overly complex queries that database systems find impossible to process in a reasonable period of time.
  • SQL is a non-standard. While it's possible to write SQL that will work under Oracle, PostgreSQL, and MySQL, it's very hard to do without a working knowledge of the quirks and flaws of all three systems.
Mozilla proposed an alternative, called IndexedDB. IndexedDB works like this:
  • On the browser's back end, data is stored in a database, either using a custom technology, or, more often, using SQLite,
  • A standard, DOM-like, interface is provided allowing programmers to make basic queries into the database using regular Javascript functions.
  • If programmers want to use SQL, they can find someone who's written a SQL-front end to the IndexedDB API and use their library.
This proposal is controversial. Critics argue that IndexedDB is substantially worse than the system it replaces, and that many of Mozilla's expressed concerns are overblown. They argue that creating a standard based on the current iteration of SQLite would not be as hard as Mozilla claims; they argue that given a SQL database is being used at a low level anyway, the system is less efficient than Web SQL Database; they argue that Web SQL Database is, due to its support for SQL, much more powerful, given you can build complex relational queries in the system, while IndexedDB does not support relational databases.

At this point, the W3C has officially "deprecated" Web SQL Database and is promoting IndexedDB. That said, given the relative unpopularity of IndexedDB, and popularity of Web SQL Database, it seems highly unlikely that the latter will disappear as a de-facto standard. In some ways, the W3C's decisions are disappointing as they reduce the likelihood that a version of Web SQL Database that has been "fixed" will appear.

While I can see both sides of the argument, I'm firmly in the camp that sees IndexedDB as a solution that'll result in more complex, more difficult to maintain, code, that'll encourage dependencies on bloated third party libraries. I'm not seeing the upside.



  1. The issue of WebSQL is being discussed right now:

    It may well be that Mozilla eventually decide to provide an embedding of SQLite.

    There is a design flaw in IndexedDB that will have performance consequences for anyone who wants to store more than 50,000 objects. IndexedDB is not stateless: it traps application schema knowledge within the database, leading to complicated initialization, indexing and migration code, and the inability on the part of the developer to manage indices in the application. This is being discussed here:

    FriendFeed encountered this problem and described it here:

    It's not possible to use IndexedDB to index object array values, something which would have been possible were IndexedDB stateless. There is now a move on the part of some of the contributors to make amends, but most do not seem to understand the issue, or show any sense of urgency. Most proposals in this regard have been relegated to a "Version 2" which may take years to materialize. I doubt that a Version 2 would be necessary, if these design flaws were addressed.

    There seems to be a case of not-invented-here syndrome. The stated intention was to develop something superior to SQLite, or any other embeddable database, for instance Tokyo Cabinet or Berkeley. Yet not enough has been learned from interface advances made by these databases, including Redis, to name a few. Most indexed key-value stores today treat object values as opaque. IndexedDB does not.

    When I voiced these concerns, I was advised by Jonas Sicking of Mozilla that I could use WebSQL as an alternative. He did not respond when I replied that WebSQL had been deprecated, by Mozilla. Perhaps Mozilla have plans to embed SQLite?

    From my recent tests, IndexedDB is at least an order of magnitude slower than WebSQL. In fact, as far as I am aware, Chrome's IndexedDB is built on top of WebSQL.

    But it would not be possible to build a performant SQL wrapper on IndexedDB. One would have to consider using the new FileSystem API for this.

    It is disappointing to see that IndexedDB does not deliver for any of its limitations.

  2. @Joran,

    I'm reading the thread and not really seeing much in terms of movement from the W3C community. It also doesn't appear to be web developers engaging with the W3C, which is a shame because I suspect the vast majority of web developers will simply ignore both - WebSQL because the Big Two are ignoring it, IndexedDB because - well, honestly, it's just not what anyone's asked for.

    I guess we have to hope that third parties will create the plug-ins needed to make the big two work.

  3. What is really sad here is that W3C should do its job here. Which it's not. At the end of the day, one big player (aka Google or Apple) will decide to support one and not the other, which will make a de facto standard. And it's pretty much already the case: WebSQL would be dead for a long time if iOS did support IDb sooner.

    For now, most Web developers are fallbacking to a JSON strings into LocalStorage, or simply use WebSQL because yes, that's what we need, not an over engineered complex slow solution like what iDb is.