How OpenSEO Handles PostgreSQL Connections with Hyperdrive and Per-Request Clients

TLDR: OpenSEO connects to PostgreSQL exclusively through Cloudflare Hyperdrive, creating fresh clients for each Worker request using AsyncLocalStorage to comply with Cloudflare Workers' I/O restrictions while leveraging Hyperdrive's origin-side connection pooling.

OpenSEO is an open-source SEO management platform that supports both Cloudflare D1 and PostgreSQL database backends. When the DATABASE_PROVIDER environment variable is set to postgres, the application implements a sophisticated per-request client pattern that ensures compatibility with Cloudflare Workers' execution model. This architecture delegates connection pooling to Hyperdrive while maintaining strict request isolation at the application level.

Configuring the Database Provider

OpenSEO determines which database backend to use through a centralized provider check. In src/db/provider.ts (lines 5-14), the application reads the DATABASE_PROVIDER environment variable and returns either "postgres" or "d1" based on the configuration.

When running in PostgreSQL mode, the application never connects directly to the database instance. Instead, it retrieves the connection string from the Cloudflare Hyperdrive binding, which handles connection pooling and optimization at the edge.

Hyperdrive Connection String Resolution

The connection string resolution logic in src/db/provider.ts (lines 21-33) differentiates between local development and production environments. The function getPostgresConnectionString() extracts the connection string from env.HYPERDRIVE.connectionString when available.

In local development, this resolves to the localConnectionString defined in wrangler.jsonc. In production, it points to the Hyperdrive endpoint that manages the actual connection pool to the origin PostgreSQL server. This abstraction ensures that the application code remains environment-agnostic while Hyperdrive handles the complexities of connection management.

Implementing Per-Request PostgreSQL Clients

Because Cloudflare Workers cannot reuse sockets across different requests—throwing "Cannot perform I/O on behalf of a different request" when attempted—OpenSEO creates a fresh PostgreSQL client for every request. The src/db/pg/client.ts file defines the withPgClient<T>(fn) function (lines 12-20) that manages this lifecycle.

The implementation uses AsyncLocalStorage to maintain the client instance for the duration of the current request. When invoked, withPgClient checks that the provider is set to "postgres"; if not, it simply executes the callback function without database setup.

// src/db/pg/client.ts
import postgres from 'postgres';
import { AsyncLocalStorage } from 'async_hooks';

const asyncLocalStorage = new AsyncLocalStorage<ReturnType<typeof postgres>>();

export async function withPgClient<T>(fn: () => Promise<T>): Promise<T> {
  if (getDatabaseProvider() !== "postgres") {
    return fn();
  }
  
  const sql = postgres(getPostgresConnectionString(), { 
    max: 1, 
    connect_timeout: 10 
  });
  
  return asyncLocalStorage.run(sql, fn);
}

AsyncLocalStorage Scope Management

The AsyncLocalStorage mechanism ensures that any database calls made within the callback share the same client instance without explicit parameter passing. This allows middleware and route handlers to access the database through a proxy that resolves to the request-scoped client automatically.

Connection Pooling Strategy

The postgres client is instantiated with max: 1, following Hyperdrive's recommendation to disable client-side pooling. Since Hyperdrive already manages a connection pool at the edge, additional client-side pooling would create unnecessary overhead. The connect_timeout: 10 parameter ensures rapid failure if the connection cannot be established.

Notably, the implementation does not call sql.end() after the request completes (lines 56-61). This deliberate omission allows streamed responses to continue querying the database even after the handler function returns, supporting OpenSEO's real-time audit and ranking features.

Wiring Per-Request Clients to Entry Points

Every entry point that potentially accesses the database wraps its logic with withPgClient(). This includes HTTP fetch handlers, scheduled cron jobs, and workflow executions.

HTTP Fetch Handlers

In src/server.ts (lines 35-38), the fetch handler invokes withPgClient() to ensure database availability throughout the request lifecycle:

import { withPgClient, pgDb } from "@/db";

async function getUserProjects(userId: string) {
  // `pgDb` is a proxy that resolves to the request-scoped DB instance
  return await pgDb.project.findMany({ where: { ownerId: userId } });
}

export async function fetch(request: Request, env: Env, ctx: ExecutionContext) {
  return withPgClient(async () => {
    const userId = /* extract from auth ... */;
    const projects = await getUserProjects(userId);
    return new Response(JSON.stringify(projects), { status: 200 });
  });
}

Scheduled Cron Jobs

The scheduled cron handler (lines 31-33) follows the same pattern, creating a fresh client for each cron invocation:

import { withPgClient } from "@/db";
import { reconcileStaleAudits } from "@/server/features/audit/services/auditReconciler";

export async function scheduled(controller: ScheduledController, env: Env) {
  // Runs with a per-request PostgreSQL client (no-op in D1 mode)
  await withPgClient(() => reconcileStaleAudits());
}

Workflow Executions

Long-running workflows such as RankCheckWorkflow and SiteAuditWorkflow also utilize withPgClient() at their entry points (lines 7-9). This ensures that even multi-step background processes maintain proper connection isolation while benefiting from Hyperdrive's pooling capabilities.

Summary

OpenSEO's PostgreSQL integration demonstrates a serverless-native approach to database connectivity:

• Environment-based provider selection in src/db/provider.ts switches between D1 and PostgreSQL without code changes.
• Hyperdrive abstraction manages connection strings and origin-side pooling transparently.
• Per-request client creation via withPgClient() ensures compliance with Workers' I/O isolation requirements.
• AsyncLocalStorage scoping eliminates the need to pass database clients through every function call.
• No explicit cleanup enables streaming queries to persist beyond handler completion.

Frequently Asked Questions

Why does OpenSEO create a new PostgreSQL client for every request?

Cloudflare Workers enforce strict I/O isolation that prevents socket reuse across requests, throwing "Cannot perform I/O on behalf of a different request" if violated. By creating a fresh client for each request through withPgClient(), OpenSEO ensures compliance with these constraints while using Hyperdrive to maintain efficient connection pooling at the origin.

What is the purpose of max: 1 in the postgres client configuration?

The max: 1 setting disables client-side connection pooling, following Cloudflare Hyperdrive's architectural recommendations. Since Hyperdrive already maintains a pool of connections to the PostgreSQL origin, client-side pooling would add unnecessary latency and resource consumption without providing additional benefits.

How does OpenSEO handle PostgreSQL connections in local development?

During local development, the getPostgresConnectionString() function in src/db/provider.ts retrieves the connection string from the HYPERDRIVE binding, which resolves to the localConnectionString defined in wrangler.jsonc. This allows developers to test against a local PostgreSQL instance while using the same code path as production.

Can I reuse the PostgreSQL client across multiple requests in OpenSEO?

No, the architecture explicitly prevents client reuse across requests. The withPgClient() wrapper in src/db/pg/client.ts creates a new client instance for every invocation, storing it in AsyncLocalStorage only for the duration of the current request. This design is mandatory for Cloudflare Workers compatibility and cannot be bypassed without causing runtime errors.

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