Web SQL Database: In Memoriam

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.

What happened to PouchDroid?

Just in case anyone is wondering about PouchDroid, a few things changed since I started working on it three months ago:

  1. I found out CouchBase Lite exists. So you may want to try that instead of some crazy JavaScript thing.
  2. I started working on PouchDB itself.

I do plan on eventually updating PouchDroid, but for the time being there’s still plenty to be done in PouchDB. Long-term goals will be:

  1. API parity with PouchDB (at least, the important parts).
  2. Get a rigorous suite of tests in place.
  3. Once the tests are passing, port it to pure Java.

If you still want to use PouchDroid in a Cordova/PhoneGap app, I’d recommend using PouchDB itself and the SQLite plugin instead. PouchDroid uses a slightly modified version of the SQLite plugin, which probably won’t give you any noticeable performance improvements. As for the XHR overrides, I’ll probably take that out, so you should just set up CORS on your CouchDB.

If you want to use PouchDroid in a Java Android project, it’s still pretty nifty for small sync tasks (e.g. the PouchDroidMigrationTask, which can sync a SQLite database to CouchDB). But if you want something more full-featured and reliable, wait for 1.0.

PouchDroid v0.1.0 is out!

PouchDroid

PouchDroid

I’ve managed to nail down the bulk of the API for PouchDroid, and I’m releasing a super-tentative 0.1.0 version today, just in time for Christmas.

Obligatory caveat: Please do not use it in production yet. God no, not yet. Your user’s data is more precious to me than that.

Do go try it out, though! There’s now a well-thought-out README and a “Getting Started” tutorial. And a dorky logo I made in GIMP. (Aw yeah.)

So, it’s official: PouchDB’s empire has spread to Android. And now that JavaScript is a first-class citizen on iOS, could that platform be far behind?

Porting PouchDB to Android: initial work and thoughts

Update: CouchDroid has been renamed to PouchDroid, and I’ve released version 0.1.0. The code examples below are out of date. Please refer to the instructions and tutorials on the GitHub page.

I love PouchDB. It demonstrates the strength and flexibility of CouchDB, and since it supports both WebSQL and IndexedDB under the hood, it obviates the need to learn their separate APIs (or to worry about the inevitable browser inconsistencies). If you know CouchDB, you already know PouchDB.

And most importantly, it offers two-way sync in just a few lines of code. To me, this is magical:

var db = new PouchDB('mydb')
db.replicate.to('http://foo.com:5984/db', {continuous : true});
db.replicate.from('http://foo.com:5984/db', {continuous : true});

I wanted to bring this same magic to Android, so I started working on an Android adapter for PouchDB. I’m calling it CouchDroid, until I can think of a better name. The concept is either completely crazy or kinda clever, which is why I’m writing this post, in the hopes of getting early feedback.

The basic idea is this: instead of rewriting PouchDB in Java, I fire up an invisible WebView that runs PouchDB in JavaScript. I override window.openDatabase to redirect to the native Java SQLite APIs, so that all of the SQL queries run on a background thread (instead of tying up the UI thread, like they normally would). I also redirect XMLHttpRequest into Java, giving me control over the HTTP request threads, and helping avoid any messy server-side configuration of CORS/JSONP for web security.

Result: it works on a fresh CouchDB installation, no assembly required. And it’s actually pretty damned fast.

The code is still a little rough around the edges, but it can already do bidirectional sync, which is great. Callbacks look weird in Java, but static typing, generics, and content assist make the Pouch APIs a dream to work with. (My precious Ctrl+space works!)

Here’s an example of bidirectional sync between two Android devices and a CouchDB server using CouchDroid. First, we define what kinds of documents we want to sync by extending PouchDocument. This is Android, so let’s store some robots:

public class Robot extends PouchDocument {

  private String name;
  private String type;
  private String creator;
  private double awesomenessFactor;
  private int iq;
  private List<RobotFunction> functions;

  // constructors, getters, setters, toString...
}
public class RobotFunction {

  private String name;

  // constructors, getters, setters, toString...
}

I’m using Jackson for JSON serialization/deserialization, which means that your standard POJOs “just work.” The PouchDocument abstract class simply adds the required CouchDB fields _id and _rev.

In our Activity, we extend CouchDroidActivity (needed to set up the Java <-> JavaScript bridge), and we add a bunch of robots to a PouchDB<Robot>:

public class MainActivity extends CouchDroidActivity {

  private PouchDB<Robot> pouch;

  // onCreate()...

