# Database Requirements for OpenSEO: Cloudflare D1 and PostgreSQL Configuration

> Discover OpenSEO database requirements. Learn about Cloudflare D1 for serverless and PostgreSQL for self-hosted instances using Drizzle ORM dual-dialect.

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

---

**OpenSEO requires a SQLite-compatible database (provisioned via Cloudflare D1) for standard serverless deployments, while offering full PostgreSQL support for self-hosted instances through Drizzle ORM dual-dialect configuration.**

The `every-app/open-seo` repository implements a flexible data layer designed for edge deployment. Understanding the database requirements for OpenSEO is essential whether you are deploying to Cloudflare Workers or managing a self-hosted infrastructure. The project leverages **Drizzle ORM** to abstract database specifics, enabling seamless switching between SQLite and PostgreSQL backends without modifying schema definitions.

## Primary Database Architecture (Cloudflare D1)

Standard OpenSEO deployments target **Cloudflare D1**, a serverless SQLite-compatible database optimized for Cloudflare Workers. This configuration provides geographic distribution and edge-caching capabilities ideal for SEO tooling workloads.

### Drizzle Configuration for D1

The [`drizzle.config.ts`](https://github.com/every-app/open-seo/blob/main/drizzle.config.ts) file establishes the SQLite connection parameters and migration paths. It explicitly sets the dialect to `"sqlite"` and directs Drizzle-Kit to output migration files to the `./drizzle` directory.

```typescript
// ── drizzle.config.ts ──
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  dialect: "sqlite",
  schema: "./src/db/schema.ts",
  out: "./drizzle",
});

```

## Self-Hosted PostgreSQL Support

For users requiring self-hosted deployments or advanced database features, OpenSEO supports **PostgreSQL** through a parallel configuration. The schema remains identical, ensuring zero application code changes when switching backends.

### PostgreSQL Configuration

The [`drizzle-pg.config.ts`](https://github.com/every-app/open-seo/blob/main/drizzle-pg.config.ts) file switches the Drizzle dialect to `"pg"` while referencing the same TypeScript schema definitions. This configuration enables migration generation for PostgreSQL-specific SQL syntax.

```typescript
// ── drizzle-pg.config.ts ──
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  dialect: "postgresql",
  schema: "./src/db/schema.ts",
  out: "./drizzle-pg",
});

```

## Unified Schema Definitions

Both database backends share identical TypeScript schema definitions located in `src/db/`. Files such as [`src/db/better-auth-schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/better-auth-schema.ts), [`src/db/audit.schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/audit.schema.ts), and [`src/db/project-context.schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/project-context.schema.ts) define tables using Drizzle's SQLite-core exports, which the ORM translates appropriately when connected to PostgreSQL.

```typescript
// ── src/db/better-auth-schema.ts ──
import { sqliteTable, text, integer } from "drizzle-orm/sqlite-core";

export const user = sqliteTable("user", {
  id: text("id").primaryKey(),
  email: text("email").notNull(),
  hashedPassword: text("hashed_password"),
  createdAt: integer("created_at", { mode: "timestamp" }).defaultNow(),
});

```

## Runtime Database Client Selection

The application dynamically instantiates the appropriate database driver based on environment variables. The client creation logic in [`src/lib/db.ts`](https://github.com/every-app/open-seo/blob/main/src/lib/db.ts) detects the database type by checking the `DATABASE_URL` protocol prefix.

```typescript
// ── src/lib/db.ts ──
import { drizzle } from "drizzle-orm";
import { DatabaseSync } from "node:sqlite";
import { Client as PGClient } from "pg";
import * as schema from "../db/schema";

export function createDatabaseClient(env: { DATABASE_URL?: string }) {
  if (env.DATABASE_URL?.startsWith("postgres")) {
    const pg = new PGClient({ connectionString: env.DATABASE_URL });
    return drizzle(pg, { schema });
  } else {
    const sqlite = new DatabaseSync(env.D1_DATABASE_URL ?? ":memory:");
    return drizzle(sqlite, { schema });
  }
}

```

## Data Migration Between Databases

Database migrations utilize **Drizzle-Kit** commands. For D1 deployments, generate and apply migrations using:

```bash

# Generate migration files for D1 (SQLite)

pnpm run db:generate

# Apply migrations to the D1 instance

pnpm run db:migrate

```

### Migrating from D1 to PostgreSQL

When transitioning from Cloudflare D1 to a self-hosted PostgreSQL instance, the [`scripts/migrate-d1-to-postgres.ts`](https://github.com/every-app/open-seo/blob/main/scripts/migrate-d1-to-postgres.ts) utility transfers existing data while preserving schema integrity. This script accepts source and target database URLs via command-line arguments.

```bash

# Transfer data from D1 to PostgreSQL

pnpm ts-node scripts/migrate-d1-to-postgres.ts \
  --source-d1-url $D1_DATABASE_URL \
  --target-pg-url $POSTGRES_URL

```

## Summary

- **Cloudflare D1 (SQLite)** serves as the default database for OpenSEO serverless deployments, configured via [`drizzle.config.ts`](https://github.com/every-app/open-seo/blob/main/drizzle.config.ts).
- **PostgreSQL** is fully supported for self-hosted environments using [`drizzle-pg.config.ts`](https://github.com/every-app/open-seo/blob/main/drizzle-pg.config.ts) with identical schema definitions.
- Schema files in `src/db/` work across both dialects without modification, ensuring backend portability.
- Runtime client selection in [`src/lib/db.ts`](https://github.com/every-app/open-seo/blob/main/src/lib/db.ts) automatically detects the database type via the `DATABASE_URL` environment variable.
- The [`scripts/migrate-d1-to-postgres.ts`](https://github.com/every-app/open-seo/blob/main/scripts/migrate-d1-to-postgres.ts) utility enables seamless data transfer between deployment modes.

## Frequently Asked Questions

### Does OpenSEO require Cloudflare D1 specifically, or can I use any SQLite database?

While optimized for Cloudflare D1, OpenSEO works with any SQLite-compatible database. The `createDatabaseClient` function in [`src/lib/db.ts`](https://github.com/every-app/open-seo/blob/main/src/lib/db.ts) accepts standard SQLite connection strings via the `D1_DATABASE_URL` environment variable, though D1 provides the intended serverless experience with automatic replication.

### What changes are needed to switch from D1 to PostgreSQL?

No schema modifications are required. Deploy the [`drizzle-pg.config.ts`](https://github.com/every-app/open-seo/blob/main/drizzle-pg.config.ts) configuration for generating PostgreSQL-specific migrations, and set a PostgreSQL connection string in the `DATABASE_URL` environment variable. The runtime client automatically detects the `postgres` protocol prefix and instantiates the appropriate driver.

### Is there a performance difference between D1 and PostgreSQL in OpenSEO?

Cloudflare D1 offers lower latency for edge-deployed Workers due to geographic distribution and proximity to compute resources. PostgreSQL provides superior concurrency control, advanced indexing strategies, and complex query optimization suitable for high-traffic self-hosted deployments or analytical workloads.

### How do I migrate existing data from D1 to PostgreSQL?

Use the provided migration script with valid connection strings for both databases: `pnpm ts-node scripts/migrate-d1-to-postgres.ts --source-d1-url $D1_DATABASE_URL --target-pg-url $POSTGRES_URL`. This script reads the SQLite schema from D1 and inserts data into the corresponding PostgreSQL tables while maintaining referential integrity.