Developing a HTML5 offline storage solution for iOS/Android in 2011

Question

The problem:

I need a device agnostic (e.g. HTML5) solution for storing and querying 250,000+ rows of data offline on a phone or tablet type device (e.g. iOS/Android). The idea being I have people working in remote areas without any cellular data connection and they need to run queries on this data and edit it while offline. Partly it will be geo-location based so if there are assets in the area they are in (uses GPS) then it will show those assets and let them be edited. When they return to the office they can sync the data back to the office server.

The reason that I'm approaching this from a web standard point of view is basically to save money and time by writing it once in HTML5 and then it works across multiple platforms rather than writing it twice in Objective C and Java. Also if you write something that's platform agnostic then you're not locked in and don't go down with the ship when everyone moves to a newer one. We had a similar app written for Windows Mobile 5, now it's useless as that platform is dead.

The offline database on the device needs to be:

• fast (responses under 2 seconds)
• potentially perform joins and have relationships with other tables able to query the database
• select data within a certain range or criteria e.g. by x & y co-ordinate based on the GPS reading.

Options:

HTML5 local storage:

Fine for small amounts of data <5,000 key/values, you can even store arrays/objects in it if you convert it to JSON.

Cons:

• For more than 10,000 rows even on a high end machine the browser will slow to a crawl.
• Can't do complex queries on the data to pull out the data you want as you have to iterate through the whole storage and manually search for it.
• Limitations with the amount of storage that can be stored

Web SQL Database:

• Meets the requirements.
• Fast to run a query on 250,000 rows (1-2secs)
• Can create complex queries, joins etc
• Supported by Safari, Android and Opera so will work on iOS and Android devices

Cons:

• Deprecated as of November 2010
• Security flaw with cross-directory attacks. Not really an issue as we won't be on shared hosting

IndexedDB:

Key/value object store similar to local storage except with indexes.

Cons:

• Slow to run a query on 200,000 rows (15-18secs)
• Can't run complex queries
• Can't do joins with other tables
• Not supported by main phone or tablet devices e.g. iPad/Android
• Standard not complete

This leaves the only option of implementing the deprecated Web SQL method which may only work for another year or so. IndexedDB and local storage are unusable at present.

I'm not sure how Mozilla and Microsoft got the Web SQL Database standard deprecated and why the W3C let it happen. Supposedly between them they have 77% of the desktop browser market. On advanced mobile devices Mozilla and Microsoft have nearly zero influence as Safari, Opera and Android have over 90% of the market share. How Mozilla & Microsoft can dictate which standard should be used in the mobile market which is where offline storage is most likely to be used doesn't make any sense.

In the comments from Mozilla about why they wanted to go with IndexedDB instead are mainly about 'developer aesthetics' and they don't like the idea of running SQL in JavaScript. I'm not buying it.

1. Currently the proposed standard is inferior and an extremely basic NoSQL implementation that is slow and doesn't even support the advanced features people need in a database. There is a lot of boilerplate code to establish the database and get data out but they claim people will write some nice abstraction libraries over the top of it that will provide more advanced features. As of Oct 2011 they're nowhere to be seen.

2. They've deprecated the existing Web SQL standard which actually works and is implemented in the main mobile/tablet browsers. Whereas their 'new' and 'better' standard is not available in the major mobile browsers.

3. What are we as developers supposed to use for the next 3-5 years which is when the IndexedDB specification might get around to being standardised, have more features, implemented in the main mobile/tablet browsers and there's some nice libraries to make things easier?

The W3C should keep the Web SQL Database standard running in parallel and just fix the issues. It already has support for the major mobile platforms and it works pretty well. The fact that Mozilla and Microsoft as the two players with the most desktop browser share were able to get this standard scrapped is pretty dubious and could be seen as an attempt to hinder progress on the mobile web platforms until they are able to catch up and offer competing solutions against iOS/Safari and Android.

In conclusion does anyone have a solution for my problem that will work for iOS/Android for phone/tablet devices. Maybe a nice wrapper API that can use multiple database implementations in the background with querying capability and it lets you choose which database has priority. I've seen things like lawnchair but I'm pretty sure it only lets you use local storage by default and falls back to the to the others. I think I'd rather it used Web SQL (by default) then the slower options.

Any help for a solution much appreciated, thanks!

Sforza

Well written article! This is one of those situations where the native applications wins the native vs web app argument hands down - but I know you don't want to hear that. In which case Web SQL is the best option from my knowledge - I'd also force the user to download rows relevant to the locations they were going to as opposed to the whole database - if you consider they may need to update somewhere with a horrific connection, not to mention the speed increase in searching through a DB 1/5 the size (unsure on the scale of your DB) – Banshee

