Connect to a database over a unix socket using SQLAlchemy

Question

I'm trying to connect to my Cloud SQL DB using SQLAlchemy from my cloud function but I can't seem to work out the correct connection string.

DATABASE_URL=postgres://$DB_USER:$_DB_PWD@/$DB_NAME?unix_socket=/cloudsql/$DB_INSTANCE

Which gives me the error:

pyscopg2.ProgrammingError: invalid dns: invalid connection option "unix_socket"

What is the correct way to connect to a Postgresql 9.6 DB over a unix socket using pyscopg2?

Answer 1

The special keyword needed here is host:

DATABASE_URL=postgres://user:password@/dbname?host=/path/to/db

Note that the path in host should be a path, not the socket file itself (psycopg2 assumes the socket has the standard naming convention .s.PGSQL.5432)

https://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#unix-domain-connections