# How Database Migrations Work in OpenSEO: Drizzle, D1, and PostgreSQL Guide > Learn how OpenSEO handles database migrations with Drizzle ORM, D1, and PostgreSQL. Discover plain-SQL migration management using Cloudflare Wrangler and Drizzle-Kit. - Repository: [Every App/open-seo](https://github.com/every-app/open-seo) - Tags: how-to-guide - Published: 2026-09-02 --- **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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/package.json) to handle different environments: ```bash # 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`](https://github.com/every-app/open-seo/blob/main/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: ```bash 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`](https://github.com/every-app/open-seo/blob/main/src/db/schema-parity.test.ts). This file asserts that both [`src/db/schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/schema.ts) and [`src/db/pg/schema.ts`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/src/db/schema.ts) and [`src/db/pg/schema.ts`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/scripts/migrate-d1-to-postgres.ts) when switching from D1 to PostgreSQL. - **Schema parity** is enforced by [`src/db/schema-parity.test.ts`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/src/db/schema-parity.test.ts) test file enforces structural equivalence between [`src/db/schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/schema.ts) (SQLite) and [`src/db/pg/schema.ts`](https://github.com/every-app/open-seo/blob/main/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`.