Posts Tagged ‘websql’

29 Sep

IndexedDB, WebSQL, LocalStorage – what blocks the DOM?

Posted by Nolan Lawson in Webapps. Tagged: , , .

Update June 2019: This blog post was written in 2015. The benchmarks are out-of-date. WebSQL is still deprecated and in fact being removed from iOS Safari. Web Workers are not necessarily a panacea. I’d recommend using IndexedDB for large data, and LocalStorage for small amounts of data that you need synchronous access to.

When it comes to databases, a lot of people just want to know: which one is the fastest?

Never mind things like memory usage, the CAP theorem, consistency, read vs write speed, test coverage, documentation – just tell me which one is the fastest, dammit!

This mindset is understandable. A single number is easier to grasp than a big table of features, and it’s fun to make grand statements like “Redis is 20x faster than Mongo.” (N.B.: I just made that up.)

As someone who spends a lot of time on browser databases, though, I think it’s important to look past the raw speed numbers. On the client side especially, the way you use a database, and how it interacts with the JavaScript environment, has a big impact on something more important than performance: how your users perceive performance.

In this post, I’m going to take a look at various browser databases with regard not only to their speed, but to how much they block the DOM.

TLDR: IndexedDB isn’t nearly the performance home-run that many in the web community think it is. In my tests, I found that it blocked the DOM significantly in Firefox and Chrome, and was slower than both LocalStorage and WebSQL for basic key-value insertions.

Browser database landscape

For the uninitiated, the world of browser databases can be a confusing one. Lawnchair, PouchDB, LocalForage, Dexie, Lovefield, LokiJS, AlaSQL, MakeDrive, ForerunnerDB, YDN-DB – that’s a lot of databases!

As it turns out, though, the situation is much simpler than it appears on the surface. In fact, there are only three ways of storing data in the browser:

Every “database” listed above uses one of those three under the hood (or they operate in-memory). So to understand browser storage, you only really need to understand LocalStorage, WebSQL, and IndexedDB 1.

LocalStorage is a lightweight way to store key-value pairs. The API is very simple, but usage is capped at 5MB in many browsers. Plus the API is synchronous, so as we’ll see later, it can block the DOM. Browser support is very good.

WebSQL is an API that is only supported in Chrome and Safari (and Android and iOS by extension). It provides an asynchronous, transactional interface to SQLite. Since 2010, it has been deprecated in favor of IndexedDB.

IndexedDB is the successor to both LocalStorage and WebSQL, designed to replace them as the “one true” browser database. It exposes an asynchronous API that supposedly avoids blocking the DOM, but as we’ll see below, it doesn’t necessarily live up to the hype. Browser support is extremely spotty, with only Chrome and Firefox having fully usable implementations.

Now, let’s run a simple test to see when and how these APIs block the DOM.

Thou shalt not block the DOM

JavaScript is a single-threaded programming environment, meaning that synchronous operations are blocking. And since the DOM is synchronous, this means that when JavaScript blocks, the DOM is also blocked. So if any operation takes longer than 16ms, it can lead to dropped frames, which users experience as slowness, “stuttering,” or “jank.”

This is the reason that JavaScript has so many asynchronous APIs. Just imagine if your entire page was frozen during every AJAX request – wouldn’t the web be an awful user experience if it worked that way! Hence the profusion of programming constructs like callbacks, promises, event listeners, and the like.

To demonstrate DOM blocking, I’ve put together a simple demo page with an animated GIF. Whenever the DOM is blocked, Kirby will stop his happy dance and freeze in place.

Try this experiment: go to that page, open up the developer tools, and enter the following code:

for (var i = 0; i < 10000; i++) {console.log('blocked!')}

You’ll see that Kirby freezes for the duration of the for-loop:

 

This affects more than just animated GIFs; any JavaScript animation or DOM operation, such as adding or modifying elements, will also be blocked. You can’t even select a radio button; the page is totally unresponsive. The only animations that are unaffected are hardware-accelerated CSS animations.

Using this demo page, I tested four ways of of storing data: in-memory, LocalStorage, WebSQL, and IndexedDB. The test inserts a given number of “documents,” which are just unstructured JSON keyed by a string ID. I made a YouTube video showing my results, but the rest of the article will summarize my findings.

In-memory

Not surprisingly, since any synchronous code is blocking, in-memory operations are also blocking. You can test this in the demo page by choosing “regular object” or “LokiJS” (which is an in-memory database). The DOM blocks during long-running inserts, but unless you’re dealing with a lot of data, you’re unlikely to notice, because in-memory operations are really fast.

To understand why in-memory is so fast, a good resource is this chart of latency numbers every programmer should know. Or I can give you the TLDR, which I’m happy to be quoted on:

“Disk is about a bazillion times slower than memory, and the network is about a bazillion times slower than that.”

— Nolan Lawson

