Google's Cloud SQL Auth Proxy [3] allows you to set up a connection to a GCP SQL server from wherever you are via a secured tunnel. You can then point your application to a local address SQL proxy listens on and use it like local db. At least the DB "feel" like local.
The same proxy is used in many GCP services like Google Cloud Run and GKE (if you configure it as a sidecar).
Running SQL proxy is pretty easy (example is for pre 2.0.0 version):
./cloud_sql_proxy -instances=my-gcp-project:europe-north1:mydbinstancename=tcp:127.0.0.1:20000
Where
-
my-gcp-project:europe-north1:mydbinstancenameis the "connection" name of your managed SQL server. - tcp: directive tells the proxy where to listen locally
Now the trick is that when you run your proxy on your dev machine, or in a Docker compose for local development, you are fine with TCP. Yet, when you use Cloud Run, it expects you to connect to a UNIX socket, typically residing in this path:
/cloudsql/my-gcp-project:europe-north1:mydbinstancename/.s.PGSQL.5432
This is pretty different than our TCP connection - would that mean we have to have a sepoarate Django config for local development and deployment? Not necessarily.
If you look at a typical Django (postgres in this case) settings, you will notice it seems like it is expecting TCP connections. However, by convention when HOST starts with / the engine will treat it as a UNIX socket. One thing you need to remember is to set the PORT to empty value.
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.postgresql_psycopg2',
'NAME': os.environ.get('DB_NAME', 'some db '),
'USER': os.environ.get('DB_USER', 'my user'),
'PASSWORD': os.environ.get('DB_PASS'),
'HOST': os.environ.get('DB_HOST', '127.0.0.1'),
'PORT': os.environ.get('DB_PORT', '20000'),
},
}
In Cloud Run what you need to do is to set an enviromental variable DB_HOST to /cloudsql/my-gcp-project:europe-north1:mydbinstancename
This is it. Now locally you use SQL connection with TCP and with UNIX on Cloud Run. All just by changing env variables just as the Twelve Factor App [1] pattern teaches us to:)
Top comments (0)