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:
- It works in iOS’s WKWebView, the successor to UIWebView, which boasts better performance but unfortunately dropped WebSQL support.
- It allows unlimited storage in iOS: no hard cutoff after 50MB.
- 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.
- 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:
- It (mostly) just implements the WebSQL spec – no extra API complexity where possible. Under the hood, node-websql is used to maximize code reuse.
- 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+.
- 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:
| SQLite Plugin 2 | Original SQLite Plugin | WebSQL | |
|---|---|---|---|
| Writes (iOS) | 29321ms | 30374ms | 21764ms |
| Reads (iOS) | 8004ms | 9588ms | 3053ms |
| Writes (Android) | 29043ms | 33173ms | 23806ms |
| Reads (Android) | 8172ms | 11540ms | 7277ms |
And a summary comparing SQLite Plugin 2 to the competition:
| vs Original SQLite Plugin | vs WebSQL | |
|---|---|---|
| Writes (iOS) | 3.59% faster | 25.77% slower |
| Reads (iOS) | 19.79% faster | 61.86% slower |
| Writes (Android) | 14.22% faster | 22% slower |
| Reads (Android) | 29.19% faster | 12.3% slower |
(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!
Read the Tea Leaves 
Posted by Parashuram on April 11, 2016 at 8:25 AM
Amazing work !!
When working with Cordova developers, one of the things they do ask for, a lot, is secure storage. Does this plugin work with SQLCiper ?
Posted by Nolan Lawson on April 12, 2016 at 5:32 AM
Nope, although I wouldn’t be averse to adding support, assuming it doesn’t complicate the API too much. (Best way to keep from complicating it IMO is to add options to the
sqlitePluginobject rather than theopenDatabasefunction.)Another option is to do the crypto in JavaScript, although it is slightly less performant that way. (E.g. crypto-pouch has this strategy.) I also like this approach because it’s more future-safe in terms of web standards.
Posted by Heshyo on April 14, 2016 at 1:40 AM
Nice! I see you keep yourself busy! I’m using PouchDB as well as the original SQLite to avoid size limits. Does that mean I can use your new SQLite plugin as a drop in replacement of the original?
Posted by Nolan Lawson on April 14, 2016 at 11:37 AM
Yep! Just uninstall the original SQLite Plugin, install the SQLite Plugin 2, and it will work out-of-the-box with PouchDB. :)
Posted by Brad on April 24, 2016 at 10:34 AM
I’m tired of the 50MB limitation on iOS, but love using PouchDB! Is there any way to use your new SQLite Plugin 2 in the browser, without Ionic or Cordova?
Posted by rizthon on April 24, 2016 at 5:42 PM
Brad, the SQLite Plugin 2 is a Cordova plugin and thus it only works within an app on a device, so you can’t use it in the browser.
Posted by Nolan Lawson on April 28, 2016 at 12:14 PM
In fact, IndexedDB has no data limit on iOS as far as I can tell. So you could also use something like FruitDOWN to get around data limits (although FruitDOWN is definitely a quick-and-dirty hack…).
Posted by Brad on April 28, 2016 at 1:44 PM
Great thought. I was having no luck with FruitDOWN on an earlier version of PouchDB, but now with 5.3.1, I will cycle back and give it another go. It was probably something I screwed up implementing.
Posted by Alan Hodgkinson on June 21, 2016 at 7:21 AM
Nice work! I have a very basic question about the intended use of your new SQLite plugin.
The post says: “(mostly) just implements the WebSQL spec …[snip]… In all other ways, it works almost exactly the same as the original SQLite Plugin”… Huh? “mostly” ?!?
The start of the Readme of the Cordova SQLite Plugin 2 GitHub page states: “A rewrite/fork of the Cordova SQLite Plugin. In most cases, it should be a drop-in replacement”…. Huh? “most cases” ?!?
However, further down the page you list some Non-Goals, including that the plugin is “not designed to replicate 100% of the functionality of the old SQLite Plugin – deleting databases, closing databases, specifying a particular location, etc.” …Huh? “not designed for” …so it’s not a drop-in replacement?
So what, exactly, is your new plugin really intended for? I’m guessing it’s use is limited to supporting the use of PouchDB and that it is NOT suitable for use in any existing apps that use the normal SQLite plugin accessing a relational database.
Could you confirm that? Or perhaps explain exactly what the limitations are? Saying it’s only for PouchDB use is fine, if that’s really the primary goal.
My, perhaps unusual use case, was to implement a DB migration in my SQLite-based app, to transfer data from SQLite tables into a new PouchDB DB. Then I planned sync the data with a CouchDB on a server, and rewrite the database layer to use PouchDB instead of SQLite, and sync the data back again. Unfortunately, it looks like the first step may not be possible with your plugin.
(My work around will be to use the normal SQLite plugin for reading the old data for the migration, and temporarily use the IndexedDB backend for PouchDB. Then I can transition to your plugin once I’ve gotten rid of SQLIte tables. That should work, right?).
To help others (assuming I’ve understood the plugin’s intended use correctly), perhaps you could warn people of the plugin’s limitations, both at the start of this post and also at the start of the Cordova SQLite Plugin 2 GitHub Readme file. In the Readme, please also add “Support for SQLite for PouchDB” as the primary goal.
That said, I’d really like to say thanks for all your PouchDB work and your writing about it. It’s people like you who make it fun to use open source software. Just so you know, PouchDB is likely to become a mission critical part of our product offerings. Pat yourself on the back for that!
Keep up the good work!
Posted by Nolan Lawson on July 12, 2016 at 9:13 AM
Hi there, thanks for your questions. :) Here are some answers:
The cases are enumerated in the README. Basically I don’t support anything in the SQLite Plugin that is not also part of the WebSQL specification, e.g. “location”, “androidDatabaseLocation”, deleting databases, closing databases, etc.
It’s intended to be a polyfill for the WebSQL API. Since PouchDB uses the WebSQL API, yes, it is a good fit for PouchDB.
Keep in mind that I have a long history of battling stability and reliability issues with the original SQLite Plugin (I was a contributor at one point), hence my decision to fork.
Any PouchDB database object can replicate to any other PouchDB database object. So yes. :)
It supports the WebSQL database API first and foremost. Compliance with PouchDB is a side effect. Note that there’s also a LocalForage plugin.
BTW, as for comments you made on the PouchDB Slack channel, I think you should be aware that SQLite Plugin 2 is really not designed for Android, and you should only use it on Android as a last resort. Binary data in particular (attachments) are very inefficient in the SQLite Plugin due to the fact that everything has to be serialized when communicating with Cordova Plugins, and because that’s what you have to do to fit the WebSQL spec.
Hope that clears things up!
Posted by Evan Dillon on August 4, 2016 at 6:59 AM
Hi Nolan,
I’m migrating from cordova-sqlite-storage 0.8.4-dev to your sqlite-2. It appears my databases are in Documents on iOS (files that begin with ‘pouch‘ are here) but when I copy them to Library/NoCloud (cordova.file.dataDirectory) I don’t see the data that was in the datases before the copy. Is there another file or files that I need to copy? Crosswalk is also being used in this project. On Android, copying the files from the databases directory to the files directory has the expected results (I am copying from databases to cordova.file.dataDirectory)
Many thanks, Evan.
Posted by Jasho on September 17, 2016 at 8:03 PM
Thank you SO much, Nolan! I’d been working on this all week… Tried three previous SQLite plugins for my cordova app running inside an Android webview, For each problem solved another always popped up. So switched to your SQLite2 plugin, and it’s finally actually working. My work here is done. Can hardly believe it… May thanks!
Posted by Flavio Passa on October 17, 2016 at 5:39 PM
For a Sqlite pre populated database for Ionic2 Framework working 100% you should have a look at this: https://codecanyon.net/item/quizionic2-ionic2-app-template-w-sqlite-admob-inapppurchase/18182426
Posted by Mutia on November 1, 2016 at 12:49 AM
Greetings,
I have installed the Sqlite plugin 2 and I am able to connect to the database. However, the executeSQL() function seems to fail quietly – Neither its error functions nor a generic try,catch construct seems to capture the event.
At this point, I am unable to make any headway into populating and manupulating tables.
kindly assist where you can. Thank you
Posted by James on April 13, 2017 at 7:06 AM
This is real a very nice plug in. I was using https://github.com/litehelpers/Cordova-sqlite-storage with ionic 3 native
but this plugin works out about 3x as faster on my complex queries. thanks!
Posted by huyen on May 8, 2017 at 9:09 PM
nice work