  @Override
  protected void onCouchDroidReady(CouchDroidRuntime runtime) {

    pouch = PouchDB.newPouchDB(Robot.class, runtime, "robots.db");

    List<Robot> robots = Arrays.asList(
      new Robot("C3P0", "Protocol droid", "George Lucas", 0.4, 200, 
        Arrays.asList(
          new RobotFunction("Human-cyborg relations"),
          new RobotFunction("Losing his limbs"))),
      new Robot("R2-D2", "Astromech droid", "George Lucas", 0.8, 135,
        Arrays.asList(
          new RobotFunction("Getting lost"),
          new RobotFunction("Having a secret jetpack"),
          new RobotFunction("Showing holographic messages")))    
    );

    pouch.bulkDocs(robots, new BulkCallback() {

        @Override
        public void onCallback(PouchError err, List<PouchInfo> info) {
          Log.i("Pouch", "loaded: " + info);
        }
    });
  }
}

Meanwhile, on another Android device, we load a completely different list of robots:

List<Robot> robots = Arrays.asList(
  new Robot("Mecha Godzilla", "Giant monster", "Toho", 0.4, 82, 
    Arrays.asList(
      new RobotFunction("Flying through space"),
      new RobotFunction("Kicking Godzilla's ass"))),
  new Robot("Andy", "Messenger robot", "Stephen King", 0.8, 135,
    Arrays.asList(
      new RobotFunction("Relaying messages"),
      new RobotFunction("Betraying the ka-tet"),
      new RobotFunction("Many other functions"))),
  new Robot("Bender", "Bending Unit", "Matt Groening", 0.999, 120,
    Arrays.asList(
      new RobotFunction("Gettin' drunk"),
      new RobotFunction("Burping fire"),
      new RobotFunction("Bending things"),
      new RobotFunction("Inviting you to bite his lustrous posterior")))
  );

And, of course, we set up bidirectional replication on both pouches:

String remoteCouch = "http://user:password@myhost:5984/robots";
Map<String, Object> options = Maps.quickMap("continuous", true);

pouch.replicateFrom(remoteCouch, options);
pouch.replicateTo(remoteCouch, options);

Wait a few seconds (or pass in a callback), and voilà! You can check the contents on CouchDB:
C3P0 on CouchDB

And then check the contents of each PouchDB on Android:

pouch.allDocs(true, new AllDocsCallback<Robot>() {

@Override
  public void onCallback(PouchError err, AllDocsInfo<Robot> info) {
    List<Robot> robots = info.getDocuments();
    Log.i("Pouch", "pouch contains " + robots);
  }
});

This prints:

