Unable to connect Google App Engine to CloudSQL Postgres (via SQLAlchemy) in cloud shell

Question

Running a small web app on Google App Engine (Flexible) using Python 3, Flask, SQLAlchemy, the psycopg2-binary package, and CloudSQL Postgres. The app connects to CloudSQL properly in my local dev environment (I use the proxy), but it won't seem to connect when deployed in the cloud.

Worked fine the first time I deployed it last week. After pushing an update and re-deploying, the app no longer connects to CloudSQL in the cloud shell or when deployed -- even after rolling back to that previous commit. But it connects just fine on my local dev machine using the proxy.

I've verified through debugging that the necessary credentials are being properly inserted in my SQLALCHEMY_DATABASE_URI, which is: postgres+psycopg2://[user]:[pw]@/[db_name]?host=/cloudsql/breadsheet:us-west1:breadsheet.

Only change I've made on the platform since the initial deploy was upgrading from Cloud Datastore to Firestore, which is where I'm pulling my environment variables from to build the URI. No data ever came from Datastore, so that shouldn't matter.

I'm following the GAE Postgres connection guide for Unix. Here's my app.yaml:

runtime: python
env: flex

instance_class: F1

entrypoint: gunicorn -w 1 breadsheet:breadapp

error_handlers:
  - file: app/templates/errors/default_error.html

  - error_code: over_quota
    file: app/templates/errors/over_quota.html

beta_settings:
  cloud_sql_instances: breadsheet:us-west1:breadsheet

Here's the error when I run gunicorn from the cloud shell:

(venv) [me]@cloudshell:~/breadsheet (breadsheet)$ gunicorn -w 1 breadsheet:breadapp
[2019-04-07 10:23:16 -0700] [471] [INFO] Starting gunicorn 19.9.0
[2019-04-07 10:23:16 -0700] [471] [INFO] Listening at: http://127.0.0.1:8000 (471)
[2019-04-07 10:23:16 -0700] [471] [INFO] Using worker: sync
[2019-04-07 10:23:16 -0700] [474] [INFO] Booting worker with pid: 474

2019-04-07 09:40:08,838 Exception on / [GET]
Traceback (most recent call last):
[...]
  File "/home/[me]/breadsheet/venv/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 437, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/home/[me]/breadsheet/venv/lib/python3.5/site-packages/psycopg2/__init__.py", line 130, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/cloudsql/breadsheet:us-west1:breadsheet/.s.PGSQL.5432"?


The above exception was the direct cause of the following exception:
[line of code with the first database call in my app]
[...]
  File "/home/[me]/breadsheet/venv/lib/python3.5/site-packages/psycopg2/__init__.py", line 130, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/cloudsql/breadsheet:us-west1:breadsheet/.s.PGSQL.5432"?

Tried the following (and more!) to no avail:

• Whitelisting various IPs in CloudSQL management
• Adjusting IAM permissions
• Using nano to comment out the Firestore code and hard-code the URI
• Using nano to hard-code the SQLALCHEMY_DATABASE_URI under env_variables in my app.yaml

What to try next?

Answer 1

For those reading this via search in the future, I had previously skipped the export SQLALCHEMY_DATABASE_URI=postgresql+psycopg2://[USER]:[PW]@127.0.0.1:5432/[DB_NAME] because I didn't particularly care about the cloud shell environment and assumed it wouldn't make a difference for deployment. Apparently it does.

Later, when troubleshooting the db connection in cloud shell, I found myself skipping the first part (sudo mkdir /cloudsql;) of this two-step command sudo mkdir /cloudsql; sudo chmod 777 /cloudsql because I had already created the /cloudsql folder. I'm still not sure why both parts of the command are needed -- especially when the folder already exists -- but the db connection didn't work for me without them both.

One more note: setting SQLALCHEMY_DATABASE_URI under env_variables: in the app.yaml is not required, provided you set that value elsewhere in the app. Adding to the env_variables: simply makes the value available as an easily-accessible variable.