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?

Businesswoman

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

Paratroops

For whatever reason this didn't work for me and I'm also facing the OP's issue. – Sipple

Works for me, make sure not to add '.s.PGSQL.5432' at the end of the connection string only the directory it is in – Septate

I think you need postgresql instead of postgres currently (2021). – Brianbriana

Even the linked documentation doesn't say explicitly where to put the port, trial and error for user "fred" connecting to db "big_db" via socket in "/tmp/big_db_sockets/.s.PGSQL.1234" seems to be: postgresql://fred@:1234/big_db?host=/tmp/big_db_sockets – Shopworn

what is troubling is that the official docs show a unix_sock query param that causes pyscopg2 to crash... => cloud.google.com/sql/docs/postgres/connect-run#connect_to so I'm really glad I came across this thread ! – Dimitris

The GCP documentation in terms of connecting to SQL instance gave one of the toughest time in my life. It's even worse than Docker docs. unix_sock is super misleading. Give us that Bard AI instead of docs :v – Dubiety

Note: if your socket has a different port, like .s.PGSQL.5433 instead of .s.PGSQL.5432, add ?port=5433. – Cornelia