How the withPgClient Wrapper Manages Postgres Clients for Hyperdrive in OpenSEO

The withPgClient wrapper in OpenSEO leverages AsyncLocalStorage to provide scoped Postgres connections, automatically managing client lifecycle for Hyperdrive while acting as a transparent pass-through in Cloudflare D1 environments.

OpenSEO eliminates explicit database client management through a lightweight utility that scopes connections to async execution contexts. The withPgClient helper, defined in src/db/pg/client.ts, ensures that every database operation within a request or job shares a single pooled client without requiring manual parameter passing. This architecture automatically adapts to both traditional Postgres pools and Cloudflare's edge database environments including Hyperdrive and D1.

Core Architecture with AsyncLocalStorage

The wrapper centers on an AsyncLocalStorage<PgClient> instance that seeds a dedicated database client at the start of each execution scope.

When withPgClient(callback) executes, it retrieves a client from the pool via pgPool.connect(), stores it in async-local storage, and invokes the provided callback. Downstream functions access this client through the db() utility or direct exports without receiving the connection as an argument. This pattern decouples business logic from connection management while ensuring consistent client usage throughout the call chain.

Connection Lifecycle and Pool Management

Inside src/db/pg/client.ts, the wrapper handles the complete client lifecycle to prevent connection leaks and pool exhaustion.

The implementation follows a strict acquire-use-release pattern:

• Acquisition: Reserves a client from the Postgres pool using pgPool.connect()
• Execution: Runs the supplied async callback with the client stored in context
• Release: Calls client.release() immediately after callback resolution, regardless of success or failure

Error handling ensures that any exception thrown within the callback triggers client release before the error propagates upward. This guarantees that connections return to the pool even when operations fail, preventing resource exhaustion under error conditions.

Hyperdrive and D1 Environment Compatibility

OpenSEO deploys seamlessly across Cloudflare's database platforms through environment-aware logic within the wrapper.

When running in D1 mode (Cloudflare's SQLite-compatible edge database), withPgClient becomes a transparent no-op. As noted in the source comments, the wrapper detects this environment and executes the callback without Postgres-specific client management, allowing identical codebase operation against either D1 or Hyperdrive-backed Postgres pools. This dual-mode support ensures local development with SQLite and production deployment with Hyperdrive require zero code changes.

Entry Point Integration Patterns

All async entry points in OpenSEO wrap their execution logic with withPgClient to establish database context.

Server Request Handling

The primary Cloudflare Worker entry point in src/server.ts wraps the entire request handler:

export default {
  async fetch(request, env, ctx) {
    // All DB calls inside handleFetch share the same Pg client
    return withPgClient(() => handleFetch(request, env, ctx));
  },
};

This ensures that every HTTP request receives a dedicated client that persists through the entire request lifecycle, from authentication through response generation.

Workflow Steps and Background Jobs

Scheduled tasks and workflow steps utilize the same pattern. In src/server/workflows/pgStep.ts, individual workflow steps wrap their execution:

export class PgStep {
  async run(name: string, fn: () => Promise<void>) {
    // `step.do` runs the step; the wrapper guarantees a DB client
    return step.do(name, () => withPgClient(fn));
  }
}

Similarly, rank checking workflows and other background jobs like runScheduledRankChecks invoke the wrapper to ensure consistent database access patterns across synchronous and asynchronous execution boundaries.

Practical Usage and Transaction Handling

Within wrapped functions, developers access the current client through the db() utility exported from src/db/index.ts.

A typical service function retrieves the client from async-local storage rather than accepting it as a parameter:

import { db } from '@/db';  // Resolves to the client stored in ALS

export async function listProjects(userId: string) {
  const client = db();      // No need to pass client around
  const { rows } = await client.query(
    'SELECT * FROM projects WHERE user_id = $1', [userId]
  );
  return rows;
}

Transaction Boundaries

While not a full transaction manager, withPgClient facilitates explicit transaction control within callbacks. Developers can manage transaction lifecycles manually:

await withPgClient(async () => {
  const client = db();
  try {
    await client.query('BEGIN');
    await client.query('INSERT INTO audits ...', [data]);
    await client.query('UPDATE projects ...', [update]);
    await client.query('COMMIT');
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  }
});

This approach keeps transaction boundaries explicit and scoped to the specific operations that require atomicity.

Summary

• The withPgClient wrapper in src/db/pg/client.ts uses AsyncLocalStorage to eliminate explicit client parameter passing throughout OpenSEO's codebase.
• It automatically manages Postgres connection lifecycle via pgPool.connect() and client.release(), preventing connection leaks even during error conditions.
• The wrapper detects Cloudflare D1 environments and acts as a no-op, enabling identical code to run against both D1 SQLite and Hyperdrive Postgres without modification.
• All entry points including src/server.ts and workflow handlers wrap execution with withPgClient to ensure consistent database context.

Frequently Asked Questions

How does withPgClient prevent connection leaks during errors?

The wrapper implements a try-finally pattern that calls client.release() immediately after the callback executes, regardless of whether the promise resolves or rejects. This ensures that Postgres connections return to the pool even when exceptions occur inside the wrapped function, preventing resource exhaustion in production environments.

Can I use transactions with the withPgClient wrapper?

Yes. While withPgClient does not automatically wrap callbacks in transactions, it provides the scoped client necessary to execute manual transaction commands. You can call await client.query('BEGIN'), perform your operations, and commit or rollback within the callback, knowing the same client remains available throughout the entire async context.

Does withPgClient work with Cloudflare D1 instead of Hyperdrive?

Absolutely. When OpenSEO runs in D1 mode, withPgClient becomes a transparent pass-through function that executes the callback without Postgres-specific client management. According to the source comments in src/db/pg/client.ts, this no-op behavior allows the same codebase to operate against D1's SQLite-compatible API or Hyperdrive-backed Postgres pools without conditional logic in your business code.

How do I access the current Postgres client inside nested functions?

Import the db() function from src/db/index.ts (or the equivalent client export) wherever you need to execute queries. This utility retrieves the current client from the AsyncLocalStorage context established by the nearest withPgClient call in the stack, eliminating the need to pass the client object through every function signature.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}