Database Migration System in OmniRoute: How to Add Migrations Safely

OmniRoute uses a custom migration runner built on top of better-sqlite3 that executes numbered SQL files from src/lib/db/migrations/ and tracks applied versions in the _omniroute_migrations table, with built-in safety checks including automatic backups and mass-migration aborts.

The repository diegosouzapw/OmniRoute implements a robust, file-based database migration system for SQLite that prioritizes data safety over convenience. Unlike traditional ORM migrators, this system uses plain SQL files orchestrated by a TypeScript runner, providing transparent version control and multiple guardrails against accidental schema corruption.

Core Architecture Components

The migration system consists of four primary pieces that work together to ensure reliable schema evolution.

• src/lib/db/migrationRunner.ts: The central orchestrator that resolves migration directories, validates file sequences, performs safety checks, and executes scripts within SQLite transactions.
• src/lib/db/migrationRunner/constants.ts: Pure data tables defining renamed migration compatibility, legacy slot mappings, superseded duplicates, and optional FTS5 migration sets.
• _omniroute_migrations table: Automatically created tracking table storing version, name, and applied_at timestamps for every executed migration.
• Migration files: Plain SQL files following the naming convention NNN_description.sql (e.g., 032_create_api_key_lifecycle.sql), where the numeric prefix serves as the canonical version identifier.

How the Migration Runner Executes

The execution flow in runMigrations() follows a strict sequence designed to prevent data loss and handle edge cases like renamed files or legacy databases.

Discovery and Validation

First, resolveMigrationsDir() locates the migrations folder by checking several possible paths, falling back to process.cwd() if necessary. Then getMigrationFiles() reads the directory, extracts version numbers and names from filenames, and validates that no two files share the same version unless explicitly listed as superseded duplicates in constants.ts.

Legacy Reconciliation

Before applying changes, the runner calls rehomeLegacyVersionSlotMigrations() and reconcileRenamedMigrations() (lines 724-774 in migrationRunner.ts). These functions rewrite entries in the _omniroute_migrations table to handle file renames or version slot shifts, ensuring historic tracking data remains valid even when files move.

Safety Checks and Backup

The system implements three critical protections before executing SQL:

1. Name-mismatch detection (lines 424-442): Warns if a migration's filename has changed but retains its version number, preventing accidental renumbering.
2. Mass-migration abort (lines 846-877): Terminates execution if more than OMNIROUTE_MAX_PENDING_MIGRATIONS (default 50) pending migrations are detected on an existing database, protecting against tracking table loss.
3. Pre-migration backup (lines 500-506): Creates a full database copy using VACUUM INTO before any changes occur.

Transactional Execution

Each pending migration runs inside a SQLite transaction. The isSchemaAlreadyApplied() function (lines 664-669) provides idempotency guards by checking if intended schema changes already exist, skipping execution with a warning if manual alterations are detected. Special-case migrations (like the API-key lifecycle at version 032) trigger dedicated handler functions, while standard migrations execute raw SQL directly.

How to Add New Migrations Safely

Follow this protocol when extending the schema to ensure compatibility with OmniRoute's safety mechanisms.

1. Determine the Next Version Number

Inspect src/lib/db/migrations/ for the highest numeric prefix, then increment by one. If the last file is 110_some_feature.sql, create 111_new_feature.sql.

2. Create the Idempotent SQL File

Name the file with zero-padded version numbers (001, 002, etc.). Include guards against duplicate execution:

-- src/lib/db/migrations/111_add_user_preferences.sql
CREATE TABLE IF NOT EXISTS user_preferences (
  id INTEGER PRIMARY KEY,
  user_id TEXT NOT NULL,
  key TEXT NOT NULL,
  value TEXT,
  UNIQUE(user_id, key)
);

3. Update Constants for Special Cases

If your migration requires FTS5 or represents a rename of an existing migration, modify constants.ts:

// src/lib/db/migrationRunner/constants.ts

// For FTS5-dependent migrations:
export const OPTIONAL_FTS5_MIGRATION_VERSIONS = new Set(["022", "023", "111"]);

// For renamed migrations:
export const RENAMED_MIGRATION_COMPATIBILITY = [
  // existing entries...
  { fromVersion: "111", fromName: "old_users_table", toVersion: "112", toName: "add_user_preferences" },
] as const;

4. Verify Locally

Run the migration through the test harness or by starting the application:

import { getDbInstance } from "@/lib/db/core";
import { runMigrations } from "@/lib/db/migrationRunner";

const db = getDbInstance();
const status = await runMigrations(db);
console.log("Migrations applied:", status); // Returns number of newly applied migrations

Check that the _omniroute_migrations table contains your new version row.

5. Commit Changes

Include both the .sql file and any constants.ts modifications in your commit. The CI pipeline runs npm run check, which executes the migration runner and validates file numbering and constant consistency.

Key Safety Mechanisms

OmniRoute's migration system prioritizes data integrity through multiple defensive layers:

• Transaction Wrapping: Every migration executes within a SQLite transaction, ensuring atomic commits or full rollbacks on error.
• Duplicate Column Protection: The runner catches "duplicate column name" errors and treats them as idempotent successes rather than failures.
• Version Collision Detection: Prevents two migrations from claiming the same version number unless explicitly configured as superseded duplicates.
• Automatic Tracking Table Creation: ensureMigrationsTable() creates _omniroute_migrations automatically if missing, allowing fresh database initialization without manual setup.

Summary

• OmniRoute's database migration system uses file-based SQL migrations stored in src/lib/db/migrations/ with strict NNN_description.sql naming.
• The migrationRunner.ts orchestrator provides legacy reconciliation, mass-migration aborts (default limit 50), and automatic VACUUM INTO backups.
• Migration state persists in the _omniroute_migrations table, created automatically by ensureMigrationsTable().
• New migrations require zero-padded version numbers, idempotent SQL (IF NOT EXISTS), and potential updates to constants.ts for FTS5 or renamed migrations.
• All migrations run inside transactions with pre-flight schema checks via isSchemaAlreadyApplied() to prevent duplicate execution errors.

Frequently Asked Questions

What happens if I rename an existing migration file?

The runner detects this through reconcileRenamedMigrations(), which queries RENAMED_MIGRATION_COMPATIBILITY in constants.ts. If you map the old version/name pair to the new one, the system updates the _omniroute_migrations table entries without re-executing SQL. Without this mapping, the runner treats the file as a new migration and aborts if the version conflicts.

How does OmniRoute handle duplicate column errors?

Inside runMigrations(), the system catches SQLite error codes indicating duplicate columns or existing tables. Rather than failing, it logs a warning and marks that specific migration as applied, enforcing idempotency. For new migrations, you should still use IF NOT EXISTS clauses as a primary guard.

Can I disable the automatic backup before migrations?

The pre-migration backup using VACUUM INTO (lines 500-506) runs by default to protect production data. While the source code structure suggests this is integral to the safety flow, you would need to modify migrationRunner.ts to bypass this check. Disabling it is strongly discouraged as it removes protection against tracking table corruption.

What is the maximum number of pending migrations allowed?

The OMNIROUTE_MAX_PENDING_MIGRATIONS constant defaults to 50 pending migrations. If the runner detects more than 50 unapplied migrations on an existing database (one that already contains the tracking table), it aborts immediately. This safety valve prevents catastrophic scenarios where the _omniroute_migrations table is dropped or corrupted, which would otherwise cause the runner to attempt re-running every historical migration.

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