[Feature] Add Unix socket connection support to PostgreSQL session storage (e.g. Google Cloud SQL)

[hashem59]

Overview

The @shopify/shopify-app-session-storage-postgresql package currently does not support connecting to PostgreSQL via Unix domain sockets. This is a requirement for users deploying on Google Cloud (Cloud Run, App Engine, GKE) where Cloud SQL Auth Proxy provides connections exclusively through Unix sockets at paths like /cloudsql/project:region:instance.

The Problem

In postgres-connection.ts, the constructor always parses the connection URL with new URL(dbUrl), then the init() method manually extracts hostname, port, username, password, and database to pass individually to pg.Pool:

this.pool = new pg.Pool({
  host: this.dbUrl.hostname,
  user: decodeURIComponent(this.dbUrl.username),
  password: decodeURIComponent(this.dbUrl.password),
  database: this.getDatabase(),
  port: Number(this.dbUrl.port),
});

This approach:

1. Drops all query parameters from the connection URL, including ?host=/cloudsql/... which is the standard PostgreSQL way to specify Unix socket paths
2. Drops SSL parameters (related to @shopify/shopify-app-session-storage-postgresql: Support for SSL Certificate Configuration in PostgreSQL Session Storage. #2835 and @shopify/shopify-app-session-storage-postgresql : error: no pg_hba.conf entry for host #493)
3. Sets port: NaN when no port is specified (common with Unix sockets), which can cause unexpected behavior

Proposed Solution

The package already has pg-connection-string as a dependency (^2.9.1), but it is never used in postgres-connection.ts. Using it to parse the connection URL would properly handle:

• Unix socket paths via the host query parameter (e.g., postgres://user:pass@/dbname?host=/cloudsql/project:region:instance)
• SSL parameters
• All other standard PostgreSQL connection options

This is a minimal, non-breaking change — existing TCP connection strings continue to work exactly as before.

Use Case

import {PostgreSQLSessionStorage} from '@shopify/shopify-app-session-storage-postgresql';

// Google Cloud SQL via Unix socket
const shopify = shopifyApp({
  sessionStorage: new PostgreSQLSessionStorage(
    'postgres://username:password@/database?host=/cloudsql/my-project:us-central1:my-instance',
  ),
  // ...
});

Context

• Multiple users have had to fork this package to work around this limitation (see community forks on npm)
• This is the same root cause as @shopify/shopify-app-session-storage-postgresql: Support for SSL Certificate Configuration in PostgreSQL Session Storage. #2835 and @shopify/shopify-app-session-storage-postgresql : error: no pg_hba.conf entry for host #493 (connection parameters lost during URL decomposition)
• PR Add optional poolConfig to Postgres session options #727 (draft, poolConfig passthrough) and PR [FEAT] Add SSL support #2892 (SSL support) attempt to solve parts of this, but using pg-connection-string — which is already a declared dependency — would be a more comprehensive and minimal fix
• pg-connection-string v2.9.0+ has improved Unix domain socket support