# How Instatic Manages Postgres and SQLite Migrations with Identical IDs

> Learn how Instatic ensures identical IDs for Postgres and SQLite migrations. Discover their strategy for cross-database compatibility using separate SQL files and automated tests.

- Repository: [CoreBunch/Instatic](https://github.com/CoreBunch/Instatic)
- Tags: internals
- Published: 2026-08-01

---

**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 `id`s 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`](https://github.com/CoreBunch/Instatic/blob/main/server/db/migrations-pg.ts) and [`server/db/migrations-sqlite.ts`](https://github.com/CoreBunch/Instatic/blob/main/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 `id`s 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`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/src/__tests__/architecture/migration-parity.test.ts). This test suite validates:

1. Both migration arrays have identical length
2. Migration `id`s 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:

```ts
// 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`](https://github.com/CoreBunch/Instatic/blob/main/server/db/migrations-pg.ts) with a new sequential `id`
2. Translate to SQLite syntax and append to [`server/db/migrations-sqlite.ts`](https://github.com/CoreBunch/Instatic/blob/main/server/db/migrations-sqlite.ts) with the **identical `id`**
3. Run `bun test` to verify parity test passes

**Example: Adding a `tags_json` column**

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

```

```ts
// 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`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/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 `id`s 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`](https://github.com/CoreBunch/Instatic/blob/main/migrations-pg.ts) and [`migrations-sqlite.ts`](https://github.com/CoreBunch/Instatic/blob/main/migrations-sqlite.ts) isolate dialect-specific SQL
- **Identical `id`s**: The only required invariant; enables cross-database schema tracking
- **Automated parity tests**: [`migration-parity.test.ts`](https://github.com/CoreBunch/Instatic/blob/main/migration-parity.test.ts) blocks merges with mismatched migrations
- **Manual translation**: Header-documented conversion rules guide SQLite adaptation
- **Runtime selection**: [`runMigrations.ts`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/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.