My app is a Flask web server that has a postgresql backend. Up to now, I have been running setting up the DB on the same compute engine the server is on. I am transitioning my database from the compute engine to a dedicated DB server through Google Cloud SQL. My flask web app would ideally be running on Google Cloud Run.
I have been having trouble finding any instructions on the web with regards to setting up a connection using psycopg2 only, without using SQLAlchemy. My app isn't built using SQLAlchemy and would hate to have to transition it over if there is a simple solution to my problem.
I have tried the below but have not been able to connect to the database at all:
connectionString = 'dbname=%s user=%s password=%s host=%s' % (db_name, db_username, user_pwd, public_IP)
conn = psycopg2.connect(connectionString)
or
connectionString = 'dbname=%s user=%s password=%s host=%s' % (db_name, db_username, user_pwd, '/cloudsql/{}'.format("my-project-id:us-central1:my-db-name"))
conn = psycopg2.connect(connectionString)
Neither case was able to connect to Cloud SQL. The code where I use the public IP would seemingly load for a while but wouldn't connect.
Is this possible to do with Cloud SQL? If not are there any alternatives that would allow this to be done?
The second example you showed will work for connecting as long as you have the Cloud SQL Proxy deployed via Unix socket.
To test it the application locally you would install the Proxy and then run the following:
./cloud-sql-proxy --unix-socket /cloudsql <PROJECT_ID>:<INSTANCE_REGION>:<INSTANCE_NAME> &
To run the Cloud SQL Proxy with Cloud Run you can use the --add-cloudsql-instances if you are deploying via gcloud:
gcloud run deploy <SERVICE_NAME> \
--add-cloudsql-instances <PROJECT_ID>:<INSTANCE_REGION>:<INSTANCE_NAME>
Otherwise if you are using the Cloud Console you can just add a "Cloud SQL Connection" to your Cloud Run service and the Unix socket connection should work.