Connecting to GCP Cloud SQL and the path Config Option

[jenewland1999]

Hello 👋🏻

I'm a new user of the postgres package and have spent the last few days debugging a connectivity issue which I resolved after stumbling upon issue #484.

Upon reading the thread, I couldn't help but notice that, while a workaround was provided, the issue wasn't necessarily "resolved", and as such I'd like to follow it up with a more suitable fix. This could either be a change to the code itself or the docs. I believe either would work and hopefully help future users who are using postgres with GCP.

The docs state that path should be used to specify socket connections and host would be for the IP address of database itself. However, as noted in the issue above this isn't exactly the case for GCP Cloud SQL.

@porsager posted #484 (comment) in the previous issue and was never answered so I thought I'd take a moment to provide one.

That would seem to suggest that the actual path is then /cloudsql/project-name:europe-north1:prod/.s.PGSQL.5432, which would have worked if you put that in path.

I tried this to no avail in the path option. Maybe I didn't do it correctly, but I couldn't get it to work using this method. I also tried a variety of other connection URIs which according to PostgreSQL manual should be correct, but also still don't work.

Either way, I think we should support the "host" query option as well. Was the url you used with pg something google provided as connection details, or did you put that together yourself?

Supporting the host query option would be a nice addition as it is documented as a valid query param. The biggest challenge we faced was we wanted a single environment variable which stored the Connection URI that worked for both local development and in our deployed environment.

Google's dashboard doesn't really give you much in the way of a connection string they just give you details like the IP address and instance connection name. When you dive into their docs they offer a variety of options, though I believe their preferred approach is to use their Cloud SQL Auth Proxy (connection docs). You can then choose to connect to the Cloud SQL Auth Proxy either by using TCP connection or via Unix Socket.

(does google show a connection string in their dashboard, and if so, how does that look?)
They do not. However, as I'm sure anyone that has used GCP would tell you, that's about on-par with their DX/UX 😅.

Below I've included a list of test cases I tried which didn't work, but according to the PostgreSQL docs should.

// Method 1: Using the `host` Query Parameter
postgres("postgresql://postgres:postgres@/default?host=/cloudsql/<project-name>:<region>:<db-name>");

// Method 2: URL-Encoding the Unix Socket Path in Query Parameter
postgres("postgresql://postgres:postgres@/default?host=%2Fcloudsql%2F<project-name>%3A<region>%3A<db-name>");

// Method 3: Omitting the Database Name in the Path
postgres("postgresql://postgres:postgres@/?host=/cloudsql/<project-name>:<region>:<db-name>&dbname=default");

// Method 4: Providing All Parameters in the Query String
postgres("postgresql:///?user=postgres&password=postgres&host=/cloudsql/<project-name>:<region>:<db-name>&dbname=default");

// Method 5: Including the Unix Socket Path in the Authority Section (URL-Encoded)
postgres("postgresql://postgres:postgres@%2Fcloudsql%2F<project-name>%3A<region>%3A<db-name>/default");