We have a TypeScript application where we use Knex for all of our database interactions. We mostly use PostgreSQL, but for some perf-related stuff, we're running against a ClickHouse cloud instance, which we connect to from Knex via the MySQL2 protocol handler. ClickHouse has a nice constant-aliasing syntax via with <constant> as <alias> syntax (example 1 here. I'd like to use this from knex, but I can't figure out a stable way to construct it via built-in knex stuff.
I tried inverting the parameters to QueryBuilder.with, but it escaped the constant with backticks like an identifier and put the actual identifier inside of parentheses, both of which didn't work.
Eg: knex.with('blahblah', 'my_name') becomes
with `blahblah` as ('my_name')
The other thing I can think of doing is something like:
query = knex.raw(`with <constant value> as my_constant_identifier`, query);
I'm pretty sure I can make this work, but it's really brittle in terms of the code to the point that testing this in my app is non-trival.