Wrapper functions for IndexedDB

Question

I need to build an offline HTML5 web app for a iPad/tablet device where the user can download a dataset (table of data) from the server and store it on the device. The user could then disconnect from the server and view/edit the data locally on the device. This is for people who work out in remote areas where there's no cellular coverage and need to collect/update data. When they come back into the office they can sync/upload the data back to the server. The reason it needs to be HTML5 is so it's platform agnostic, ie can run it on iOS, Android etc as long as it has a modern web browser that supports HTML5.

Now I've already built the system using HTML5 local storage (for the data) and the HTML5 offline application cache (for the pages/css/js/images) and it works reasonably well with small datasets (I can view, edit and save while offline and load/sync while online). Now I need to scale up to 10,000 rows of data. It works but it's pretty slow and hangs the browser for 10secs while loading on an Intel quad core 8GB machine.

So I've been researching a few better alternatives than local storage:

1) WebSQL: Would be able to query the data using SQL language and do joins etc. Problem is it's now deprecated an won't be supported any longer so I don't want to invest time building something for it.

2) IndexedDB: Uses an object store (which technically I'm already storing objects using local storage API and storing using JSON). Potentially is faster as it uses indexes with the SQL lite backend. There's lots of boilerplate code to do simple tasks like creating the database, adding to it, reading from it, iterating over it. I just want to do a simple query like select(xyc, abc).where(abc = 123).limit(20) but instead have to write a lot of JavaScript code to do it. How does one write their own code to do joins between tables, any examples anywhere?

I've found one jQuery plugin that might make life simpler. Are there any other ones around or other libraries that ease the pain of using IndexedDB?

Many thanks!

Answer 1

I have an open source web database wrapper which supports both IndexedDB and WebSql.

Version migration is handled behind sense. The following code migrates (or initialize) to version 2.

schema_ver2 = {
    version: 2,
    size: 2 * 1024 * 1024, // 2 MB
    stores: [{
        name: 'ydn_obj',
        keyPath: 'id.value',
        indexes: [{
            name: 'age',
            type: 'INTEGER'  // type is require for WebSql
        }]
    }]
}
db = new ydn.db.Storage('db name', schema_ver2)

Query is very flexible and powerful. For example:

q = db.query('customer').when('age', '>=', 18 , '<', 25).where('sex', '=', 'FEMALE')
young_girls = q.fetch(10, 2); // limit and offset

Again with more efficient key range query if age is indexed:

q = db.query('customer', 'age').bound(18, 25, true).where('sex', '=', 'FEMALE')

It also support transaction.

p123 = db.tkey('player', 123);
db.runInTransaction(function() {
   p123.get().success(function(p123_obj) {
        p123_obj.health += 10;
        p123.put(p123_obj);
   });
}, [p123]);

Answer 2

Try linq2indexeddb. It has the query interface you want + with the indexeddb shim for websql the WebSQL API is also supported.

Answer 3

Have you considered [Lawnchair][1]? It provides a nice abstraction from the underlying storage, there are also plugins for querying, aggregating and paginating data. As an example of querying:

  // basic searching
    this.where('record.name === "brian"', 'console.log(records)') 
    this.where('record.name != ?', username, 'console.log(records)')

    // sorting results
    this.where('name === "brian"').asc('active', 'console.log(records)')

The only potential drawback I can see is that it doesn't appear to handle migrations and being generic doesn't appear to have a way of creating indexes etc.

With regards to joins, IndexedDB is designed to be a document oriented (No SQL) store not a relational database, however given this is a common scenario it appears there are two options:

1) Cursor iterate over the data items 2) If the above is too slow, you could also create a dedicated key value object store which could then be used to do an indexed lookup in the relevant store. Depending on the number of join requirements you have, this could be a chore.

Answer 4

I think JsStore will work for you.

Lets say your query looks something like this in sql -

select * from table_name where column1='abc' limit 20

In JsStore - It will be

var Connection = new JsStore.Instance("YourDbName");
Connection.select({
    From: "table_name"
    Where: {
        Column1: 'abc',
    },
    Limit:20,
    OnSuccess:function (results){
        console.log(results);
    },
    OnError:function (error) {
        console.log(error);
    }
});

So you can write sql like query using JsStore.