# How to Run OpenSEO Locally with PostgreSQL for Development

> Learn to run OpenSEO locally with PostgreSQL for development. Configure your environment, start a PostgreSQL container, and migrate your database effortlessly. Get started today!

- Repository: [Every App/open-seo](https://github.com/every-app/open-seo)
- Tags: how-to-guide
- Published: 2026-08-14

---

**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`](https://github.com/every-app/open-seo/blob/main/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:

```bash
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:

```bash
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:

```bash
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`](https://github.com/every-app/open-seo/blob/main/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`:

```dotenv
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:

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

```

Start the development server:

```bash
pnpm dev

```

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

## Verify the PostgreSQL Setup

Confirm migrations applied successfully:

```bash

# List all tables created by migrations

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

```

Test that the app writes data correctly:

```bash

# 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:

```dotenv
DATABASE_PROVIDER=d1

```

Restart the dev server to apply the change.

## Clean Up the Container

When finished testing, remove the throw-away database:

```bash
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`](https://github.com/every-app/open-seo/blob/main/src/db/provider.ts))

The `DATABASE_PROVIDER` environment variable drives client selection in [`src/db/provider.ts`](https://github.com/every-app/open-seo/blob/main/src/db/provider.ts):

- **`d1`** → Loads [`src/db/d1/client.ts`](https://github.com/every-app/open-seo/blob/main/src/db/d1/client.ts) (SQLite via Cloudflare D1)
- **`postgres`** → Loads [`src/db/pg/client.ts`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/src/db/provider.ts) | Runtime provider switching logic |
| [`src/db/pg/client.ts`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/src/db/schema-parity.test.ts) | Cross-database consistency tests |

## Complete Setup Script

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

```bash

# 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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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.