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

> Discover how OpenSEO manages PostgreSQL connections using Cloudflare Hyperdrive and per-request clients. Learn about AsyncLocalStorage and origin-side connection pooling for efficient database access.

- Repository: [Every App/open-seo](https://github.com/every-app/open-seo)
- Tags: internals
- Published: 2026-09-04

---

**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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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.

```typescript
// 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`](https://github.com/every-app/open-seo/blob/main/src/server.ts) (lines 35-38), the fetch handler invokes `withPgClient()` to ensure database availability throughout the request lifecycle:

```typescript
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:

```typescript
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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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.