Of course, the tradeoff with in-memory is that your data isn’t saved. So let’s look at some ways of writing data that will actually survive a browser refresh.

LocalStorage

In all three of Chrome, Firefox, and Edge, LocalStorage fully blocks the DOM while you’re writing data 2. The blocking is a lot more noticeable than with in-memory, since the browser has to actually flush to disk.

This is pretty much the banner reason not to use LocalStorage. Even if the API only takes a few hundred milliseconds to return after inserting 10000 records, you’ll notice that the DOM might block for a long time after that. I assume this is because these browsers cache LocalStorage to memory and then batch their write operations (here’s how Firefox does it), but in any case the UI still ends up looking janky.

In Safari, the situation is even worse. Somehow the DOM isn’t blocked at all during LocalStorage operations, but on the other hand, if you insert too much data, you’ll get a spinning beach ball of doom, and the page will be permanently frozen. I’ve filed this as a bug on WebKit.

WebSQL

We can only test this one in Chrome and Safari, but it’s still pretty instructive. In Chrome, WebSQL actually blocks the DOM quite a bit, at least for heavy operations. Whereas in Safari, the animations all remain buttery-smooth, no matter what WebSQL is doing.

This should fill you with a sense of foreboding, as we start to move on to the supposed savior of client-side databases, IndexedDB. Aren’t both WebSQL and IndexedDB asynchronous? Don’t they have nothing to do with the DOM? Why should they block DOM rendering at all?

I myself was pretty shocked by these results, even though I’ve worked extensively with these APIs over the past two years. But let’s keep going further and see how deep this rabbit hole goes…

IndexedDB

If you try that demo page in Chrome or Firefox, you may be surprised to see that IndexedDB actually blocks the DOM for nearly the entire duration of the operation 3. In Safari, I don’t see this behavior at all (although IndexedDB is painfully slow), whereas in Edge I see the occasional dropped frame.

In both Firefox and Chrome, IndexedDB is slower than LocalStorage for basic key-value insertions, and it still blocks the DOM. In Chrome, it’s also slower than WebSQL, which does blocks the DOM, but not nearly as much. Only in Edge and Safari does IndexedDB manage to run in the background without interrupting the UI, and aggravatingly, those are the two browsers that only partially implement the IndexedDB spec.

This was a pretty shocking find, so I promptly filed a bug both on Chrome and on Firefox. It saddens me to think that this is just one more reason web developers will have to ignore IndexedDB – what with the shoddy browser support and the ugly API, we can now add the fact that it doesn’t even deliver on its promise of beating LocalStorage at DOM performance.

Web workers FTW

I do have some good news: IndexedDB works swimmingly well in a web worker, where it runs at roughly the same speed but without blocking the DOM. The only exception is Safari, which doesn’t support IndexedDB inside a worker.

So that means that for Chrome and Firefox, you can always offload your expensive IndexedDB operations to a worker thread, where there’s no chance of blocking the UI thread. In my own tests, I didn’t see a single dropped frame when using this method.

It’s also worth acknowledging that IndexedDB is the only storage option inside of a web worker (or a service worker, for that matter). Neither WebSQL nor LocalStorage are available inside of a worker for any of the browsers I tested; the localStorage and openDatabase globals just aren’t there. (Support for WebSQL used to exist in Chrome and Safari, but has since been removed.)

Test results

I’ve gathered these results into a consolidated table, along with the time taken in milliseconds as measured by a simple Date.now() comparison. All tests were on a 2013 MacBook Air; Edge was run in a Windows 10 VirtualBox. “In-memory” refers to a regular JavaScript object (“regular object” in the demo page). Between each test, all browser data was cleared and the page refreshed.

Take these raw numbers with the grain of salt. They only account for the time taken for the API in question to return successfully (or finish the transaction, in the case of IndexedDB and WebSQL), and they don’t guarantee that the data was durably written or that the DOM wasn’t blocked after the operation completed. However, it is interesting to compare the speed across browsers, and it’s pretty consistent with what I’ve seen from working on PouchDB over the past couple of years.

Number of insertions   1000     10000     100000     Blocks?     Notes  
Chrome 47
   In-memory 4 10 217 Yes
   LocalStorage 18 527 4725 Yes
   WebSQL 45 213 1927 Partially Blocks a bit at the beginning
   IndexedDB 64 572 5372 Yes
   IndexedDB
     in a worker		 66 604 6108 No
Firefox 43
   In-memory 1 12 152 Yes
   LocalStorage 19 177 1950 Yes Froze significantly after loop finished
   IndexedDB 114 823 8849 Yes
   IndexedDB
     in a worker		 132 1006 9264 No
Safari 9
   In-memory 2 8 100 Yes
   LocalStorage 6 41 418 No 10000 and 100000 crashed the page
   WebSQL 26 173 1557 No
   IndexedDB 1093 10658 117790 No
