Add ability to either wrap queries or extract db or executor

[GiacoCorsiglia]

I have a helper function that takes a baseQuery: SelectQueryBuilder and paginates it, returning two queries: one with limit/offset, and another that selects a count.

To make this resilient to GROUP BY clauses in the baseQuery, I want to do

countQuery = db.selectFrom(baseQuery.as("__base")).select(eb => eb.fn.countAll().as("count"))

But—unless I'm mistaken, this will "remove" the count query from whatever transaction the baseQuery might be part of. That is, the following does not work as desired (every query in the same transaction):

db.transaction().execute((tx) => {
  const baseQuery = tx.selectFrom(...)

  const { pageQuery, countQuery } = paginate(baseQuery);

  // Not actually in the transaction (I think).
  countQuery.execute()
})

To address this, I either have to thread tx into the paginate function, or I can do a trick with baseQuery.withPlugin() to wrap:

const countQuery = baseQuery.withPlugin({
  transformQuery(args) {
    const baseQuery = new ExpressionWrapper(args.node);
    const eb = expressionBuilder();
    const countQuery = eb.selectFrom(baseQuery.as('__base')).select(eb => eb.fn.countAll().as('count'));

    return countQuery.toOperationNode();
  },

  async transformResult(args) {
    return args.result;
  }
});

which I think works.

It would be nice to have some canonical solution to this. A few initial ideas:

1. Add SelectQueryBuilder.wrap(): baseQuery.wrap((eb, query) => eb.selectFrom(query)...)
2. Expose SelectQueryBuilder.db or something like it
3. Accept an optional argument in execute() that lets you pin the execution to a given transaction.

Thanks for reading! Kysely is so awesome 😄

[igalklebanov]

Hey 👋

Add SelectQueryBuilder.wrap(): baseQuery.wrap((eb, query) => eb.selectFrom(query)...)

That's a security vulnerability - passing a query to a 3rd party library that could run a completely different query in a malicious version.

e.g.:

delete from 'users' where true or exists ({yourquery})

Expose SelectQueryBuilder.db or something like it

That's a security vulnerability - passing a query to a 3rd party library that could then execute more queries unexpectedly in a malicious version.

Accept an optional argument in execute() that lets you pin the execution to a given transaction.

That might work.

[GiacoCorsiglia]

Thanks for the response!

I hadn't considered the security implications, so that makes sense to avoid. Does the .withPlugin() trick mean this type of vulnerability exists?

Anyway, the .execute({ tx }) approach has the general benefit of letting you reuse queries built/initialized in other scopes in the current transaction.

[igalklebanov]

Does the .withPlugin() trick mean this type of vulnerability exists?

Plugins don't allow changing a query's root kind, they runtime error. Should there be more defensive measures? dunno.

Anyway, the .execute({ tx }) approach has the general benefit of letting you reuse queries built/initialized in other scopes in the current transaction.

tx is too specific. Any executor fits.

awesomeQuery.execute({ executor: tx.getExecutor() })