How to Configure OpenSEO for Postgres Database with Hyperdrive

Configure OpenSEO to use Postgres by setting DATABASE_PROVIDER=postgres in .env.local, uncommenting the HYPERDRIVE binding block in wrangler.jsonc, and running migrations via POSTGRES_DATABASE_URL while the Cloudflare Worker connects through the Hyperdrive socket at runtime.

OpenSEO is an open-source SEO management platform built for Cloudflare Workers that defaults to Cloudflare D1 (SQLite) for data storage. When you need the advanced features of a Postgres database—such as complex queries, JSONB columns, or full-text search—you can switch the provider to use Postgres with Hyperdrive, Cloudflare's managed TCP socket and connection pooling service for serverless environments.

Understanding the Database Provider Architecture

OpenSEO abstracts database access through a provider pattern defined in [src/db/provider.ts](https://github.com/every-app/open-seo/blob/main/src/db/provider.ts). By default, the application initializes a D1 client, but when the DATABASE_PROVIDER environment variable is set to postgres, the code path switches to the Postgres implementation in [src/db/pg/client.ts](https://github.com/every-app/open-seo/blob/main/src/db/pg/client.ts).

// src/db/provider.ts
// Selecting the postgres provider loads the Hyperdrive-backed client

At runtime, the Cloudflare Worker does not use a standard connection string. Instead, it receives a Hyperdrive binding—a special socket object that proxies TCP connections to your Postgres instance. This binding is accessed via the HYPERDRIVE variable in the Worker's environment, as referenced in [src/server.ts](https://github.com/every-app/open-seo/blob/main/src/server.ts) where the socket is reclaimed after each request to prevent connection leaks.

Step 1: Set the Database Provider Flag

Create or edit .env.local in the repository root to declare the Postgres provider:

# .env.local

DATABASE_PROVIDER=postgres

This flag instructs the provider factory to instantiate the postgres-js client rather than the D1 client when the application boots. According to the local Postgres documentation, this is the primary switch that changes the entire database backend.

Step 2: Configure the Hyperdrive Binding

OpenSEO ships with a commented-out Hyperdrive configuration in wrangler.jsonc. You must uncomment this block to enable the binding:

// wrangler.jsonc
{
  // ... other config ...
  "hyperdrive": [
    {
      "binding": "HYPERDRIVE",
      "localConnectionString": "postgres://openseo:openseo@localhost:5433/openseo"
    }
  ]
}

The localConnectionString field is used only during local development (wrangler dev). When deployed to Cloudflare's edge, the platform replaces this with the actual Hyperdrive socket URL associated with your account. As noted in the configuration guide, leaving this commented causes the Worker to fail at runtime when attempting to connect to Postgres.

Step 3: Environment Variables for Node Tooling

While the Worker uses the Hyperdrive binding, command-line tools like drizzle-kit and custom migration scripts require a standard connection string. Set POSTGRES_DATABASE_URL for these Node.js processes:

# For running migrations and schema generation

POSTGRES_DATABASE_URL=postgres://openseo:openseo@localhost:5433/openseo

Crucially, this variable is not used by the Worker runtime; it exists solely for the Node.js side of the toolchain. The documentation clarifies that POSTGRES_DATABASE_URL handles migrations while HYPERDRIVE handles application traffic.

Running Postgres Locally with Docker

For local development, start a throwaway Postgres container that matches the localConnectionString configured above:

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

This exposes Postgres on port 5433 to avoid conflicts with existing local instances. The container name openseo-postgres makes it easy to stop and remove later with docker rm -f openseo-postgres.

Database Migrations and Node Tooling

With the container running and environment variables set, apply the database schema using the Postgres-specific migration command:

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

This executes the migration scripts (located in [scripts/migrate-d1-to-postgres.ts](https://github.com/every-app/open-seo/blob/main/scripts/migrate-d1-to-postgres.ts)) against your local Postgres instance. These scripts read POSTGRES_DATABASE_URL directly and are not subject to the Hyperdrive abstraction used by the Worker.

Starting the Development Server

Once migrations are complete, start the Wrangler development server:

pnpm dev

The Worker will connect to localhost:5433 through the Hyperdrive socket defined in wrangler.jsonc. All queries route through src/db/pg/client.ts, which wraps drizzle-orm/postgres-js and uses the HYPERDRIVE binding for its connection pool.

Summary

• Provider Flag: Set DATABASE_PROVIDER=postgres in .env.local to switch from D1 to Postgres via [src/db/provider.ts](https://github.com/every-app/open-seo/blob/main/src/db/provider.ts).
• Hyperdrive Binding: Uncomment the HYPERDRIVE block in wrangler.jsonc to expose the socket binding; use localConnectionString for development.
• Dual Connection Strings: Use POSTGRES_DATABASE_URL for Node.js tooling (migrations), while the Worker exclusively uses the HYPERDRIVE binding.
• Local Setup: Run postgres:16 in Docker on port 5433, execute pnpm db:migrate:pg, then start pnpm dev to verify the configuration.

Frequently Asked Questions

What is the difference between HYPERDRIVE and POSTGRES_DATABASE_URL?

HYPERDRIVE is a Cloudflare Workers binding that provides a managed TCP socket to Postgres, used exclusively by the Worker runtime at the edge. POSTGRES_DATABASE_URL is a standard connection string used by Node.js tooling like drizzle-kit and migration scripts that run outside the Worker environment. The Worker ignores POSTGRES_DATABASE_URL entirely.

Do I need to uncomment the Hyperdrive binding for local development?

Yes. The wrangler.jsonc file ships with the Hyperdrive configuration commented out by default. You must uncomment the "hyperdrive": [...] block and set the localConnectionString to your Docker Postgres instance; otherwise, the Worker will throw a binding error when attempting to initialize the database client.

Can I migrate existing D1 data to Postgres?

Yes. OpenSEO includes migration scripts designed to transfer data from Cloudflare D1 to Postgres. These scripts, located in the scripts/ directory, read from your D1 export and write to the Postgres instance defined by POSTGRES_DATABASE_URL. Run pnpm db:migrate:pg after setting the connection string to execute the migration.

Does Hyperdrive work with external Postgres providers like Supabase or AWS RDS?

Yes. When deployed to production, Cloudflare Hyperdrive can proxy connections to any Postgres-compatible database, including Supabase, AWS RDS, or Google Cloud SQL. You configure the actual destination in the Cloudflare Dashboard under Hyperdrive settings; the localConnectionString in wrangler.jsonc is only for local development and is replaced automatically in production.

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