How to connect to unix socket path in a connection string?

[kelleyvanevert]

I'm struggling to get Slonik to work on Google App Engine. On GAE, you connect to the (Cloud SQL) database via a unix socket path. Locally, I can produce such a unix socket that proxies the production database with Google's helper utility cloud_sql_proxy:

./cloud_sql_proxy -dir=./cloudsql -instances=my-project-322719:europe-west4:my-project

I'm able to connect to this socket with the pg package like so:

const client =  new pg.Client({
  host:"/Users/kelley/cloudsql/my-project-322719:europe-west4:my-project", 
  user:"postgres",
  password:"n4EijI3kEOdkEfFp",
  database:"postgres"
});

But, I'm unable to phrase these settings in a single connection string like Slonik requires it. Or maybe, I'm close enough, but at least running into an error I don't understand at all:

> s=require("slonik")
> p=s.createPool("postgresql://postgres:n4EijI3kEOdkEfFp@/Users/kelley/mywheels/cloudsql/my-project-322719:europe-west4:my-project/postgres").anyFirst(s.sql`select 1`).then(console.log)
Promise { <pending> }
> (node:27101) UnhandledPromiseRejectionWarning: ConnectionError: role "postgres" is not permitted to log in
    at Object.createConnection (/Users/kelley/link-shortener/node_modules/slonik/dist/src/factories/createConnection.js:54:23)
    at runMicrotasks (<anonymous>)
    at processTicksAndRejections (internal/process/task_queues.js:93:5)
(node:27101) UnhandledPromiseRejectionWarning: Unhandled promise rejection. This error originated either by throwing inside of an async function without a catch block, or by rejecting a promise which was not handled with .catch(). To terminate the node process on unhandled promise rejection, use the CLI flag `--unhandled-rejections=strict` (see https://nodejs.org/api/cli.html#cli_unhandled_rejections_mode). (rejection id: 20)

Anyone have experience with this?

[kelleyvanevert]

Ooh, found it! :P It took my quite some time, but I found it pretty much right after posting this :P

The underlying package that does the connecting string parsing is pg-connection-string, and those docs specified how I should form it:

socket://postgres:n4EijI3kEOdkEfFp@/Users/kelley/cloudsql/my-project-322719:europe-west4:my-project?db=postgres

Putting this connection string into slonik works :)

[kaisellgren]

This worked for me (slonik@31.2.1):

const username = encodeURIComponent(process.env.DATABASE_USERNAME)
const password = encodeURIComponent(process.env.DATABASE_PASSWORD)
const socketPath = encodeURIComponent(process.env.SOCKET_PATH)
const dbName = encodeURIComponent(process.env.DATABASE_NAME)
const connectionUri = `socket://${username}:${password}@${socketPath}/${dbName}`

For example: in Google Cloud, the socket path is /cloudsql/xxx:europe-north1:yyy (before url encoding).

If using docker-compose locally, I used custom volumes:

version: '3.7'
volumes:
  postgresSocketVol:
    driver: local
    driver_opts:
      type: none
      o: bind
      device: /var/run/postgresql-volume
services:
  postgres:
    image: postgres:14.5
    ports:
      - 5400:5432 # Use TCP & port 5400 to connect when using tools like IntelliJ Database Editor (JDBC)
    environment:
      POSTGRES_DB: db
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: password
    volumes:
      - postgresSocketVol:/var/run/postgresql

And then the socket path would be /var/run/postgresql-volume (before url encoding).