24

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,426
  • 4
  • 33
  • 60

1 Answers1

21

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
  • 10,388
  • 3
  • 23
  • 41
  • 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
  • 3
    what is troubling is that the official docs show a `unix_sock` query param that causes pyscopg2 to crash... => https://cloud.google.com/sql/docs/postgres/connect-run#connect_to so I'm really glad I came across this thread ! – yactouat Aug 11 '22 at 21:59
  • 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 – Qback Mar 02 '23 at 22:34
  • Note: if your socket has a different port, like `.s.PGSQL.5433` instead of `.s.PGSQL.5432`, add `?port=5433`. – bfontaine Aug 29 '23 at 17:01