How OpenSEO Supports Both PostgreSQL (Hyperdrive) and Cloudflare D1 Databases

OpenSEO uses a provider-aware abstraction layer controlled by the DATABASE_PROVIDER environment variable to seamlessly switch between Cloudflare D1 SQLite and PostgreSQL via Hyperdrive without changing application code.

OpenSEO is architected to run on Cloudflare Workers with flexibility for different database tiers. Whether you need the zero-configuration simplicity of D1 or the advanced capabilities of PostgreSQL exposed through Hyperdrive, the codebase adapts at runtime based on a single environment variable. This design allows teams to start with the free D1 tier and migrate to PostgreSQL as requirements grow, all while using the same Drizzle ORM queries and repository patterns.

Provider Detection and Configuration

The database selection logic resides in src/db/provider.ts, which reads the DATABASE_PROVIDER environment variable at runtime. This module exports getDatabaseProvider(), a function that returns "postgres" when the variable is explicitly set to "postgres", or defaults to "d1" for empty strings, undefined values, or explicit "d1" settings.

// src/db/provider.ts
export function getDatabaseProvider(): DatabaseProvider {
  const provider = Reflect.get(env, "DATABASE_PROVIDER");
  if (provider === "postgres") return "postgres";
  if (provider === "d1" || provider === undefined || provider === "") return "d1";
  throw new Error(`Unsupported DATABASE_PROVIDER "${String(provider)}". Expected "d1" or "postgres".`);
}

Configure your target database using Wrangler secrets:

# Use Cloudflare D1 (default, zero-config)

wrangler secret put DATABASE_PROVIDER  # Set to "d1" or leave empty

# Switch to PostgreSQL via Hyperdrive

wrangler secret put DATABASE_PROVIDER postgres
wrangler secret put HYPERDRIVE '{"connectionString":"postgres://user:pass@host:5432/db"}'

Unified Database Abstraction Layer

src/db/index.ts exports a unified db object that transparently points to either the PostgreSQL client (pgDb) or the D1 client (d1Db). The implementation casts the PostgreSQL instance to match the D1 client's TypeScript type, allowing Drizzle ORM to infer the complete schema for both backends identically.

// src/db/index.ts
export const db = (getDatabaseProvider() === "postgres"
  ? pgDb
  : d1Db) as unknown as typeof d1Db;

To ensure structural consistency across SQLite and PostgreSQL dialects, OpenSEO maintains src/db/schema-parity.test.ts. This test suite verifies that both database schemas remain identical, preventing dialect-specific schema drift that could break queries when switching providers.

PostgreSQL Implementation with Hyperdrive

When running in PostgreSQL mode, OpenSEO connects to your database through Cloudflare Hyperdrive, which pools and accelerates connections to your origin PostgreSQL instance.

Connection Management

The src/db/pg/client.ts module manages PostgreSQL connections using the postgres driver. It retrieves the connection string via getPostgresConnectionString() from the HYPERDRIVE binding (or a localConnectionString defined in wrangler.jsonc for local development). The client configuration enforces max: 1 and a 10-second connection timeout to comply with Cloudflare Workers' resource constraints.

// src/db/pg/client.ts (excerpt)
const sql = postgres(getPostgresConnectionString(), { max: 1, connect_timeout: 10 });

Per-Request Client Scoping

Cloudflare Workers require that database sockets not be reused across requests. OpenSEO solves this using AsyncLocalStorage to create request-scoped client instances. The withPgClient() function (exported from src/db/pg/client.ts) wraps your database operations in a context that isolates the PostgreSQL connection to the current request.

// src/db/pg/client.ts
export async function withPgClient<T>(fn: () => Promise<T>): Promise<T> {
  if (getDatabaseProvider() !== "postgres") return fn();
  // Creates isolated client for this request only
  const sql = postgres(getPostgresConnectionString(), { max: 1 });
  return pgClientStore.run({ sql, db: createPgDb(sql) }, fn);
}

Every entry point that touches the database—including API handlers in src/serverFunctions/*, scheduled cron jobs, and workflow runs—invokes withPgClient() to ensure safe connection handling.

Cloudflare D1 Implementation

For D1 mode, src/db/d1/client.ts simply binds to the DB environment variable provided by Cloudflare Workers. No special per-request handling is required because D1 operates over HTTP rather than persistent sockets. The repository code consumes the same db export from src/db/index.ts, remaining agnostic to whether it is querying SQLite or PostgreSQL underneath.

Runtime Database Operations

The withPgClient() function acts as a compatibility layer across both database providers. When DATABASE_PROVIDER is set to "d1", the function immediately executes the provided callback without additional setup. In PostgreSQL mode, it injects a request-scoped client before executing your logic.

import { withPgClient } from "@/db/pg/client";
import { db } from "@/db";
import { someTable } from "@/db/schema";

export async function nightlyCleanup() {
  await withPgClient(async () => {
    // Executes with request-scoped Postgres client when DATABASE_PROVIDER=postgres
    // Runs normally against D1 when using default configuration
    await db.delete(someTable).where(eq(someTable.expired, true));
  });
}

Summary

• Environment-driven selection: The DATABASE_PROVIDER variable in src/db/provider.ts controls whether OpenSEO uses D1 or PostgreSQL.
• Unified interface: src/db/index.ts exports a single db object compatible with both backends, maintained by src/db/schema-parity.test.ts to ensure schema equivalence.
• Hyperdrive integration: PostgreSQL connections use src/db/pg/client.ts with request-scoped clients via AsyncLocalStorage and the HYPERDRIVE binding.
• Zero-config D1: The D1 path requires no additional connection management; all repository code works transparently through the unified abstraction.
• Migration-ready: Teams can switch between database providers by changing a single environment variable without modifying application logic.

Frequently Asked Questions

How do I switch from D1 to PostgreSQL in OpenSEO?

Set the DATABASE_PROVIDER environment variable to "postgres" and configure the HYPERDRIVE secret with your PostgreSQL connection string. The application will automatically use PostgreSQL via Hyperdrive on the next deployment. No code changes are required because src/db/index.ts re-exports the appropriate client based on the provider detection in src/db/provider.ts.

What is the purpose of withPgClient() in OpenSEO?

withPgClient() ensures that PostgreSQL connections are properly scoped to individual Cloudflare Worker requests using AsyncLocalStorage. This prevents socket reuse across concurrent requests, which violates Workers' runtime constraints. When running against D1, the function acts as a pass-through, allowing the same code to run safely under both database configurations.

Does OpenSEO maintain separate schemas for D1 and PostgreSQL?

No, OpenSEO maintains a single schema definition that works with both SQLite (D1) and PostgreSQL. The src/db/schema-parity.test.ts test suite verifies that both database dialects produce structurally identical schemas, ensuring that Drizzle ORM queries execute correctly regardless of which DATABASE_PROVIDER is active.

Can I use OpenSEO with PostgreSQL locally without Hyperdrive?

Yes, local development supports PostgreSQL through the localConnectionString property defined in wrangler.jsonc. When getPostgresConnectionString() detects a local environment, it uses this connection string instead of the HYPERDRIVE binding, allowing full PostgreSQL development and testing without deploying to Cloudflare's edge network.

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