17

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?

Martinffx
  • 2,346
  • 4
  • 30
  • 59

1 Answers1

10

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

match
  • 8,748
  • 2
  • 21
  • 38
  • 2
    For whatever reason this didn't work for me and I'm also facing the OP's issue. – Jason R Stevens CFA Jan 22 '20 at 06:25
  • 2
    Works for me, make sure not to add '.s.PGSQL.5432' at the end of the connection string only the directory it is in – Marius Dec 14 '20 at 20:56
  • I think you need `postgresql` instead of `postgres` currently (2021). – igorkf Aug 09 '21 at 14:07
  • 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 – Trevor Taylor Sep 17 '21 at 04:42