Knex : nested raw queries, escape the '?' character

I'm trying to use the Postgresql LTREE with knex.
To manage it I have to use raw queries of knex because obviously LTREE is not native in knex (it is specific to postgresql)

An operator in postgresql and LTREE is the character ?, in knex.raw, the ? character is used to bind values (as everyone knows), so there is a conflict.

Once again it's not a problem because we can use \\? to prevent replacement of values where knex find a ? in a raw query.

My problem is that I need to do a 'SELECT EXISTS' of my query containing the knex.raw with the \\? character and in knex I use : knex.raw(myQuery).wrap('SELECT EXISTS(', ')') to do my SELECT EXISTS. So I have nested raw queries, one for the select exists and one in myQuery for the postgresql ltree condition.

During the execution of the query, the first knex.raw transform the original \\? into => ? which is normal and the second knex.raw will do the same job, he will find a ? and will want to bind data but I do not give him data and so knex throws an error !!!

A solution is to put \\\\? instead of \\?, with this, the first knex.raw will transform the query with \\? and the second knex.raw will transform the final query with ? which is what I want in postgresql (without trying to do any bindings)

This is great! But myQuery is generated by a generic function which is called in context with a SELECT EXISTS but also in context without SELECT EXISTS and if I put \\\\? with only one knex.raw (context without SELECT EXISTS) it will also throw an error by postgresql this time (because postgresql can't recognize \\?).

Is it possible to escape the `?` character through all knex.raw ?

A bad solution (but a working one) is to set a parameter to the function that generates the query to precise if it is a context of nested raw queries or not.

edit :

Here a simple example of the code we can have :

const functionThatCreatesTheSubQuery = () => {
    const condition = knex.raw('columnWithLTree \\? array["Root.Noeud1"]::lquery[]');
    return this.where(condition);
};
knex.raw( 
    knex.select('property')
        .from('table') 
        .where(functionThatCreatesTheSubQuery())
).wrap('SELECT EXISTS (', ')');

This fails because the first knex.raw remove the first double \\ of \\? and the second knex.raw for the .wrap will wait for binding

Solution

You are passing query builder to knex.raw(...) as a parameter in

knex.raw( 
    // this is not valid parameter
    knex.select('property')
        .from('table') 
        .where(functionThatCreatesTheSubQuery())
).wrap('SELECT EXISTS (', ')');

It should be something like:

knex.raw('SELECT EXISTS (?)', [
    knex.select('property')
        .from('table') 
        .where(functionThatCreatesTheSubQuery())
]);

Raw call signature is knex.raw(String, [binding1, binding2, ...])

Here is runkit example which shows that question mark is still escaped https://runkit.com/embed/bbtfooz9o1yn

The reason why the original query seems to work is that probably at some point, when query builder is passed to knex.raw() as a first parameter, .toString() is called for it, which converts query builder to plain SQL string, where ?-marks has been unescaped.