How Instatic Handles Database Migrations Between SQLite and PostgreSQL

Instatic uses parallel migration files with identical IDs and a dialect-agnostic runner to apply synchronized schema changes across both SQLite and PostgreSQL, handling SQLite's limitations through conditional foreign key management.

Instatic manages database schema evolution through a unique dual-file approach that maintains parity between SQLite and PostgreSQL. Unlike traditional ORM-based migration systems, Instatic keeps separate but synchronized migration definitions in server/db/migrations-pg.ts and server/db/migrations-sqlite.ts, ensuring that both databases remain structurally compatible while respecting each engine's specific DDL requirements. This architecture allows the application to run interchangeably on either database using the same migration logic.

The Parallel Migration File Architecture

Instatic defines migrations in two separate TypeScript files that export arrays of Migration objects. Each object contains an id, an optional disableForeignKeys flag for SQLite-specific constraint handling, and a dialect-specific sql string.

• server/db/migrations-pg.ts: Contains PostgreSQL-specific DDL using native types like JSONB, TIMESTAMPTZ, and BOOLEAN.
• server/db/migrations-sqlite.ts: Contains SQLite-compatible DDL with type translations such as jsonb → text, timestamptz → text, and boolean → integer.

Both arrays must maintain identical IDs in identical order. This parity is enforced by src/__tests__/architecture/migration-parity.test.ts, which fails the build if the migration sequences diverge.

Type Translation Examples

When defining a new table, developers write appropriate SQL for each dialect:

// server/db/migrations-pg.ts
{
  id: '018_add_new_table',
  sql: `
    CREATE TABLE IF NOT EXISTS new_table (
      id UUID PRIMARY KEY,
      data JSONB NOT NULL,
      created_at TIMESTAMPTZ NOT NULL DEFAULT now()
    );
  `,
}

// server/db/migrations-sqlite.ts
{
  id: '018_add_new_table',
  sql: `
    CREATE TABLE IF NOT EXISTS new_table (
      id TEXT PRIMARY KEY,
      data TEXT NOT NULL,          -- SQLite stores JSON as TEXT
      created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
    );
  `,
}

The Migration Runner Implementation

The core orchestration logic resides in server/db/runMigrations.ts. This dialect-agnostic runner accepts any client implementing the DbClient interface and executes migrations idempotently.

Migration Tracking

The runner creates a schema_migrations table if it does not exist, using only portable SQL types to ensure compatibility across both databases:

