How to set up one connection pool or one connection without a connection pool to share TEMPORARY TABLE at Objectio.js?

Question

I need to use the Postgres temp table (only) to store temp generated data to share that data between other controllers and methods.

My app (Nest.js + Objection.js)

// app.module.ts

async onModuleInit() {
      this.createVideoTempFolder();
      // create temporaty table at account.repository.ts
      await this.accountRepository.createAccountTempTable();
    }

// account.repository.ts

public async createAccountTempTable() {
    // create TEMP TABLE FOR CURRENT SESSION
    const queryRaw = this.modelClass.knex();
    await queryRaw.raw(`CREATE TEMPORARY TABLE accounts ON COMMIT PRESERVE ROWS as select * from accounts_main;`);

 }

But my App doesn't see any temporary table at an existing connection pool. It means that every router request starts a new connection pool with Objection.js and the temporary table stays at a previously created connection pool and my App doesn't see any temporary table.

How to set up one connection pool or one connection without a connection pool to share TEMPORARY TABLE at Objection.js?

score 1 · Answer 1 · answered Dec 18 '19 at 23:32

There is non public API in knex which can be used to get connection from pool manually and releasing it back. Something like that is meant to be made public too in knex.

To use that connection with knex you can use https://knexjs.org/#Builder-connection

Something like this:

let connection = await knex.client.acquireConnection();
try {
    const queryRaw = this.modelClass.knex().connection(connection);
    await queryRaw.raw(`CREATE TEMPORARY TABLE accounts ON COMMIT PRESERVE ROWS as select * from accounts_main;`);
} finally {
    knex.client.releaseConnection(connection);
}

How to set up one connection pool or one connection without a connection pool to share TEMPORARY TABLE at Objectio.js?

1 Answers1