1

I have a sub-query that is being used in four different queries that all execute to present some dashboard like functionality. The common ground for all queries is this sub-query which I want to extract in a temporary table. This has been a success, but using Knex.js (which is amazing) the connections are pooled, which means that all the queries use different connections which again lead to the temporary table being dropped in between.

Is there any way to force using one connection, and one session in Knex this way? Or would I need to look for other workarounds, such as wrapping it all in a transaction?

Håvard
  • 67
  • 6

1 Answers1

4

Basically you can do that only by running those queries in the same transaction. That forces knex to use the same connection for all of the queries.

Another way to do it is to acquire connection from pool manually with knex.client.aqcuireConnection() and use knex.connection(connection) to run queries in that single connection. Finally you need to release the connection back to the pool to not leak connections with knex.client.releaseConnection(connection).

Something like: 

let connection = await knex.client.acquireConnection();
try {
    const res = await knex('table').connection(connection).where('id',1);
    const res2 = await knex('table2').connection(connection).where('id',1);
} finally {
    knex.client.releaseConnection(connection);
}
Mikael Lepistö
  • 18,909
  • 3
  • 68
  • 70
  • Thanks for your input. I'll try this and report back. Of the two approaches, do you know which one would be more performant? – Håvard Jan 29 '20 at 19:35
  • 1
    Performance is the same for both, I would use the transaction version if it is possible. That way there is less room for error and causing connection leaks from pool. Maybe I should add some leaseConnection function to knex that works a bit like knex.transaction(trx => {}) which handles automatically acquire / releasing. – Mikael Lepistö Jan 30 '20 at 09:59