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

> Learn how Instatic database migrations sync SQLite and PostgreSQL schemas with parallel migration files and a dialect-agnostic runner. Master SQLite's foreign key limitations easily.

- Repository: [CoreBunch/Instatic](https://github.com/CoreBunch/Instatic)
- Tags: how-to-guide
- Published: 2026-07-26

---

**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`](https://github.com/CoreBunch/Instatic/blob/main/server/db/migrations-pg.ts)** – Contains PostgreSQL-specific DDL with 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-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.

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

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

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