# How OpenSEO Implements Dual-Database Support for D1 and PostgreSQL

> Learn how OpenSEO manages dual database support for D1 SQLite and PostgreSQL. Discover its provider-aware abstraction layer and unified db handle for seamless application integration.

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

---

**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`](https://github.com/every-app/open-seo/blob/main/src/db/provider.ts), where the `getDatabaseProvider()` function determines which backend to use at runtime. The system defaults to D1 unless explicitly configured otherwise.

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

```typescript
// 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`](https://github.com/every-app/open-seo/blob/main/drizzle.config.ts)):
- Sets `dialect: "sqlite"`
- Points to [`src/db/d1/schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/d1/schema.ts)

**PostgreSQL Configuration** ([`drizzle-pg.config.ts`](https://github.com/every-app/open-seo/blob/main/drizzle-pg.config.ts)):
- Sets `dialect: "postgresql"`
- Points to [`src/db/pg/schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/pg/schema.ts)

Both schema files define identical table structures, and [`src/db/schema-parity.test.ts`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/src/db/pg/client.ts) module implements this pattern using `AsyncLocalStorage` to maintain request-scoped state:

```typescript
// 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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/src/db/provider.ts), defaulting to D1 but supporting explicit PostgreSQL opt-in.
- A unified `db` export in [`src/db/index.ts`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/src/db/pg/client.ts), satisfying Cloudflare Workers' execution model.
- Database-specific operations like `.batch` are abstracted through [`src/db/runBatch.ts`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/drizzle.config.ts) for SQLite and [`drizzle-pg.config.ts`](https://github.com/every-app/open-seo/blob/main/drizzle-pg.config.ts) for PostgreSQL—that generate types from parallel schema definitions. The [`src/db/schema-parity.test.ts`](https://github.com/every-app/open-seo/blob/main/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.