They can't 'just fix the issues' with WebSQL because one of the requirements for the standard advancing to W3C Recommendation status is that there are 'independent and interoperable implementations'. Since the spec is basically 'do what SQLite does' this is never going to happen. – Sexology

@Vanthel Thanks yeah the best regions are already split up so its loading in a subset of all the data, but the largest ones are still ~250,000 rows. I suppose they could be broken up even further maybe. – Sforza

@Sexology Why does a standard need independant and interoperable implementations to progress? Hasn't it already been implemented already in three browsers? Or are those 3 browsers using the same source code? What is wrong with 'doing what SQLite does'? That in itself is already a good standard. Can't the editors just copy paste the stuff they want from the SQLite spec into the WebSQL spec? Though I think it would make more sense just to reference the same spec so it evolves/improves as SQLite does. – Sforza

Hey you just described my final exam-project :) As I see it, there's 2 options if you require offline and descent performance; 1. Use Local storage and strip the data down to absolute basic. or 2. Build a native app (with a scalable UI?), and then clone it to the other platform (you've allready set the specs i the first one, so it's way faster to develop it again for the other platforms. The downside is you'll have to maintain more than one) – Elman

Because before they required that what we had were W3C Recommendations which no browsers actually implemented. All three browsers are using SQLite. There isn't an SQLite spec, that's one of the reasons why it's not a good basis for a standard. – Sexology

@Sexology How do you mean there's no spec? It's based on the SQL92 standard with a few minor omissions. I found this page which seems like a specification. Also what about all the other documentation on the SQLite website, that's effectively part of the specification isn't it? What else does it need to be valid? – Sforza

Oracle, MS-SQL and PostgreSQL all claim conformance with the SQL-92 standard, have you ever tried writing moderately complex SQL which works unchanged on all three? – Sexology

BTW, you may enjoy this thread on the public-webapps mailing list in March. – Sexology

@Sexology I don't think the aim of Web SQL is to maintain conformance or compatibility with Oracle, MySQL, MS-SQL or Postgres's implementation of SQL-92. It's just to maintain compatibility with SQLite's own implementation of it as that's what the underlying storage engine is. – Sforza

I have a very similar scenario right now +1 on the well-written question! :) – Privateer

Interesting benchmark for IndexedDB. I wonder if the performance headache's you're seeing change all for Chrome's upcoming LevelDB implementation thechromesource.com/… – Pinkston

Answer 1

I would recommend checking out the JayData library, that actually has the exact purpose of creating a storage agnostic data access layer for mobile devices. JayData provides an abstraction layer with JavaScript Language Query (JSLQ) and JavaScript CRUD support and let's you work on the exact same way with different offline and online data store types. JayData supports dealing with complex entities and also entity relationships either locally or remotely.

At the time of writing JayData supports the following stores or protocols: webSQL(sqLite)/IndexedDB/OData/YQL/FBQL.

Your particular problem with different systems providing different storage engines can be easily addressed with the provider fallback feature of JayData: it will use whatever storage layer it can find while still provides the same API toward the consumer code.

With regarding WebSQL being deprecated by 2012: at the time of writing it is WebSQL that still has a 95% device coverage including Samsung SmartTV and amazon Kindle. Check out kindle executing WebSQL unit tests with JayData.

Rapp

I agree, the JavaScript Language Query support and provider model makes JayData library a good option for HTML5 offline storage solution. – Spectacular

Update: JayData now supports HTML5 localStorage, too. JayData library does the JSON stringify/parse job and serves clean, unified data management API and concept. – Heeheebiejeebies

I'd be happy to give your answer as the accepted one as it sounds exactly what is needed, but do you have any performance benchmarks for a large database e.g. 250,000 rows? I would be interested to see if it can handle that many rows using both IndexedDB and WebSQL underlying storage (depending on device) then time how long it takes to do a basic 'where' type query to find one of the rows based on some criteria. – Sforza

@Sforza I have just made such a benchmark. Getting 5-10 items from 250K items took me 60ms on websql using an Android nexus 2, and it was 25ms on a Lumia 920 with Windows Phone. (IndexedDB is definitely faster). – Rapp

I was using the Pro editions with Index support and I was query fields utilizing that index. It is probable much less performant without indices. – Rapp

Ok that's very fast! Nice work. A nice comparison/benchmark graph on the JayData site would be a good addition IMHO. – Sforza

@Sforza You are very right on that, and we will. IndexedDB is generally much faster in every aspect - except for complex multi field queries. Inserting data can be slow on WebSQL however. Transaction open for writing can be costly (60-120ms). – Rapp

Answer 2

I would checkout CouchBase Lite. It's a near full featured implementation of CouchDB that runs on Android and iOS.

iOS

Android

If you wrapped your App in something like PhoneGap you could create native HTML 5 apps for both platforms and you'd only have to do a tiny bit of Android/iOS specific programming to implement CouchDB.

