WhatDB? Proposed syntax.

whatdb-proposed-syntax.md

WhatDB?

Proposed syntax.

Preparing a query

Internally, this prepares the predicate for an array.filter() operation.

.where( propertyName )               : Start query. Returns query object.
.and( propertyName )                 : Continues query with logical AND. Returns query object.
.or ( propertyName )                 : Continues query with logical OR. Returns query object.
.startsWith(), .endsWith()           : Adds string start/end match comparison to the query. Returns query object.
.includes()                          : Adds substring match to the query. Returns query object.
.is()|.isGreaterThan()|.isLessThan() : Adds value comparison to the query. Returns query object.

Executing a query.

Internally, this executes and returns the result of an array.filter() operation. If a specific set of properties is requested, an array.map() is also performed.

.get ([property1, property2, …])     : Executes query. If properties are specified, returns only those properties.

Sorting.

Internally, these result in an array.sort() operation.

.sortBy()                            : Sorts by given property in ascending order.
.sortAscendingBy()                   : Alias for sortBy()
.sortDescendingBy()                  : Sorts by given property in descending order.

Grouping and aggregation.

Internally, these result in an array.reduce() operation.

.groupBy()                           : Groups the result by the passed property. Returns array.
.sum()                               : Reduces to the sum of the given property. Returns a numerical value.
.average()                           : Reduces to the average of the given property. Returns a numerical value.

Result values and assertions.

.expectOne()                         : Returns the only item in the array. If array has more than one item, throws.
.expectMany()                        : Returns array. If array has no items or one item, throws.
.expectNone()                        : Returns null. If array is not empty, throws.
.expect(n)                           : Returns the array with N items. If array.length !== n, throws.

Example:

require('@small-tech/whatdb')

const people = [
  { name: 'Aral', pet: {type: 'dog', name: 'Oskar'}, likesToPlay: 'RimWorld' },
  { name: 'Laura', pet: {type: 'dog', name: 'Oskar'}, likesToPlay: 'Dishonored 2'}
]

let person

person = people
  .where('name')
  .is('Laura')
  .and('pet.name')
  .is('Oskar')
  .get()

// Returns [ { name: 'Laura', pet: {type: 'dog', name: 'Oskar'}, likesToPlay: 'Dishonored 2'} ]

try {
  person = people.where('pet.type').is('dog').and('likesToPlay').includes('World').get('name').expectOne()
} catch (error) {
  // e.g., if there is more than one.
  console.log(`Query error: ${error}`)
}

// Returns: {name: 'Aral'}
// With get('name.value') instead, would return the string value: 'Aral'

Limits?

get(n)
get(m, ['pet.type', 'pet.name'])

Paging?

.startAt(n).get(m)

Negations?

not()
isNot('cat')
andNot('likesToPurr')

Numeric comparison?

greaterThan(n)
greaterOrEqualThan(n)
lessThan(m)
lessOrEqualThan(m)
equalTo(o)

Ranges?

between(n, m)

This allowed?

const people = [
  { name: 'Aral', 
    pet: [
      {type: 'dog', name: 'Oskar', barks: 'Loudly'},
      {type: 'dog', barks: 'Muted'},
      {type: 'cat', name: 'Nina', likesToPurr: false},
      {type: 'bird',
        name: 'Tweeter',
        healthStatus: {
          feathers: 'optimal',
          beak: ['needs cutting', 'needs sharpening']
        }
      }
    ], 
    likesToPlay: 'RimWorld'
  },
  { 
    name: 'Laura', 
    pet: {type: 'dog', name: 'Oskar'},
    likesToPlay: 'Dishonored 2'
  }
]

If yes, then maybe you might need to look at combinatorial complexity and clarity of queries. Maybe they need parentheses / subqueries or something.

Sets?

people.where('pet.type').in(['dog', 'bird'])

Guess it will not be relational, as you and Laura duplicated your dog in the sample :)
Means that "get all dogs named 'Oskar'" is problematic, unless the restriction is you only query people (top-level json) based on filter criteria (all the other props). I guess this is the idea, given the code sample.

But there'll be a lot of duplication since per the example in reality you'd both own the 4 pets. "Give me all people with a bird where the beak needs cutting".

Just a number of 2 cts 😸

in the code example above it is not clear if a collection of a single object is returned. so

person = people.....get() looks like it would return just a single object, but a query like this might return many items. I would strongly suggest making this clear upfront. Some APIs I've used have different methods like "findOne / findRecord" for single things or "find / findAll" for collections. The former always return a single thing (or null), the latter always return arrays/collections, even if those are empty.

One thing that can't hurt is going over ActiveRecord (ORM inside ruby on rails), for one thing, they have a lot of examples. And other than that, I really think that rails gets many things right, so maybe there's something in there that fits? https://guides.rubyonrails.org/active_record_basics.html

I would chime in nesting of conditions by passing a function to where, orWhere, andWhere and whereNot:

