How Instatic Handles Database Migrations for PostgreSQL and SQLite

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, while SQLite equivalents live in server/db/migrations-sqlite.ts.

// 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:

// 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 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)	SQLite (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 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.

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:

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

// 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 test will fail if the IDs diverge or if sequences are mismatched.

Testing and Validation

The 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 and server/db/migrations-sqlite.ts with strictly synchronized IDs
• The migration runner in 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 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.

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