Cloud SQL proxy in App Engine PHP

[sl0wik]

Because database migration is a step required to deploy majority of modern PHP apps, App Engine should have /cloudsql folder with socket connections to binded to Cloud SQL server the same way App Engine does. Without access to Cloud SQL it is impossible to deploy app without a CI script.

[sl0wik]

I'm working on a possible solution and I'm wondering if the source of container running App Engine (PHP) is publicly available? Perhaps this functionality can be turned into a buildpack.

[spew]

Feature request: Add a CloudSQLProxy buildpack to the AppEngine order groups which will install and run the SQL Proxy at build time, but not launch time. The buildpack would opt in when an appropriate env var is set in the app.yaml.

The proxy would be started with a command such as follows:

cloud_sql_proxy -enable_iam_login -dir=/layers/google.cloudsql.proxy/connections

Note there are some complications around where we must set the default gcloud project when using automatic instance discovery, see documentation here: https://cloud.google.com/sql/docs/mysql/sql-proxy#instances-options. It may be that the default gcloud project is set on Cloud Build workers already.

[sl0wik]

-enable_iam_login is not required anymore and should be dropped. -enable_iam_login might result in errors on MySQL (https://github.com/GoogleCloudPlatform/cloud-sql-proxy/issues/1436).

[spew]

Is another flag needed to provide equivelant functionaltiy?

[sl0wik]

MySQL IAM works without this flag, but , in the case of PostgreSQL solution seems to be hacky. According to https://github.com/GoogleCloudPlatform/cloud-sql-proxy/issues/1436#issuecomment-1256681991 it's recommended to run two proxies or wait for v2. I would probably try to reach the App Engine team and do the same solution they did, or implement it without -enable_iam_login flag with some option to set it manually on build. It's unclear why -enable_iam_login started throwing errors with MySQL connection. I was trying to find PR causing it but couldn't find anything.

[iamacarpet]

@sl0wik did you ever get your fix sorted for this?

We had a similar problem running migrations locally, and work around with these entries in ~/.bash_aliases:

alias phpdockercreatefolders='mkdir -p ~/.config ~/.composer ~/.cache/composer ~/cloudsql && touch ~/.git-credentials ~/.gitconfig'

alias cloud_sql_proxy='phpdockercreatefolders && docker run -it -v ~/.config:/.config -v ~/cloudsql:/cloudsql -w / --user $(id -u):$(id -g) gcr.io/cloud-sql-connectors/cloud-sql-proxy:2.4.0 --credentials-file /.config/gcloud/application_default_credentials.json --unix-socket /cloudsql'

alias php81='phpdockercreatefolders && docker run -it -v "$PWD":/usr/src/app -v ~/.config:/.config -v ~/.git-credentials:/.git-credentials -v ~/.gitconfig:/.gitconfig -v ~/.composer:/.composer -v ~/.cache/composer:/.cache/composer -v ~/cloudsql:/cloudsql -w /usr/src/app --user $(id -u):$(id -g) gcr.io/gae-runtimes/buildpacks/php81/builder:latest php'
alias php81composer='phpdockercreatefolders && docker run -it -v "$PWD":/usr/src/app -v ~/.config:/.config -v ~/.git-credentials:/.git-credentials -v ~/.gitconfig:/.gitconfig -v ~/.composer:/.composer -v ~/.cache/composer:/.cache/composer -v ~/cloudsql:/cloudsql -w /usr/src/app --user $(id -u):$(id -g) gcr.io/gae-runtimes/buildpacks/php81/builder:latest composer'
alias php81fix='phpdockercreatefolders && docker run -it -v "$PWD":/usr/src/app -w /usr/src/app --user $(id -u):$(id -g) gcr.io/gae-runtimes/buildpacks/php81/builder:latest make fix'
alias php81artisan='phpdockercreatefolders && docker run -it -v "$PWD":/usr/src/app -v ~/cloudsql:/cloudsql -w /usr/src/app --user $(id -u):$(id -g) gcr.io/gae-runtimes/buildpacks/php81/builder:latest php artisan'
alias php81test='phpdockercreatefolders && docker run --network=host -v "$PWD":/usr/src/app -v ~/cloudsql:/cloudsql -w /usr/src/app --user $(id -u):$(id -g) gcr.io/gae-runtimes/buildpacks/php81/builder:latest php artisan test'

It meant running cloud_sql_proxy in one shell, and your php (migration) command in another, but it worked with existing published containers.

Is this similar to what you ended up doing?

I would probably try to reach the App Engine team and do the same solution they did

You mention this above in the context of the MySQL IAM flag: I was under the impression that Cloud SQL IAM auth in general wasn't supported on App Engine or Cloud Run. Have you got any information to suggest otherwise?

[sl0wik]

Unless something changed, MySQL IAM is working on App Engine. It was tricky and docs are inconsistent but this snippet worked: https://github.com/firevel/firevel/pull/34/files

In ref to migration. You can do a double build (the first build run migration, the other one does deploy), or run migration using https://packagist.org/packages/firevel/artisan . For the first approach, you can check https://github.com/firevel/gcp-serverless-exec-wrapper, its the best way to go if you use Cloud Run.

Second approach could be: 1) Deploy with --no-promote flag. 2) Run migration using firevel/artisan. 3) Promote version.

I know this serverless artisan approach seems odd but at the end this seems to be the most reliable way to remotely execute code in App Engine.