# How Instatic Handles Database Migrations for PostgreSQL and SQLite

> Learn how Instatic expertly manages database migrations for PostgreSQL and SQLite with synchronized streams and dialect-specific SQL ensuring seamless cross-engine application logic.

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

---

**Instatic maintains two synchronized migration streams—one for PostgreSQL and one for SQLite—that share identical migration IDs but contain dialect-specific SQL, allowing the same application logic to run across different database engines without schema drift.**

Instatic implements a dual-file strategy to manage **database migrations** across multiple storage engines. By maintaining separate migration arrays for PostgreSQL and SQLite while enforcing strict ID parity, the system accommodates each dialect's type system and function syntax while guaranteeing that both schemas evolve identically over time.

## Parallel Migration Streams for Each Engine

Instatic defines migrations as plain TypeScript arrays exported from dedicated files. The project ships with two parallel migration definitions that live in separate modules but must remain synchronized through their migration IDs.

### The Migration File Structure

Each migration stream exports a `Migration[]` array containing objects with an `id` and `sql` property. The PostgreSQL definitions reside in [`server/db/migrations-pg.ts`](https://github.com/CoreBunch/Instatic/blob/main/server/db/migrations-pg.ts), while SQLite equivalents live in [`server/db/migrations-sqlite.ts`](https://github.com/CoreBunch/Instatic/blob/main/server/db/migrations-sqlite.ts).

```typescript
// server/db/migrations-pg.ts
export const pgMigrations: Migration[] = [
  { id: '001_baseline', sql: 'create table users (...)' },
  { id: '002_plugin_schedules', sql: '...' },
  // ...
];

```

The SQLite file exports `sqliteMigrations` with the **exact same IDs** but dialect-specific SQL statements:

```typescript
// server/db/migrations-sqlite.ts
export const sqliteMigrations: Migration[] = [
  { id: '001_baseline', sql: 'create table users (...)' },
  { id: '002_plugin_schedules', sql: '...' },
  // ...
];

```

### Enforcing Migration ID Parity

To prevent schema drift between engines, Instatic includes a CI test at [`src/__tests__/architecture/migration-parity.test.ts`](https://github.com/CoreBunch/Instatic/blob/main/src/__tests__/architecture/migration-parity.test.ts) that verifies both migration arrays contain identical ID sequences. If you add a migration to one file without the corresponding entry in the other, the test suite fails, blocking the deployment.

## Dialect-Specific Type Mapping

While migration IDs must match, the SQL content differs to accommodate each engine's data types and functions.

| PostgreSQL ([`migrations-pg.ts`](https://github.com/CoreBunch/Instatic/blob/main/migrations-pg.ts)) | SQLite ([`migrations-sqlite.ts`](https://github.com/CoreBunch/Instatic/blob/main/migrations-sqlite.ts)) |
|--------------------------------|--------------------------------|
| `jsonb` | Translated to `text` with `_json` column suffix handling |
| `timestamptz` | Translated to `text` |
| `bytea` | Translated to `blob` |
| `bigint`, `boolean` | Translated to `integer` |

The SQLite file includes a header comment documenting these translation rules. During runtime, the SQLite adapter automatically parses any column ending in `_json` to handle JSON serialization differences.

## The Migration Runner Architecture

The core migration logic resides in [`server/db/runMigrations.ts`](https://github.com/CoreBunch/Instatic/blob/main/server/db/runMigrations.ts) and remains completely dialect-agnostic. It imports whichever migration array is appropriate for the currently connected database.

### Runtime Dialect Detection

The system reads `process.env.DATABASE_URL` at startup to determine which driver to instantiate. The `getDbClient()` function returns either a `PostgresClient` or `SqliteClient`, each exposing the appropriate migration array through a `migrations` property.

```typescript
import { getDbClient } from './db/client';
import { runMigrations } from './db/runMigrations';

const db = getDbClient();  // Returns PostgresClient or SqliteClient
await runMigrations(db);   // Executes the correct migration stream

```

### Execution Flow

The runner applies migrations transactionally:

1. **Load pending migrations** — Compares the migration array against entries in the `schema_migrations` tracking table
2. **Execute sequentially** — Runs each missing migration's SQL in order
3. **Record completion** — Inserts the migration `id` into `schema_migrations` upon success

The system enforces an **additive, non-destructive** policy: migrations may only use `CREATE TABLE`, `ALTER TABLE ADD COLUMN`, or `INSERT ... ON CONFLICT ...` statements. The codebase explicitly forbids `DROP TABLE` or column removal operations to protect live data.

## Adding a New Migration (Step-by-Step)

When evolving the schema, you must update both migration files with the same ID but engine-appropriate SQL:

```typescript
// server/db/migrations-pg.ts
export const pgMigrations: Migration[] = [
  /* ... existing migrations ... */
  {
    id: '018_user_last_seen',
    sql: `alter table users add column last_seen_at text default (now());`,
  },
];

```

```typescript
// server/db/migrations-sqlite.ts
export const sqliteMigrations: Migration[] = [
  /* ... existing migrations ... */
  {
    id: '018_user_last_seen',  // Must match PG ID exactly
    sql: `
      alter table users add column last_seen_at text
        default (strftime('%Y-%m-%dT%H:%M:%fZ','now'));
    `,
  },
];

```

Commit both files together in a single changeset. The [`migration-parity.test.ts`](https://github.com/CoreBunch/Instatic/blob/main/migration-parity.test.ts) test will fail if the IDs diverge or if sequences are mismatched.

## Testing and Validation

The [`migration-parity.test.ts`](https://github.com/CoreBunch/Instatic/blob/main/migration-parity.test.ts) architecture test guarantees that:

- Both migration arrays contain the same number of entries
- Every ID in the PostgreSQL list exists in the SQLite list with identical spelling
- IDs follow sequential naming conventions (`001_`, `002_`, etc.)

This enforcement ensures that developers cannot accidentally ship a schema change that works on PostgreSQL but breaks SQLite installations (or vice versa).

## Summary

- Instatic maintains **dual migration streams** 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 strictly synchronized IDs
- The **migration runner** in [`server/db/runMigrations.ts`](https://github.com/CoreBunch/Instatic/blob/main/server/db/runMigrations.ts) selects the appropriate array at runtime based on `DATABASE_URL`
- **Dialect-specific type mapping** handles differences between PostgreSQL's `jsonb`/`timestamptz` and SQLite's `text`/`blob` equivalents
- **Additive-only policies** prevent destructive schema changes that could lose production data
- **CI parity tests** block deployments if migration IDs fall out of sync between dialects

## Frequently Asked Questions

### How does Instatic decide which migration file to execute?

The `getDbClient()` function inspects `process.env.DATABASE_URL` to instantiate either a PostgreSQL or SQLite client. When `runMigrations(db)` is called, it accesses the `db.migrations` property, which points to either `pgMigrations` or `sqliteMigrations` depending on the active adapter.

### What happens if I only add a migration to the PostgreSQL file?

The [`migration-parity.test.ts`](https://github.com/CoreBunch/Instatic/blob/main/migration-parity.test.ts) test in `src/__tests__/architecture/` will fail in CI, preventing the merge. This safety check ensures that schema changes are always reflected in both database engines to maintain compatibility.

### Are destructive schema changes like DROP COLUMN supported?

No. Instatic follows an **additive-only** migration policy. You may add tables, add columns, or insert seed data, but you cannot drop tables or remove columns. This constraint protects data integrity across deployments and ensures SQLite and PostgreSQL schemas remain compatible.

### How does Instatic handle JSON data differently between Postgres and SQLite?

PostgreSQL migrations use native `jsonb` columns, while SQLite migrations use `text` columns with the `_json` suffix. The SQLite adapter automatically parses these `_json` columns during query execution, providing transparent serialization that matches PostgreSQL's JSONB behavior without requiring application-level changes.