Edge 20
   In-memory 7 19 331 Yes
   LocalStorage 198 4624 N/A Yes 100000 crashed the page
   IndexedDB 315 5657 28662 Slightly A few frames lost at the beginning
   IndexedDB
     in a worker		 985 2881 24236 No

 

Edit: The LocalStorage results are inaccurate, because there was a bug in the test suite causing it to improperly store the JavaScript objects as '[object Object]' rather than using JSON.stringify(). After the fix, LocalStorage performs more poorly.

Key takeaways from the data:

  1. WebSQL is faster than IndexedDB in both Chrome (~2x) and Safari (~100x!) even though I’m inserting unstructured JSON with a string key, which should be IndexedDB’s bread and butter.
  2. LocalStorage is slightly faster than IndexedDB in all browsers (disregarding the crashes).
  3. IndexedDB is not significantly slower when run in a web worker, and never blocks the DOM that way.

Again, these numbers wasn’t gathered in a super rigorous way (I only ran the tests once; didn’t average them or anything), but it should give you an idea of what kind of behavior you can expect from these APIs in different browsers. You can run the demo page yourself to try to reproduce my results.

Conclusion

Running IndexedDB in a web worker is a nice workaround for DOM slowness, but in principle it ought to run smoothly in either environment. Originally, the whole selling point of IndexedDB was that it would improve upon both LocalStorage and WebSQL, finally giving web developers the same kind of storage power that native developers have enjoyed for the past several years.

IndexedDB’s awkward asynchronous API was supposed to be a bitter medicine that, if you swallowed it, would pay off in terms of performance. But according to my tests, that just isn’t the case, at least with IndexedDB’s two flagship browsers, Chrome and Firefox.

I’m still hopeful that browser vendors will resolve all these issues with IndexedDB, although with the spec being over five years old, it sure feels like we’ve been waiting a long time. As someone who does both native and web development for a living, I’m tired of reciting a list of reasons why the web “isn’t quite there yet.” And IndexedDB has been too high on that list for too long.

IndexedDB was the web’s chance to finally get local storage right. It was the chosen one. It was supposed to lead us out of the morass of half-baked solutions and provide the best and fastest way to work with data on the client side. It’s come a long way, but I’m still waiting for it to make good on that original promise.

Footnotes


1: Yes, I’m ignoring cookies, the File API, window.name, SessionStorage, the Service Worker cache, and probably a few other oddballs. There are actually lots of ways of storing data (too many in my opinion), but all of them have niche use cases except for LocalStorage, WebSQL, and IndexedDB.


2: In this article, when I say “Chrome,” “Firefox,” “Edge,” and “Safari”, I mean Chrome Canary 47, Firefox Developer Edition 43, Edge 20, and WebKit Nightly 10600.8.9. All tests were run on a 2013 MacBook Air; Edge was run in Windows 10 using Virtual Box.


3: This blog post used to suggest that the DOM was blocked for the entire duration of the transaction, but after an exchange with Ben Kelly I changed the wording to “nearly the entire duration.”


Thanks to Dale Harvey for providing feedback on a draft of this blog post.

26 Apr

Web SQL Database: In Memoriam

Posted by Nolan Lawson in Webapps. Tagged: , , , .

All signs seem to indicate that Apple will finally ship IndexedDB in Safari 7.1 sometime this year. This means that Safari, the last holdout against IndexedDB, will finally relent to the inevitable victory of HTML5’s new, new storage engine.

So I thought this would be a good time to hold a wake for Web SQL – that much maligned, much misunderstood also-ran that still proudly ships in Safari, Chrome, Opera, iOS, and every version of Android since 2.0.

Often in the tech industry we’re too quick to eviscerate some recently-obsoleted technology (Flash, SVN, Perl), because, as with politics and religion, nothing needs discrediting so much as the most recently reigning zeitgeist. Web SQL deserves better than that, though, so I’m here to give it its dues.

openDatabase('mydatabase', 1, 'mydatabase', 5000000, function (db) {
  db.transaction(function (tx) {
    tx.executeSql('create table rainstorms (mood text, severity int)', 
        [], function () {
      tx.executeSql('insert into rainstorms values (?, ?)', 
          ['somber', 6], function () {
        tx.executeSql('select * from rainstorms where mood = ?', 
            ['somber'], function (tx, res) {
          var row = res.rows.item(0);
          console.log('rainstorm severity: ' + row.severity + 
              ',  my mood: ' + row.mood);
        });
      });
    });
  }, function (err) { 
    console.log('boo, transaction failed!: ' + err); 
  }, function () {
    console.log('yay, transaction succeeded!');
  });
});

The gist of the story is this: in 2009 or so, native iOS and Android apps were starting to give the web a run for its money, and one area where the W3C recognized some room for improvement was in client-side storage. So Apple and Google hacked up the Web SQL Database API, which basically acknowledged that SQLite was great, mobile devs on iOS and Android loved it, and so both companies were happy to ship it in their browsers [1].

