How to Deploy OpenSEO with PostgreSQL: A Complete Cloudflare Workers Guide

You can deploy OpenSEO with PostgreSQL by setting DATABASE_PROVIDER=postgres, configuring a Cloudflare Hyperdrive binding in wrangler.jsonc, and running migrations from the drizzle-pg/ folder.

OpenSEO ships with a provider‑aware database layer that defaults to Cloudflare D1 (SQLite) but seamlessly supports PostgreSQL for production workloads. This guide walks through the exact steps to switch providers, based on the official every-app/open-seo source code.

---

Understanding OpenSEO's Database Architecture

The database abstraction lives in src/db/. At runtime, the code inspects DATABASE_PROVIDER to determine whether to use the D1 SQLite driver or the PostgreSQL driver.

Key design points:

• Provider flag: DATABASE_PROVIDER=postgres triggers PostgreSQL mode
• Hyperdrive binding: Cloudflare Workers receive the connection string via a named Hyperdrive binding, not hardcoded secrets
• Schema parity: Parallel definitions in src/db/ (SQLite) and src/db/pg/ (PostgreSQL) stay synchronized via src/db/schema-parity.test.ts

---

Prerequisites

Before starting, ensure you have:

• A running PostgreSQL instance (local Docker or managed service)
• The OpenSEO repository cloned locally
• Node.js and pnpm installed
• wrangler CLI authenticated with your Cloudflare account

---

Step 1: Provision Your PostgreSQL Instance

For local development, use the Docker setup documented in docs/LOCAL_POSTGRES.md:

docker run --name openseo-postgres \
  -e POSTGRES_USER=openseo \
  -e POSTGRES_PASSWORD=openseo \
  -e POSTGRES_DB=openseo \
  -p 5433:5432 \
  -d postgres:16

For production, provision a managed PostgreSQL instance that Cloudflare Hyperdrive can reach. Cloudflare supports connections to AWS RDS, Google Cloud SQL, and other providers.

---

Step 2: Apply PostgreSQL Migrations

OpenSEO stores PostgreSQL migrations separately in drizzle-pg/. Run them with the connection string exported:

POSTGRES_DATABASE_URL=postgres://openseo:openseo@localhost:5433/openseo \
  pnpm db:migrate:pg

This executes the migration scripts defined in drizzle-pg.config.ts against your database.

---

Step 3: Configure the Provider Flag

Create or update .env.local (or your production secret store) to switch the runtime provider:

DATABASE_PROVIDER=postgres

The worker-side code in src/db/ reads this variable to instantiate the correct driver.

---

Step 4: Bind the Hyperdrive Connection

In wrangler.jsonc, uncomment the hyperdrive block and name your binding:

{
  "name": "open-seo",
  "type": "javascript",
  // ...
  "hyperdrive": [
    {
      "binding": "HYPERDRIVE",
      "type": "hyperdrive",
      "localConnectionString": "postgres://openseo:openseo@localhost:5433/openseo"
    }
  ]
}

Binding behavior:

• Local development: Miniflare resolves HYPERDRIVE to localConnectionString
• Production: Cloudflare resolves HYPERDRIVE to the secret you configure (HYPERDRIVE_URL or HYPERDRIVE_LOCAL_CONNECTION_STRING_HYPERDRIVE)

---

Step 5: Deploy the Worker

After configuration, deploy with:

# Local testing

pnpm dev

# Production deployment

wrangler deploy

# or

pnpm deploy

The worker will resolve the Hyperdrive binding at runtime and route all database calls through the PostgreSQL driver.

---

Step 6: Verify the Deployment

Confirm your setup by inspecting the database:

1. Connect with psql or any PostgreSQL client:

   psql postgres://openseo:openseo@localhost:5333/openseo

2. List tables created by migrations:

   \dt

3. Create a test project in OpenSEO and verify rows appear in the projects table.

---

Key Files Reference

File	Purpose
docs/LOCAL_POSTGRES.md	Full local PostgreSQL setup guide
drizzle-pg.config.ts	Drizzle-Kit configuration for PostgreSQL
drizzle-pg/	Hand-written migration scripts
src/db/pg/	PostgreSQL-specific schema definitions
wrangler.jsonc	Worker configuration with Hyperdrive binding
src/db/schema-parity.test.ts	Test ensuring SQLite/PostgreSQL schema sync
README.md	General deployment overview

---

Summary

• Set DATABASE_PROVIDER=postgres in environment configuration to switch from D1
• Configure Hyperdrive in wrangler.jsonc to securely provide connection strings
• Run pnpm db:migrate:pg after provisioning PostgreSQL to apply schema
• Maintain schema parity using pnpm db:generate:pg when modifying src/db/ definitions
• Test with src/db/schema-parity.test.ts to prevent dialect drift

---

Frequently Asked Questions

What PostgreSQL versions does OpenSEO support?

OpenSEO targets PostgreSQL 14+, though the migrations in drizzle-pg/ are generally compatible with PostgreSQL 12 and later. The local development container uses PostgreSQL 16 as specified in docs/LOCAL_POSTGRES.md.

Can I switch between D1 and PostgreSQL without data loss?

No direct migration path exists between D1 and PostgreSQL in OpenSEO. The schemas are compatible, but you must export data from D1 via SQL dump and reload it into PostgreSQL, or use OpenSEO's API to re-sync projects and keywords.

Why does OpenSEO use Hyperdrive instead of direct connection strings?

Hyperdrive provides connection pooling and reduces cold-start latency for Cloudflare Workers. It also keeps credentials out of worker code—only the binding name HYPERDRIVE appears in wrangler.jsonc, while actual connection strings live in Cloudflare's secret store.

How do I keep SQLite and PostgreSQL schemas synchronized?

After modifying any schema file in src/db/, run pnpm db:generate:pg to produce PostgreSQL migrations. The test suite in src/db/schema-parity.test.ts fails if the dialects diverge, enforcing synchronization at build time.

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