knex: Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call? 1

Hi! guys in my application I was facing (sometime in between) TimeoutError: Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call?

My config of knex is

 const config =    client: process.env.CLIENT,
    connection: {
      host: process.env.DBHOST,
      user: process.env.DBUSER,
      password: process.env.DBPASS,
      database: process.env.DATABASE
    },
    pool: { min: 0, max: 30, acquireTimeoutMillis: 60 * 1000 },
    seeds: {
      directory: './db/sds'
    },
    migrations: {
      directory: './db/mg'
    }
}
import knexLib from 'knex';
export const con = knexLib(config);

and I’m using it something like

import {con} from './con';
import {} from '../err'


const handler = (req)=>{
const trx = con.transaction();
try{
  const result = await con('insert-table').transacting(trx).insert(req.list).returning('*');
  const resultOfLog = await Promise.all(
  result.map((o)=>{
 return con('log-table').transacting(trx).insert({event_id: 1, resource: o.id});
})
);
trx.commit();
return result;
} catch(error){
trx.rollback();
  return new FormatError(error);
}
}

About this issue

Original URL
State: closed
Created 3 years ago
Comments: 16 (4 by maintainers)

Most upvoted comments

Did you try adding the following?

idleTimeoutMillis: 600000

Connection is automatically closed when on connection idle after idleTimeoutMillis

see https://cloud.google.com/sql/docs/postgres/samples/cloud-sql-postgres-knex-timeout

Did you try adding the following?

npm install pg@latest --save

and check pg - node versions https://github.com/knex/knex/issues/2820#issuecomment-687655891 and https://github.com/knex/knex/issues/3912#issuecomment-657715661

otherwise you can also see set propagateCreateError to false in the pool (not advised & might not work out for you anyways )

propagateCreateError: false

see https://github.com/knex/knex/issues/2820#issuecomment-481710112

vintagexav on Mar 25, 2021

You are creating transactions and never committing/rolling them back. That fills the pool until there is no connections left.

Better way to use transactions with automatic commit/rollback is like this:

const resultsAfterTransactionIsComplete = await knex.transaction(async trx => {
  const result = await trx('insert-table').insert(req.list).returning('*');

  // insert logs in the same transaction
  const logEntries = result.map(o=> ({event_id: 1, resource: o.id}));
  await trx('log-table').insert(logEntries);

  // returning from transaction handler automatically commits and frees connection back to the pool
  return results; 
});
´``

elhigu on Mar 26, 2021