How OpenSEO Manages PostgreSQL Connections on Cloudflare Workers

OpenSEO creates a fresh, isolated PostgreSQL client for every request using AsyncLocalStorage, ensuring safe database access within serverless Worker invocations without explicit connection cleanup.

OpenSEO abstracts its database layer behind a provider-based architecture that supports both Cloudflare D1 and PostgreSQL backends. When configured for PostgreSQL, the system creates per-request connections via Hyperdrive and Drizzle ORM, eliminating shared global state while maintaining developer ergonomics through a transparent Proxy API.

Provider-Based Database Selection

OpenSEO determines its database backend at runtime through environment configuration. The system reads the DATABASE_PROVIDER environment variable in src/db/provider.ts to decide whether to instantiate PostgreSQL or fall back to Cloudflare D1.

Selecting the Database Provider

The provider selection logic evaluates the DATABASE_PROVIDER variable. If set to "postgres", the system initializes PostgreSQL connectivity; otherwise, it defaults to D1. This abstraction allows deployments to switch storage backends without modifying application code.

Hyperdrive Connection String

When PostgreSQL mode is active, the system retrieves the connection string from the HYPERDRIVE binding. The getPostgresConnectionString() function in src/db/provider.ts extracts this configuration, enabling Cloudflare's Hyperdrive edge-proxy to manage connection pooling and latency reduction between the Worker and the PostgreSQL instance.

Request-Scoped Connection Lifecycle

The PostgreSQL implementation avoids shared global connections by creating a dedicated client for each request. This pattern is essential for Cloudflare Workers, where global state persists across invocations but must not leak between concurrent requests.

The withPgClient Wrapper

Located in src/db/pg/client.ts, the withPgClient function orchestrates request-scoped database access. When the provider is set to PostgreSQL, it creates a new postgres client with specific constraints: max: 1 (limiting the pool to a single connection) and a 10-second connection timeout to prevent hanging requests (lines 74-82).

The implementation wraps the raw client with withQueryRetries from src/db/pg/retry.ts for automatic query resilience and stores both the client and a Drizzle ORM instance in an AsyncLocalStorage context named pgClientStore.

AsyncLocalStorage Context Management

The pgClientStore maintains database instances throughout the asynchronous execution of a request. By storing the Drizzle ORM instance (db) alongside the raw SQL client, OpenSEO ensures that all database operations within a single request share the same connection without risk of cross-request contamination.

Connection Pooling Constraints

The deliberate choice of max: 1 in the postgres client configuration reflects the stateless, short-lived nature of Worker invocations. Rather than maintaining long-lived connection pools, OpenSEO creates a single, short-lived connection per request, optimizing for the serverless execution model where functions scale horizontally rather than maintaining persistent connections.

Transparent Database Access via Proxy

OpenSEO exposes the request-scoped database through a Proxy object that simplifies developer experience while enforcing architectural boundaries.

The pgDb Proxy Object

The exported pgDb in src/db/pg/client.ts is a JavaScript Proxy that intercepts property accesses and forwards them to the Drizzle ORM instance stored in the current AsyncLocalStorage context. This design allows developers to import and use pgDb directly without manually passing database instances through function parameters.

Error Handling Outside Request Context

If code attempts to access pgDb outside of a withPgClient wrapper, the Proxy throws an explicit error instructing developers to wrap database calls properly. This runtime check, implemented in lines 30-42 of src/db/pg/client.ts, prevents accidental use of undefined database contexts in background tasks or global scopes.

Lifecycle and Cleanup Strategy

Unlike traditional Node.js applications, OpenSEO explicitly avoids calling sql.end() on PostgreSQL connections. As documented in lines 56-61 of src/db/pg/client.ts, Cloudflare Workers automatically terminate sockets at the end of an invocation, making explicit cleanup unnecessary and potentially disruptive for streamed responses that continue processing after the handler returns.

Practical Usage Examples

The following patterns demonstrate proper PostgreSQL access within the OpenSEO request lifecycle.

Basic Query Execution

All database operations must occur within the withPgClient wrapper:

// src/example/usage.ts
import { pgDb, withPgClient } from "@/db/pg/client";
import * as schema from "@/db/pg/schema";

export async function listProjects() {
  return await withPgClient(async () => {
    const rows = await pgDb
      .select()
      .from(schema.projects)
      .orderBy(schema.projects.createdAt.desc());

    return rows;
  });
}

Transaction Handling

Transactions work within the same request-scoped context, utilizing Drizzle's transaction API:

// src/example/transaction.ts
import { pgDb, withPgClient } from "@/db/pg/client";
import * as schema from "@/db/pg/schema";

export async function createProject(name: string) {
  return await withPgClient(async () => {
    await pgDb.transaction(async (trx) => {
      await trx.insert(schema.projects).values({ name });
    });
  });
}

Summary

• Provider abstraction: src/db/provider.ts selects between D1 and PostgreSQL via the DATABASE_PROVIDER environment variable.
• Request isolation: Each Worker invocation receives a dedicated PostgreSQL client configured with max: 1 and a 10-second timeout.
• Context safety: AsyncLocalStorage in src/db/pg/client.ts ensures database instances never leak between concurrent requests.
• Developer experience: The pgDb Proxy provides transparent access to Drizzle ORM while enforcing proper request scoping.
• Serverless lifecycle: OpenSEO relies on Cloudflare Workers' automatic socket termination rather than explicit connection cleanup.

Frequently Asked Questions

How does OpenSEO switch between Cloudflare D1 and PostgreSQL?

The system checks the DATABASE_PROVIDER environment variable in src/db/provider.ts. When set to "postgres", it uses the Hyperdrive connection string; otherwise, it defaults to D1 bindings. This allows runtime configuration changes without code modifications.

Why does OpenSEO limit PostgreSQL connections to max: 1?

The max: 1 configuration in src/db/pg/client.ts ensures each Worker request maintains exactly one database connection. This prevents resource exhaustion in the stateless serverless environment and aligns with Cloudflare Workers' execution model where horizontal scaling handles concurrency rather than connection pooling.

What happens if I use pgDb outside of withPgClient?

The pgDb Proxy detects missing AsyncLocalStorage context and throws an explicit error. This runtime safeguard, implemented in src/db/pg/client.ts, prevents undefined database access and reminds developers to wrap all database operations within withPgClient.

How does the retry logic work with PostgreSQL queries?

The withQueryRetries wrapper in src/db/pg/retry.ts automatically retries failed queries when invoked through withPgClient. This provides transient fault tolerance for network blips without requiring manual try-catch blocks in business logic.

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