How Database Migrations Work in OpenSEO: Drizzle, D1, and PostgreSQL Guide

OpenSEO manages database migrations using Drizzle ORM with plain-SQL migration files stored in provider-specific directories, driven by Cloudflare Wrangler for SQLite/D1 and Drizzle-Kit for PostgreSQL.

The every-app/open-seo repository implements a dual-provider database strategy that supports both Cloudflare D1 (SQLite) and self-hosted PostgreSQL backends. Understanding how database migrations in OpenSEO are structured requires examining the schema definitions, migration scripts, and deployment tooling that keep these environments synchronized.

Schema Definition Layer

OpenSEO maintains separate TypeScript schema files for each database provider to generate type-safe migrations.

• SQLite/D1 schema: Located at src/db/schema.ts, this file exports the canonical schema used by Cloudflare's D1 database service.
• PostgreSQL schema: Located at src/db/pg/schema.ts, this file defines the equivalent structure for Postgres deployments.

Both files serve as the source of truth for Drizzle ORM's migration generation. When you modify these schemas, Drizzle compares them against the current database state to generate incremental SQL files.

Migration File Structure and Storage

Rather than using programmatic migration APIs, OpenSEO stores hand-written and generated SQL files in versioned directories:

• drizzle/: Contains sequential migration files for SQLite/D1 (e.g., drizzle/0011_colorful_dark_beast.sql)
• drizzle-pg/: Contains equivalent migrations for PostgreSQL

Each filename includes a numeric prefix (e.g., 0010_, 0011_) that ensures execution order. Drizzle tracks applied migrations in provider-specific history tables: d1_migrations for SQLite and pg_migrations for PostgreSQL.

Running Migrations Locally and in Production

The migration workflow integrates with Cloudflare Wrangler through configuration in wrangler.jsonc. This file registers the drizzle/ folder as the migrations source and specifies the d1_migrations table for tracking state.

OpenSEO provides npm scripts in package.json to handle different environments:

# Generate a new migration SQL file from schema changes

pnpm db:generate

# Apply migrations to local D1 instance

pnpm db:migrate:local

# Apply migrations to production D1 database

pnpm db:migrate:prod

# Apply migrations to PostgreSQL instance

pnpm db:migrate:pg

The local and production commands invoke wrangler d1 migrations apply DB with the --local or --remote flags respectively, while the PostgreSQL command runs Drizzle-Kit directly against your Postgres connection string.

Cross-Provider Data Migration

When migrating from Cloudflare D1 to a self-hosted PostgreSQL instance, OpenSEO includes a specialized one-off script at scripts/migrate-d1-to-postgres.ts. This TypeScript utility:

1. Paginates through D1 tables to avoid memory constraints
2. Bulk-inserts rows into the corresponding PostgreSQL tables
3. Preserves data integrity while the underlying schema migrates

Execute the transfer using:

pnpm ts-node scripts/migrate-d1-to-postgres.ts

Schema Parity Testing

To prevent drift between the SQLite and PostgreSQL implementations, OpenSEO includes a test suite in src/db/schema-parity.test.ts. This file asserts that both src/db/schema.ts and src/db/pg/schema.ts exports remain structurally equivalent after any migration.

Running these tests ensures that changes made for one provider are consistently reflected in the other, maintaining the dual-provider compatibility that OpenSEO requires.

Summary

• OpenSEO uses Drizzle ORM with plain-SQL migration files stored in drizzle/ (SQLite/D1) and drizzle-pg/ (PostgreSQL) directories.
• Schema definitions live in src/db/schema.ts and src/db/pg/schema.ts, generating type-safe migrations via pnpm db:generate.
• Cloudflare Wrangler manages D1 migrations through wrangler.jsonc, with commands like pnpm db:migrate:local and pnpm db:migrate:prod.
• Data portability is handled by scripts/migrate-d1-to-postgres.ts when switching from D1 to PostgreSQL.
• Schema parity is enforced by src/db/schema-parity.test.ts to keep both database providers synchronized.

Frequently Asked Questions

Where are database migration files stored in OpenSEO?

Migration files are stored in two separate directories: drizzle/ for SQLite/D1 migrations and drizzle-pg/ for PostgreSQL migrations. Each file uses a numeric prefix (e.g., 0011_) to determine execution order, and Drizzle tracks applied migrations in the d1_migrations or pg_migrations tables respectively.

How do I migrate existing data from D1 to PostgreSQL in OpenSEO?

Use the scripts/migrate-d1-to-postgres.ts script provided in the repository. This TypeScript utility paginates through your D1 database and bulk-inserts records into PostgreSQL while preserving referential integrity. Run it with pnpm ts-node scripts/migrate-d1-to-postgres.ts after setting up your Postgres connection.

What prevents the SQLite and PostgreSQL schemas from diverging in OpenSEO?

The src/db/schema-parity.test.ts test file enforces structural equivalence between src/db/schema.ts (SQLite) and src/db/pg/schema.ts (PostgreSQL). This automated test runs in the CI pipeline to ensure that any schema change made for one provider is mirrored in the other.

How does Wrangler know where to find migration files in OpenSEO?

The wrangler.jsonc configuration file explicitly registers the drizzle/ directory as the migrations source and defines d1_migrations as the table name for tracking migration history. This allows Wrangler's CLI to locate and execute SQL files in the correct sequence when you run pnpm db:migrate:local or pnpm db:migrate:prod.

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