How the Per-Request Postgres Client Pattern Works with Hyperdrive Connection Pooling in Open-SEO

Open-SEO creates a fresh database client for every incoming request while delegating actual socket management to Cloudflare Hyperdrive's edge-level connection pool, eliminating TCP handshake overhead without sacrificing request isolation.

The every-app/open-seo repository implements a sophisticated database architecture designed for edge computing environments. By combining a per-request Postgres client pattern with Hyperdrive's intelligent connection pooling, the application achieves sub-millisecond connection acquisition while maintaining strict request-scoped resource boundaries.

Detecting the Hyperdrive Provider Configuration

The integration begins in src/db/provider.ts, where the application detects whether it is running in a Cloudflare Workers environment with Hyperdrive enabled. The provider uses the HYPERDRIVE binding to extract a pre-configured connection string rather than constructing one from environment variables.

// src/db/provider.ts
const hyperdrive = Reflect.get(env, "HYPERDRIVE") as { connectionString?: string };
const hyperdriveUrl = hyperdrive?.connectionString?.trim();

if (hyperdriveUrl) {
  return hyperdriveUrl;          // 👉 Hyperdrive connection string
}
// …fallback to regular DATABASE_URL

When the HYPERDRIVE binding is present, the application treats Hyperdrive as the primary Postgres endpoint. This binding contains a connection string that points to Hyperdrive's local proxy rather than directly to the database, enabling the edge-level pooling behavior.

Implementing the Per-Request Client Pattern

The core of the pattern resides in src/db/pg/client.ts, which exports the getPgClient function. This function creates a new pg.Pool instance for each incoming request using the connection string supplied by the provider, then immediately acquires a client from that pool.

// src/db/pg/client.ts (simplified)
export async function getPgClient(req: Request) {
  const connString = await getConnectionString(); // comes from provider.ts
  const pool = new pg.Pool({ connectionString: connString });
  const client = await pool.connect();            // acquire a connection
  
  // store client on request so handlers can reuse it
  (req as any).pgClient = client;
  return client;
}

Because the client is created at the start of a request handler (such as a TanStack server function) and attached to the request context, subsequent database calls within the same request lifecycle can reuse the same connection. This ensures transaction consistency while maintaining isolation between concurrent requests.

How Hyperdrive Connection Pooling Works at the Edge

While the application code creates a new pool instance per request, Hyperdrive maintains an edge-level pool of persistent TCP connections to the Postgres instance. When the per-request client calls pool.connect(), it does not open a new TCP socket. Instead, it checks out an existing connection from Hyperdrive's local pool.

The interaction follows this flow:

1. Connection Acquisition: When getPgClient() executes pool.connect(), the underlying driver communicates with the Hyperdrive proxy at localhost:5432 (or the configured port), which provides an already-established connection from its pool.
2. Request Processing: The application uses the client normally, executing queries with full Postgres protocol support.
3. Automatic Cleanup: When the request completes, the Cloudflare Workers runtime tears down the isolate, automatically returning the connection to Hyperdrive's pool. While explicit client.release() calls are good practice, Hyperdrive handles socket reclamation when the request context ends.

This architecture delivers three critical advantages:

• Zero-overhead socket creation: Because Hyperdrive reuses TCP connections across invocations, the latency of obtaining a client is reduced to microseconds rather than milliseconds.
• Automatic resource management: The edge runtime closes sockets when the request ends, preventing connection leaks without requiring explicit cleanup logic in every route handler.
• API compatibility: Code written for standard pg.Pool patterns works unchanged; the pooling logic is transparent to the application layer.

Practical Implementation Examples

The following TanStack server function demonstrates the pattern in production code. Notice that the client is acquired at the function boundary and used throughout the handler without manual socket management.

// Example: a TanStack server function that uses the per-request client
import { getPgClient } from "@/db/pg/client";

export const load = async ({ request }) => {
  const client = await getPgClient(request);
  const { rows } = await client.query(
    `SELECT id, name FROM projects WHERE user_id = $1`,
    [request.user.id]
  );
  // No manual release needed; Hyperdrive handles it
  return { projects: rows };
};

For scenarios requiring explicit transaction control or error handling, you can manually release the client to return it immediately to Hyperdrive's pool:

// Example: manual release (optional but safe)
export const action = async ({ request }) => {
  const client = await getPgClient(request);
  try {
    await client.query(`INSERT INTO logs(msg) VALUES($1)`, ["action run"]);
  } finally {
    client.release(); // returns the connection to Hyperdrive's pool
  }
};

Summary

• src/db/provider.ts detects the Hyperdrive binding and extracts the connection string, falling back to standard environment variables when running outside Cloudflare.
• src/db/pg/client.ts implements the per-request client pattern by creating a new pg.Pool for each request and attaching the acquired client to the request context.
• Hyperdrive's edge-level pool provides the underlying TCP connections, making pool.connect() instantaneous while maintaining persistent sockets to the database.
• The pattern supports both automatic cleanup via the Workers runtime lifecycle and explicit client.release() calls for fine-grained resource control.

Frequently Asked Questions

Does creating a new Pool for every request impact performance?

No. While the code instantiates a new pg.Pool object per request, Hyperdrive maintains the actual TCP connections. The Pool constructor simply creates a lightweight interface to Hyperdrive's local proxy; no network handshake occurs during instantiation. The edge runtime's isolate model makes this object creation negligible compared to the benefits of request isolation.

What happens if I forget to call client.release()?

Hyperdrive automatically reclaims connections when the Workers isolate terminates at the end of the request. However, calling client.release() explicitly is recommended for long-running requests or when using connection limits within a single isolate. Without explicit release, the connection remains occupied until the request completes, potentially reducing concurrency within that specific isolate.

Can I use this pattern with other Postgres libraries like postgres.js or pg-promise?

Yes. The provider abstraction in src/db/provider.ts returns a standard Postgres connection string compatible with any library supporting postgres:// URLs. You would replace the pg.Pool instantiation in src/db/pg/client.ts with your preferred driver's connection logic while maintaining the per-request client creation pattern and Hyperdrive connection string retrieval.

How does the application behave without Hyperdrive configured?

When the HYPERDRIVE binding is absent, src/db/provider.ts falls back to the standard DATABASE_URL environment variable. In this mode, the per-request client pattern still functions, but each pool.connect() call establishes a fresh TCP connection to the database unless you implement application-level pooling. For non-Hyperdrive deployments, consider adding node-postgres native pooling or connection pooling middleware to avoid connection exhaustion.

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