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

> Quickly set up PostgreSQL for local development with Drizzle. Learn to configure Docker, environment variables, and your database provider for seamless integration.

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

---

**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](https://github.com/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`:

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

```bash
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`](https://github.com/every-app/open-seo/blob/main/drizzle-pg.config.ts), which pulls the connection string from `POSTGRES_DATABASE_URL`:

```typescript
// 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:

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

```bash

# .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)](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:

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

```bash

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

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

```bash
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)](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)](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)](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`](https://github.com/every-app/open-seo/blob/main/src/db/pg/schema.ts) or [`src/db/d1/schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/d1/schema.ts) must be mirrored in the other file, or the test fails.