How to Configure Database Backends for OpenSEO (SQLite/PostgreSQL)

To configure database backends for OpenSEO, set the DATABASE_PROVIDER environment variable to either d1 (SQLite) or postgres, then run the corresponding Drizzle migration and ensure your Hyperdrive binding is configured for PostgreSQL deployments.

OpenSEO abstracts its storage layer behind a provider-agnostic architecture that supports both Cloudflare D1 (SQLite) and PostgreSQL backends. This guide explains how to configure database backends for OpenSEO by leveraging the runtime detection system implemented in the every-app/open-seo repository.

Understanding the Database Provider Architecture

OpenSEO uses a single source of truth for database selection located in src/db/provider.ts. The getDatabaseProvider() function reads the DATABASE_PROVIDER environment variable at runtime and returns either "d1" or "postgres".

When DATABASE_PROVIDER is undefined, empty, or explicitly set to "d1", the application defaults to the SQLite backend. If set to "postgres", the application initializes the PostgreSQL client using a Cloudflare Hyperdrive connection string.

The TypeScript types for these environment variables are declared in src/env.d.ts, ensuring type safety across the codebase.

Configuring SQLite (D1) Backend

SQLite is the default database backend for OpenSEO when no provider is specified.

Set the environment variable in your .env file:

DATABASE_PROVIDER=d1

Or leave it undefined, as the system defaults to D1.

The SQLite schema is defined in src/db/d1/schema.ts and uses the Drizzle ORM configuration stored in drizzle.config.ts with dialect: "sqlite".

Configuring PostgreSQL Backend

To use PostgreSQL, you must configure both the environment variable and a Hyperdrive binding.

First, set the provider in your environment:

DATABASE_PROVIDER=postgres

Then, configure the Hyperdrive binding in your wrangler.toml:

[vars]
DATABASE_PROVIDER = "postgres"

[[hyperdrive]]
binding = "HYPERDRIVE"
database = "your-postgres-db"

The getPostgresConnectionString() function in src/db/provider.ts retrieves the connection string from env.HYPERDRIVE.connectionString at runtime. The PostgreSQL schema is maintained separately in src/db/pg/schema.ts and uses drizzle-pg.config.ts with dialect: "postgresql".

Running Database Migrations

You must run different migration commands depending on your chosen backend.

For SQLite (D1):

npx drizzle-kit generate --config drizzle.config.ts

For PostgreSQL:

npx drizzle-kit generate --config drizzle-pg.config.ts

The scripts/migrate-d1-to-postgres.ts utility script can migrate existing D1 data to PostgreSQL if you are switching backends in production.

Accessing the Database in Application Code

Application code interacts with the database through provider-aware imports that work transparently across both backends.

Import the database client from the barrel file:

import { db } from "@/db";

async function listProjects() {
  return await db.select().from(projects).limit(10);
}

For provider-specific logic, use the getDatabaseProvider() function:

import { getDatabaseProvider } from "@/db/provider";

const provider = getDatabaseProvider();
if (provider === "postgres") {
  // PostgreSQL-specific optimizations
}

Because the code imports from the provider-aware barrel (src/db/schema.ts), repositories work against either backend without modification.

Summary

• Provider Detection: The getDatabaseProvider() function in src/db/provider.ts reads DATABASE_PROVIDER and defaults to D1 (SQLite) when unspecified.
• SQLite Configuration: Set DATABASE_PROVIDER=d1 or leave undefined; use drizzle.config.ts for migrations.
• PostgreSQL Configuration: Set DATABASE_PROVIDER=postgres and configure the HYPERDRIVE binding in wrangler.toml; use drizzle-pg.config.ts for migrations.
• Schema Separation: SQLite schemas live in src/db/d1/schema.ts while PostgreSQL schemas are in src/db/pg/schema.ts.
• Runtime Consistency: Import from @/db to write database code that works transparently across both backends.

Frequently Asked Questions

What happens if DATABASE_PROVIDER is not set?

If DATABASE_PROVIDER is undefined, empty, or contains an unsupported value, getDatabaseProvider() defaults to "d1" (SQLite) according to the logic in src/db/provider.ts. This ensures backward compatibility and safe local development defaults.

Can I migrate existing data from D1 to PostgreSQL?

Yes. The scripts/migrate-d1-to-postgres.ts script handles provider-aware migrations and can copy existing D1 data into a new PostgreSQL instance. Run this after configuring your PostgreSQL backend and before switching your production deployment.

Do I need to write different database code for each backend?

No. OpenSEO uses provider-aware barrel exports (src/db/schema.ts) that abstract the underlying dialect. You write standard Drizzle ORM queries that work against both SQLite and PostgreSQL. Only use getDatabaseProvider() when you need dialect-specific optimizations.

How does the PostgreSQL connection work in production?

In production, the getPostgresConnectionString() function extracts the connection string from the HYPERDRIVE Cloudflare binding (env.HYPERDRIVE.connectionString). This connection pooling service is configured in wrangler.toml and injected into the Workers runtime environment automatically.

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