What Database Backends Does OpenSEO Support? D1 and PostgreSQL Options Explained

OpenSEO supports two database backends: Cloudflare D1 (SQLite) as the default and PostgreSQL as an optional scalable alternative, selected via the DATABASE_PROVIDER environment variable.

The open-source SEO toolkit from every-app/open-seo gives developers flexibility in choosing their data layer. You can start with D1 for simplicity and low operational overhead, then migrate to PostgreSQL when your project demands higher storage limits or advanced query capabilities.

How the Database Provider Is Configured

OpenSEO determines which backend to use through a centralized provider system defined in src/db/provider.ts. The implementation uses a discriminated union type that strictly limits valid options:

type DatabaseProvider = "d1" | "postgres";

export function getDatabaseProvider(): DatabaseProvider {
  const provider = process.env.DATABASE_PROVIDER ?? "d1";
  if (provider === "d1" || provider === "postgres") return provider;
  throw new Error(`Unsupported DATABASE_PROVIDER "${String(provider)}". Expected "d1" or "postgres".`);
}

The type constraint appears in src/env.d.ts to ensure type safety across the codebase:

interface ProcessEnv {
  readonly DATABASE_PROVIDER?: "d1" | "postgres";
}

Backend 1: Cloudflare D1 (SQLite)

D1 is the default database engine, requiring zero additional configuration. It leverages Cloudflare's serverless SQLite platform, making it ideal for:

• Small-to-medium projects
• Rapid prototyping and local development
• Deployments where managed database infrastructure is undesirable

When DATABASE_PROVIDER is unset or explicitly set to "d1", OpenSEO initializes a Drizzle-ORM D1 client through src/db/index.ts:

export const db = (getDatabaseProvider() === "postgres")
  ? drizzlePostgres(/* … */)
  : drizzleD1(/* … */);  // D1 path taken when provider is "d1"

Running Locally with D1

# D1 is the default — no environment variables required

pnpm dev

Backend 2: PostgreSQL

PostgreSQL becomes available when you set DATABASE_PROVIDER=postgres. This backend integrates through drizzle-orm/postgres-js and supports:

• Production workloads exceeding D1's storage constraints
• Complex analytical queries
• Existing PostgreSQL infrastructure

The configuration files drizzle-pg.config.ts and drizzle-prod.config.ts declare the dialect as "postgresql", ensuring schema migrations and query generation align with PostgreSQL semantics.

Required Environment Variables

Variable	Purpose
DATABASE_PROVIDER=postgres	Activates PostgreSQL mode
POSTGRES_DATABASE_URL	Connection string for database access

In production Cloudflare deployments, this typically references a Hyperdrive Postgres binding for connection pooling and latency optimization.

Running Locally with PostgreSQL

export DATABASE_PROVIDER=postgres
export POSTGRES_DATABASE_URL=postgres://user:pass@localhost:5432/open_seo
pnpm dev

Production Deployment Configuration

DATABASE_PROVIDER=postgres
POSTGRES_DATABASE_URL=postgres://user:pass@host:5432/db

The deployment pipeline in alchemy.run.ts automatically handles Hyperdrive binding when these variables are present.

Database Client Initialization

The unified database export in src/db/index.ts provides a single db interface regardless of backend. This abstraction lets application code remain provider-agnostic:

export const db = (getDatabaseProvider() === "postgres")
  ? drizzlePostgres(/* postgres client config */)
  : drizzleD1(/* D1 binding config */);

Both clients expose identical Drizzle-ORM interfaces, so queries, transactions, and migrations work transparently across backends.

Migrating Between Backends

OpenSEO includes a dedicated migration utility for moving from D1 to PostgreSQL without data loss. The script scripts/migrate-d1-to-postgres.ts handles:

• Schema translation between SQLite and PostgreSQL dialects
• Data export from D1 and import into PostgreSQL
• Type conversion and constraint preservation

Run this when your D1 instance approaches storage limits or when PostgreSQL-specific features become necessary.

Key Files in the OpenSEO Database Layer

File	Responsibility
src/db/provider.ts	Provider detection and validation
src/env.d.ts	Environment variable type definitions
src/db/index.ts	Runtime client selection and export
drizzle-pg.config.ts	PostgreSQL-specific ORM configuration
drizzle-prod.config.ts	Production PostgreSQL settings
scripts/migrate-d1-to-postgres.ts	D1-to-PostgreSQL data migration

Summary

• Two backends supported: Cloudflare D1 (SQLite) and PostgreSQL, configured through DATABASE_PROVIDER
• D1 is default: Zero-config local and serverless deployment via drizzle-orm D1 adapter
• PostgreSQL for scale: Activated with DATABASE_PROVIDER=postgres and POSTGRES_DATABASE_URL
• Type-safe provider system: src/db/provider.ts enforces valid values at build and runtime
• Seamless abstraction: src/db/index.ts exposes a unified db client regardless of backend
• Migration path included: scripts/migrate-d1-to-postgres.ts enables backend transitions

Frequently Asked Questions

How do I switch from D1 to PostgreSQL in OpenSEO?

Set DATABASE_PROVIDER=postgres and provide POSTGRES_DATABASE_URL in your environment. For existing D1 data, run scripts/migrate-d1-to-postgres.ts to transfer records before switching the provider in production.

What happens if I set an invalid DATABASE_PROVIDER value?

The getDatabaseProvider() function in src/db/provider.ts throws a runtime error: Unsupported DATABASE_PROVIDER "invalid". Expected "d1" or "postgres". This prevents accidental misconfiguration from reaching production.

Does OpenSEO support MySQL or other database engines?

No. According to the source code in src/db/provider.ts, the DatabaseProvider type is strictly limited to "d1" | "postgres". Adding support for additional backends would require extending this union type and implementing corresponding Drizzle-ORM client initialization in src/db/index.ts.

Can I use PostgreSQL locally without Cloudflare Hyperdrive?

Yes. For local development, POSTGRES_DATABASE_URL can point to any accessible PostgreSQL instance—local Docker containers, managed services, or remote servers. Hyperdrive binding is only required for optimized Cloudflare Workers production deployments.

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