How OpenSEO Implements Dual-Database Support for D1 and PostgreSQL

OpenSEO uses a provider-aware database abstraction that switches between Cloudflare D1 (SQLite) and PostgreSQL based on the DATABASE_PROVIDER environment variable, exposing a unified db handle to the rest of the application while managing dialect-specific nuances like request-scoped PostgreSQL connections and atomic batch operations.

OpenSEO is an open-source SEO platform built specifically for Cloudflare Workers that requires flexible storage options to accommodate both hobby deployments on the free tier and production workloads. The project achieves sophisticated dual-database support through a runtime provider selection system, allowing the same codebase to run interchangeably on Cloudflare D1 (SQLite) or a PostgreSQL backend via Hyperdrive without requiring changes to business logic.

Database Provider Detection and Selection

The selection logic resides in src/db/provider.ts, where the getDatabaseProvider() function determines which backend to use at runtime. The system defaults to D1 unless explicitly configured otherwise.

// 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)}".`);
}

The environment variable DATABASE_PROVIDER accepts three valid states:

• Unset or empty: Defaults to D1/SQLite mode
• d1: Explicitly selects Cloudflare D1
• postgres: Activates PostgreSQL mode with Hyperdrive binding

This detection runs early in the Worker lifecycle, ensuring all subsequent database operations target the correct backend.

Unified Database Interface with Type Safety

All application code imports a single database handle from src/db/index.ts, which dynamically points to the appropriate client based on the detected provider. This abstraction eliminates the need for conditional database logic throughout the codebase.

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

The cast to typeof d1Db relies on a structural guarantee: both backends expose identical table schemas. Developers write queries against this unified db export, whether performing simple selects or complex joins, and the underlying driver handles dialect-specific connection details transparently.

Maintaining Schema Parity Across Dialects

To support the unified interface safely, OpenSEO maintains two separate Drizzle ORM configurations that generate type definitions for each dialect while enforcing structural equality through automated testing.

SQLite/D1 Configuration (drizzle.config.ts):

• Sets dialect: "sqlite"
• Points to src/db/d1/schema.ts

PostgreSQL Configuration (drizzle-pg.config.ts):

• Sets dialect: "postgresql"
• Points to src/db/pg/schema.ts

Both schema files define identical table structures, and src/db/schema-parity.test.ts enforces this contract. The test suite asserts structural equality between the two dialect definitions, ensuring the type cast in src/db/index.ts remains valid as the schema evolves. This parity test also includes linting rules—such as verifying that no code calls .batch outside the designated abstraction layer—to prevent accidental use of dialect-specific features that break cross-database compatibility.

PostgreSQL Request-Scoped Connection Management

Because Cloudflare Workers cannot reuse TCP sockets across request invocations, PostgreSQL connections require special handling. Unlike D1, which uses Cloudflare's native binding, PostgreSQL connections must be created and destroyed within the scope of a single request.

The src/db/pg/client.ts module implements this pattern using AsyncLocalStorage to maintain request-scoped state:

// src/db/pg/client.ts
export async function withPgClient<T>(fn: () => Promise<T>): Promise<T> {
  if (getDatabaseProvider() !== "postgres") return fn();   // D1 mode → noop
  // …create a new postgres client, store it in AsyncLocalStorage, run fn…
}

All entry points that touch the database—including fetch handlers, scheduled jobs, and workflow steps—wrap their logic with withPgClient when running in PostgreSQL mode. This ensures the pgDb proxy used in src/db/index.ts always accesses a valid, request-scoped client. In D1 mode, the helper becomes a transparent pass-through, avoiding any performance overhead.

Abstracting Dialect-Specific Operations

D1 provides a .batch method for atomic multi-statement writes, but PostgreSQL lacks an equivalent API. To maintain dual-database support without sacrificing atomicity on D1, OpenSEO centralizes all batch operations through src/db/runBatch.ts.

This abstraction layer implements the batch logic for D1 while falling back to sequential transactions or individual statements for PostgreSQL when necessary. The schema-parity.test.ts file enforces this discipline through a specific test case ("no direct db.batch") that fails if any code outside runBatch.ts attempts to call .batch directly, preventing accidental vendor lock-in and ensuring the codebase remains portable between backends.

Environment Configuration for Local and Production

Setting up the correct backend requires minimal configuration changes across environments:

Local Development:

• Default: D1/SQLite mode (no variables set)
• PostgreSQL: Set DATABASE_PROVIDER=postgres and provide a connection string via .env.local (consumed by drizzle-pg.config.ts) or a Hyperdrive binding

Production:

• D1: No configuration required (free tier default)
• PostgreSQL: Set DATABASE_PROVIDER=postgres in .env.production and configure the Hyperdrive binding

The drizzle-pg.config.ts configuration automatically picks up local connection strings for development, while production deployments use the Hyperdrive binding injected by the Cloudflare platform.

Summary

• OpenSEO detects the database backend at runtime via DATABASE_PROVIDER in src/db/provider.ts, defaulting to D1 but supporting explicit PostgreSQL opt-in.
• A unified db export in src/db/index.ts provides a single interface to application code, dynamically selecting the appropriate driver.
• Schema parity is enforced by src/db/schema-parity.test.ts, ensuring type safety across the SQLite and PostgreSQL dialects.
• PostgreSQL connections are request-scoped using AsyncLocalStorage via the withPgClient helper in src/db/pg/client.ts, satisfying Cloudflare Workers' execution model.
• Database-specific operations like .batch are abstracted through src/db/runBatch.ts to maintain compatibility across both backends.

Frequently Asked Questions

How does OpenSEO decide which database backend to use?

The decision occurs at runtime in src/db/provider.ts via the getDatabaseProvider() function. It checks the DATABASE_PROVIDER environment variable: if the value is "postgres", it selects PostgreSQL; if the value is "d1", undefined, or empty, it defaults to D1. Any other value throws an unsupported provider error, preventing silent failures from typos.

Why does PostgreSQL require request-scoped connections in OpenSEO?

Cloudflare Workers run in a serverless, isolate-per-request model where TCP sockets cannot be shared across invocations. Because PostgreSQL connections are TCP-based, they must be established at the start of a request and closed at the end. The withPgClient helper in src/db/pg/client.ts manages this lifecycle using AsyncLocalStorage, whereas D1 uses Cloudflare's native binding protocol that doesn't suffer from this limitation.

How does OpenSEO ensure schema compatibility between D1 and PostgreSQL?

The project maintains two separate Drizzle configuration files—drizzle.config.ts for SQLite and drizzle-pg.config.ts for PostgreSQL—that generate types from parallel schema definitions. The src/db/schema-parity.test.ts test file continuously asserts that both schemas define identical tables, columns, and relationships. This structural guarantee allows the unified db export to safely cast both clients to the same TypeScript type.

Can I use native PostgreSQL features that D1 doesn't support?

No. To maintain dual-database support, OpenSEO restricts the codebase to operations supported by both backends. The schema parity tests and linting rules prevent the use of dialect-specific features like PostgreSQL's jsonb operators or D1's .batch method outside the abstraction layers. If you require PostgreSQL-specific functionality, you would need to fork the codebase and remove the D1 compatibility layer.

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