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

> Discover how Open-SEO's Drizzle ORM database schema works seamlessly with D1 and Postgres. Run type-safe repository code against both databases without modification. Learn more!

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

---

**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`](https://github.com/every-app/open-seo/blob/main/src/db/provider.ts), the `getDatabaseProvider()` function validates the configuration and returns either `"d1"` or `"postgres"`:

```typescript
// 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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/src/db/schema.ts) file acts as a unified barrel that exports a single `runtimeSchema` object based on the active provider:

```typescript
// 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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/src/db/d1/client.ts) creates a standard Drizzle client using the D1 binding:

```typescript
// 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`](https://github.com/every-app/open-seo/blob/main/src/db/pg/client.ts) implements a **request-scoped proxy** pattern using `AsyncLocalStorage`:

```typescript
// 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:

```typescript
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`](https://github.com/every-app/open-seo/blob/main/src/db/index.ts) file exports a single `db` handle that works with standard Drizzle APIs regardless of the underlying provider:

```typescript
// 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:

```typescript
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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/src/db/runBatch.ts) module abstracts this difference:

```typescript
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:

```typescript
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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/src/db/pg/client.ts), while D1 uses a persistent client.
- **Parity Guarantee**: [`src/db/schema-parity.test.ts`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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.