adding type-safe `between`, `not between` and their `symmetric` versions at `FunctionBuilder`.

[igalklebanov]

trafficstars

That PR got me wondering if these predicates could be added without the complexity described in the comment section by @koskimas.. but still with type-safety, autocompletion and decent ergonomics.

fn module comes to the rescue, as it enables a type-safe "contextful" way of building raw sql for things like count(c) and max(c).

So why not extend its purpose (callable functions) and use it for other common raw sql use cases?

This PR adds 4 methods (instead of adding whereBetween, whereNotBetween, havingBetween, havingNotBetween, etc. to multiple query builders and affecting compilation code).. and makes consumer's experience slightly better (syntactic sugar yum yum).

In addition to between and not between it also adds symmetric versions for each, something that can simplify consumer's code when they're using postgres - no need to compare bounds before passing them as args.

I promise this is the last time I'll mention between.. Thank you for your time! <3

---

tldr

This PR basically adds a type-safe helper for writing between and not between (and their symmetric versions) predicates in where and having clauses.

const { between, betweenSymmetric, notBetween, notBetweenSymmetric } = db.fn

const someMillenials = await db
    .selectFrom('person')
    .where(between('dob', '1981-01-01', '1996-12-31'))
    .where(notBetween('dob', '1982-01-01', '1982-02-01'))
    .where(betweenSymmetric('dob', '1996-12-31', '1981-01-01'))
    .where(notBetweenSymmetric('dob', '1982-02-01', '1982-01-01'))
    .selectAll()
    .execute()

select * from "person" where "dob" between $1 and $2 and "dob" not between $3 and $4 and "dob" between symmetric $5 and $6 and "dob" not between symmetric $7 and $8

[koskimas]

You really want to add between don't you 😄

Sorry to do this to you again, but there are a couple of problems with this one:

1. fn is a namespace for SQL functions like max(), cast(), concat() etc. Between is an operator and not a function.
2. This is not symmetric with other where methods now. People will wonder why it's whereExists(...) and not where(exists(...)) or why isn't there a whereBetween method instead of where(between(...))
3. To get the autocompletion to work you need to use the where(eb => eb.fn.between(...)) version. where(between(...)) will suggest all columns of all tables, even if they are not accessible in the context.
4. The query building logic is inside the functions and not in the query compiler. Of course this is true for all the functions. I should probably add a FunctionNode.

If you really want to add between, we can add the whereBetween, havingBetween and onBetween methods to the query builders. The code is really simple and shouldn't increase the maintenance load that much.

[igalklebanov]

You really want to add between don't you 😄

We do lots of date range queries.. 😃

Sorry to do this to you again

All good!

If you really want to add between, we can add the whereBetween, havingBetween and onBetween methods to the query builders. The code is really simple and shouldn't increase the maintenance load that much.

Cool! I'll try to come up with a PR..