KnexTimeoutError: Knex: Timeout acquiring a connection-CodePudding

I get the following error when connecting to knex: KnexTimeoutError: Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call?

Here's my code:

Api.js

const Knex = require('knex');
const config = require('./config');
const configuration = {
user: config.config.sqlUser, // e.g. 'my-user'
password: config.config.sqlPw, // e.g. 'my-user-password'
database: config.config.sqlDbName, // e.g. 'my-database'
};
configuration.host = `${config.config.sqlConnectionName}`;
const knex = Knex({client: 'pg', connection: configuration});
knex.client.pool.max = 5;
knex.client.pool.min = 5;
knex.client.pool.createTimeoutMillis = 30000; // 30 seconds
knex.client.pool.idleTimeoutMillis = 600000; // 10 minutes
knex.client.pool.createRetryIntervalMillis = 200; // 0.2 seconds
knex.client.pool.acquireTimeoutMillis = 600000; // 10 minutes



router.get('/things', async (req, res) =>{
 
   await methods.getThings(req, res, knex);
});

methods.js:

exports.getThings = async (req, res, knex) => {
let response = {};
try{
    console.log("knex.client.pool.max");
    console.log(knex.client.pool.max);
    response = await knex.select('id', 'userUid', 'firstName', 'lastName', 'cv', 'statement', 'country', 'represented').from('things').where('approved',true)
}
catch (err){
    console.log("error: ", err);
    return res.status(500).json(err);
}
return res.status(200).json(response)
}

I'm using these: Node v14.0.0 pg "8.7.1" knex "0.95.14"

Seems like it's a problem with creating connection (30s timeout in logs) to cloud sql. How can I create the connection properly? Should I use cloud-proxy and how?

I have a startup script in VM that starts a node express server.

CodePudding user response：

This error can mean many things, but that's where to start: Firstly it may also result from a typo in your database host name, So check your credentials twice! the attribute propagateCreateError should be set to false to prevent the Timeout acquiring a connection. The pool is probably full. Try to add this line to Your pool configuration. Also change the min and max e.g. 2-6 Good Luck!

knex.client.pool.propagateCreateError = false;

In addition, I found two interesting sources that you probably should read 1 2

CodePudding user response：

If you're running this app on a VM, you'll need to also run the Cloud SQL Auth Proxy as a separate process.

You'd start the proxy with something like this:

cloud_sql_proxy -instances=<INSTANCE_CONNECTION_NAME>=tcp:5432

Then you're app can connect on 127.0.0.1 (instead of the sqlConnectionName).