How the Drizzle ORM Database Schema Works with D1 and Postgres in Open-SEO

Open-SEO implements a provider-aware Drizzle ORM database schema that allows the same type-safe repository code to run against either Cloudflare D1 (SQLite) or PostgreSQL without modification.

The every-app/open-seo repository uses a runtime provider detection system to switch between SQLite and Postgres dialects while maintaining a unified API. This architecture enables zero-config self-hosting on Cloudflare's free tier while supporting opt-in Postgres deployments for production workloads. The implementation relies on parallel schema definitions, a request-scoped client proxy for Postgres, and comprehensive parity testing to guarantee identical behavior across both database providers.

Runtime Provider Detection

The system determines which database driver to use by inspecting the DATABASE_PROVIDER environment variable at runtime.

In src/db/provider.ts, the getDatabaseProvider() function validates the configuration and returns either "d1" or "postgres":

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

This function drives all subsequent database decisions, ensuring the correct client and schema are loaded based on the deployment environment.

Parallel Schema Definitions

Open-SEO maintains two complete sets of table definitions to support both dialects natively.

SQLite/D1 Schema Files

Tables for Cloudflare D1 use drizzle-orm/sqlite-core and reside in files like src/db/app.schema.ts. These define the canonical structure using SQLite-specific types and constraints.

Postgres Schema Files

Corresponding Postgres definitions live in src/db/pg/*.schema.ts and import from drizzle-orm/pg-core. Each file mirrors its SQLite counterpart exactly, ensuring structural parity between the two dialects.

The Schema Barrel

The src/db/schema.ts file acts as a unified barrel that exports a single runtimeSchema object based on the active provider:

// src/db/schema.ts (excerpt)
const runtimeSchema =
  getDatabaseProvider() === "postgres"
    ? { ...pgApp, ...pgAudit, ...pgSam, ...pgAuth, ...pgBilling, ...pgGsc, ...pgReddit, ...pgTelemetry }
    : { ...sqliteApp, ...sqliteAudit, ...sqliteSam, ...sqliteAuth, ...sqliteBilling, ...sqliteGsc, ...sqliteReddit, ...sqliteTelemetry };

export const {
  user,
  projects,
  savedKeywords,
  // … all other tables
} = runtimeSchema as unknown as AppSchema;

The AppSchema type is derived from the SQLite definitions, providing static type safety. The cast is safe because src/db/schema-parity.test.ts continuously validates that both dialects share identical tables, columns, primary keys, and foreign key behaviors.

Database Client Implementation

The repository abstracts connection handling behind a unified db export while managing the fundamental differences between D1's stateless connections and Postgres's socket requirements.

D1 SQLite Client

For Cloudflare D1 deployments, src/db/d1/client.ts creates a standard Drizzle client using the D1 binding:

// src/db/d1/client.ts
export const d1Db = drizzle(env.DB, { schema });

This client persists across requests and supports D1-specific features like the .batch() API for atomic writes.

Postgres Per-Request Client

Because Cloudflare Workers cannot reuse Postgres sockets across requests, src/db/pg/client.ts implements a request-scoped proxy pattern using AsyncLocalStorage:

// src/db/pg/client.ts (excerpt)
export const pgDb = new Proxy(
  {},
  {
    get(_target, prop, receiver) {
      const store = pgClientStore.getStore();
      if (!store) throw new Error("Postgres accessed outside request scope");
      return Reflect.get(store.db, prop, receiver);
    },
  },
) as ReturnType<typeof createPgDb>;

The withPgClient() wrapper function creates a fresh Postgres connection for each request and stores it in the async local storage:

export async function withPgClient<T>(fn: () => Promise<T>): Promise<T> {
  if (getDatabaseProvider() !== "postgres") return fn();
  const sql = withQueryRetries(postgres(getPostgresConnectionString(), { max: 1 }));
  return pgClientStore.run({ sql, db: createPgDb(sql) }, fn);
}

Entrypoints that touch the database wrap their logic with withPgClient(), ensuring proper connection lifecycle management in Postgres mode while operating as a no-op when using D1.

Unified Database Interface

The src/db/index.ts file exports a single db handle that works with standard Drizzle APIs regardless of the underlying provider:

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

Repository code imports this unified handle and writes queries once:

import { db, projects } from "@/db";
import { eq } from "drizzle-orm";

export async function getProjectById(id: string) {
  return await db.select().from(projects).where(eq(projects.id, id)).limit(1);
}

This pattern eliminates provider-specific logic from business logic, achieving true database agnosticism.

Handling Dialect Differences

While the schemas are structurally identical, the implementations handle behavioral differences between SQLite and Postgres through abstraction layers.

Schema Parity Testing

The src/db/schema-parity.test.ts file enforces that both dialect definitions remain synchronized. It verifies that tables, columns, indexes, and constraints match between the SQLite and Postgres implementations. This test also asserts that no code outside src/db/runBatch.ts calls the .batch() method, ensuring that D1-specific batching doesn't leak into provider-agnostic code.

Batch Writes and Transactions

D1 supports atomic batch writes through its .batch() API, while Postgres requires explicit transactions. The src/db/runBatch.ts module abstracts this difference:

import { runBatch } from "@/db/runBatch";
import { db, savedKeywords } from "@/db";
import { eq } from "drizzle-orm";

export async function deleteProjectKeywords(projectId: string) {
  await runBatch(async (batch) => {
    batch.delete(savedKeywords).where(eq(savedKeywords.projectId, projectId));
  });
}

In Postgres mode, runBatch executes statements within a transaction. In D1 mode, it uses the native batch API. This allows repository code to perform bulk operations efficiently on both platforms.

For explicit transactions in Postgres, code uses the withPgClient wrapper:

import { withPgClient } from "@/db";

export async function completeRun(runId: string) {
  return await withPgClient(async () => {
    await db.transaction(async (tx) => {
      await tx.update(rankCheckRuns).set({ status: "completed" }).where(eq(rankCheckRuns.id, runId));
      await tx.insert(rankSnapshots).values([/* … */]);
    });
  });
}