However, Microsoft and (especially) Mozilla balked, countering that the SQL language is not really a standard, and having one implementation in WebKit didn’t meet the “independent implementations” requirement necessary to be considered a serious spec.

So by 2010, Web SQL was abandoned in favor of IndexedDB, which is a document store that can be thought of as the NoSQL answer to Web SQL. It was designed by Nikunj Mehta at Oracle (of all places), and by 2014 every major browser, including IE 10 and Android 4.4, has shipped a version of IndexedDB, with Safari expected to join later this year.

As a rank-and-file developer, though, who’s worked with both Web SQL and IndexedDB, I can’t shake the feeling that the W3C made the wrong choice here. Let’s remember what Web SQL actually gave us:

  • SQLite in the browser. Seriously, right down to the sqlite_master table, fts indexes for full-text search, and the idiosyncratic type system. The only thing you didn’t get were PRAGMA commands – other than that, you still had transactions, joins, binary blobs, regexes, you name it.
  • 5MB of storage by default, up to 50MB or more depending on the platform, to be confirmed by the user with a popup window at various increments.
  • The ability to easily hook into the native mobile SQLite databases, e.g. using the SQLite plugin for Cordova/PhoneGap.
  • A high-level, performant API based on an expressive language most everybody knows (SQL).
  • A database which had already been battle-tested on mobile devices, i.e. the place where performance matters.
  • A database which, let’s not forget, is also open-source.

Now what we have instead is IndexedDB, which basically lets you store key/value pairs, where the values are JavaScript object literals and the keys can be one or more fields from within that object. It supports gets, puts, deletes, and iteration. In Chrome it’s built on Google’s LevelDB, whereas in Firefox it’s actually backed by SQLite. In IE, who knows.

Enough has been written already about the failure of IndexedDB to capture the hearts of developers. And the API certainly won’t win any beauty contests:

html5rocks.indexedDB.open = function() {
  var version = 1;
  var request = indexedDB.open("todos", version);

  // We can only create Object stores in a versionchange transaction.
  request.onupgradeneeded = function(e) {
    var db = e.target.result;

    // A versionchange transaction is started automatically.
    e.target.transaction.onerror = html5rocks.indexedDB.onerror;

    if(db.objectStoreNames.contains("todo")) {
      db.deleteObjectStore("todo");
    }

    var store = db.createObjectStore("todo",
      {keyPath: "timeStamp"});
  };

  request.onsuccess = function(e) {
    html5rocks.indexedDB.db = e.target.result;
    html5rocks.indexedDB.getAllTodoItems();
  };

  request.onerror = html5rocks.indexedDB.onerror;
};

Instead of retreading the same old ground, though, I’d like to give my own spin on the broken promises of IndexedDB, as well as acknowledge where it has succeeded.

The death of Web SQL: a play in 1 act

To understand the context of how IndexedDB won out over Web SQL, let’s flash back to 2009. Normally you’d need sleuthing skills to solve a murder mystery, but luckily for us the W3C does everything out in the open, so the whole story is publicly available on the Internet.

The best sources I’ve found are this IRC log from late 2009, the corresponding minutes, the surprisingly heated follow-up thread, and Mozilla’s June 2010 blog post acting as the final nail in the coffin [3].

Here’s my retelling of what went down, starting with the 2009 IRC log:

PROLOGUE

Six houses, all alike in dignity,
In fair IRC, where we lay our scene,
From ancient grudge to new mutiny,
Where civil blood makes civil hands unclean.
From Oracle, that SQL seer of IndexedDB,
To Google, the stronghold of search,
We add Mozilla, the Web SQL killa,
And Apple, peering from its mobile perch.
Here, a storage war would set keys to clack,
Tongues to wag, and specs to shatter, 
There was also Microsoft and Opera,
Who don't really seem to matter.

THE PLAYERS

NIKUNJ MEHTA, of House ORACLE, an instigator
JONAS SICKING, of House MOZILLA, an assassin
MACIEJ STACHOWIAK, of House APPLE, a pugilist
IAN FETTE, of House GOOGLE, a pleader
CHARLES MCCATHIENEVILE, of House OPERA, a peacemaker

ACT 1

SCENE: A dark and gloomy day in Mountain View, or
perhaps a bright and cheery one, depending on your 
IRC client's color scheme.

OK, enough joking around. Let’s let the players tell the story in their own words. I’ll try not to editorialize too much [2].

Jonas Sicking (Mozilla):

we’ve had a lot of discussions
primarily with MS and Oracle, Oracle stands behind Nikunj
we’ve talked to a lot of developers
the feedback we got is that we really don’t want SQL

Ian Fette (Google):

We’ve implemented WebDB, we’re about to ship it

Maciej Stachowiak (Apple):

We’ve implemented WebDB and have been shipping it for some time
it’s shipping in Safari

