gcp cloud sql socket connection

[niclaslovdahl]

Trying to set up cloud sql socket connection on gcp with the following options with no luck

const sql = postgres({
    path: '/cloudsql/<project-name>:<region>:<db-name>',
    user: 'user',
    password: 'password',
    database: 'database',
  });

getting connection error

address: "/cloudsql/<project-name>:<region>:<db-name>"
code: "ECONNREFUSED"
errno: -111
message: "connect ECONNREFUSED /cloudsql/<project-name>:<region>:<db-name>"
stack: "Error: connect ECONNREFUSED /cloudsql/<project-name>:<region>:<db-name>
    at PipeConnectWrap.afterConnect [as oncomplete] (node:net:1247:16)
    at cachedError (/server/node_modules/.pnpm/[email protected]/node_modules/postgres/cjs/src/query.js:166:23)
    at new Query (/server/node_modules/.pnpm/[email protected]/node_modules/postgres/cjs/src/query.js:36:24)
    at sql (/server/node_modules/.pnpm/[email protected]/node_modules/postgres/cjs/src/index.js:105:11)
    at createDbConn (/server/dist/pkg/db/postgres.js:18:27)"

the cloud sql connection is set up with the right permissions and i am able to connect successfully using pg.

Any thoughts? Thank you!

[porsager]

How does you connection object to pg look? (the same)?

I suppose in the above you have the correct project, region and db-name in your /cloud/... string, but simply replaced the actual ones in the error log?

[niclaslovdahl]

Thanks Rasmus for answering so fast!

I'm using a connection string in pg where i pass the host as query param.

postgres://user:password@/database?host=/cloudsql/<project-name>:<region>:<db-name>

But this connection string generates TypeError [ERR_INVALID_URL]: Invalid URL in postgres

Yes i replace the socket URL with the actual ones in the issue but use the real one in my configuration.

[porsager]

Ah i see. Could you try to post a sample of the link you use in pg without the placeholders, but just some random strings instead?

[porsager]

Could you try to log sql.options before making your first request and look if everything looks right? (might be some url encoding on some of the props causing trouble?)

[niclaslovdahl]

The url with random strings if i understood you right?

postgres://user:password@/database?host=/cloudsql/my-project-name:eu-north1:prod

Here is the options and log output of sql.options (with changed credentials)

const sql = postgres({
    path: '/cloudsql/project-name:europe-north1:prod',
    user: 'user',
    password: 'password',
    database: 'database',
  });

sql.options

Option: {
 host: [ 'localhost' ],
 port: [ 5432 ],
 path: '/cloudsql/project-name:europe-north1:prod',
 database: 'database',
 user: 'user',
 pass: 'password',
 max: 10,
 ssl: false,
 idle_timeout: null,
 connect_timeout: 30,
 max_lifetime: [Function: max_lifetime],
 max_pipeline: 100,
 backoff: [Function: backoff],
 keep_alive: 60,
 prepare: true,
 debug: false,
 fetch_types: true,
 publications: 'alltables',
 target_session_attrs: undefined,
 connection: { application_name: 'postgres.js' },
 types: {},
 onnotice: undefined,
 onnotify: undefined,
 onclose: undefined,
 onparameter: undefined,
 socket: undefined,
 transform: {
   undefined: undefined,
   column: { from: undefined, to: undefined },
   value: { from: undefined, to: undefined },
   row: { from: undefined, to: undefined }
   },
   parameters: {},
   shared: { retries: 0, typeArrayMap: {} },
  ...
}

[niclaslovdahl]

Finally got it working by changing path to host. Leaving the example configuration here if anyone else get stuck.

const sql = postgres({
    host: '/cloudsql/project-name:europe-north1:prod',
    user: 'user',
    password: 'password',
    database: 'database',
  });

[porsager]

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.

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?

(does google show a connection string in their dashboard, and if so, how does that look?)

[coryvirok]

For anyone else still struggling to use UNIX domain sockets in the database URL:

I wasn't able to get a URI with socket path working with this lib so I reverted to using pg-connection-string:

import postgres from 'postgres'
import pgConnectionString from 'pg-connection-string'

// process.env.DATABASE_URL:
// socket://user:password@/path/to/socket?db=dbName

const connectionConfig = pgConnectionString.parse(process.env.DATABASE_URL)
const pg = postgres({
  host: connectionConfig.host ?? undefined,
  port: connectionConfig.port ? parseInt(connectionConfig.port) : undefined,
  user: connectionConfig.user,
  password: connectionConfig.password,
  database: connectionConfig.database ?? undefined,
  // NOTE: casting this to undefined since pg-connection-string allows for
  // more types than postgres driver.
  ssl: connectionConfig.ssl as undefined,
})

The trick with the above is to not use path in the config (as mentioned by OP) and to use host from pg-connection-string since it sets host = socket path.

[olup]

Thanks @coryvirok for your workaround. Too bad there isn't a way to just pass on the url.