Introduce a more general query mechanism beyond keys/ranges

[inexorabletash]

Provide the primitives and/or higher level syntax to allow more complex queries to be submitted to the database backend, without having to do so much iteration in script.

[inexorabletash]

One hand-wavy approach would be to add boolean/set operations as query types, e.g.:

store.get(IDBQuery.and(IDBQuery.is("name", "alice"), IDBQuery.is("salary", IDBKeyRange.bounds(1e5,2e5))))

... with ways of referencing indexes, etc. This could be the target of an ES6 "tagged template string" microsyntax compiler, or we could introduce a blessed query syntax.

[inexorabletash]

Collection of use cases that are easy with SQL but difficult or inefficient in IDB:

• http://stackoverflow.com/questions/26515004/order-grouped-selection-by-condition-in-indexeddb
• http://stackoverflow.com/questions/32370143/filtering-and-sorting-with-indexeddb
• http://stackoverflow.com/questions/21731347/indexeddb-idbkeyrange-compound-multiple-index-not-working

[sicking]

When considering this, we really need to think about what pieces we'd rather leave to libraries, and which ones we should build in to the API.

For the first two use cases in the previous comment, I think an SQL database would create a temporary table and insert results in that table.

We could do something similar and add APIs on the transaction object for creating a temporary btree or hash table and then allow reading/writing in that.

[kristofdegrave]

I think you should stick with the NOSQL approach. If I can make a suggestion: make it possible to have multiple filters, or have a callback in which you can filter your data. That is an approach I used in my own lib I wrote. I do all my filtering in a webworker and I give the user the ability to provide a filtering function. (My worker for filtering and ordering the data. https://linq2indexeddb.codeplex.com/SourceControl/latest#Linq2IndexedDB/Linq2IndexedDB/Source/Worker.js and this is a blog post on how to use it http://www.kristofdegrave.be/2012/07/linq2indexeddb-custom-filters.html)

[nolanlawson]

Due to the popularity of MongoDB, CouchDB has implemented a Mongo-inspired query API, cheekily called Mango. (PouchDB's version is called pouchdb-find.)

I suggest we implement something similar to tap into web developer familiarity with MongoDB as "the" canonical NoSQL database.

Sketch:

store.get(new IDBQuery({
  name: 'Nolan'
}))

equivalent to:

store.get(new IDBQuery({
  name: {
    $eq: 'Nolan'
  }
}))

Fancier:

store.getAll(new IDBQuery({
  firstName: 'Nolan',
  age: {
    $lt: 40,
    $gte: 30
  }
}, {
  sort: ['lastName', 'firstName'],
  limit: 20,
  descending: true,
  skip: 1
}))

The second argument would be optional. Default sorting can be primary key. Default limit would be Infinity. descending would default to false. skip would default to 0.

I suggest the engine should automatically determine the index to use for a given API (via some handwavy query planner algorithm). If no index is found, then an inefficient in-memory method should be used (possibly logging a warning). Based on experience with PouchDB/Cloudant/CouchDB, a warning is more useful than throwing an error.

[inexorabletash]

If there's no index on a property then this involves decoding every record, which (at least in Chrome) is a process hop and likely jank, akin to creating a new index on existing store. While that's a "quality of implementation" thing I wonder if that'll be common across browsers.

It also might be incentive for browsers to create/maintain automatic indexes; you'd pay the cost on the first query. (I bet there's a fancy database term for that.)

[inexorabletash]

age: { $gte: 30, $lt: 40 } vs. age: IDBKeyRange.bounds(30, 40, false, true) ?

The former is more readable, the latter is more consistent with the API. I suppose both is doable, producing the same internal range type.

[inexorabletash]

Does this inform solutions for n-dimensional index query need in https://github.com/w3c/IndexedDB/issues/40 ?

[nolanlawson]

Yes, this seems to have a lot of overlap with #40. The example in that case could be solved Mango-style with:

{
  x: { $gt: 10, $lt: 50 },
  y: { $gt: 20, $lt: 30 }
}

(Assuming the index is on an array of [x, y].)

[inexorabletash]

Another case: The "join" pattern with multiple cursors comes up a lot. I used it in a single index over in this fulltext search example:

https://gist.github.com/inexorabletash/a279f03ab5610817c0540c83857e4295

I'm noting this here rather than in #92 since it involves a single index; like the examples here over a single store with one or more indexes, the primary keys are implicitly the same so it's simpler to express.

Mango-style, would that be { words: { $all: [ 'x', 'y' ] } } ?

[inexorabletash]

TPAC 2019 Web Apps Indexed DB triage notes:

We've synthesized the core of a proposal at #298 - please take a look.