01-database

01-database.md

Create Cloud SQL DB instance

gcloud sql instances create commerce-sql-00 --database-version=POSTGRES_12 --tier db-f1-micro --region europe-west2

Set Postgres superuser password

gcloud sql users set-password postgres --instance=commerce-sql-00 --prompt-for-password

Get instance connection name

gcloud sql instances describe commerce-sql-00 --format="value(connectionName)"

Start the Cloud SQL proxy

cloud_sql_proxy -instances=$INSTANCE_CONNECTION_NAME=tcp:5432

Connect to Postgres

psql -h 127.0.0.1 -p 5432 -U postgres

Bootstrap Script

Save the following script as a file, with whatever name

#!/bin/bash

if [[ -z $REPO|| -z $BRANCH ]]; then
  echo 'REPO and/or BRANCH empty, exiting'
  exit 1
fi

gen_secret () { openssl rand -base64 24 | tr -d '+/='; }

SQL_INSTANCE=${SQL_INSTANCE:-commerce-sql-00}
INSTANCE_CONNECTION_NAME=${INSTANCE_CONNECTION_NAME:-`gcloud sql instances describe "$SQL_INSTANCE" --format="value(connectionName)"`}

if [[ -z $INSTANCE_CONNECTION_NAME ]]; then
  echo 'INSTANCE_CONNECTION_NAME empty, exiting'
  exit 1
fi


cloud_sql_proxy -instances=$INSTANCE_CONNECTION_NAME=tcp:5432 &>/dev/null & SQL_PROXY_PID="$!"

DATABASE_NAME="${REPO//-/_}_${BRANCH}"
DATABASE_PASSWORD=`gen_secret`

cat << EOF | gcloud secrets create "$REPO-$BRANCH-env" "--data-file=-"
INSTANCE_CONNECTION_NAME=$INSTANCE_CONNECTION_NAME
DATABASE_NAME=$DATABASE_NAME
DATABASE_USERNAME=$DATABASE_NAME
DATABASE_PASSWORD=$DATABASE_PASSWORD
ADMIN_JWT_SECRET=`gen_secret`
JWT_SECRET=`gen_secret`
EOF

cat << EOF | psql -h 127.0.0.1 -p 5432 -U postgres -f -
CREATE DATABASE $DATABASE_NAME;
CREATE ROLE $DATABASE_NAME;
GRANT ALL PRIVILEGES ON DATABASE $DATABASE_NAME TO $DATABASE_NAME;
ALTER ROLE $DATABASE_NAME LOGIN;
ALTER ROLE $DATABASE_NAME PASSWORD '$DATABASE_PASSWORD';
EOF


kill $SQL_PROXY_PID

echo "Done!"

Give permissions to execute with chmod e.g. chmod +x filename.sh

This script can be invoked like so:

REPO=repo-name BRANCH=master ./filename.sh

This will:

1. Execute the SQL to create a database and a role in Postgres
2. Create a secret in Secret Manager with the env file to be consumed by the relevant codebase (e.g. some Strapi or Hasura project to be deployed, see cloudbuild.yaml in magdairis-strapi as an example).