Pros:

• Fast View engine for querying across many rows of data.
• Dirt simple and powerful replication support baked in.

Cons:

• Key-Value Store - It'll take some time to get used to.

Murvyn

Thanks, sounds like it could work, however you need a Mac system I believe to develop for it and produce the keys etc? We don't have any macs. – Sforza

CouchDB is quite interesting but I'm not convinced that its model of making index ("view") updates lazy (triggered by a query, or by a batch process) really works well. Most applications I've seen assume that once you've added some data, it can be queried efficiently, and most other NoSQL databases push more work into the write transactions so that read transactions are faster. – Mattson

@RichVel: In my experience the view updates have never been a problem on the mobile devices. That said one of the biggest reasons to use either CouchDB or TouchDB is for the super powerful yet simple to use replication. – Murvyn

I started looking at CouchDB for replication to mobile, but I didn't really want to expose the server DB to the mobile, or have to use CouchDB on server to get replication. So I'm now looking at a data synchronization cloud service called Simperium, see simperium.com and simperium – Mattson

Answer 3

I've made some more research while looking for a solution for my own project. It looks like this library is rather promising: http://nparashuram.com/IndexedDBShim/

It allows to use IndexedDB API having WebSQL behind the scenes.

It's tests pass on recent iPad, iPhone 5, Android 4.2.2.

Hope this helps someone.

Palsgrave

Late to the party, but I also recommend indexeddbshim.js. It works pretty will in a Cordova/PhoneGap environment to provide a single solution across IOS and Android. – Unsung

Answer 4

I would tell you to use Corona for it . It's a private Platform used for crossed-mobile applications which has support to SQLite .

Pros

• It's easy and has a big support for SQLite , and don't need to do strange things with Html5 storage

Cons

• you must pay for it if you wanna use it in the Android Market or the iOS Market.

I paste here what they say about it:

Corona includes support for SQLite databases on all platforms. This is based on the built-in sqlite support on the iPhone, and a compiled version of SQLite on Android. Note that this increases the size of the Android binary by 300K.

SQLite is available in all versions of Android, iPhone, and iPad, as well as in the Corona Simulator...

Kanazawa

Answer 5

"I've seen things like lawnchair but I'm pretty sure it only lets you use local storage by default and falls back to the to the others. I think I'd rather it used Web SQL (by default) then the slower options."

This is configurable, each of the 'adapters' for storage engines is self contained, you can pass an adapter to the Lawnchair constructor, or, alternatively, change the order in which it falls back to other storage options by concatenating the javascript files differently when creating the library. e.g. for indexed-db then falling back to sqlite then gears sqlite:

git clone https://github.com/brianleroux/lawnchair.git  
cd lawnchair  
cat src/Lawnchair.js src/adapters/indexed-db.js src/adapters/webkit-sqlite.js src/adapters/gears-sqlite.js > my_lawnchair.js

Of course, as the other answers suggest, you can wrap your html5 into an native app using phonegap etc. then you'll have plenty of options, but if you want to stick to web standards then this may be a good way to go until we've got wide adoption of IndexedDB.

Araliaceous

Answer 6

Why not write a simple storage engine in javascript (which covers the "standards-based" part)? Apparently you don't need anything very fancy, so it should not take too much effort to have it working.

I would do the following:

• Store everything in bson or a similar binary format.
• Parse and create indexes in files, and read at startup.
• Query using javascript and read from the big file from your (offline obviously) web application.
• Store updated objects separately.

This solution is only feasible if the database is simple enough. But I think it might work -- javascript support is good on mobile devices.

For inspiration here is a Btree+ implementation in javascript.

To read the local files you will need the file API, which can be used to access local files. It is supported in most modern browsers, even Safari 6. I have not been able to determine if current iPhone browsers support this API though.

Gloriole

How does the JavaScript on the phone have access to read/write to/from the filesystem on the devices? – Sforza

Good point. Apparently the File API is not as far as it seems, but my answer can be a bit too optimistic. CouchBase looks like a safer bet. – Gloriole

Answer 7

It worths to check out my open source library https://bitbucket.org/ytkyaw/ydn-db/wiki/Home

Javascript database module for Indexeddb, WebDatabase (WebSQL) and WebStorage (localStorage) storage mechanisms supporting version migration, advanced query and transaction.

Being NoSQL library, join is manual, but not impossible. There is already key joining algorithms build-in the library.

Drumfire

If in the code I need to do a SQL query to retrieve some data and sort it, but my device is running IndexedDB, can your library convert that SQL query which will get the data from the IndexedDB database? – Sforza

No. SQL support in my library is very basic. Please see here what it can dev.yathit.com/ydn-db/sql-query.html – Drumfire