Search code examples
postgresqlgoogle-app-enginenestjstypeorm

Cant Access PostgreSQL on Google Cloud SQL from NestJS project on Google App Engine

This is my first question in Stack Overflow so please excuse me when my information is lack.

Issue

I am struggling to connect PostgreSQL on CloudSQL from NestJS on Google App Engine. When I try to use the application in local environment the program works but when it comes to production in Google App Engine then it does not work.

Since i struggled days, I decided to ask awesome community here.

My Environment

  • Node.js: v10.19.0
  • NestJS: 6.10.5
  • TypeORM
  • PostgreSQL: 11.5.1

My app.yaml

runtime: nodejs10
env: standard

default_expiration: "4d 5h"

env_variables:
  DATABASE_HOST: < public IP for Cloud SQL instance >
  DATABASE_USERNAME: username
  DATABASE_PASSWORD: password
  DATABASE_NAME: databasename
  INSTANCE_CONNECTION_NAME: "PROJECT_ID:REGION:INSTANCE_ID:DATABASE_NAME"

handlers:
- url: /.*
  secure: always
  redirect_http_response_code: 301
  script: auto

resources:
  cpu: 1
  memory_gb: 0.5
  disk_size_gb: 10

Error

[Nest] 18   - 02/27/2020, 8:25:46 AM   [TypeOrmModule] Unable to connect to the database. Retrying (3)... +34816ms

2020-02-27 08:25:46 default[20200227t163916]  Error: connect ETIMEDOUT 34.84.188.209:5432      at TCPConnectWrap.afterConnect [as oncomplete] (net.js:1106:

Others Operations

GAE Service Account

Also I added the Cloud SQL Client authority my GAE Service account (something like this service-PROJECT_ID@gae-api-prod.google.com.iam.gserviceaccount.com).

I also added package.json as written in below:

  "engines": {
    "node": "10.x.x"
  },

In the typeorm options, I added extra socketpath.

extra: {
   socketPath: `/cloudsql/<INSTANCE_CONNECTION_NAME>/`,
            },
I do not understand if this option should be set or not (I have tried both). 
socketPath: `/cloudsql/<INSTANCE_CONNECTION_NAME>/.s.PGSQL.5432

or 

socketPath: `/cloudsql/<INSTANCE_CONNECTION_NAME>
Solution

  • According to the example provided in GitHub

    https://github.com/GoogleCloudPlatform/nodejs-docs-samples/blob/master/appengine/cloudsql_postgresql/app.flexible.yaml

    The INSTANCE_CONNECTION_NAME environment variable does not include the DATABASE_NAME as a parameter.

    e.g. my-awesome-project:us-central1:my-cloud-sql-instance

    Maybe this could be causing that the name of the instance is not being resolved for the Proxy.