How Instatic Database Migrations Handle Postgres and SQLite Dialects

Instatic maintains parallel migration files—migrations-pg.ts for Postgres and migrations-sqlite.ts for SQLite—managed by a unified runner in server/db/runMigrations.ts that executes dialect-specific SQL while maintaining schema parity through automated testing.

The CoreBunch/Instatic project implements a portable database migration system that supports both PostgreSQL and SQLite from a single codebase. Unlike ORM-based solutions that abstract dialect differences, Instatic embraces explicit SQL translation to leverage native features like Postgres jsonb while maintaining compatibility with SQLite's simpler type system.

Migration Architecture and the Core Interface

All migrations implement the Migration interface defined in server/db/runMigrations.ts:

export interface Migration {
  id: string        // Unique identifier, e.g. "001_baseline"
  sql: string       // One-or-multiple DDL/DML statements
  disableForeignKeys?: boolean   // SQLite-only flag
}

The runMigrations function creates a portable schema_migrations tracking table that records applied migration IDs and timestamps across both dialects. When the server starts, it loads either pgMigrations from server/db/migrations-pg.ts or sqliteMigrations from server/db/migrations-sqlite.ts based on the DATABASE_URL environment variable, then executes any migration whose id is not yet recorded in the tracking table.

Dialect-Specific Type Mappings

Instatic handles fundamental type differences through explicit translation in the parallel migration files.

JSON Storage

Postgres uses the native jsonb type for structured data:

capabilities_json jsonb not null default '[]'::jsonb

SQLite stores JSON as text, relying on application-layer parsing for columns suffixed with _json:

capabilities_json text not null default '[]'

Timestamps and Booleans

Postgres migrations use timestamptz with default now(), while SQLite uses ISO-8601 text defaults via strftime('%Y-%m-%dT%H:%M:%fZ','now'). Both map to portable ISO strings in the tracking table.

For boolean values, Postgres uses native boolean (true/false), whereas SQLite stores booleans as integers (1/0). The application layer handles conversion using Boolean(row.enabled).

Binary Data and Large Integers

Binary data maps directly to bytea in Postgres and blob in SQLite. For large integers, Postgres uses bigint while SQLite's integer type natively supports 64-bit values without precision loss.

Foreign Key Handling in SQLite

SQLite requires special handling for schema modifications that rebuild tables. The disableForeignKeys flag on a migration triggers PRAGMA foreign_keys = OFF before execution in server/db/runMigrations.ts. After the transaction completes, the runner validates integrity with pragma foreign_key_check before re-enabling constraints. Postgres handles these operations natively without disabling constraints.

When schema changes require table recreation in SQLite—such as modifying CHECK constraints—the runner performs the table-recreate dance inside a transaction with pragma defer_foreign_keys = on to maintain referential integrity.

Ensuring Migration Parity

To prevent drift between dialects, the src/__tests__/architecture/migration-parity.test.ts test asserts that both migrations-pg.ts and migrations-sqlite.ts contain identical sets of id values in the same order. This guarantees that databases evolve identically regardless of the underlying engine.

Adding a New Migration

Follow this workflow when extending the schema:

1. Create the migration entry with a sequential id (e.g., 021_new_feature).
2. Write the Postgres version first, then translate types for SQLite.
3. Add entries to both pgMigrations and sqliteMigrations arrays.
4. Run the parity test using bun test src/__tests__/architecture/migration-parity.test.ts.
5. Deploy—the next server start automatically applies pending migrations.

Example adding a tags column to media_assets:

// server/db/migrations-pg.ts
{
  id: '021_media_assets_tags',
  sql: `
    alter table media_assets
      add column if not exists tags_json jsonb not null default '[]'::jsonb;
  `,
}

// server/db/migrations-sqlite.ts
{
  id: '021_media_assets_tags',
  sql: `
    alter table media_assets
      add column tags_json text not null default '[]';
  `,
}

Summary

• Instatic maintains parallel migration files in server/db/migrations-pg.ts and server/db/migrations-sqlite.ts to handle dialect-specific SQL.
• The Migration interface in runMigrations.ts supports a disableForeignKeys flag for SQLite table rebuilds.
• Type mappings handle differences in JSON (jsonb vs text), timestamps (timestamptz vs ISO text), and booleans (native vs integer).
• The migration-parity.test.ts test enforces identical migration sequences across both dialects.
• All migrations use additive-only operations (CREATE IF NOT EXISTS, ADD COLUMN IF NOT EXISTS) to protect live data.

Frequently Asked Questions

How does Instatic decide which migration file to use?

The runMigrations function inspects the DATABASE_URL environment variable to determine the database type. It imports pgMigrations from migrations-pg.ts for PostgreSQL connections or sqliteMigrations from migrations-sqlite.ts for SQLite connections, then executes the appropriate array against the connected database.

What happens if the Postgres and SQLite migration files get out of sync?

The migration-parity.test.ts test fails during the test suite execution, blocking builds until parity is restored. This ensures that development environments using SQLite remain compatible with production PostgreSQL instances.

Can I use destructive migrations like DROP COLUMN in Instatic?

No. The migration system is designed to use only additive operations such as CREATE TABLE IF NOT EXISTS and ALTER TABLE ... ADD COLUMN IF NOT EXISTS. This protects live installations from accidental data loss during deployments.

Why does SQLite need the disableForeignKeys flag while Postgres does not?

SQLite enforces foreign key constraints strictly during table alterations, requiring PRAGMA foreign_keys = OFF when rebuilding tables to change constraints or column types. Postgres handles these alterations natively without disabling constraints, making the flag unnecessary for that dialect.

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