Connect simple Node app in Google App Engine Flexible to Google Cloud SQL

Question

I'm using postGraphile as a GraphQL server - works great locally.

Trying to push it into an App Engine instance.

Cannot for the life of me figure out how to get the App Engine to connect to Cloud SQL. I can direct connect to Cloud SQL from my computer (I'm white-listed), and I even tried whilelisting the IP of the app engine instance to no effect

Here's my app.yaml settings:

# per google's instructions, I'vd added the instance here, and now added a tcp port
beta_settings:
  cloud_sql_instances: webstr-dev-237715:us-central1:webstr-dev=tcp:5432

# [START runtime]
runtime: nodejs
env: flex
threadsafe: yes
service: wgraphile

In my package.json, I run postgragphile with a connection parameter. If I run this connection string:

postgraphile -o -c postgres://webstr:[SECRET]@localhost:5432/str_dev

I get a connection refused error:

A serious error occurred when building the initial schema. Exiting because `retryOnInitFail` is not set. Error details:

Error: connect ECONNREFUSED 127.0.0.1:5432
    at TCPConnectWrap.afterConnect [as oncomplete] (net.js:1106:14)

If I run it with this connection string:

postgraphile -o -c postgres://webstr:[SECRET]@172.17.0.1:5432/str_dev

I get this connection reset error:

Postgres connection: postgres://webstr:[SECRET]@172.17.0.1/str_dev
  ‣ Postgres schema(s):  public


A serious error occurred when building the initial schema. Exiting because `retryOnInitFail` is not set. Error details:

Error: read ECONNRESET
    at TCP.onStreamRead (internal/stream_base_commons.js:111:27)

---

I have also tried using a unix socket connection string with the direct IP like:

postgres://webstr:[SECRET]@35.202.32.69:5432/str_dev?unix_sock=/cloudsql/<cloud_sql_instance_name>/.s.PGSQL.5432

Help?

Answer 1

It looks like you are using App Engine Flex. If you check out the Connecting from App Engine page, you need to check the following:

1. You need to make sure that the Cloud SQL Admin API is enabled
2. You need to make sure the App Engine service account (default is [YOUR_PROJECT_NUMBER]@gae-api-prod.google.com.iam.gserviceaccount.com) has the Cloud SQL Client IAM permission

Then, your app.yml needs the following (which is looks like you are doing):

beta_settings:
  cloud_sql_instances: <INSTANCE_CONNECTION_NAME>=tcp:<PORT>

Finally, once you've done that, you should be able to connect at 172.17.0.1:<PORT>.

Answer 2

All righty, well!

I just wanted to deploy postGraphile with nothing more than command-line arguments to the cloud to serve between my psql hosted in Google Cloud SQL and an Angular App hosted in Google App Engine.

In the past, I've used Google App Engine to host middle-tiers. After MUCH struggle, I figured out that I need to use cloud_sql_instances to connect to the psql instance, and that I needed to set the host and port in the command line.

Make sure you've got a project with your Cloud SQL postgres database, with the Google Cloud Admin SQL turned on, and App Engine turned on. Reserve the default service in App Engine for whatever your front-end is.

Here's my app.yaml, the deployment file:

beta_settings:
  cloud_sql_instances: webstr-dev-######:us-central1:webstr-dev=tcp:5432

# [START runtime]
runtime: nodejs
env: flex
threadsafe: yes
service: wgraphile

manual_scaling:
  instances: 1
resources:
 cpu: .5
 memory_gb: .5
 disk_size_gb: 10

health_check:
  enable_health_check: False

# [END runtime]


handlers:
  - url: /(.*)
    static_files: ./\1
    upload: ./(.*)

#  settings to keep gcloud from uploading files not required for deployment
skip_files:
  - ^node_modules$
  - ^README\..*
  - ^package-lock.json
  - \.gitignore
  - \.es*
  - ^\.git$
  - ^errors\.log

under beta_set

• tings, cloud_sql_instances: webstr-dev-######:us-central1:webstr-dev=tcp:5432 tells us that we are opening a unix pipe to a cloud instance in the gcp project in this case 'webstr-dev-######' in central region 1, connecting to cloud sql instance websr-dev.
• the =tcp:5432 maps that unix socket to tcp port 5432.
• I couldn't get using the unix port directly working, which is why the tcp port piece is in there
• You can get the full instance name from your Cloud SQL Instance in this area of the Cloud SQL interface:

In my package.json, I specify postgraphile, and some project details, and the start script. For short:

{
  "name": "myprojectname",
  "version": "1.0.0",
  "scripts": {
    "start": "postgraphile --port 8080 -o --watch --enhance-graphiql -o -q / --host 0.0.0.0 -c postgres://user:[email protected]:5432/str_dev",

  },
  "engines": {
    "node": "^10.15",
    "npm": "^6.9"
  },
  "license": "ISC",
  "dependencies": {
    "postgraphile": "^4.4.3"
  }
}

The project will end up at https://[project-name].appspot.com/ - Your graphql endpoint will be that url. - You can access grap

hiql at https://[project-name].appspot.com/graphiql

Regarding the start command, the saving graces are: - --port 8080 Google cloud will map port 8080 to the name of the service you are creating, so you won't need to specify :5000 like we do on a local postgraphile, just the end point url https://[project-name].appspot.com/ - -q / switch puts the graphql endpoint at the name of the service itself - --host 0.0.0.0 allows GAE's nginx to successfully bind to the service - -o circumvents annoying CORS nonsense - --watch lets postgraphile keep up with your database changes

DEPLOYING: I deployed the project with gcloud. I used gcloud init to setup my connection to my google cloud project. Then I used gcloud app deploy in this directory to push it up.