Am I doing Google Cloud deployment wrong?

I've previously used Heroku and AWS and am now setting up a service on the Google Cloud platform using App Engine and Cloud SQL (Postgres).

We've tried to build the app using 12 factor principles.

The setup has been proving so tedious that I'm starting to question if I've just fundamentally missed something.

Here's the things that just have me confused:

1. Being encouraged to write passwords into a file that ends up in source control (app.yaml).
2. Needing to hack a workaround for loading environment variables (if I don't want them committed to source control).
3. Finding that to connect to the cloud sql instance I need a line referring to the specific instance in app.yaml - so now I need app.staging.yaml and app.production.yaml ?
4. Finding that line only seems to support 1 DB instance and unclear if there is support if we want the app to connect to 2 DBs.

Have I missed some major development in server admin where these have become best practices? Having just discovered #3 & #4, I'm really starting to think I must've done something fundamentally wrong in my set up. Have I?

I haven't specifically attempted to configured a application the way you are describing but I know those are not the takeaways that Google intends. To address some specifics:

1. Connecting to Cloud SQL specifically says the following to suggest storing passwords is not what they intend and provide a separate solution for secrets.

   # Remember - storing secrets in plaintext is potentially unsafe. Consider using
   # something like https://cloud.google.com/secret-manager/docs/overview to help keep
   # secrets secret.
   

2. Based on this article, Google App engine does not support environment variables in app.yaml (Comments on similar issues with Google App Engine). My fix for a similar problem was the following code:

   #!/bin/bash
   # deploy-helper.sh
   
   prepare_yaml() {
       [ -z "$1" ] && echo "ERROR: template.yaml filename must be provided." && exit 1
   
       template="${1}"         # ARG #1 : filename of template yaml
       finalYAML=$(mktemp)     # make temporary file
   
       generated_stdin_cmds="$(echo "cat <<EOF >\"$finalYAML\""; cat $template; echo EOF;)"
       source /dev/stdin <<< "$generated_stdin_cmds"
       echo "$finalYAML";      # return filepath of filled-in file
       return 0
   }
   
   # Set environment variables 
   TYPE="prod"                     # in script file
   source env/production.vars      # read them in from a file
   
   DEPLOYMENT_FILE="app.flexible.yaml"
   tmpfile="$(prepare_yaml "app.tpl.yaml")"
   if [ $? -eq 0 ]; then
       mv "$tmpfile" "$PWD/$DEPLOYMENT_FILE"   # gcloud wants specific filename
       gcloud app deploy "$DEPLOYMENT_FILE"
       rm "$DEPLOYMENT_FILE"                   # Cleanup temporary file
   fi
   

   NOTE: This script can also be written in python by using the subprocess library. This is how I automated the build for my GKE project.

   Next, Take your application app.yaml file and add bash variable string replacements like this example app-tpl.flexible.yaml:

   # example app-tpl.flexible.yaml
   ---
   runtime: custom
   env: flex
   env_variables:
     MYSQL_SOCK: "/cloudsql/${project}:${region}:${instance}"
     MYSQL_DB: "${db_name}"
     MYSQL_USER: "${db_user}"
     MYSQL_PASSWORD: "${db_pw}"
   

   Make file containing of the necessary variables (ignored in source control)

   #!/bin/bash
   # FILE: env/production.vars
   
   project="project-name"
   region="europe-west1"
   instance="prod001"
   db_name="db001"
   db_user="root"
   db_pw="qwerty"
   

3. This should be solved by answer #2. Add a flag/option on the script to execute for swapping the SQL instance or change the environment variable name.

4. Looks like you will need to configure two CloudSQL connection instances and then the automatic CloudSQL Proxy will be able to connect to 2 databases. See cloud.google.com/sql/docs/mysql/connect-app-engine-flexible and this answer since they got it working with different port numbers.

Good Luck!

@codejedi's answer above (use Google Secrets Manager or inject them during deployment) is what I'd go with today.

At the time I posted this question (April 2018), Google Secrets Manager wouldn't be released for another 18 months.

I ended up rolling my own solution at the time using Datastore.