Google Cloud SQL — Tips & Tricks

Google Cloud SQL, PostgreSQL Edition

When creating a new Cloud SQL instance…

https://console.cloud.google.com/sql/

When creating a database…

Connecting to a Cloud SQL instance from Node.js

# PostgreSQL
# https://www.postgresql.org/docs/current/static/libpq-envars.html
PGHOST=104.197.10.130
PGPORT=5432
PGUSER=postgres
PGPASSWORD=~E7Rf][k*P/$9.%F
PGDATABASE=example_dev
PGSSLMODE=verify-ca
PGSSLCERT=./ssl/client-cert.dev.pem
PGSSLKEY=./ssl/client-key.dev.pem
PGSSLROOTCERT=./ssl/server-ca.dev.pem
PGSERVERNAME=example-dev:pg12
PGHOST=/cloudsql/example-dev:us-central1:pg12
PGUSER=postgres
PGPASSWORD=~E7Rf][k*P/$9.%F
PGDATABASE=example_dev
Error [ERR_TLS_CERT_ALTNAME_INVALID]: Hostname/IP does not match certificate's altnames: Host: localhost. is not cert's CN: example-dev:pg12

Switching between environments…

$ yarn start                   # Connects to `example_dev` (default)
$ yarn start --env=local # Connects to the local database
$ yarn start --env=test # Connects to the test (QA) database
$ yarn db:migrate --env=local # Migrates local database
$ yarn db:migrate --env=test # Migrates test (QA) database

Storing db passwords…

.env.local           # Settings for local database
.env.dev # Settings for dev (shared) database
.env.test # Settings for test (QA) database
.env.prod # Settings for production database
.env.prod.override # Overrides for prod environment
import dotenv from "dotenv";
import minimist from "minimist";
const { env } = minimist(process.argv.slice(2), {
default: { env: "dev" }
});
dotenv.config({ path: `.env.${env}.override` });
dotenv.config({ path: `.env.${env}` });
yarn env:set {name} {secret}     # Encrypt and save to .env.{env}
yarn env:get {name} # Read from .env.{env} and decrypt

Using Cloud SQL Proxy…

Using PostgreSQL CLI tools…

$ brew install libpq

Creating backups…

Bringing the technical edge to early-stage companies with advice on software architecture, best practices, database design, web infrastructure, and DevOps.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store