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!

Solution 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.

Solution 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.

Solution 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.

Solution 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...

Solution 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.

View more solutions

Comments

[zuallauz]

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.

[zuallauz]

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

[alexfernandez]

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.

[Admin]

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

[RichVel]

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.

[rwilliams]

@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.

[RichVel]

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

[Robesz]

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

[zuallauz]

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?

[Kyaw Tun]

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

[zuallauz]

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.

[Peter Aron Zentai]

@zuallauz 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).

[Peter Aron Zentai]

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.

[zuallauz]

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

[Peter Aron Zentai]

@zuallauz 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).

[Douglas Timms]

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.