pouch contains [Robot [name=Bender, type=Bending Unit, creator=Matt Groening, 
awesomenessFactor=0.999, iq=120, functions=[RobotFunction [name=Gettin' drunk], RobotFunction 
[name=Burping fire], RobotFunction [name=Bending things], RobotFunction [name=Inviting you to bite 
his lustrous posterior]]], Robot [name=C3P0, type=Protocol droid, creator=George Lucas, 
awesomenessFactor=0.4, iq=200, functions=[RobotFunction [name=Human-cyborg relations], RobotFunction 
[name=Losing his limbs]]], Robot [name=Mecha Godzilla, type=Giant monster, creator=Toho, 
awesomenessFactor=0.4, iq=82, functions=[RobotFunction [name=Flying through space], RobotFunction 
[name=Kicking Godzilla's ass]]], Robot [name=R2-D2, type=Astromech droid, creator=George Lucas, 
awesomenessFactor=0.8, iq=135, functions=[RobotFunction [name=Getting lost], RobotFunction 
[name=Having a secret jetpack], RobotFunction [name=Showing holographic messages]]], Robot 
[name=Andy, type=Messenger robot, creator=Stephen King, awesomenessFactor=0.8, iq=135, functions=
[RobotFunction [name=Relaying messages], RobotFunction [name=Betraying the ka-tet], RobotFunction 
[name=Many other functions]]]]

So within seconds, all five documents have been synced to two separate PouchDBs and one CouchDB. Not bad!

In addition to adapting the PouchDB API for Java, I also wrote a simple migration tool to mirror an existing SQLite database to a remote CouchDB. It could be useful, if you just want a read-only web site where users can view their Android data:

new CouchDroidMigrationTask.Builder(runtime, sqliteDatabase)
    .setUserId("fooUser")
    .setCouchdbUrl("http://user:password@foo.com:5984/db")
    .addSqliteTable("SomeTable", "uniqueId")
    .addSqliteTable("SomeOtherTable", "uniqueId")
    .setProgressListener(MainActivity.this)
    .build()
    .start();

This converts SQLite data like this:

sqlite&gt; .schema Monsters
CREATE TABLE Monsters (_id integer primary key autoincrement, 
  uniqueId text not null,
  nationalDexNumber integer not null,
  type1 text not null,
  type2 text,
  name text not null);
sqlite&gt; select * from Monsters limit 1
1|001|1|Grass|Poison|Bulbasaur

into CouchDB data like this:

{
   "_id": "fooUser~pokemon_11d1eaac.db~Monsters~001",
   "_rev": "1-bd52d48dba37ce490c38d455726296f0",
   "table": "Monsters",
   "user": "fooUser",
   "sqliteDB": "pokemon_11d1eaac.db",
   "appPackage": "com.nolanlawson.couchdroid.example1",
   "content": {
       "_id": 1,
       "uniqueId": "001",
       "nationalDexNumber": 1,
       "type1": "Grass",
       "type2": "Poison",
       "name": "Bulbasaur"
   }
}

Notice that the user is included as a field and as part of the _id, so you can easily set up per-user write privileges. For per-user read privileges, you still need to set up one database per user.

CouchDroid isn’t ready for a production release yet. But even in its rudimentary state, I think it’s pretty damn exciting. As Android developers, wouldn’t it be great if we didn’t have to write so many SQL queries, and we could just put and get our POJOs? And wouldn’t it be awesome if that data were periodically synced to the server, so we didn’t even have to think about intermittent availability or incremental sync or conflict resolution or any of that junk? And wouldn’t our lives be so much easier if the data was immediately available in a RESTful web service like CouchDB, so we didn’t even need to write any server code? The dream is big, but it’s worth pursuing.

For more details on the project, check it out on GitHub. The sample apps in the examples directory are a good place to start. Example #1 is the migration script above, Example #2 is some basic CRUD operations on the Pouch API, and Example #3 is the full bidirectional sync described above. More to come!

Introducing the SuperSaiyanScollView: super-fast sectioned lists for Android

Update: This blog post is now out-of-date. Please see the official documentation on GitHub.

Say you’re writing an Android app, and you have a ListView you’d like to divide into sections:

Sectioned list views.

Sectioned list views.

Normally, you’d need to write a custom ListAdapter, where you define the resources yourself and juggle two different types of View. Not fun.

public class MyBoringAdapter<Foo> extends ArrayAdapter<Foo> {

    // constructors...
    
    public int getViewTypeCount() {
      return 2;
    }
    
    public int getItemViewType(int pos) {
      return isHeader(pos) ? 0 : 1;
    }

    // more boilerplate...
    
    public View getView(int pos, View view, ViewGroup parent) {
        if (isHeader(pos)) {
            // sigh
        } else {
            // so tired of this crap
        }
    }
}

Now, what happens if you want to change the ordering? Or add new sections? Or add fast-scroll overlays?

It’s one of the most common UI patterns in Android, and yet (surprisingly) it’s still a pain to implement. Nothing in the stock Android SDK provides this functionality.

Enter the SuperSaiyanScrollView (dramatic gong sound). It’s a standalone library that you can easily import into any Android app, and it seamlessly adds fast-scrolling, sorting, and sectioning. Plus, it looks great on both Android 4.0 and pre-4.0 devices, on tablets and phones.

SuperSaiyanScrollView on HTC Magic (Eclair) and Galaxy Nexus (Jelly Bean)

SuperSaiyanScrollView on HTC Magic (Eclair) and Galaxy Nexus (Jelly Bean)

Why “Super Saiyan”? Because:

  1. I made it, so I get to name it.
  2. It’s super-fast, super-powerful, and it kicks (stock) Android’s ass.
Their power levels are definitely over 9000.

Their power levels are definitely over 9000.

Usage

The SuperSaiyanScrollView code attemps to be as unobtrusive as possible. To use it, you just need to wrap your existing ListView in a SuperSaiyanScrollView and your existing Adapter in a SectionedListAdapter.

In your layout XML file, add a SuperSaiyanScrollView around your ListView:

    <com.nolanlawson.supersaiyan.widget.SuperSaiyanScrollView
      android:id="@+id/scroll"
      android:layout_width="match_parent"
      android:layout_height="match_parent">

      <ListView
        android:id="@android:id/list"
        android:layout_width="match_parent"
        android:layout_height="match_parent"
        android:scrollbars="none"
        />

    </com.nolanlawson.supersaiyan.widget.SuperSaiyanScrollView>

(I like to set android:scrollbars="none", to remove the omnipresent gray scrollbars and stick with the “fast” blue scrollbars.)

Next, wrap your existing Adapter (e.g. an ArrayAdapter) in a SectionedListAdapter. The SectionedListAdapter uses a fluent “builder” pattern, similar to AlertDialog.Builder:

    SectionedListAdapter<MyCoolAdapter> adapter = 
        SectionedListAdapter.Builder.create(this, myCoolAdapter)
        .setSectionizer(new Sectionizer<MyCoolListItem>(){

          @Override
          public CharSequence toSection(MyCoolListItem item) {
            return item.toSection();
          }
        })
        .sortKeys()
        .sortValues()
        .build();

To include the SuperSaiyanScrollView in your Android app, simply follow these steps:

  1. Check out the code from GitHub:

    git clone https://github.com/nolanlawson/SuperSaiyanScrollView.git
    
  2. If you use Eclipse/ADT, go to Import -> Existing Android Code -> and choose the SuperSaiyanScrollView/library/ folder.
  3. If you use Proguard, add the following to your proguard.cfg:

    -keep class com.nolanlawson.supersaiyan.widget.** { *; }
    

For more information on importing library projects, read this section of the Android developer guide.

Examples

I’m going to walk through some short examples, which should demonstrate the simplicity and flexibility of the SuperSaiyanScrollView. The source code for these apps is included in the GitHub project, and you can download the APKs here:

Example #1: Countries

In this example, we have a list of countries, which we’d like to sort by continent. The finished app looks like this:

Example 1: Countries

We have a simple Country object:

public class Country {

  private String name;
  private String continent;

  /* getters and setters ... */
  
  @Override
  public String toString() {
    return name;
  }
}

We use a basic ArrayAdapter<Country> to display the countries:

ArrayAdapter<Country> adapter = new ArrayAdapter<Country>(
        this, 
        android.R.layout.simple_spinner_item, 
        countries);

Next, we wrap it in a SectionedListAdapter. In this case, we’d like to section countries by their continent, sort the continents by name, and sort countries by name:

    sectionedAdapter = 
        SectionedListAdapter.Builder.create(this, adapter)
        .setSectionizer(new Sectionizer<Country>(){

          @Override
          public CharSequence toSection(Country input) {
            return input.getContinent();
          }
        })
        .sortKeys()
        .sortValues(new Comparator<Country>() {
          
          public int compare(Country lhs, Country rhs) {
            return lhs.getName().compareTo(rhs.getName());
          }
        })
        .build();

A Sectionizer is a simple callback that provides a section name for the given list item. In your own code, this might be a HashMap lookup, a database query, or a simple getter (as in this example).

Notice also that the keys (i.e. the section titles) and the values (i.e. the list contents) can be sorted independently, or not sorted at all. By default, they’re sorted according to the input order.

Now, let’s try to change the sections dynamically! In the action bar, the user can switch between alphabetic sorting and continent sorting:

alphabetic sorting vs. continent sorting

To do so, we first get a reference to the SuperSaiyanScrollView:

SuperSaiyanScrollView superSaiyanScrollView = 
    (SuperSaiyanScrollView) findViewById(R.id.scroll);

Then, we call the following function whenever the user chooses alphabetic sorting:

  private void sortAz() {

    // use the built-in A-Z sectionizer
    sectionedAdapter.setSectionizer(
        Sectionizers.UsingFirstLetterOfToString);

    // refresh the adapter and scroll view
    sectionedAdapter.notifyDataSetChanged();
    superSaiyanScrollView.refresh();
  }

Notice that the SectionedListAdapter and SuperSaiyanScrollView need to be informed whenever their content changes.

Next, when the user switches back to continent sorting, we call this function:

  private void sortByContinent() {

    // use the by-continent sectionizer
    sectionedAdapter.setSectionizer(new Sectionizer<Country>(){

          @Override
          public CharSequence toSection(Country input) {
            return input.getContinent();
          }
        });

    // refresh the adapter and scroll view
    sectionedAdapter.notifyDataSetChanged();
    superSaiyanScrollView.refresh();
  }

Notice that you never need to call adapter.sort() or Collections.sort() yourself. The SectionedListAdapter handles everything. And it does so without ever modifying the underlying adapter, which means that view generation is lightning-fast.

Example #2: Pokémon

This example shows off some of the advanced functionality of the SuperSaiyanScrollView. We have three different sortings, the size of the overlay box changes to fit the text size, and we can dynamically hide both the overlays and the section titles.

alphabetic vs by-region sorting

First off, the size of the overlay can be configured in XML. In this example, we start off with a single-letter alphabetic sorting, so we want the overlays to be a bit smaller than normal.

Add a namespace to the root XML tag in your layout XML:

<RelativeLayout
  ...
  xmlns:myapp="http://schemas.android.com/apk/res/com.example.example1"
  ...
  >
</RelativeLayout>

Next, use values prefixed with ssjn_ to define the size of the overlay:

<com.nolanlawson.supersaiyan.widget.SuperSaiyanScrollView
  ...
  myapp:ssjn_overlaySizeScheme="normal">

  <ListView
    ...
    />

</com.nolanlawson.supersaiyan.widget.SuperSaiyanScrollView>

I include the built-in schemes small (for one letter), normal (for most use cases), and large and xlarge (for longer section titles). Section titles of up to two lines (separated by \n) are supported.

Small, normal, large, and xlarge overlays in my AMG Geneva app.

Small, normal, large, and xlarge overlays in my AMG Geneva app.

If you want, you can also manually specify the font size, width, height, and text color yourself:

<com.nolanlawson.supersaiyan.widget.SuperSaiyanScrollView
  ...
  myapp:ssjn_overlayWidth="400dp"
  myapp:ssjn_overlayHeight="200dp"
  myapp:ssjn_overlayTextSize="12sp"
  myapp:ssjn_overlayTextColor="@android:color/black" >

  <ListView
    ...
    />
</com.nolanlawson.supersaiyan.widget.SuperSaiyanScrollView>

Now, in the Java source, we have a PocketMonster object:

public class PocketMonster {

  private String uniqueId;
  private int nationalDexNumber;
  private String type1;
  private String type2;
  private String name;
  
  /* getters and setters */

  @Override
  public String toString() {
    return name;
  }
}

We have a simple PocketMonsterAdapter to define how the monsters are displayed in the list:

public class PocketMonsterAdapter 
    extends ArrayAdapter<PocketMonster> {
  
  // Constructors...
  
  @Override
  public View getView(int pos, View view, 
      ViewGroup parent) {
    
    PocketMonster monster = 
        (PocketMonster) getItem(pos);
    
    /* Create and style the view... */

    return view;
  }
}

We wrap this adapter in a SectionedListAdapter that, by default, sections and sorts everything alphabetically:

    adapter = SectionedListAdapter.Builder.create(this, subAdapter)
        .setSectionizer(Sectionizers.UsingFirstLetterOfToString)
        .sortKeys()
        .sortValues(new Comparator<PocketMonster>(){

          @Override
          public int compare(PocketMonster lhs, 
                PocketMonster rhs) {
            return lhs.getName().compareToIgnoreCase(
                rhs.getName());
          }})
        .build();

Notice that we call both sortKeys() and sortValues(), because we want both the section titles and the Pokémon to be ordered alphabetically. Since PocketMonster does not implement Comparable, we defined a custom Comparator.

Now let’s say we want to organize the Pokémon by region:

Pokémon sorted by region.

Some quick background: Pokémon are ordered by their “national ID,” an integer value that starts at 1 (Bulbasaur) and goes up to 718 (Zygarde). Every time Nintendo releases a new generation of Pokémon games, they add about 100 new monsters, set the game in a new “region,” and sell about a bazillion new Pokémon toys.

So basically, we can determine the regions from the Pokémon’s ID. We’ll define a new
Sectionizer, which is called when the user selects “sort by region”:

  private void sortByRegion() {
    adapter.setSectionizer(new Sectionizer<PocketMonster>() {

      @Override
      public CharSequence toSection(PocketMonster input) {
        int id = input.getNationalDexNumber();
        
        // Kanto region will appear first, followed 
        // by Johto, Hoenn, Sinnoh, Unova, and Kalos
        if (id <= 151) {
          return "Kanto (Generation 1)";
        } else if (id <= 251) {
          return "Johto (Generation 2)";
        } else if (id <= 386) {
          return "Hoenn (Generation 3)";
        } else if (id <= 493) {
          return "Sinnoh (Generation 4)";
        } else if (id <= 649) {
          return "Unova (Generation 5)";
        } else {
          return "Kalos (Generation 6)";
        }
      }
    });

    // uses the nat'l pokedex order, since 
    // that's the original input order
    adapter.setKeySorting(Sorting.InputOrder);
    adapter.setValueSorting(Sorting.InputOrder);
    scrollView.setOverlaySizeScheme(
        OverlaySizeScheme.Large);

    // refresh the adapter and scroll view
    adapter.notifyDataSetChanged();
    scrollView.refresh();
  }

Notice that we’ve changed the key and value sorting to Sorting.InputOrder, because now we want to order Pokémon by their national IDs, which was the order the data was read in. (A custom Comparator would have also done the trick.) Additionally, we’ve increased the size of the overlay to accommodate the longer section text.

Now, let’s say we want to organize Pokémon by type. Each Pokémon has at least one elemental type (such as “fire” or “water”), but some have two. Ideally we would like to list Pokémon in multiple categories, so they could appear multiple times in the list.

To do so, we will define a MultipleSectionizer instead of a regular Sectionizer:

  private void sortByType() {
    adapter.setMultipleSectionizer(
        new MultipleSectionizer<PocketMonster>() {

      @Override
      public Collection<? extends CharSequence> toSections(
          PocketMonster monster) {
        String type1 = monster.getType1();
        String type2 = monster.getType2();

        if (!TextUtils.isEmpty(type2)) { // two types
          return Arrays.asList(type1, type2);
        } else { // one type
          return Collections.singleton(type1);
        }
      }
    });
    adapter.setKeySorting(Sorting.Natural);
    adapter.setValueSorting(Sorting.InputOrder);
    scrollView.setOverlaySizeScheme(OverlaySizeScheme.Normal);

    // refresh the adapter and scroll view
    adapter.notifyDataSetChanged();
    scrollView.refresh();
  }

Notice that the key sorting has again changed, this time to Sorting.Natural, which simply sorts alphabetically. Value sorting has changed to Sorting.InputOrder, because we’ve decided to sort Pokémon by their national IDs.

This works as expected:

Pokémon sorted by type.

Notice that Charizard appears in both in the “Fire” and “Flying” sections, since he has two types.

This example app also shows how you can disable the section titles or section overlays, just in case you don’t like them. These values can also be set during the Builder chain, using hideSectionTitles() and hideSectionOverlays().

comparison of hiding overlays and hiding section titles

You can read the Java documentation for more information about customizing the SuperSaiyanScrollView and the SectionedListAdapter.

Summary

The SuperSaiyanScrollView is a cool new library, and you should be using it. File bugs on me ‘n’ stuff, if there are any missing features you’d like to have.

S3 bucket listing that’s easier on the eyes

Update: I learned that Shrub exists. It’s much nicer than what I hacked up in an hour!

This is just a quick one.

I host a lot of public files on an Amazon S3 bucket. It’s my main mechanism for publishing releases of my open-source software.

So I was amazed to discover recently that S3 doesn’t have an easy way to just… show all the files. Like, not even a basic directory listing, which you could easily get with an Apache server. Just nothing.

Directory listing in Apache

This is all I wanted.

Well, that’s not entirely true. There is this ancient sample code from Amazon, made in 2008, that I found frozen in ice. But it looks like crap.

Amazon's standard S3 directory listing

“$folder$”? Seriously?

So I made a better one, using Bootstrap for styling. Below is a screenshot, and here it is in action.

My pretty Bootstrap S3 index.html

Much better.

To use it, just download the index.html file from the GitHub page and drop it into the root of your public S3 bucket. That’s it!

As an aside, isn’t it awesome how easy web development has become, thanks to modern tools like Bootstrap, JQuery, and Handlebars? That file from Amazon used 174 lines of Javascript, whereas mine is only 99. Of course I have three external dependencies, but I use CDNs, so you probably won’t notice a difference in performance. How cool is that?

CouchDB doesn’t want to be your database. It wants to be your web site.

I’d like to talk to you today about Couch apps. No, not CouchApps. No, not necessarily CouchApps either. The phrase has been bandied around a lot, so it’s worth explaining what I’m mean: I’m talking about webapps that exclusively use CouchDB for their backend, whether or not they’re actually hosted within CouchDB and regardless of how they’re built.

Yes, this is a thing people are actually trying to do, and no, it’s not crazy. The purpose of this article is to explain why.

First off, some background: CouchDB is a NoSQL database (or key-value store, as the cool kids say) written in Erlang. It is probably the origin of this joke. Nobody who uses CouchDB cares that it is written in Erlang, though, because the big selling point is that you can interact with it using Javascript, JSON, and plain ol’ HTTP. It is “a database for the web,” the first of its kind.

CouchDB: it’s a database, right?

When I first started using CouchDB, I tried to treat it like any other database. I looked for connectors based on the language I was using: Ektorp for Java, AnyEvent::CouchDB for Perl, Nano for Node. And I used the web interface (bewilderingly called “Futon”) as I would a query browser – neat for debugging, but not much else. The fact that it ran in a web browser just kinda seemed like a gimmick.

Recently, though, when I was working on a Node app that didn’t go anywhere but was a fun diversion, I came across this quote by Couch apostle J. Chris Anderson:

Because CouchDB is a web server, you can serve applications directly [to] the browser without any middle tier. When I’m feeling punchy, I like to call the traditional application server stack “extra code to make CouchDB uglier and slower.”

Suddenly, I realized what CouchDB was all about.

No wait, CouchDB is a miracle

See, here I was, using client-side Javascript to talk to Express to talk to Node to talk to Nano to talk to Couch, and at each step I was converting parameter names from underscores to camel case (or whatever my petty hangups are), all the while introducing bugs as I tried to make each layer fit nicely with the next one. And I had a working web server right in front of me! CouchDB! Why not just call it directly, you fool?! (I shout at myself in hindsight.)

I think the reason a lot of developers, like myself, might have missed this epiphany is that we’re used to treating databases as, well, databases. Whether it’s MongoDB or MySQL or Oracle, you gotta have your JDBC connector for Java and perhaps an ORM layer or maybe you just give up on Hibernate and write all the database objects yourself, so half of your code is getters and setters, but that’s OK, because that’s how we abstract the database.

You see, you can’t just have your peanut butter and jelly sandwich! You need an interface between the bread and the peanut butter, and an abstraction layer between the peanut butter and the jelly, and don’t even get me started on the jelly and the bread! What, you want your bread to get soggy?

As a programmer, I’m so used to treating databases as this other, alien thing that needs to be handled with latex gloves, separately from my application code, that reaching for the nearest library has become a reflex.

But you don’t need that with CouchDB. Because… it’s just HTTP. Any extra layers just give you another API to learn.

CouchDB is the web done right

And in fact, CouchDB is better than HTTP, because CouchDB actually fulfills the promise of what RESTful services were supposed to be, instead of the kludges we’ve come to expect. Look! DELETE actually deletes things! POST isn’t just what you use when you need to send more data than a GET allows! And HEAD and PUT are actually useful, instead of just being trivia to impress your friends at dinner parties — “Oh, did you know that there are actually more HTTP commands than just GET and POST?” “Oh, how fascinating!”

You see, once you set aside your preconceived notion of what a database is supposed to be, you can actually get rid of all your fancy connectors and just use a standard HTTP library. (I like Requests for Python.) You can even use the network debugger in a browser window to see how CouchDB does everything. It’s all just AJAX!

And then, if you make it this far down the rabbit hole, you might notice that CouchDB actually has a user authentication database, with password hashing. You might also notice that it’s even got roles and privileges and administrator controls. And that’s when you realize, with fascinated horror, the most insidious thing about CouchDB:

CouchDB doesn’t want to be your database; it wants to be your web site.

And finally, this is where we come back to the subject of Couch apps. A Couch app is just a pure HTML/CSS/Javascript application, with only CouchDB as its backend, and this is the intended use case for CouchDB.

Now, think about what this proposition means to you as a developer. The web is moving more and more towards rich, client-side applications — we’ve had jQuery for years, and now we even have MVC with platforms like Ember, Knockout, and AngularJS. If CouchDB does user authentication (it’s got a “signup” button right on the home page, for crying out loud), paging, indexing, full-text search, geo data, and it all speaks HTTP, well… what does that actually leave us to do on the server?

Take a long look in the mirror, and really ask yourself! And yes, for those of you who do machine learning and scientific computing and business intelligence, I can already see you raising your hands, but for the rest of us who get paid to write Twitter clones, the answer is: not much. Your average CRUD app can magically transform into a PGPD app (PUT, GET, POST, DELETE), you can throw it up on CouchDB with some nice HTML and CSS to style it, and be at your local brewpub by 3. Or maybe you could just send the default Futon interface to the client and tell them you wrote it.

Futon interface in CouchDB

“See, it’s a collaborative document editor, and the dude on the Couch is a lazy writer…”

Now, this is the dream. And CouchDB, as it stands in 2013, actually gets us pretty damn far toward that dream. The app I’m releasing this week, Ultimate Crossword, is a testament to that. It’s a pure Couch app that only cheats by using Solr for full-text search (because I was too lazy to learn the Lucene plugin). It’s got user accounts, data aggregation, and even continuous syncing between the client and server thanks to the wonderful PouchDB.

Building this site gave me a lot of insight into what’s possible with a Couch app. However, I also got a reality check about where CouchDB still falls short of achieving the dream. I’ve got four big complaints:

1) No per-document read privileges

This is a big one. CouchDB has three basic security modes:

  • Everyone can do everything.
  • Some people can write (some documents), everyone can read (all documents).
  • Some people can write (some documents), some people can read (all documents).

If you want to give users exclusive read access to certain documents, you have to create a separate database for each user. And unfortunately, CouchDB has no feature to do this automatically. So you need a process on the server with administrative privileges to do it, breaking the pure “Couch app” ideal. Then, if you want to aggregate the data, you actually need another process to sync to a separate database, and… well, it just gets messy. I’m strongly rooting for this feature to show up in a future CouchDB release.

2) No password recovery.

This is a feature that users have come to expect from modern web sites. And despite all its security flaws (in that it makes your email a single point of failure), it seems here to stay.

Now, CouchDB can store arbitrary data in the users table (like email addresses), and you can even do custom validation. But for the whole “give us your email, and we’ll send you a new password” thing, you’re on your own.

On the bright side, the passwords are all salted and PBKDF2-hashed, so no attacker has much to gain from cracking your Couch.

3) No database migration.

