Deploying to GCP

aka Google Cloud Platform (App Engine)

This post is a work in progress. Please see #161 for notes.

PostGraphile CLI and CloudSQL

Section author: @redaikidoka

Deploying PostGraphile with nothing more than command-line arguments to the cloud to serve between PostgreSQL hosted in Google Cloud SQL and an Angular App hosted in Google App Engine.

Need to use cloud_sql_instances to connect to the PostgreSQL instance.

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

Example deployment file app.yaml:

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_settings, 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 the area of the Cloud SQL interface titled "Connect to this instance"

Create a graphile.config.mjs alongside your package.json so PostGraphile can load the configuration automatically:

import { PostGraphileAmberPreset } from "postgraphile/presets/amber";
import { makePgService } from "postgraphile/adaptors/pg";

export default {
  extends: [PostGraphileAmberPreset],
  pgServices: [makePgService({ connectionString: process.env.DATABASE_URL })],
  grafserv: {
    host: "0.0.0.0",
    port: 8080,
    graphqlPath: "/",
    // Quick hack for development; use a proper CORS policy in production.
    // dangerouslyAllowAllCORSRequests: true,
  },
};

In package.json specify postgraphile, some project details, and the start script. E.g.:

{
  "name": "myprojectname",
  "version": "1.0.0",
  "scripts": {
    "start": "postgraphile"
  },
  "engines": {
    "node": ">=24"
  },
  "license": "ISC",
  "dependencies": {
    "postgraphile": "^5.0.0"
  }
}

The project will end up at https://[project-name].appspot.com/

• Your GraphQL endpoint will be that URL.
• You can access Ruru at https://[project-name].appspot.com/graphiql

In graphile.config.mjs, the settings are:

• grafserv.host: "0.0.0.0" allows GAE's nginx to successfully bind to the service
• grafserv.port: 8080 binds to port 8080, which is a special port number that Google cloud will automatically expose via the service name, so you can access your PostGraphile service directly at https://[project-name].appspot.com/
• grafserv.graphqlPath: "/" puts the GraphQL endpoint at the root / (rather than /graphql as is the default)

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.

Deploying an express app

Section author: @garcianavalon

GCP configuration:

runtime: nodejs
env: flex

env_variables:
  PGUSER: "your-database-user"
  PGHOST: "/cloudsql/your-cloudsql-instance-connection-string"
  PGPASSWORD: "your-password"
  PGDATABASE: "your-database-name"

beta_settings:
  cloud_sql_instances: your-cloudsql-instance-connection-string

1. You will need flexible environment for websocket support (subscriptions). If you are not interested in real-time features you can use standard environment and save some bucks. In that case, remove the beta_settings section
2. This requires using postgraphile as a library. Minimum setup would be something like:

/project
|--package.json
|--/src
   |--index.js

Create the following files:

package.json

{
  "scripts": {
    "start": "node src/index.mjs"
  }
}

src/graphile.config.mjs

import { PostGraphileAmberPreset } from "postgraphile/presets/amber";
import { makePgService } from "postgraphile/adaptors/pg";

export default {
  extends: [PostGraphileAmberPreset],
  pgServices: [
    makePgService({
      connectionString: process.env.DATABASE_URL,
    }),
  ],
  grafserv: {
    host: "0.0.0.0",
    port: 8080,
  },
};

src/index.mjs

import express from "express";
import preset from "./graphile.config.mjs";
import { postgraphile } from "postgraphile";
import { grafserv } from "postgraphile/grafserv/express/v4";

const app = express();

const pgl = postgraphile(preset);
const serv = pgl.createServ(grafserv);

await serv.addTo(app);

app.listen(8080);

PostgreSQL authorization with Google Cloud SQL

The postgres user on Google Cloud SQL is not a superuser, unlike the Postgres user account you've likely been using in development. As such, if you need it to be able to switch into a different role then you must grant that role to the postgres user. For example, if you created the role anonymous in your database, and you want the postgres role to be able to perform SET LOCAL role TO anonymous; then you could run:

GRANT anonymous TO postgres;

Helpful resources

See https://github.com/GoogleCloudPlatform/nodejs-docs-samples/tree/master/appengine/hello-world/flexible for example Node.js project on GCP.

See information about configuring port forwarding: https://cloud.google.com/appengine/docs/flexible/custom-runtimes/configuring-your-app-with-app-yaml