How to Run OpenSEO Locally with PostgreSQL for Development

Set DATABASE_PROVIDER=postgres in .env.local, start a Docker PostgreSQL container on port 5433, run pnpm db:migrate:pg to apply migrations, then start the dev server with pnpm dev.

OpenSEO is built to run on Cloudflare D1 (SQLite) by default, but it also supports PostgreSQL for installations that outgrow D1's storage limits. Running OpenSEO locally with PostgreSQL lets you test scaling features and verify database parity without disrupting the default SQLite workflow. This guide walks through the complete setup using the official repository at every-app/open-seo.

Prerequisites

Before you begin, ensure you have:

• Docker Desktop or Docker Engine installed
• The regular local development setup completed per docs/LOCAL_DEVELOPMENT.md
• Node.js and pnpm configured for the project

Start a Local PostgreSQL Container

OpenSEO expects PostgreSQL to be available on a non-standard port to avoid conflicts with system-wide installations. Run the container:

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

Port 5433 is deliberate—this prevents collision with any existing PostgreSQL service on 5432.

Verify the container is ready:

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

The connection string you'll use is:

postgres://openseo:openseo@localhost:5433/openseo

Apply PostgreSQL Schema Migrations

The PostgreSQL schema definitions and migrations live in drizzle-pg/. Apply them using the dedicated npm script:

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

This executes drizzle-kit with the correct target database. The db:migrate:pg script is defined in package.json and handles the migration execution.

Configure OpenSEO to Use PostgreSQL

The database provider is controlled by an environment variable. Create or edit .env.local:

DATABASE_PROVIDER=postgres

How the Connection Works

OpenSEO's Cloudflare Workers runtime reads database configuration through a Hyperdrive binding defined in wrangler.jsonc. When running locally, Miniflare resolves this binding to a localConnectionString that points at your Docker container.

If you need to override the connection string directly, pass it as a command-line variable:

CLOUDFLARE_HYPERDRIVE_LOCAL_CONNECTION_STRING_HYPERDRIVE=postgres://openseo:openseo@localhost:5433/openseo \
  pnpm dev

Start the development server:

pnpm dev

The worker now connects to PostgreSQL instead of the default D1 SQLite database.

Verify the PostgreSQL Setup

Confirm migrations applied successfully:

# List all tables created by migrations

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

Test that the app writes data correctly:

# Check row counts after creating a project in the UI

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

If you see expected tables and increasing row counts, your local PostgreSQL development environment is fully operational.

Switch Back to D1 SQLite

To revert to the default SQLite provider, change the environment variable:

DATABASE_PROVIDER=d1

Restart the dev server to apply the change.

Clean Up the Container

When finished testing, remove the throw-away database:

docker rm -f openseo-postgres

Architecture Overview: How Database Switching Works

Understanding the internal implementation helps debug issues and extend functionality.

Database Provider Abstraction (src/db/provider.ts)

The DATABASE_PROVIDER environment variable drives client selection in src/db/provider.ts:

• d1 → Loads src/db/d1/client.ts (SQLite via Cloudflare D1)
• postgres → Loads src/db/pg/client.ts (PostgreSQL via pg driver)

This abstraction ensures the rest of the application code remains provider-agnostic.

Schema Parity Between Dialects

OpenSEO maintains separate schema files for each database:

Dialect	Location
SQLite/D1	src/db/*.schema.ts
PostgreSQL	src/db/pg/*.schema.ts

The test file src/db/schema-parity.test.ts enforces consistency between these definitions, preventing drift as the schema evolves.

Key Source Files

File	Purpose
src/db/provider.ts	Runtime provider switching logic
src/db/pg/client.ts	PostgreSQL connection client
src/db/pg/*.schema.ts	PostgreSQL-specific Drizzle schemas
drizzle-pg/	Hand-written PostgreSQL migration SQL
wrangler.jsonc	Hyperdrive binding configuration
src/db/schema-parity.test.ts	Cross-database consistency tests

Complete Setup Script

Copy and run this sequence for a fresh PostgreSQL development environment:

# 1. Start PostgreSQL container

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

# 2. Wait for database readiness

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

# 3. Apply migrations

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

# 4. Configure environment

echo "DATABASE_PROVIDER=postgres" >> .env.local

# 5. Start development server

pnpm dev

# 6. Verify (in another terminal)

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

Summary

• Container: Run PostgreSQL 16 on port 5433 via Docker to avoid local conflicts
• Migrations: Use pnpm db:migrate:pg with POSTGRES_DATABASE_URL pointing to your container
• Configuration: Set DATABASE_PROVIDER=postgres in .env.local to switch drivers
• Connection: Miniflare resolves the Hyperdrive binding to your local PostgreSQL instance automatically
• Verification: Query tables directly via docker exec to confirm data persistence

Frequently Asked Questions

Can I use an existing PostgreSQL installation instead of Docker?

Yes. Override the connection string via CLOUDFLARE_HYPERDRIVE_LOCAL_CONNECTION_STRING_HYPERDRIVE when running pnpm dev, or modify the localConnectionString in your Miniflare configuration. Ensure your PostgreSQL version is 14 or higher for compatibility with the Drizzle schema definitions.

Why does OpenSEO use two separate schema file sets?

The SQLite and PostgreSQL dialects have incompatible type systems and index behaviors. Separate files in src/db/ and src/db/pg/ allow dialect-specific optimizations while src/db/schema-parity.test.ts enforces structural consistency. This design supports D1 for serverless deployments and PostgreSQL for larger self-hosted installations.

How do I generate new PostgreSQL migrations after schema changes?

Use pnpm db:generate:pg to create new Drizzle migration files in drizzle-pg/. Review the generated SQL for dialect-specific issues, then apply with pnpm db:migrate:pg. Always run the parity tests afterward to ensure SQLite and PostgreSQL schemas remain synchronized.

What happens if DATABASE_PROVIDER is unset or misspelled?

The provider defaults to d1 (SQLite) as implemented in src/db/provider.ts. An invalid value will cause a runtime error when the worker attempts to initialize the database client. Check the Miniflare console output for explicit provider resolution messages during startup.

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