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.

Related

• I've posted more thoughts on the subject