CREATE TABLE IF NOT EXISTS schema_migrations (
  id TEXT PRIMARY KEY,
  applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Execution Workflow

For each migration in the array, the runner performs three steps:

1. Check existence: Query schema_migrations to skip already-applied IDs.
2. Apply within transaction: Execute the SQL inside a database transaction.
3. Record completion: Insert the migration ID into schema_migrations.

The runner selects the appropriate migration array at startup based on the DATABASE_URL environment variable:

import { sqliteClient } from './sqlite/client'
import { sqliteMigrations } from './migrations-sqlite'
import { pgMigrations } from './migrations-pg'
import { runMigrations } from './runMigrations'

const migrations = process.env.DATABASE_URL?.startsWith('postgres')
  ? pgMigrations
  : sqliteMigrations

await runMigrations(sqliteClient, migrations)

Handling SQLite-Specific Constraints

SQLite imposes unique limitations on schema modifications, particularly regarding foreign key enforcement and column alterations. Instatic addresses these through specific patterns in the SQLite migration file and runner logic.

Foreign Key Management

SQLite cannot toggle PRAGMA foreign_keys inside a transaction—the pragma becomes a no-op. For migrations requiring constraint relaxation, such as table rebuilds, the SQLite migration sets disableForeignKeys: true, prompting the runner to:

1. Execute PRAGMA foreign_keys = OFF outside the transaction.
2. Run the migration SQL.
3. Verify integrity with PRAGMA foreign_key_check.
4. Re-enable enforcement with PRAGMA foreign_keys = ON.

Table Rebuild Pattern

When adding constraints or altering columns that SQLite does not support directly, migrations use the "create-new-table-copy-drop-rename" approach:

// server/db/migrations-sqlite.ts
{
  id: '020_rebuild_data_rows',
  disableForeignKeys: true,
  sql: `
    PRAGMA defer_foreign_keys = ON;

    CREATE TABLE data_rows__tmp (
      id TEXT PRIMARY KEY,
      table_id TEXT NOT NULL REFERENCES data_tables(id) ON DELETE RESTRICT,
      new_field TEXT,  -- added in this migration
      ...other columns...
    );

    INSERT INTO data_rows__tmp (id, table_id, ...other columns...)
    SELECT id, table_id, ...other columns... FROM data_rows;

    DROP TABLE data_rows;
    ALTER TABLE data_rows__tmp RENAME TO data_rows;

    CREATE INDEX IF NOT EXISTS data_rows_table_idx ON data_rows (table_id, updated_at DESC);
  `,
}

Enforcing Migration Parity

To prevent drift between dialects, src/__tests__/architecture/migration-parity.test.ts validates that both migration files contain the same IDs in the same sequence. This architectural test guarantees that developers cannot add a migration to one dialect without providing the equivalent for the other, maintaining the interchangeability guarantee.

All migrations follow additive and non-destructive principles: they use IF NOT EXISTS, ON CONFLICT ... DO UPDATE, and never drop columns or tables that contain data. This approach ensures that rollback scenarios are handled through forward-only migrations rather than destructive reversions.

Summary

• Instatic maintains synchronized migration histories through parallel files in server/db/migrations-pg.ts and server/db/migrations-sqlite.ts with identical migration IDs.
• The runMigrations.ts runner applies changes idempotently using a portable schema_migrations tracking table and transaction-wrapped execution.
• SQLite-specific limitations are handled through the disableForeignKeys flag and the "create-copy-drop-rename" table rebuild pattern, with foreign key verification via PRAGMA foreign_key_check.
• The architecture test migration-parity.test.ts enforces that both dialect files remain synchronized, preventing deployment incompatibilities.
• All schema changes remain additive and non-destructive, ensuring data integrity across both SQLite and PostgreSQL deployments.

Frequently Asked Questions

Why does Instatic use separate migration files instead of a database-agnostic ORM?

Instatic separates dialect-specific DDL into dedicated files to leverage native database features while maintaining portability. This approach allows PostgreSQL deployments to use advanced types like JSONB and TIMESTAMPTZ for performance and indexing benefits, while SQLite deployments use compatible TEXT representations. The shared DbClient interface abstracts runtime differences, but schema definitions require dialect-specific optimization that ORM layers often obscure or genericize poorly.

How does Instatic handle SQLite's inability to alter certain column constraints?

For schema changes that SQLite does not support natively—such as adding CHECK constraints to existing columns or modifying foreign key references—Instatic uses the table rebuild pattern. The migration creates a new table with the desired schema, copies data from the old table, drops the original, and renames the new table. The disableForeignKeys: true flag signals the runner to temporarily disable foreign key enforcement outside the transaction boundary, preventing constraint violations during the rebuild process.

What prevents the SQLite and PostgreSQL migration histories from diverging?

The repository includes src/__tests__/architecture/migration-parity.test.ts, which programmatically compares the id fields in both migration arrays. If the sequences differ in length, order, or ID values, the test fails and blocks the build. This automated check ensures that every schema change available in PostgreSQL has a corresponding SQLite implementation, maintaining the guarantee that the application logic works identically regardless of which database is configured.

Are Instatic database migrations reversible?

Instatic migrations are designed to be forward-only and additive rather than reversible. They never drop tables or columns containing data, and they use IF NOT EXISTS clauses to remain idempotent. If a schema change needs to be undone, developers create a new migration that restores the previous state rather than rolling back. This approach eliminates the complexity of down-migration scripts and ensures that production databases never lose data through schema changes.

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