How to Set Up PostgreSQL for Local Development with Drizzle in OpenSEO

Set up PostgreSQL for local development with Drizzle by starting a Docker container, configuring POSTGRES_DATABASE_URL, and toggling DATABASE_PROVIDER=postgres in your environment.

OpenSEO uses a provider-aware db layer (src/db/…) that abstracts database access behind Drizzle ORM. While the default configuration targets Cloudflare D1 (SQLite), you can opt into PostgreSQL for local development by switching a single environment flag. This guide walks through the complete setup using the repository's official tooling and configuration files.

Prerequisites

Before starting, ensure you have:

• Docker installed for running PostgreSQL locally
• Node.js and pnpm installed
• A local copy of the every-app/open-seo repository

Step 1: Start a Local PostgreSQL Container

The fastest way to get PostgreSQL running locally is with Docker. The following command creates a throw-away container named openseo-postgres on port 5433:

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

Verify the container is ready:

docker exec openseo-postgres pg_isready -U openseo -d openseo

Step 2: Apply Drizzle Migrations to PostgreSQL

OpenSEO separates its Drizzle configuration by database provider. For PostgreSQL, Drizzle Kit reads from drizzle-pg.config.ts, which pulls the connection string from POSTGRES_DATABASE_URL:

// drizzle-pg.config.ts (lines 8-15)
export default defineConfig({
  dialect: 'postgresql',
  schema: './src/db/pg/schema.ts',
  dbCredentials: {
    url: process.env.POSTGRES_DATABASE_URL,
  },
});

Run migrations with the environment variable set:

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

This command invokes Drizzle Kit, which applies the SQL migration files located under drizzle-pg/.

Step 3: Configure the Application to Use PostgreSQL

Tell the Cloudflare Vite runtime to bind the Hyperdrive variable to PostgreSQL instead of D1. Create or edit .env.local:

# .env.local

DATABASE_PROVIDER=postgres

The runtime reads this flag and automatically binds the Hyperdrive configuration to your local Docker container. As noted in [docs/LOCAL_POSTGRES.md](https://github.com/every-app/open-seo/blob/main/docs/LOCAL_POSTGRES.md#L55-L63), the worker code itself never talks directly to Postgres—it only uses the binding.

Step 4: Start the Development Server

Launch the application with either command:

pnpm dev          # standard dev server

pnpm dev:agents   # if using the dev-agent workflow

Step 5: Verify the PostgreSQL Setup

Confirm the schema was created and populated:

# List all tables

docker exec openseo-postgres psql -U openseo -d openseo -c "\dt"

# Check project data

docker exec openseo-postgres psql -U openseo -d openseo -c "SELECT count(*) FROM projects;"

Step 6: Clean Up When Finished

Remove the container:

docker rm -f openseo-postgres

Understanding the PostgreSQL Architecture

OpenSEO's database abstraction ensures schema parity between SQLite and PostgreSQL. Key architectural details:

Provider-specific schema files (src/db/pg/schema.ts) define PostgreSQL-compatible types and constraints that Drizzle consumes when DATABASE_PROVIDER=postgres is set.

Hyperdrive binding in wrangler.jsonc resolves to the same POSTGRES_DATABASE_URL value, ensuring the runtime and migration tooling connect to identical endpoints.

CI enforcement via [src/db/schema-parity.test.ts](https://github.com/every-app/open-seo/blob/main/src/db/schema-parity.test.ts) guarantees that any schema change applies to both dialects—CI fails if they drift apart.

Migrating Data from D1 to PostgreSQL

If you have existing local data in D1, use the provided migration helper:

pnpm db:migrate:d1-to-pg

This script ([scripts/migrate-d1-to-postgres.ts](https://github.com/every-app/open-seo/blob/main/scripts/migrate-d1-to-postgres.ts#L42-L55)) validates POSTGRES_DATABASE_URL and transfers data from the SQLite database to PostgreSQL.

Summary

• Docker container: Run PostgreSQL 16 locally on port 5433 with docker run
• Connection string: Set POSTGRES_DATABASE_URL for Drizzle Kit migrations
• Environment flag: Add DATABASE_PROVIDER=postgres to .env.local
• Migration command: Run pnpm db:migrate:pg to apply schema changes
• Architecture: Hyperdrive binding abstracts direct Postgres access—workers use bindings, not raw connections

Frequently Asked Questions

What version of PostgreSQL does OpenSEO require?

OpenSEO targets PostgreSQL 16 as shown in the Docker examples in [docs/LOCAL_POSTGRES.md](https://github.com/every-app/open-seo/blob/main/docs/LOCAL_POSTGRES.md). Earlier versions may work but are not officially tested in CI.

Why does the app use Hyperdrive instead of connecting directly to PostgreSQL?

The Hyperdrive binding (wrangler.jsonc) provides connection pooling and caching at the edge. The worker code remains provider-agnostic—it only uses the binding, which resolves to the correct database based on DATABASE_PROVIDER. This matches the production architecture where Cloudflare manages the Postgres connection.

Can I run both SQLite and PostgreSQL simultaneously?

No—DATABASE_PROVIDER is a single-value flag. However, you can switch between them by changing the environment variable and restarting the dev server. Schema parity testing ensures both paths remain compatible.

How do I keep PostgreSQL and SQLite schemas synchronized?

Run [src/db/schema-parity.test.ts](https://github.com/every-app/open-seo/blob/main/src/db/schema-parity.test.ts) in CI. Any structural change to src/db/pg/schema.ts or src/db/d1/schema.ts must be mirrored in the other file, or the test fails.

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