items
.where((query) => {
  query.where('foo').is('bar');
})
.orWhere((query) => {
  query.where('baz').isNot('bar');
})
.andWhere((query) => {
  query.where('foo').in(['a', 'b', 'c']);
});
.whereNot((query) => {
  ...
});

while andWhere is an alias to where just for better readability.

This is how Laravel's ORM is doing it and I find it very convinient.

Also a friend of mine started this: https://github.com/StephanHoyer/hedy a functional ORM. Maybe it could be of use.

Thanks for all your suggestions @aschrijver, very helpful :)

Limits?

get(n)
get(m, ['pet.type', 'pet.name'])

Would probably make sense to add separate methods instead of overloading the get() method. e.g.,

Limits and paging

first: get first item
last: get last item
limitTo(n): limit returned results to the first n 
page(n): if limitTo is set, returns page n of results

Negations?

not()
isNot('cat')
andNot('likesToPurr')

Are these aliases? Not sure I get exactly how each would work.

Numeric comparison?

greaterThan(n)
greaterOrEqualThan(n)
lessThan(m)
lessOrEqualThan(m)
equalTo(o)

Covered a couple in the is… methods. Having the full set would be good.

Ranges?

between(n, m)

Do you see a use case to this separate from what can be achieved using limits and paging?

Thanks again :)

This allowed?
…
If yes, then maybe you might need to look at combinatorial complexity and clarity of queries. Maybe they need parentheses / subqueries or something.

It would be very difficult to disallow given that it should work transparently with object notation. However, I might reconsider that as I start actually building this out. My thought initially is to disallow circular references as I haven’t found a library that can handle JSON serialisation with circular references in an acceptably performant manner.

Sets?

people.where('pet.type').in(['dog', 'bird'])

Hmm, don’t know, will have a think.

Guess it will not be relational, as you and Laura duplicated your dog in the sample :)
Means that "get all dogs named 'Oskar'" is problematic, unless the restriction is you only query people (top-level json) based on filter criteria (all the other props). I guess this is the idea, given the code sample.

But there'll be a lot of duplication since per the example in reality you'd both own the 4 pets. "Give me all people with a bird where the beak needs cutting".

You could implement IDs in your data structure and have relations that way.

Thank you for your feedback @ccoenen, appreciate it! :)

person = people.....get() looks like it would return just a single object, but a query like this might return many items. I would strongly suggest making this clear upfront. Some APIs I've used have different methods like "findOne / findRecord" for single things or "find / findAll" for collections. The former always return a single thing (or null), the latter always return arrays/collections, even if those are empty.

Ah, in my naïve initial draft, I was thinking expectOne() would do that but that’s really overloading the meaning of that method. If we implement limits according to @aschrijver’s suggestion, then .first would fulfill that need but it’s not clear, semantically… will have a think.

One thing that can't hurt is going over ActiveRecord (ORM inside ruby on rails), for one thing, they have a lot of examples. And other than that, I really think that rails gets many things right, so maybe there's something in there that fits? https://guides.rubyonrails.org/active_record_basics.html

Oh, indeed. Just did and it had been a while since I’d played with it. If you think about it, Site.js is basically what Ruby on Rails would have been had it been made for individuals/single-tenant web instead of organsations/centralised-web so yeah, lots of learn from (as well as lots to avoid for our particular use case) :)

Hey, @der-On, appreciate the thoughts :)

items
.where((query) => {
  query.where('foo').is('bar');
})
// …

One thing I want to avoid is having functions within the syntax. Mostly because, given that queries return plain old JavaScript arrays, if you need to do anything more complicated, you can go directly to custom logic. I think that’s a good place to draw the boundary: the interface exists to support basic, everyday “80%” work and anything more complicated, just chain your own custom function and use lower-level primitives for finer grained control/expressiveness.

This is how Laravel's ORM is doing it and I find it very convinient.

Will check it out; thanks :)

Also a friend of mine started this: https://github.com/StephanHoyer/hedy a functional ORM. Maybe it could be of use.

Neat, will check that out also. At first glance, looks similar :)

Negations?

not()
isNot('cat')
andNot('likesToPurr')

Are these aliases? Not sure I get exactly how each would work.

Yea, me neither.. exactly. There should be some way to exclude from a query, that is still intuitive wrt query syntax already adopted. I typed this just off the top of my head. Let's see..

legalPetOwner = people.where('pet.type').isNot('jaguar')
legalPetOwner = people.where('pet.type').not().in(['jaguar', 'lion', 'chimpansee'])
decentBoss = people.where('company.minWage').greaterThan(60000).andNot('name').is('Jeff')
decentBoss = people.where(company).isNot(null).and('lastname').not().startsWith('Zuck)

This can certainly be improved.. looks a bit contrived. Note the isNot(null) to check for existence of a nested object, where in 3rd example it is unclear what a missing where('company.minWage') indicates. You might need a .exists() in that case, maybe. I thought of the not() as just a general way to negate whatever comes after it.

The andNot().is() could also be a and().isNot(), but andNot() leaves more options for what comes after it and might improve readability in some cases. The last not() certainly looks contrived, and should be an andNot('lastname').