(At the time, Web SQL was called “Web DB,” and IndexedDB was called “Web Simple DB,” or just “Nikunj.”)

So basically, Sicking (of Mozilla) throws down the gauntlet: users don’t want SQL, and the solution proposed by Nikunj Mehta is backed by all three of Oracle, Microsoft, and Mozilla. Fette (of Google) and Stachowiak (of Apple) respond huffily that they’re already shipping Web SQL.

Ian Fette (Google):

we’re also interested in the Nikunj One

Fette makes a concession here. Recall that Google was quick to implement both Web SQL and IndexedDB, at least in Chrome. The Android stock browser/WebView didn’t get IndexedDB until version 4.4.

Ian Fette (Google):

the Chrome implementation shares some but not quite all of the code
beside shipping it, web sites have versions that target the iPhone and use it
we can’t easily drop it in the near future for that reason

However, Google doesn’t want to stop shipping Web SQL: that genie’s already out of the bottle, and web sites are already using it.

Later on they discuss LocalStorage. This is an interesting part of the conversation: it’s acknowledged that LocalStorage is limited because it’s synchronous only. It’s suggested that instead of IndexedDB, they could simply extend LocalStorage, but nobody bites on that proposal.

Jeremy Orlow (Google):

Google is not happy with the various proposals

Adrian Bateman (Microsoft):

Microsoft’s position is that WebSimpleDB is what we’d like to see
we don’t think we’ll reasonably be able to ship an interoperable version of WebDB
trying to arrive at an interoperable version of SQL will be too hard

Here we arrive at one of the best arguments against Web SQL: creating a separate implementation to match the WebKit version would just be too hard – Microsoft and Mozilla would have to rewrite SQLite itself, with all its funky idiosyncrasies, or just include it wholesale, in which case it’s not an independent implementation.

Chris Wilson (Microsoft):

it seems with multiple interoperable implementations
that you can’t really call it stillborn
when we started looking at WebDB
the reason we liked Nikunj was that it doesn’t impose
but it has the power
the part that concerned us with WebDB is that it presupposes SQLite
we’re not really sure

Ivan Herman (W3C):

Proposal
all the browsers shipping WebDB are WebKit based
proposal: we move WebDB to WebKit.org, and we kill it as a deliverable from this group

Charles C. McCathieNevile (Opera):

I think we’re likely to ship it

At this point, it’s pretty much taken for granted that Web SQL will be dropped from the HTML5 spec. The only thing they’re deciding now is whether to give it to a nice farm family upstate, or take it out back and shoot it.

Google won’t let it go, though. And several times, the speakers are even reminded that they’ve run out of time for discussion of web storage. Google makes the case for full-text search:

Sam Ruby (Apache):

Apple and Google have expressed an interest in added full text search to the api we’ve used

Jeremy Orlow (Google):

that’s extremely important to Google too

Ian Fette (Google):

To use this for gmail we have to be able to do fulltext and we don’t think we can do that performant in JS so we would like native code to do that.

Nikunj Mehta (Oracle):

In some discussions we can provide keyword/context, but fulltext incoroprates some more concepts that can get hairy in different languages. It should perform aequately with a qiuck index.
…
Spec was originally written on berkeleyDB which had no way to retrieve object based on key index. had a way to join dbs but we added a way to lookup an object from the index and treating the indices, and use of joins dropped.

So Google was really adamant that they needed full-text search for Gmail, but nobody else besides Apple was convinced.

Here’s an interesting experiment: open up Gmail in Chrome, and set your developer tools to emulate a mobile device, say the Nexus 4. Perform a search, and then check out the Resources tab to see if Google is doing anything interesting with storage.

Gmail uses Web SQL

Gmail client storage on mobile browsers.

If you’re not sure what you’re looking at, I’ll let you in on the secret: that’s a virtual table, created with the full-text search (FTS) capabilities of SQLite. Note that IndexedDB is not being used at all. And if you use desktop Gmail, neither database is used.

So clearly, Google has already voted with their code to support Web SQL, at least on mobile.

Correction: it turns out that’s not actually a FTS table – it’s just a regular table with some fancy triggers. Queries are cached, but they’re not actually run on the client. Still, FTS is indeed possible in Web SQL, and I think my point about Google preferring Web SQL over IndexedDB still stands.

Back to the IRC log:

Jeremy Orlow (Google):

In Gmail example, if you are searching for a to and from address you might have zillions of addresses so it might be a big burden on the system

Ian Fette (Google):

In terms of the world, Mozilla won’t implement WebDB, and we want to get Gmail working with a DB and there are others who want to get apps working. Plus or minus some detail, it seems Web Simple Database can do taht

Famous last words. It’s five years later, and clearly Google still doesn’t think IndexedDB is ready for primetime, at least in Gmail. Maybe IndexedDB v2 will save the day, though: the working draft contains a proposal for FTS, among other goodies.

The email follow-up: shots fired

