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

> Learn how OpenSEO seamlessly supports PostgreSQL Hyperdrive and Cloudflare D1 databases with its provider-aware abstraction layer. Switch databases without code changes using the DATABASE_PROVIDER environment variable.

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

---

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

```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)}". Expected "d1" or "postgres".`);
}

```

Configure your target database using Wrangler secrets:

```bash

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

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

```typescript
// 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`](https://github.com/every-app/open-seo/blob/main/src/db/pg/client.ts)) wraps your database operations in a context that isolates the PostgreSQL connection to the current request.

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

```typescript
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`](https://github.com/every-app/open-seo/blob/main/src/db/provider.ts) controls whether OpenSEO uses D1 or PostgreSQL.
- **Unified interface**: [`src/db/index.ts`](https://github.com/every-app/open-seo/blob/main/src/db/index.ts) exports a single `db` object compatible with both backends, maintained by [`src/db/schema-parity.test.ts`](https://github.com/every-app/open-seo/blob/main/src/db/schema-parity.test.ts) to ensure schema equivalence.
- **Hyperdrive integration**: PostgreSQL connections use [`src/db/pg/client.ts`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/src/db/index.ts) re-exports the appropriate client based on the provider detection in [`src/db/provider.ts`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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.