Database Requirements for OpenSEO: Cloudflare D1 and PostgreSQL Configuration

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 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.

// ── 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 file switches the Drizzle dialect to "pg" while referencing the same TypeScript schema definitions. This configuration enables migration generation for PostgreSQL-specific SQL syntax.

// ── 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, src/db/audit.schema.ts, and src/db/project-context.schema.ts define tables using Drizzle's SQLite-core exports, which the ORM translates appropriately when connected to PostgreSQL.

// ── 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 detects the database type by checking the DATABASE_URL protocol prefix.

// ── 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:

# 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 utility transfers existing data while preserving schema integrity. This script accepts source and target database URLs via command-line arguments.

# 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.
• PostgreSQL is fully supported for self-hosted environments using 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 automatically detects the database type via the DATABASE_URL environment variable.
• The 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 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 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.

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