In D1 mode, withPgClient simply executes the callback, and transactions are handled through the batching mechanism.

Summary

• Provider Detection: src/db/provider.ts selects the database dialect at runtime via the DATABASE_PROVIDER environment variable.
• Dual Schema: Parallel definitions in src/db/*.schema.ts (SQLite) and src/db/pg/*.schema.ts (Postgres) provide native type support for both platforms.
• Unified API: The src/db/index.ts barrel exports a single db handle and table definitions that work identically across D1 and Postgres.
• Request Scoping: Postgres connections are managed per-request through AsyncLocalStorage in src/db/pg/client.ts, while D1 uses a persistent client.
• Parity Guarantee: src/db/schema-parity.test.ts ensures structural equivalence between dialects and restricts D1-specific batching to isolated modules.

Frequently Asked Questions

How does Open-SEO handle database connections in Cloudflare Workers with Postgres?

Open-SEO creates a fresh Postgres connection for each request using the withPgClient() wrapper in src/db/pg/client.ts. This function stores the connection in an AsyncLocalStorage instance, and the exported pgDb proxy retrieves it dynamically. This design works around Cloudflare Workers' inability to reuse sockets across requests while maintaining a clean import interface for repository code.

Can I switch from D1 to Postgres without changing my repository queries?

Yes. The src/db/index.ts barrel exports a unified db handle that provides the same Drizzle ORM API regardless of the provider. As long as your code imports tables from src/db/schema.ts and uses the standard db.select(), db.insert(), and db.update() methods, it will work on both D1 and Postgres without modification. Only batch operations require the runBatch() abstraction.

Why does Open-SEO maintain two separate schema files instead of using Drizzle's universal column types?

Open-SEO uses dialect-specific cores (drizzle-orm/sqlite-core and drizzle-orm/pg-core) to leverage native type definitions and constraints for each database. While this requires maintaining parallel files, the src/db/schema-parity.test.ts test suite guarantees they remain identical. This approach provides optimal performance characteristics and type fidelity for each platform while the runtime barrel in src/db/schema.ts presents a single, type-safe interface to the application.

What happens if I call Postgres-specific methods when running on D1?

The db export is typed as the D1 client type (typeof d1Db), so TypeScript will flag incompatible method calls at compile time. At runtime, if you somehow bypass the type system and call Postgres-specific APIs on D1, the code would likely fail because the underlying D1 driver doesn't implement those methods. The parity tests and unified schema exports prevent this by ensuring only common, supported operations are available through the main database interface.

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