Cloud SQL instance connection working locally, but not on App Engine

Question

I am trying to host an API implemented as a Node.js app on Google Cloud App Engine. The database that the API utilizes is a PostgreSQL 9.6 database which is hosted with a Cloud SQL instance. The database is connected to the Node.js API through Knex.

When hosted on App Engine, the API endpoints that do not require any contact with the database work fine. However, when the database needs to be contacted to complete the API call, the following error appears in the logs:

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

However, if I use Knex to connect to the Cloud SQL instance locally and address the API through localhost:3000, an API call requiring the database completes perfectly fine. This verifies that the PostgreSQL database instance on Cloud SQL is working fine and that something about hte Node.js app being hosted on App Engine is making a connection to the Cloud SQL instance fail.

I connect to the database like so:

module.exports = require('knex')({
        client: 'pg',
        debug:  true,
        connection: {
          host : '35.194.32.254', 
          user : 'postgres',
          password : 'mypassword',
          database : 'mydatabase'
        },
      });

Again, local connections succeed, App Engine connections fail.

My app.yaml file is as follows:

runtime: nodejs
env: flex
beta_settings:
  cloud_sql_instances: my-project-12345:us-central1:mydatabase

To ensure this is not a permission issue, I granted the App Engine service account [email protected] the Cloud SQL Client permission. I've also verified that the Cloud SQL instance is deployed in the same region as the Node.js app on App Engine.

My question is this: Why can't Node.js on the App Engine connect to the Cloud SQL instance if it can connect to it locally?

Ventilator

Please read this guide: cloud.google.com/appengine/docs/flexible/nodejs/…, you should be using the UNIX socket /cloudsql/... to connect instead of the IP. If you connect by IP then you have to deal with IP whitelisting. – Hartal

Using the UNIX socket via config.socketPath = /cloudsql/... and removing the IP yields the error Unhandled rejection Error: connect ECONNREFUSED 127.0.0.1:5432 and an eventual nginx 502. Why is this? – Ventilator

The reason is because config.socketPath is only used for MySQL. Since I am using PostgreSQL, I need to use config.host – Ventilator

Same problem here and I'm following official guide (github.com/GoogleCloudPlatform/nodejs-docs-samples/blob/master/…) – Woehick

Answer 1

As per Vadim's comment, UNIX sockets must be used to connect from App Engine to Cloud SQL otherwise IP whitelisting must be involved, something which there is little guides on how to do.

To accomplish this, we simply change the host from 35.194.32.254 to the UNIX socket /cloudsql/my-project-12345:us-central1:mydatabase:

module.exports = require('knex')({
    client: 'pg',
    debug:  true,
    connection: {
        host : '/cloudsql/esp-mobile-182605:us-central1:esp-db', // 127.0.0.1 for local testing, 35.194.32.254 to locally use hosted db on Cloud SQL
        user: 'postgres',
        password: 'seniordesign',
        database: 'esp_db'
    },
});

Ventilator

I tried to connect to google postgres the same way. But it is not connecting. Do you have any idea. Can u help me. My config is this const client = new Client({ user: 'xxx', host: '/cloudsql/proj-name:us-central1:app-name', database: 'xxxxx', password: 'xxxxxx', port: 5432, //connectionString: process.env.DATABASE_URL, //ssl: true, }); client.connect(); – Lockout

Answer 2

• Following the official documentation
  https://cloud.google.com/sql/docs/postgres/connect-app-engine-standard

• Copy and paste from here
  https://github.com/GoogleCloudPlatform/nodejs-docs-samples/blob/master/cloud-sql/postgres/knex/server.js

• DO NOT set DB_HOST in .env file (this was my problem)

Woehick