How Drizzle's Dual-Target Migration Maintains Schema Compatibility Between SQLite/D1 and Postgres

Drizzle's dual-target migration setup maintains schema compatibility between SQLite/D1 and Postgres by using a provider-aware barrel file that conditionally exports dialect-specific schemas while enforcing type safety through a canonical SQLite-derived interface and automated parity testing.

The open-seo repository demonstrates how to maintain identical database schemas across Cloudflare D1 (SQLite) and PostgreSQL using Drizzle ORM's dual-target migration strategy. By separating dialect-specific definitions while exposing them through a unified TypeScript interface, the codebase achieves runtime flexibility without sacrificing compile-time safety.

The Challenge of Supporting Multiple Database Dialects

Supporting both SQLite (via Cloudflare D1) and PostgreSQL in a single application introduces significant complexity. Column types, index syntax, and constraint declarations differ between dialects, yet the application code must interact with both databases through a single, consistent API. Raw SQL migrations diverge quickly, and maintaining feature parity manually risks runtime errors when the TypeScript types no longer match the actual database schema.

Separating Schema Definitions by Dialect

In every-app/open-seo, schema definitions live in dialect-specific files to accommodate syntactic differences between SQLite and PostgreSQL. The SQLite schema resides in src/db/d1/schema.ts, while the PostgreSQL equivalent lives in src/db/pg/schema.ts. Both files export tables with identical structural signatures—same table names, column names, and constraints—but use dialect-appropriate type definitions (e.g., integer vs serial, blob vs bytea).

This separation allows Drizzle to generate accurate migrations for each target without compromise. The drizzle.config.ts file targets the SQLite dialect and points to the D1 schema, while drizzle-pg.config.ts configures the PostgreSQL generator:

// drizzle.config.ts - SQLite/D1 configuration
export default {
  dialect: 'sqlite',
  schema: './src/db/d1/schema.ts',
  out: './drizzle',
  driver: 'd1-http',
  // D1-specific connection details...
};

// drizzle-pg.config.ts - PostgreSQL configuration  
export default {
  dialect: 'postgresql',
  schema: './src/db/pg/schema.ts', 
  out: './drizzle-pg',
  // Postgres connection details...
};

The Provider-Aware Barrel Pattern

The unification happens in src/db/schema.ts, which acts as a provider-aware barrel. This file imports both dialect-specific schemas and uses getDatabaseProvider() from src/db/provider.ts to detect the runtime environment. It then conditionally spreads the appropriate schema modules into a single runtimeSchema object:

// src/db/schema.ts
import { getDatabaseProvider } from "@/db/provider";
import * as sqliteApp from "./d1/schema.ts";
import * as pgApp from "./pg/schema.ts";

const runtimeSchema =
  getDatabaseProvider() === "postgres"
    ? { ...pgApp }
    : { ...sqliteApp };

export const schema = runtimeSchema as unknown as AppSchema;

Type casting enforces compile-time parity. The runtimeSchema is cast to AppSchema, a type derived from the SQLite schema definitions. This makes SQLite the "canonical" reference type. Any structural deviation in the PostgreSQL schema—such as adding a column that doesn't exist in the SQLite version—results in a TypeScript compilation error when the cast fails.

Enforcing Compatibility with Schema Parity Tests

To catch structural drifts that TypeScript's type system might miss (such as differing index names or constraint definitions), the repository includes src/db/schema-parity.test.ts. This Vitest suite programmatically compares the two dialect schemas using Drizzle's introspection helpers including getTableColumns and getTableConfig.

The test extracts metadata for tables, columns, primary keys, unique constraints, foreign keys, and check constraints from both src/db/d1/schema.ts and src/db/pg/schema.ts, then asserts identical sorted representations:

// src/db/schema-parity.test.ts
describe("schema parity: application tables", () => {
  it("define the same set of tables on both backends", () => {
    const pgTables = extractTables(pgSchema);
    const sqliteTables = extractTables(sqliteSchema);
    
    expect(sortStrings([...pgTables.keys()])).toEqual(
      sortStrings([...sqliteTables.keys()])
    );
  });
  
  // Additional tests for columns, indexes, constraints...
});

If a developer adds a column to the PostgreSQL schema without updating the SQLite equivalent, this test fails in CI, preventing merges that would break the casting logic in src/db/schema.ts.

Generating Migrations for Both Targets

Migration generation remains separate for each dialect to respect their distinct SQL syntax. The open-seo repository uses npm scripts to trigger the appropriate Drizzle Kit configuration:

# Generate SQLite/D1 migrations

npm run db:generate

# Generate PostgreSQL migrations  

npm run db:generate:pg

These commands output to ./drizzle and ./drizzle-pg respectively. Both migration directories remain in version control, allowing deployment pipelines to apply the correct migration bundle based on the target environment. Because the source schemas maintain parity, the generated migrations will always produce structurally identical database states.

Runtime Provider Detection

The getDatabaseProvider() function in src/db/provider.ts determines which database is active by reading environment variables or runtime context. This utility drives the conditional export in the barrel file, ensuring that when the application runs against D1, it uses SQLite-compatible queries, and when running against Postgres, it uses PostgreSQL syntax—all while presenting the same table interfaces to the rest of the application.

Summary

• Separate dialect files (src/db/d1/schema.ts and src/db/pg/schema.ts) handle SQLite and PostgreSQL syntax differences while maintaining identical structural signatures.
• The provider-aware barrel (src/db/schema.ts) conditionally exports the correct schema based on getDatabaseProvider() and casts to the canonical AppSchema type for compile-time safety.
• Schema parity tests (src/db/schema-parity.test.ts) use Drizzle introspection helpers like getTableConfig to verify identical tables, columns, and constraints across both dialects.
• Independent migration configurations (drizzle.config.ts and drizzle-pg.config.ts) generate dialect-specific SQL while keeping both sets of migrations in version control.
• Runtime detection ensures the application always uses the correct query syntax for the active database without duplicating business logic.

Frequently Asked Questions

Why is SQLite used as the canonical type for AppSchema?

SQLite serves as the canonical type because Cloudflare D1 (the primary production target) runs on SQLite, making it the most restrictive dialect in terms of feature support. By deriving AppSchema from the SQLite definitions, the type system naturally prevents developers from using PostgreSQL-specific features that would break D1 compatibility, ensuring the lowest common denominator drives the API design.

What happens if the schemas drift between dialects?

Any drift triggers immediate failures. At compile time, TypeScript rejects the cast to AppSchema if the PostgreSQL schema adds properties missing from the SQLite version. At test time, src/db/schema-parity.test.ts fails when comparing table structures, columns, or constraints, blocking CI/CD pipelines until parity is restored.

How do I add a new table to both databases?

First, define the table in both src/db/d1/schema.ts and src/db/pg/schema.ts using dialect-appropriate types. Then run npm run db:generate and npm run db:generate:pg to create the migration files. Finally, execute npm run test to verify the schema-parity test passes before committing.

Can this pattern support additional databases beyond SQLite and Postgres?

Yes, the pattern scales to additional dialects by adding new schema files (e.g., src/db/mysql/schema.ts) and extending the conditional logic in src/db/schema.ts to check for the new provider. However, each additional dialect requires corresponding parity test updates and migration configurations, increasing maintenance overhead linearly with each supported database.

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