This is a big one for me, although I wonder if I’m the only one. Since my early days of Java development, I’ve appreciated having Liquibase so I could track my database schema changes in version control.

In theory, CouchDB should be ideal for something like this, since it versions everything, and even its views (aka indexes) are their own documents. But I haven’t found a good recipe for managing this yet. For the time being, I just keep a series of Python scripts that create the databases.

4) Views are not indexes, and documents are not tables.

One of the nice things about SQL databases as a development paradigm is the flexibility of the SQL language itself. Decided you wanna sort by dogsLastName instead of favoritePokemon? No problem, we’ll just add an index. Too much data getting sent across the wire? No big deal, we’ll just SELECT the fields we need, instead of SELECT(*).

In CouchDB, you can’t do a WHERE and you can’t just SELECT the fields you want. Any query that’s not simply fetching a whole document by its ID requires a view, and those are costly to create. I’ve worked with Couch databases containing millions of documents, and rebuilding a view would often take days. I’d have a coworker ask me to add a new filter criterion for a view, and on Friday I’d say, “Okay, it’ll be ready by Monday.” For the Ultimate Crossword app, I stupidly decided to use CouchDB to crunch the data itself, and I ended up needing five separate Couch servers running on solid state drives in order to process it in in a reasonable amount of time. (CouchDB is best thought of as a single-process application. It’s append-only, so it uses one process per database file.)