After the 2009 meeting, there’s this follow-up email thread, which makes for great reading if you want to see what a W3C fist fight looks like. Curiously, nobody at Google joins in the fray, and we have only Stachowiak at Apple rising to Web SQL’s defense:

Maciej Stachowiak (Apple):

We actually have a bit of a chicken-and-egg problem here. Hixie has
said before he’s willing to fully spec the SQL dialect used by Web
Database. But since Mozilla categorically refuses to implement the
spec (apparently regardless of whether the SQL dialect is specified),
he doesn’t want to put in the work since it would be a comparatively
poor use of time.

Great point. It’s a little disingenuous of Mozilla to cite their own non-participation as a lack of independent implementations.

Maciej Stachowiak (Apple):

At the face-to-face, Mozilla representatives said that most if not all of the developers they spoke to said they wanted “anything but SQL” in a storage solution. This clashes with our experience at Apple, where we have been shipping Web Database for nearly two years now, and where we have seen a number of actual Web applications deployed using it (mostly targeting iPhone).

To me, this argument is so obvious it’s heartbreaking: SQL is a very newbie-friendly language, and iOS (and Android) developers are already familiar with SQLite. So why fix what ain’t broke?

Maciej Stachowiak (Apple):

It seems pretty clear to me that, even if we provide Web SimpleDB as an alternative, our mobile-focused developers will continue to use theSQL database. First, they will not see a compelling reason to change. Second, SimpleDB seems to require more code to perform even simple tasks (comparing the parallel examples in the two specs) and seems to be designed to require a JS library to be layered on top to work well. For our mobile developers, total code size is at a premium. They seem less willing than desktop-focused Web developers to ship large JS libraries, and have typically used mobile-specific JS libraries or aggressively pruned versions of full JS libraries.

An excellent point, and the Gmail example shows that this prediction has been borne out in practice. Jonas Sicking responds:

Jonas Sicking (Mozilla):

If we do specify a specific SQL dialect, that leaves us having to implement it. It’s very unlikely that the dialect would be compatible with SQLite (especially given that SQLite uses a fairly unusual SQL dialect with regards to datatypes) which likely leaves us implementing our own SQL engine.

…

I definitely agree that we don’t want a solution that punishes the mobile market. I think the way to do that is to ensure that SimpleDB is useful even for mobile platforms.

Sicking is right about the difficulty that Mozilla faces here, but in hindsight he was a little optimistic about IndexedDB on mobile. HTML5 is only now starting to catch up to native apps in terms of performance, while big players like Facebook have stopped betting on it entirely.

Maciej Stachowiak (Apple):

> Indeed. I still personally wouldn’t call it multiple independent
> implementations though.

Would you call multiple implementations that use the standard C library independent? Obviously there’s a judgment call to be made here. I realize that in this case a database implementation is a pretty key piece of the problem. But I also think it would be more fruitful for you to promote solutions you do like, than to try to find lawyerly reasons to stop the advancement of specs you don’t (when the later have been implemented and shipped and likely will see more implementations).

Stachowiak is clearly bitter that Web SQL got rejected for what he cites as “lawyerly” reasons. He continues:

Maciej Stachowiak (Apple):

I don’t think SimpleDB is useless for mobile platforms. You certainly *could* use it. But it does have three significant downsides compared to the SQL database: (1) it’s very different from what developers have already (happily) been using on mobile; (2) the target design point is that it’s primarily expected to be used through JavaScript libraries layered on top, and not directly (so you have to ship more code over the wire); and (3) for more complex queries, more of the work has to be done in JavaScript instead of in the database engine (so performance will likely be poor on low-power CPUs). For these reasons, I expect a lot of mobile developers will stick with the SQL database, even if we also provide something else.

Sicking had admitted earlier that he was “… not experienced enough to articulate all of the reasons well enough.” So after this onslaught from Stachowiak, another Mozilla employee, Robert O’Callahan, rushes to his colleague’s aid:

Robert O’Callahan (Mozilla):

> Would you call multiple implementations that use the standard C library
> independent? Obviously there’s a judgment call to be made here.

Yes. Multiple implementations passing query strings (more or less) verbatim to SQLite for parsing and interpretation would not pass that judgement call… IMHO, but wouldn’t you agree?

…

I think the problem is rather coming up with a SQL definition that can be implemented by anything other than SQLite (or from scratch, of course). One weird thing about SQLite is that column types aren’t enforced. So either the spec requires something like SQLite’s “type affinity” (in which case it doesn’t fit well with most other SQL implementations, and precludes common performance optimizations), or it requires strict type checking (which perhaps you could implement in SQLite by adding CHECK constraints?). But the latter course is probably incompatible with deployed content, so contrary to Jonas I expect the spec would be implementable *only* on top of SQLite (or from scratch, of course), or perhaps some unnatural embedding into other engines where all values are text or variants. Experience with alternative implementations would be important.

