Caveat

I’m not a Google Cloud Platform expert, and this isn’t a tutorial. I’m just hoping that if people run into the same errors I ran into and search for those exact error messages, that they’ll find this blog post, and maybe it’ll be helpful to them.

What I found

Making the connection

Google’s documentation on connecting a Cloud Function to Cloud SQL isn’t super straightforward. When I tried to implement their examples, I came across all sorts of errors like:

sqlalchemy.exc.InterfaceError: (pg8000.exceptions.InterfaceError) communication error
(Background on this error at: https://sqlalche.me/e/20/rvf5)

or

File "/workspace/main.py", line 58, in postgres_connect sqlalchemy.engine.url.URL.create(AttributeError: type object 'URL' has no attribute 'create'"

I oddly found that using cloud-sql-proxy would allow the “Cloud” Function to run properly locally (even without a host specified—I guess because the assumption is that the host is 127.0.0.1, and that’s what the cloud-sql-proxy connects you to?).

I ran into issues connecting with Unix sockets (and it wasn’t because the socket path length was longer than 108 characters, which the documentation already warns about).

Ultimately, (at the urging of a colleague) I ditched trying to use Unix connectors, and I went with the Cloud SQL connector approach outlined in Connect from Cloud Functions, and that worked.

The requirements.txt file needed:

cloud-sql-python-connector
pg8000
SQLAlchemy

Using the connection

I didn’t know a ton about SQL Alchemy, so I had to do a bit of searching around and trial-and-error to get a useful connection.

So, first of all, you get the pool from the code Google provides you. What do you do with the pool?

I assigned it to a variable called engine, but then I had to run conn = engine.connect() in order to make the connection useful.

Even then, it wasn’t exactly clear how to make an actual SQL query. If you have a SQL query (e.g., SELECT field1, field2, field3 FROM table WHERE somecriteria = somevalue;) and put that into a variable like query, you can’t just run conn.execute(query) on your query, because it will complain about it not being able to figure out the text. So you actually have to use query = sqlalchemy.text(original_query) before you can run something like output = connect.execute(query). After that, the output should be an easy list to iterate through.