How to access Cloud SQL from Google Colab

Question

I have a Google Cloud SQL instance with a public IP, only accessible to whitelisted IP and through an SSL connection.

I'd like to know how I can connect to this database from Google Colab with Python.

If I try to connect like any external application, the connection is refused since the ip of the "client" is not whitelisted (and I can't whitelist it since I don't it and it's highly probable it's volatile)

Is there a shortcut, like with Google App Engine to connect to the database using its instance and a google client?

Thanks

Answer 1

A little late to answer, but I think I have a solution and it involved using the Cloud SQL Proxy. Overall, you first need to use the Gcloud SDK (included with Colab) to authenticate, then install the proxy, then spin it up. I did this in two blocks

# gcloud login and check the DB
!gcloud auth login
!gcloud config set project [YOUR PROJECT ID]
!gcloud sql instances describe [YOUR CLOUDSQL INSTANCE ID]

This last line will output a dump of info and we want connectionName in particular. The next block then downloads the proxy and tells it to proxy for that CloudSQL instance:

# download and initialize the psql proxy
!wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O cloud_sql_proxy
!chmod +x cloud_sql_proxy
# "connectionName" is from the previous block
!nohup ./cloud_sql_proxy -instances="[connectionName]"=tcp:5432 &
!sleep 30s

Later on you can (and I've found it helpful) to check the proxy's logs with

!cat nohup.out

And finally, you can construct a connection with the address 127.0.0.1:5432 (or whatever port you set above. I did so with psycopg2 like this

conn = psycopg2.connect(
    host='127.0.0.1', port='5432', database=[YOUR DB NAME],
    user=[USERNAME], password=[PASSWORD])

It seems to work, though it's definitely a bit slower than a direct connection.