# How Instatic Handles Database Migrations Between SQLite and PostgreSQL

> Learn how Instatic manages database migrations between SQLite and PostgreSQL using parallel migration files and a dialect-agnostic runner for synchronized schema changes, overcoming SQLite limitations.

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

---

**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`](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), 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`](https://github.com/CoreBunch/Instatic/blob/main/server/db/migrations-pg.ts)**: Contains PostgreSQL-specific DDL using native types like `JSONB`, `TIMESTAMPTZ`, and `BOOLEAN`.
- **[`server/db/migrations-sqlite.ts`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/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:

```typescript
// 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()
    );
  `,
}

```

```typescript
// 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`](https://github.com/CoreBunch/Instatic/blob/main/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:

```sql
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:

```typescript
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:

```typescript
// 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`](https://github.com/CoreBunch/Instatic/blob/main/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`](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) with identical migration IDs.
- The **[`runMigrations.ts`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/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.