# How Instatic Database Migrations Handle Postgres and SQLite Dialects

> Instatic database migrations manage Postgres and SQLite dialects seamlessly with parallel migration files and a unified runner. Ensure schema parity and efficient data handling.

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

---

**Instatic maintains parallel migration files—[`migrations-pg.ts`](https://github.com/CoreBunch/Instatic/blob/main/migrations-pg.ts) for Postgres and [`migrations-sqlite.ts`](https://github.com/CoreBunch/Instatic/blob/main/migrations-sqlite.ts) for SQLite—managed by a unified runner in [`server/db/runMigrations.ts`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/server/db/runMigrations.ts):

```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`](https://github.com/CoreBunch/Instatic/blob/main/server/db/migrations-pg.ts) or `sqliteMigrations` from [`server/db/migrations-sqlite.ts`](https://github.com/CoreBunch/Instatic/blob/main/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:

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

```

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

```sql
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`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/src/__tests__/architecture/migration-parity.test.ts) test asserts that both [`migrations-pg.ts`](https://github.com/CoreBunch/Instatic/blob/main/migrations-pg.ts) and [`migrations-sqlite.ts`](https://github.com/CoreBunch/Instatic/blob/main/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`:

```ts
// 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`](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) to handle dialect-specific SQL.
- The `Migration` interface in [`runMigrations.ts`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/migrations-pg.ts) for PostgreSQL connections or `sqliteMigrations` from [`migrations-sqlite.ts`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/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.