How Instatic Database Migrations Work with SQLite and PostgreSQL: A Complete Guide

Instatic synchronizes SQLite and PostgreSQL schemas using parallel migration files with identical IDs, applied by a dialect-agnostic runner that handles SQLite's foreign-key limitations through a special disableForeignKeys flag.

Instatic, an open-source project maintained by CoreBunch, supports both SQLite and PostgreSQL through a unified migration system. The architecture ensures that the same logical schema changes apply consistently across both database engines while respecting each dialect's specific constraints and type mappings.

The Dual-File Migration Strategy

Instatic maintains two separate migration files that share the same logical sequence but contain dialect-specific SQL:

• server/db/migrations-pg.ts – Contains PostgreSQL-specific DDL with native types like JSONB, TIMESTAMPTZ, and BOOLEAN.
• server/db/migrations-sqlite.ts – Contains SQLite-specific DDL with type translations (e.g., jsonb → text, timestamptz → text, boolean → integer).

Both files export arrays of Migration objects containing an id and sql property. The id values are identical across both files and ordered sequentially, allowing the migration runner to treat them interchangeably based on the active database connection.

// server/db/migrations-pg.ts
export const pgMigrations: Migration[] = [
  {
    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
export const sqliteMigrations: Migration[] = [
  {
    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'))
      );
    `,
  },
]

Migration Parity Enforcement

To prevent schema drift between dialects, Instatic includes a dedicated architecture test at src/__tests__/architecture/migration-parity.test.ts. This test guarantees that both migration files contain the same IDs in the same order, failing the build if a developer adds a migration to one file without the corresponding entry in the other.

This automated enforcement ensures that PostgreSQL and SQLite instances remain logically equivalent throughout the application's lifecycle.

The Migration Runner Workflow

The core migration logic resides in server/db/runMigrations.ts. The runner executes four distinct steps for each migration:

1. Create tracking table – Creates schema_migrations (using portable SQL with TEXT and current_timestamp) if it does not exist.
2. Check migration status – Queries schema_migrations to determine if the migration id has already been applied, skipping if found.
3. Apply within transaction – Executes the SQL string inside a database transaction.
4. Record completion – Inserts the migration id into schema_migrations with the current timestamp.

All migrations follow an additive and non-destructive philosophy: they never drop tables or columns, instead using IF NOT EXISTS clauses and ON CONFLICT … DO UPDATE patterns.

Handling SQLite-Specific Constraints

SQLite presents unique challenges for schema changes, particularly regarding foreign key constraints. Instatic addresses these through the disableForeignKeys flag on specific migrations.

When a migration requires rebuilding a table (such as adding a new enum value or changing a constraint), the runner executes this workflow:

1. Temporarily disables foreign key enforcement with PRAGMA foreign_keys = OFF outside the transaction (since the pragma is a no-op inside transactions).
2. Runs the migration SQL.
3. Verifies integrity with PRAGMA foreign_key_check.
4. Re-enables foreign keys.

The table rebuild follows the "create-new-table-copy-drop-rename" pattern:

// 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,
      -- other columns...
    );

    INSERT INTO data_rows__tmp SELECT * 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);
  `,
}

For PostgreSQL, the same migration ID runs standard SQL without the disableForeignKeys flag, as PostgreSQL handles constraint changes natively within transactions.

Running Migrations on Startup

The server selects the appropriate migration set based on the DATABASE_URL environment variable and executes them through the abstract DbClient interface:

import { sqliteClient } from './sqlite/client'
import { pgClient } from './pg/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

const client = process.env.DATABASE_URL?.startsWith('postgres')
  ? pgClient
  : sqliteClient

await runMigrations(client, migrations)

Because the application code interacts with the database only through the DbClient abstraction, the same codebase runs unchanged regardless of whether the underlying database is PostgreSQL or SQLite.

Summary

• Parallel migration files in server/db/migrations-pg.ts and server/db/migrations-sqlite.ts maintain identical IDs with dialect-specific SQL.
• Architecture tests enforce parity between PostgreSQL and SQLite migration sequences.
• The runMigrations function in server/db/runMigrations.ts tracks applied migrations in a schema_migrations table and handles dialect-specific quirks.
• SQLite rebuilds use the disableForeignKeys flag to temporarily bypass foreign key constraints during table reconstruction.
• Non-destructive patterns ensure safe schema evolution across both database engines without data loss.

Frequently Asked Questions

How does Instatic keep SQLite and PostgreSQL schemas synchronized?

Instatic maintains separate migration files for each dialect with identical migration IDs and ordering. An architecture test in src/__tests__/architecture/migration-parity.test.ts verifies that both files contain the same sequence of IDs, ensuring that any schema change applied to PostgreSQL has a corresponding SQLite equivalent.

What is the purpose of the disableForeignKeys flag in SQLite migrations?

The disableForeignKeys flag signals the migration runner to temporarily disable foreign key enforcement before executing the migration. This is required for SQLite table rebuilds (such as adding columns or changing constraints) because SQLite's PRAGMA foreign_keys cannot be modified inside a transaction. The runner verifies integrity with PRAGMA foreign_key_check before re-enabling constraints.

How does the migration runner track which migrations have been applied?

The runner creates a schema_migrations table on first run, storing each migration's id and timestamp. Before applying any migration, it queries this table to check if the ID exists. If found, the migration is skipped; if not found, the SQL executes within a transaction and the ID is recorded upon successful completion.

Can I use the same SQL syntax for both PostgreSQL and SQLite migrations?

No, you must write dialect-specific SQL for each database. PostgreSQL migrations use native types like JSONB and TIMESTAMPTZ, while SQLite migrations translate these to TEXT or INTEGER types. However, the migration IDs and logical sequence must remain identical between the two files to maintain parity.

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