Knex.js force session reuse for 4 following queries

Question

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?

Answer 1

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);
}