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!

Thalia

I believe WebSQL has been abandoned in favor of IndexedDB... – Greenback

Watch out for WebSQL, Apple 5.0.1 no longer persists WebSQL data. In other words, this will cripple your app's local database. Phonegap has a workaround. issues.apache.org/jira/browse/CB-330 – Emplane

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]);

Udder

Answer 2

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

Dewan

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.

Mun

Thanks @Mun I have looked at it. It appears it supports local storage by default. Whereas I'd prefer if it did WebSQL by default as it's a lot faster but I don't think there's an option to change up the order of adapters. WebSQL can query 200,000 rows in under 2 seconds. IndexedDB takes 15 seconds. Local storage hangs after about 10,000 rows. – Thalia

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.

Cassilda

Answer 5

sync wrapper version for IndexedDB to localStorage API. I can't find any sync version of these wrapper because IndexedDB is async and localStorage is sync so I need do some hack https://gist.github.com/xnohat/b7aa5035278478871697b7ad6255efb2

class IndexedDBStorage {
  constructor(dbName = 'localStorageDB', storeName = 'localStorageStore') {
      this.dbName = dbName;
      this.storeName = storeName;
      this._init();
      this.cache = {};
  }

  _init() {
      const request = window.indexedDB.open(this.dbName, 1);
      request.onerror = (event) => console.error('Error opening indexedDB');
      request.onsuccess = async (event) => {
          this.db = event.target.result;
          await this._populateCache();
          this._syncCache();
      };
      request.onupgradeneeded = (event) => {
          const db = event.target.result;
          db.createObjectStore(this.storeName);
      };
  }

  async _populateCache() {
      const store = this._getStore();
      return new Promise((resolve, reject) => {
          const request = store.openCursor();
          request.onsuccess = (event) => {
              const cursor = event.target.result;
              if (cursor) {
                  this.cache[cursor.key] = cursor.value;
                  cursor.continue();
              } else {
                  resolve(); // Finished populating the cache
              }
          };
          request.onerror = (event) => reject('Error populating cache');
      });
  }

  async _syncCache() {
      for (const key in this.cache) {
          await this._asyncSetItem(key, this.cache[key]);
      }
  }

  async _asyncSetItem(key, value) {
      const store = this._getStore('readwrite');
      return new Promise((resolve, reject) => {
          const request = store.put(value, key);
          request.onsuccess = () => resolve();
          request.onerror = (event) => reject('Error storing value');
      });
  }

  _getStore(mode = 'readonly') {
      const transaction = this.db.transaction([this.storeName], mode);
      return transaction.objectStore(this.storeName);
  }

  setItem(key, value) {
      this.cache[key] = value;
      this._asyncSetItem(key, value).catch(console.error);
  }

  getItem(key) {
      if (this.cache[key]) {
          return this.cache[key];
      }
      // Fetch from indexedDB and store in cache (in the background)
      this._asyncGetItem(key).then(value => {
          this.cache[key] = value;
      });
      return null; // or some default value
  }

  async _asyncGetItem(key) {
      const store = this._getStore();
      return new Promise((resolve, reject) => {
          const request = store.get(key);
          request.onsuccess = (event) => resolve(event.target.result);
          request.onerror = (event) => reject('Error retrieving value');
      });
  }

  removeItem(key) {
      delete this.cache[key];
      this._asyncRemoveItem(key).catch(console.error);
  }

  async _asyncRemoveItem(key) {
      const store = this._getStore('readwrite');
      return new Promise((resolve, reject) => {
          const request = store.delete(key);
          request.onsuccess = () => resolve();
          request.onerror = (event) => reject('Error removing value');
      });
  }

  clear() {
      this.cache = {};
      this._asyncClear().catch(console.error);
  }

  async _asyncClear() {
      const store = this._getStore('readwrite');
      return new Promise((resolve, reject) => {
          const request = store.clear();
          request.onsuccess = () => resolve();
          request.onerror = (event) => reject('Error clearing store');
      });
  }
}

const idbStorage = new Proxy(new IndexedDBStorage(), {
  get: (target, prop) => {
      if (typeof target[prop] === 'function') {
          return target[prop].bind(target);
      }
      return target.getItem(prop);
  },
  set: (target, prop, value) => {
      target.setItem(prop, value);
      return true;
  }
});

window.idbStorage = idbStorage;

Bev