Disciplines · Runbooks

Runbook: Migrate raw pg.Pool to @oshun/database

The client object exposes the same query, connect, and end methods as a

4sections4 minread
Markdown source
On this page

Owner: platform team Last reviewed: 2026-05-28

The audit of v1 identified ~30 services that instantiate new Pool / new pg.Pool directly from the pg package instead of going through the @oshun/database wrapper. This bypass costs us:

  • Health checks — @oshun/database exposes checkPostgresHealth so /health endpoints land in a uniform shape across services. Direct pg.Pool users have to hand-roll the check (or skip it).
  • Connection-string normalization — parsePostgresConnectionString
    • maskPostgresConnectionString in @oshun/database strip credentials before logs hit cloud-aggregated stdout. Raw pg users often log the unredacted DSN by accident.
  • Pool metrics — instrumentPostgresClient ships acquire / wait / release timings to the metrics sink. Raw pools have no such hook.
  • Pluggable pool implementation — when the platform switches pgbouncer → pg-native or adds the read-replica router, the @oshun/database boundary is the only place we have to change. Raw pools need each service to do its own swap.

This runbook documents the migration path. As of writing, ~30 sites still need to move; the list is at the bottom.

Pattern#

Before#

ts
import pg from 'pg';

const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,
  idleTimeoutMillis: 30_000,
  connectionTimeoutMillis: 5_000,
});

await pool.query('SELECT 1');

After#

ts
import { createPostgresClient } from '@oshun/database';

const client = createPostgresClient({
  connectionString: process.env.DATABASE_URL,
  max: 10,
  idleTimeoutMillis: 30_000,
  connectionTimeoutMillis: 5_000,
});

await client.query('SELECT 1');

The client object exposes the same query, connect, and end methods as a pg.Pool, so most call sites only change the constructor. If you depend on pool.totalCount / pool.idleCount for ad-hoc metrics, use client.stats() instead — it returns a fully-typed PostgresPoolStats object covering the same fields plus acquire-wait p50/p95.

Connection from env#

If the service reads DATABASE_URL and applies no overrides, createPostgresClientFromEnv() does the right thing:

ts
import { createPostgresClientFromEnv } from '@oshun/database';

const client = createPostgresClientFromEnv(); // reads DATABASE_URL

Transactions#

ts
import { withTransaction } from '@oshun/database';

await withTransaction(client, async (tx) => {
  await tx.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [
    amount,
    fromId,
  ]);
  await tx.query('UPDATE accounts SET balance = balance + $1 WHERE id = $2', [
    amount,
    toId,
  ]);
});

withReadOnlyTransaction and withSerializableTransaction give you the read-only / SERIALIZABLE isolation variants without each service re-implementing the BEGIN/COMMIT/ROLLBACK boilerplate.

Health check#

ts
import { checkPostgresHealth } from '@oshun/database';

const health = await checkPostgresHealth(client);
// → { ok: true, latencyMs: 4, poolStats: { ... } }

Wire this into the service's /health route so the response shape matches every other Oshun service.

Migration checklist (per service)#

  1. Add "@oshun/database": "workspace:*" to the service's package.json dependencies (most already have it transitively; confirm with pnpm why @oshun/database in the service dir).
  2. Find the new pg.Pool(...) site:
    bash
    grep -rn "new pg\\.Pool\\|new Pool(" apps/<service>/src
  3. Replace with createPostgresClient(...) (or createPostgresClientFromEnv() if applicable).
  4. Remove the import pg from 'pg' / import { Pool } from 'pg' line if it's no longer used.
  5. Adjust any pool.totalCount / pool.idleCount reads to client.stats().
  6. Wire /health through checkPostgresHealth if the service has a health endpoint that doesn't already use it.
  7. Run the service's tests; verify nothing breaks. Watch for type errors on pool.connect() callbacks — client.connect() returns Promise<PoolClient> (same shape) but the type is exported from @oshun/database rather than pg.

Tracked sites (as of 2026-05-28)#

Migrated (18 / 30)#

  • apps/arete/api/src/plugins/database.ts
  • apps/demeter/api/src/plugins/database.ts
  • apps/hestia/api/src/plugins/database.ts
  • apps/lilith/svc-consent-management/src/app.ts
  • apps/lilith/svc-settlement/src/storage/postgres-store.ts
  • apps/lilith/svc-catalog/src/app.ts
  • apps/nyx/api/src/middleware/api-key-auth.ts
  • apps/nyx/pipelines/src/event-calculator.ts
  • apps/nyx/pipelines/src/catalog-syncer.ts
  • apps/nyx/pipelines/src/tle-updater.ts
  • apps/nyx/pipelines/src/ephemeris-generator.ts
  • apps/oshun/bff/scripts/backfill-stubs-to-canonical.ts
  • apps/oshun/bff/src/routes/domain-stubs-postgres.ts
  • apps/veritas/ai-workers/src/workers/base/pool.ts
  • apps/veritas/api/src/infrastructure/postgres/createPool.ts
  • apps/veritas/ingestion/src/db/pool.ts
  • apps/yemaya/workers/src/workers/notification-worker.ts
  • libs/yemaya/assets/src/database/lfs-quota-connection.ts

Still to migrate (12 / 30) — each blocked on a specific concern#

Multi-pool sites (one wrapper, multiple distinct pg.Pool instances — needs per-pool care to wire the right config to each pool):

  • apps/veritas/video/src/main.ts (4 pools)
  • apps/veritas/audio/src/main.ts (4 pools)
  • libs/saraswati/db/src/connection.ts (2 pools: primary + telemetry)
  • libs/lakshmi/db/src/connection.ts (3 pools: primary + read replica + ingest)
  • libs/lakshmi/db/src/migrations.ts (2 pools)

Nx layer-boundary blocked (the lib is tagged layer:infra which prevents importing @oshun/database; needs lilith owners to retag or move the consumer):

  • libs/lilith/common/audit-logger.ts
  • libs/lilith/service-lib/database.ts

Pre-existing lint debt that surfaces when the file is touched (each needs the file owner to clean up the unrelated warnings before the migration can land):

  • libs/iris/memory/persistence/src/postgres-store.ts

Already-using a different abstraction (libs/asase/migrations uses its own connection-pool wrapper; libs/cybele/db builds config but delegates instantiation to the caller; libs/saraswati's telemetry pool sits in a sub-module — needs per-lib decisions):

  • libs/asase/migrations/src/connection-pool.ts
  • libs/cybele/db/src/connection.ts

Exempt from this migration:

  • libs/shared/database/src/legacy-pool.ts — the @oshun/database internal that exposes a raw-pool escape hatch for one-off scripts.
  • libs/shared/database/src/postgres-client.ts — the @oshun/database internal that wraps pg.Pool. The whole point of the wrapper.
  • Anything under test/ or __mocks__/ — test fixtures often use raw pg directly so they can poke at internals the wrapper hides.

Enforcement#

The pre-commit stub-indicator-scan does not currently flag raw pg.Pool instantiations. A follow-up will add an ESLint rule (@oshun/no-raw-pg-pool) that fails CI for any new new Pool from 'pg' outside the exempt set above. Until that lands, the audit / gaps doc remains the source of truth.