Connect to Cloud SQL from node.js app on Compute Engine using knex

How do I connect to Cloud SQL from node.js app running in Compute engine using knex? I tried with public ip with and without cloud sql authproxy (tried to install and start proxy in a startup-script), but got error when calling endpoint with knex-connection: KnexTimeoutError: Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call?

Here's my startup script:

set -v

# Talk to the metadata server to get the project id
PROJECTID=$(curl -s "http://metadata.google.internal/computeMetadata/v1/project/project-id" -H "Metadata-Flavor: Google")
REPOSITORY="..."
# CMS="..."

# Install logging monitor. The monitor will automatically pick up logs sent to
# syslog.
curl -s "https://storage.googleapis.com/signals-agents/logging/google-fluentd-install.sh" | bash
service google-fluentd restart &

# Install dependencies from apt
apt-get update
apt-get install build-essential

apt-get install -yq ca-certificates git supervisor postgresql-client
apt-get install --yes liblcms2-2 liblcms2-dev liblcms2-utils

# Install nodejs
rm -r /opt/nodejs
mkdir /opt/nodejs
curl https://nodejs.org/dist/v14.0.0/node-v14.0.0-linux-x64.tar.gz | tar xvzf - -C /opt/nodejs --strip-components=1
ln -s -f /opt/nodejs/bin/node /usr/bin/node
ln -s -f /opt/nodejs/bin/npm /usr/bin/npm
# Get the application source code from the Google Cloud Repository.
# git requires $HOME and it's not set during the startup script.
export HOME=/root
rm -r /opt/app
# rm -r /cms
git config --global credential.helper gcloud.sh
git clone https://source.developers.google.com/p/${PROJECTID}/r/${REPOSITORY} /opt/app


# Install app dependencies
cd /opt/app
git checkout development

npm install
npm install bunyan @google-cloud/logging-bunyan 
npm install sharp@0.29.0




# Create a nodeapp user. The application will run as this user.
cd /
cd /home/app
userdel -f app
cd /
rm -r /home/app
cd /opt/app
useradd -m -d /home/app app
chown -R app:app /opt/app
curl -o cloud-sql-proxy https://storage.googleapis.com/cloud-sql-connectors/cloud-sql-proxy/v2.3.0/cloud-sql-proxy.linux.amd64
chmod +x cloud-sql-proxy
export GOOGLE_APPLICATION_CREDENTIALS=/service-account-key/key.json
export INSTANCE_HOST='<public-ip>'
export DB_PORT='5432'
export DB_USER=’...’
export DB_PASS=’...’
export DB_NAME=’...’
./cloud_sql_proxy -instances=dreamlouvre:europe-west1:dream-louvre-sql=tcp:5432 -credential_file=$GOOGLE_APPLICATION_CREDENTIALS &
# Configure supervisor to run the node app.
rm /etc/supervisor/conf.d/node-app.conf
cat >/etc/supervisor/conf.d/node-app.conf << EOF
[program:app]
directory=/opt/app
command=npm start
autostart=true
autorestart=true
user=app
environment=HOME="/home/app",USER="app",NODE_ENV="development",SQL_CONNECTION_NAME="...",SQL_DB_NAME="...",SQL_USER="...",SQL_PW="...",
stdout_logfile=syslog
stderr_logfile=syslog
EOF
supervisorctl reread
supervisorctl update
# Application should now be running under supervisor

and here's my knex-instance script:

exports.initKnex = () => {

    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 = 1;
    knex.client.pool.min = 1;
    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
    return knex;
} 

And here's the endpoint that causes the error:

exports.getCurrency = async (req, res) => {

    let response = {};
    try{
        response = await knex('exchangeRates').where({'countryCode': req.params.cc}).select('currencyCode', 'rate', 'digits');
        //console.log("RESPONSE: ", response);
      }
    catch (err){
        console.log("error: ", err);
        return res.status(500).json(err);
    }
    return res.status(200).json({'data':response});

}

I noticed a couple things with your configuration.

Firstly, you are downloading v2 of the Cloud SQL Proxy (v2.3.0), yet your CLI invocation command is using v1 syntax ./cloud_sql_proxy. This is the old v1 method, while ./cloud-sql-proxy is used now in v2. You can view the migration guide for guidance on the differences between the two CLI commands and flags.

In short your ./cloud_sql_proxy command in v2 form would be:

./cloud-sql-proxy --port=5432 --credentials-file=$GOOGLE_APPLICATION_CREDENTIALS dreamlouvre:europe-west1:dream-louvre-sql &

Secondly, since you are using the Cloud SQL Proxy, your INSTANCE_HOST variable should not be set to your Cloud SQL instance IP address, it should be set to 127.0.0.1 to interact with the Proxy.

Thirdly, it looks as though your Knex configuration is missing a port field being specified. It should look something like this (see full code sample here)

const configuration = {
  host: process.env.INSTANCE_HOST, // e.g. '127.0.0.1'
  port: process.env.DB_PORT, // e.g. '5432'
  user: process.env.DB_USER, // e.g. 'my-user'
  password: process.env.DB_PASS, // e.g. 'my-user-password'
  database: process.env.DB_NAME, // e.g. 'my-database'
}

Finally, my recommendation would be to use the Cloud SQL Node.js Connector instead of the Cloud SQL Proxy. It is a direct in-process replacement for the Proxy, that provides all the same benefits but in a native npm package.

You can install it as follows:

npm install @google-cloud/cloud-sql-connector

It also supports Knex like so:

const Knex = require('knex');
const {Connector} = require('@google-cloud/cloud-sql-connector');

// connectWithConnector initializes connection pool for a Cloud SQL instance
// of Postgres using the Cloud SQL Node.js Connector.
const connectWithConnector = async config => {
  const connector = new Connector();
  const clientOpts = await connector.getOptions({
    instanceConnectionName: 'project-id:region:instance-name', // Cloud SQL Instance Connection Name
    ipType: 'PUBLIC',
  });
  const dbConfig = {
    client: 'pg',
    connection: {
      ...clientOpts,
      user: process.env.DB_USER, // e.g. 'my-user'
      password: process.env.DB_PASS, // e.g. 'my-user-password'
      database: process.env.DB_NAME, // e.g. 'my-database'
    },
    // ... Specify additional properties here.
    ...config,
  };
  // Establish a connection to the database.
  return Knex(dbConfig);