CODEWITHSUNDEEP
node.jspostgresqlgoogle-cloud-sqlknex.jscloud-sql-proxy
samuq
samuq

Reputation: 346

KnexTimeoutError: Knex: Timeout acquiring a connection

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.

Upvotes: 1

Views: 10721

Answers (3)

zanthez
zanthez

Reputation: 45

"express": "^4.16.2",
"knex": "^0.14.2",
"objection": "^2.1.3",
"pg": "^8.0.3",

and npm install i fixed my problem (end of the 4 day)

Upvotes: 0

enocom
enocom

Reputation: 1816

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 your app can connect on 127.0.0.1 (instead of the sqlConnectionName).

Upvotes: 2

MarioG8
MarioG8

Reputation: 5921

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

Upvotes: 2

Related Questions