# Database Migration System in OmniRoute: How to Add Migrations Safely

> Learn about OmniRoute's database migration system built on better-sqlite3. Safely add new migrations with automatic backups and mass-migration aborts for robust data management.

- Repository: [Diego Rodrigues de Sa e Souza/OmniRoute](https://github.com/diegosouzapw/OmniRoute)
- Tags: how-to-guide
- Published: 2026-07-10

---

**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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/NNN_description.sql) (e.g., [`032_create_api_key_lifecycle.sql`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/constants.ts).

### Legacy Reconciliation

Before applying changes, the runner calls `rehomeLegacyVersionSlotMigrations()` and `reconcileRenamedMigrations()` (lines 724-774 in [`migrationRunner.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/110_some_feature.sql), create [`111_new_feature.sql`](https://github.com/diegosouzapw/OmniRoute/blob/main/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:

```sql
-- 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`](https://github.com/diegosouzapw/OmniRoute/blob/main/constants.ts):

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

```typescript
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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/NNN_description.sql) naming.
- The [`migrationRunner.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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.