Read the Tea Leaves Software and other dark arts, by Nolan Lawson

TL;DR: I rewrote the Cordova SQLite Plugin; it’s faster and better-tested. Try it out!

For better or worse, WebSQL is still a force to be reckoned with in web development. Although the spec was deprecated over 5 years ago, it still lives on, mostly as a fallback from its more standards-friendly successor, IndexedDB. (See LocalForage, PouchDB, IndexedDBShim, and YDN-DB for popular examples of this.)

Thankfully, this WebSQL-as-polyfill practice is becoming less and less necessary, as pre-Kitkat Android slowly fades into memory, and Safari fixes its lingering IndexedDB issues. That said, there is still good reason to doubt that web developers will be able to safely hop onto the IndexedDB bandwagon anytime soon, at least without fallbacks.

For one, it’s unclear when the fixes from WebKit will be released in Safari proper (and how soon we can stop worrying about old versions of Safari). Secondly, although Safari’s “modern IndexedDB” rewrite has resolved many of its gnarliest bugs, their implementation is still around 50x slower (!) than WebSQL, even in the WebKit nightlies. (It depends on the use case, but see my database comparison tool for a demonstration of batch insert performance).

Even more saddening for the web platform as a whole is that, despite being blessed with no less than three storage engines (LocalStorage, WebSQL, and IndexedDB), many developers are still electing to go native for their storage needs. The Cordova SQLite plugin (which mimics the WebSQL API via native access to SQLite) remains a popular choice for hybrid developers, and may even be influencing the decision to go hybrid.

As a proponent of web standards, I’ve always felt a bit uneasy about the SQLite Plugin. However, after struggling with the alternatives, I must admit that it does have some nice properties:

1. It works in iOS’s WKWebView, the successor to UIWebView, which boasts better performance but unfortunately dropped WebSQL support.
2. It allows unlimited storage in iOS: no hard cutoff after 50MB.
3. It allows durable storage – i.e. the browser cannot start arbitrarily deleting data when disk space runs low. This is something neither IndexedDB or WebSQL can provide until the Durable Storage API has shipped (and no browser currently has). If you think this isn’t a real problem in practice, watch this talk.
4. It offers the ability to bundle prepopulated database files within the app, avoiding the overhead of initializing a large database at startup.

So while IndexedDB is definitely the future of storage on the web (how many years have we been saying that?), the SQLite Plugin still has its place.

I’ve actually contributed to the project before, but over the past couple years I’ve found myself unable to keep up with the changing project direction, and from my vantage point on PouchDB, I’ve watched several regressions, breaking changes, and API complexities creep into the project. I wanted to contribute, but I think my goals for the SQLite Plugin differed too much from that of the current maintainer.

So I did what’s beautiful in open source: I forked it! Actually I mostly rewrote it, while taking some snippets here and there, but in spirit it’s a fork. The new library, which I’ve creatively christened SQLite Plugin 2, diverges from its forebear in the following ways:

1. It (mostly) just implements the WebSQL spec – no extra API complexity where possible. Under the hood, node-websql is used to maximize code reuse.
2. It’s heavily tested – I ported over 600 tests from node-websql and PouchDB, which I’ve verified pass on Android 4.0+ and iOS 8+.
3. In order to keep the footprint and API surface small, it only uses the built-in SQLite APIs on Android and iOS, rather than bundling SQLite itself with the plugin.

In all other ways, it works almost exactly the same as the original SQLite Plugin, on both iOS and Android. (For Windows Phone, cordova-plugin-websql already has us covered.)

Performance test

I didn’t set out to write the fastest possible WebSQL shim, but I figured folks would be interested in how my remake stacks up against the original. So I put together a small benchmark.

Again, these tests were borrowed from PouchDB: one test mostly involves reads, and the other mostly involves writes. As it turns out, PouchDB “writes” are not purely INSERTs, and PouchDB reads are not simple SELECTs (due to the CouchDB-style revision model), but hopefully this test should serve as a pretty good representation of what an actual app would do.

Each test was run 5 times with 1000 iterations each, with the median of the 5 runs taken as the final result. The test devices were a 6th generation iPod Touch running iOS 9.3.1 and a Nexus 5X running Android 6.0.1. For completeness, I also tested against pure WebSQL.

Here are the results:

And a summary comparing SQLite Plugin 2 to the competition:

(Full results are available in this spreadsheet.)

As it turns out, SQLite Plugin 2 actually outperforms the original SQLite Plugin by quite a bit, which I credit to a smaller data size when communicating with the native layer, as well as some minor optimizations to the way SQLite itself is accessed (e.g. avoiding calculating the affected rows for a straight SELECT query).

Of course, one should also note that pure WebSQL is much faster than either plugin. This doesn’t surprise me; any Cordova plugin will always be at a disadvantage to straight WebSQL, due to the overhead of serializing the messages that are sent between the WebView and the native layer. (N.B.: just because something is “native” doesn’t necessarily mean it’s faster!)

Furthermore, if you’re storing large binary data (images, audio files, etc.), the performance will probably get even worse relative to regular WebSQL, since that large data needs to be encoded as a string (base64 or otherwise) when sent to the native side. In those cases, the most efficient choice is undoubtedly IndexedDB on Android and WebSQL on iOS, since Safari IndexedDB lacks Blob support and is already quite slow as-is. (Both PouchDB and LocalForage will intelligently store Blobs in this manner, preferring built-in Blob support where available.)

So please, heed some advice from the author himself: avoid this plugin whenever possible. Unless you absolutely need WKWebView support, unlimited storage, durable storage, or prepopulated databases, just use regular IndexedDB or WebSQL instead. Or at the very least, try to architect your app so that you can easily swap in a more standards-based approach in the future (i.e., IndexedDB!). LocalForage, PouchDB, and YDN-DB are great libraries for this, since they largely abstract away the underlying storage engine.

Conclusion

Hopefully the SQLite Plugin 2 will serve as a useful tool for hybrid developers, and can help ease the transition to the rosy future where IndexedDB and Durable Storage are well-supported in every browser. Until then, please try it out, file bugs, and let me know what you think!