All valid points. SQLite has its own quirks, and Web SQL is basically a thin layer over SQLite. Although Stachowiak does point out later that “WebKit has around 15k lines of code which implement asynchronicity, do checking and rewrites on the queries, export DOM APIs, manage transactions, expose result sets, etc.”

O’Callahan continues:

Robert O’Callahan (Mozilla):

Do you have easy access to knowledge about the sort of complex queries these mobile apps do? That would be very useful.

To Apple’s discredit, such data was never provided (as far as I know). Although again, the example with Gmail is pretty instructive here.

Robert O’Callahan (Mozilla):

We already ship SQLite and implementing Web Database using SQLite would definitely be the path of least resistance for us. We’re just concerned it might not be the right thing for the Web.

This, of course, is why Mozilla is awesome. Whatever the advantages of Web SQL may have been, you can’t say that Mozilla didn’t have the best interests of the web in mind when they killed it.

Stachowiak counters somewhat weakly, ceding to most of O’Callahan’s points, but taking up the utilitarian argument that Web SQL is better for developers:

Maciej Stachowiak (Apple):

It seems that a database layer with a good amount of high-level concepts (including some kind of query language) is likely to be easier to code against directly for many use cases. Thus, application programmers, particularly in environments where extra abstraction layers are particularly costly

[Furthermore,] some mobile web developers have existing investment in SQL in particular, and do not appear to have had problems with it as a model. It would be a shame to abandon them, as in many ways they have been better pioneers of offline Web apps than mainline desktop-focused Web developers.

It seems plausible to me that SQL is not the best solution for all storage use cases. But it seems like a pretty aggressive position to say that, as a result, it should be out of the Web platform (and not just augmented by other facilities). It seems like that would underserve other use cases

Smelling blood, O’Callahan moves in for the kill:

Robert O’Callahan (Mozilla):

> Thus, it did not seem there would be a practical benefit to
> specifying the SQL dialect. Thus, those present said they were satisfied to
> specify that SQLite v3 is the dialect.

What exactly does that mean? Is it a specific version of SQLite? Almost every SQLite release, even point releases, adds features.

The fact that SQLite bundles new features, bug fixes and performance improvements together into almost every release makes it especially difficult to build a consistent Web API on. Have you frozen your SQLite import to a particular version? Or do you limit the SQLite dialect by parsing and validating queries? Or do you allow the dialect to change regularly as you update your SQLite import?

I thought there was a consensus that pointing to a pile of C code isn’t a good way to set standards for the Web. That’s why we write specs, and require independent implementations so we’re not even accidentally relying on a specific pile of C code. This seems to be a departure from that.

Another great point from O’Callahan. I recall writing a Cordova app where I actually had to fetch the SQLite version from the sqlite_master table, in order to figure out what features of FTS were supported. It wasn’t pretty. (Although, to be fair, we web developers are no strangers to such hacks; just take a look at the jQuery source code some time.)

There’s a little more back-and-forth in the thread, and Charles McCathieNevile (of Opera) jumps in to mediate a bit. They discuss performance, and whether any guarantees can be made about the big-O performance of IndexedDB. Ultimately, Nikunj Mehta has the last word:

Nikunj Mehta (Oracle):

WebSimpleDB will always remain easy and good to use directly, even though it will also support those who want to use libraries on top. Whether people would still prefer to use libraries or not, will depend on their use case. Specific use cases would help to find a more objective solution to your issue.

So here we arrive at a major selling point of IndexedDB: it’s low-level – much, much lower than SQL – so it’s not designed to be used directly by developers. In fact, I tend of think of IndexedDB as a thin transactional layer over LevelDB (on Chrome, anyway), which itself is best described as a tool for building databases rather than a database itself.

Also, from working on PouchDB, where we support all three of Web SQL, IndexedDB, and LevelDB, I can confirm that the first is the easiest to work with, and the last is the hardest. IndexedDB is definitely a far cry from raw LevelDB, but it has nothing close to the flexibility provided by Web SQL’s diverse toolkit. (Disclaimer: the other authors may disagree.)

Broken promises of IndexedDB: did library authors fill the gap?

So let’s return to Mehta’s original point: IndexedDB was designed to be low-level enough that the void could be filled by JavaScript libraries. In the same way that nobody uses the native XMLHttpRequest or DOM APIs ever since jQuery came along, the assumption was that library authors would pick up the slack for IndexedDB’s cumbersome API.

And although I count myself as a member of that cohort (hint, hint: try PouchDB), with the benefit of hindsight I’d like to evaluate how well that plan has played out:

  • To date, there are plenty of libraries built on top of IndexedDB/WebSQL, although none has achieved jQuery-like dominance yet. (Maybe PouchDB will.)
  • On the other hand, native apps continue to trounce web apps on mobile.
  • Meanwhile, Google and (especially) Apple have dragged their feet on IndexedDB, slowing its adoption on mobile devices.
  • Although arguably, they had no choice, given the performance needs of mobile devices. One of the downsides of a low-level JavaScript API is that the rest has to be implemented in, well, JavaScript, which tends to be slower than native C code. Unsurprisingly, in our performance tests with PouchDB, we’ve found that the Web SQL backend is nearly always faster than the IndexedDB backend, sometimes by a decimal order of magnitude.