Also, the fact that you can’t SELECT arbitrary fields means you need to start thinking about how much data you want to send over the wire with each document, and how to threshold it. I found myself structuring my database into a summary/detail format early on, and modeling the documents very tightly to the user interface, in ways that just made me feel icky.

Database purists, of course, would say that this is where the latex gloves are supposed to come out. But I think that if CouchDB simply had a better system for managing migrations (see #3) and/or faster view creation, this would be a non-issue. I’d also love it if the output of a view could be put into its own database, so I could have endlessly kaleidoscoping views of my data. One more for the wishlist!

Conclusion

Despite these drawbacks, I still think CouchDB has a lot of potential to revolutionize the way people write webapps. I certainly still plan to use it for quick hacking (hell, the crossword app only took me ten days to write), and Couch’s append-only design means I’ll never have to worry about my data getting corrupted. (It’s been proudly touted as “the Honda Accord of databases.”)

But for all its developers’ humility, CouchDB is a really exciting technology. When you step back and look at it, it’s a daring, crazy proposition, a bold statement about how awesome web development would be if we could just let it be the web. It’s a raving streetside lunatic, grabbing random people by the shoulders and screaming at them with frantic urgency: “We don’t need the server anymore! We only need the database! The database is the server!”

In short, CouchDB is an expression of an ideal, a fantastical tale of science fiction told by wide-eyed dreamers. And if there’s one truth about wide-eyed dreamers, it’s this: with hindsight, their predictions either seem delusional, or inevitable.

(Psssst! Go check out my Ultimate Crossword app! It’ll make you feel bad about your user authentication!)

Update: I decided to remove the CouchDB user authentication from the Ultimate Crossword app (I realized it was irresponsible to let people collaboratively “solve” the puzzle), but it’s still a pure Couch app!

Follow

Get every new post delivered to your Inbox.

Join 566 other followers