How Instatic Manages Postgres and SQLite Migrations with Identical IDs

Instatic enforces migration parity between PostgreSQL and SQLite by storing dialect-specific SQL in separate files with strictly identical id strings, verified by automated architecture tests.

When a Node.js application supports multiple database backends, schema drift between dialects is a constant risk. The Instatic project solves this by treating migration ids as immutable anchors that keep PostgreSQL and SQLite schemas synchronized—regardless of how different the underlying SQL becomes.

Separate Migration Files with Shared IDs

Instatic defines every schema change twice. The files server/db/migrations-pg.ts and server/db/migrations-sqlite.ts export arrays of migration objects, each with an id and sql property:

• id — a string like "018_media_tags" that serves as the single source of truth
• sql — dialect-specific DDL/DML for that backend

The id is what the migration runner uses to track applied migrations in the schema_migrations table. Because both files must contain the same ids in the same order, the runner can deterministically know which logical migration comes next, even when the actual SQL differs dramatically.

Dialect Translation Rules

SQLite migrations in server/db/migrations-sqlite.ts begin with a header comment documenting the manual conversions required from PostgreSQL syntax. Common transformations include:

• jsonb → text
• timestamptz → text
• boolean → integer
• DEFAULT now() → strftime('%Y-%m-%dT%H:%M:%fZ', 'now')

These translations are performed by hand when a developer adds a new migration. There is no automatic transpilation—human review ensures semantic correctness across dialects.

The Migration Parity Test

The project enforces synchronization through src/__tests__/architecture/migration-parity.test.ts. This test suite validates:

1. Both migration arrays have identical length
2. Migration ids match at every index position

If a developer adds a migration to one file but forgets the other, or assigns a different id, the test fails with a detailed mismatch report:

// src/__tests__/architecture/migration-parity.test.ts
describe('Migration parity — migrations-pg.ts ↔ migrations-sqlite.ts', () => {
  test('PG and SQLite have the same number of migrations', () => {
    // fails if array lengths differ
  });

  test('PG and SQLite have the same migration IDs in the same order', () => {
    // fails if any id at index i differs
  });
});

A typical failure message:

[migration-parity] Migration ID mismatch(es):
  [17]  pg:     018_media_tags
         sqlite: (missing)

This gate prevents any code with divergent migration histories from reaching production.

Adding a New Migration: The Checklist

Developers follow a strict workflow to maintain Postgres and SQLite migration parity:

1. Write PostgreSQL DDL in server/db/migrations-pg.ts with a new sequential id
2. Translate to SQLite syntax and append to server/db/migrations-sqlite.ts with the identical id
3. Run bun test to verify parity test passes

Example: Adding a tags_json column

// server/db/migrations-pg.ts
{
  id: '018_media_tags',
  sql: `
    ALTER TABLE media_assets
      ADD COLUMN tags_json jsonb NOT NULL DEFAULT '[]';
  `,
}

// server/db/migrations-sqlite.ts
{
  id: '018_media_tags',   // ← must match exactly
  sql: `
    -- SQLite conversion: jsonb → text
    ALTER TABLE media_assets
      ADD COLUMN tags_json text NOT NULL DEFAULT '[]';
  `,
}

The id string "018_media_tags" is the bridge. The migration runner in server/db/runMigrations.ts loads either pgMigrations or sqliteMigrations based on DATABASE_URL, then executes each sql string in order, recording the id as completed.

Runtime Execution

The migration runner (server/db/runMigrations.ts) operates simply:

• Detect dialect from DATABASE_URL
• Import the appropriate migration array
• Iterate through migrations in order
• Skip any id already present in schema_migrations
• Execute remaining sql strings transactionally

Because ids are identical across dialects, a PostgreSQL installation and SQLite installation that started from the same baseline will always agree on which logical schema version they share—even if the column types and constraints differ at the SQL level.

Summary

• Dual file structure: migrations-pg.ts and migrations-sqlite.ts isolate dialect-specific SQL
• Identical ids: The only required invariant; enables cross-database schema tracking
• Automated parity tests: migration-parity.test.ts blocks merges with mismatched migrations
• Manual translation: Header-documented conversion rules guide SQLite adaptation
• Runtime selection: runMigrations.ts loads the correct array based on connection string

Frequently Asked Questions

What happens if I forget to add a migration to both files?

The architecture test migration-parity.test.ts will fail immediately with a specific index and id mismatch. CI pipelines running bun test prevent the PR from merging until both files are synchronized.

Why doesn't Instatic use an ORM to handle dialect differences?

The project relies on hand-written SQL for precise control over schema evolution. The identical id convention provides ORM-like versioning guarantees without sacrificing the transparency of raw DDL migrations.

Can I use different id formats for Postgres and SQLite migrations?

No. The id must match character-for-character. The parity test performs strict string equality comparison. Using a different casing, numbering scheme, or timestamp format will trigger a test failure.

How does the migration runner handle partial failures?

Migrations execute within transactions. If a sql string fails, the transaction rolls back and the id is not recorded in schema_migrations. The runner will retry the same migration on next startup, allowing fix-forward after code correction.

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