My own take on IndexedDB

I have a few personal theories as to why IndexedDB still hasn’t really taken off, and they mostly circle back to the same points made by Stachowiak and Fette five years ago.

First off, it’s hard to get developers to care about offline functionality for any platform other than mobile – you just don’t have the same problems with poor performance and spotty Internet connections. And on mobile devices, Web SQL is king (sorry Windows Phone), meaning that in practice mobile devs can just forget that IndexedDB exists.

Secondly, IndexedDB doesn’t offer much beyond what you can already get with LocalStorage, and its API is a lot tougher to understand. It’s asynchronous, which is already a challenge for less experienced developers. And if you don’t need to do any fancy pagination, then usually a plain old localStorage.get()/localStorage.put() along with some JSON parsing/serializing will serve you just fine.

Compare this with the Web SQL database, which is also asynchronous, but which provides a fluent query language and a bevy of additional features, one of the most underrated of which is full-text search. Just think about what a client-side search engine with support for tokenization and stemming (the Porter stemmer is baked right in!) could do for your app’s comboboxes, and then compare that with IndexedDB’s meager offerings.

Another theory is that Apple’s criticisms of IndexedDB became a self-fulfilling prophesy. Clearly they’ve put more effort into Web SQL than IndexedDB, the spec be damned, and by failing to implement IndexedDB in Safari and iOS, they’ve probably stunted its growth by years.

Finally, it’s worth acknowledging that IndexedDB is just a crummy API. If you look at the HTML5 Rocks example and don’t start having flashbacks to xmlHttpRequest.onreadystatechange = function() ..., then you haven’t been doing web dev for very long.

However, nobody wants to have to resort to a third-party wrapper unless it offers the kinds of benefits that jQuery gave us over the DOM – interoperability, robustness, and an API that’s so convenient and understandable that a generation of web developers probably believes the $ is just a part of the language.

PouchDB: the jQuery of databases

Of course, this is exactly the problem we’re trying to solve with PouchDB. (I know, here comes the shameless plug.) PouchDB isn’t just a great tool for syncing data between JavaScript environments and CouchDB; it’s also a general-purpose storage API designed to work well regardless of the browser it’s running in. Think of it as jQuery for databases.

Currently, PouchDB falls back to Web SQL on browsers that don’t support IndexedDB, and it can fall back to a remote CouchDB on browsers that don’t support either. In the future, we’ll also support LocalStorage and a simple in-memory store, which will basically extend our reach everywhere, and give developers a drop-in database that “just works.”

Of course, we also do a lot of magic under the hood to work around browser bugs, in both Web SQL and IndexedDB. And there are a lot of bugs – enough for a whole other blog post. So that’s another way that we’re like jQuery.

Mostly, though, we’re just trying to move HTML5 storage forward, and to fulfill the original vision of web developers having access to neat JavaScript libraries built on top of IndexedDB. If PouchDB (or some similar library) manages to achieve mainstream success, then Nikunj Mehta will be vindicated, regardless of how developers feel about IndexedDB itself.

Conclusion

Web SQL will probably never truly die. Google and Apple are invested enough that they can’t remove it from their browsers without breaking thousands of mobile apps and web sites (including their own).

And when I write web apps, I tend to care enough about mobile performance that, until IndexedDB catches up, I’ll probably continue giving a nod to Web SQL with code like this:

var pouch = new PouchDB('mydb', {adapter: 'websql'});
if (!pouch.adapter) { // fall back to IndexedDB
  pouch = new PouchDB('mydb');
}

Web SQL, I salute you. You’re no longer in our hearts, but you’ll remain in our pockets for years to come.

Disclaimer: I apologize if I’ve misquoted anyone or taken what they said out of context. Please feel free to rip me a new one in the comments, on Twitter, or on Hacker News.

Notes:

[1]: In fact, Web SQL had been shipping in Safari since 2007. Presumably they wanted to test it out in the wild before committing to a formal spec.

[2]: I editorialize a lot.

[3]: I’m skipping some details of the story; Web SQL certainly wasn’t killed in a day. The criticisms of Web SQL, especially the “SQLite is not a standard” part, can be traced back to an April 2009 blog post and email by Vladimir Vukicevic of Mozilla. The conclusion reached by both Stachowiak and Sicking at the end of that thread was, to quote Stachowiak, that “the best path forward is to spec a particular SQL dialect, even though that task may be boring and unpleasant and not as fun as inventing a new kind of database.” Nikunj Mehta disagreed, and then went on to invent a new kind of database.