# How OmniRoute Manages 154 SQLite Database Schema Migrations

> Discover how OmniRoute handles 154 SQLite database schema migrations using a versioned ledger, transactional runner, and safety thresholds for robust data management.

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

---

**OmniRoute uses an incremental SQL migration system with a versioned ledger table (`omniroute_migrations`) and a TypeScript migration runner that applies numbered scripts transactionally, with safety thresholds and optional FTS5 support.**

This article examines how the OmniRoute project handles schema evolution across 154 migrations in its single SQLite database. The implementation follows an IS 4-style versioning model where every schema change is tracked, ordered, and applied deterministically across desktop, server, and Docker environments.

## The Migration Architecture

OmniRoute stores all operational data in one SQLite file under the user's data directory. Schema changes propagate through **numbered SQL migration files** (`001_… .sql`, `002_… .sql`, up to `157_… .sql`) that execute in strict lexicographic order.

The migration system centers on two core files:

- **[`src/lib/db/migrationRunner.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/migrationRunner.ts)** — The orchestrator containing `runMigrations()`
- **[`src/lib/db/core.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/core.ts)** — Database initialization that triggers migrations automatically

## How the Migration Runner Works

In [`src/lib/db/migrationRunner.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/migrationRunner.ts), the `runMigrations()` function implements a six-step pipeline:

### 1. Migration Discovery

The runner reads `src/lib/db/migrations/` and sorts files lexicographically. This ensures `010_…` executes after `009_…`, even with leading zeros.

### 2. Ledger Tracking

A hidden table `omniroute_migrations` records every successfully applied version. On startup, the runner queries this table and skips any migrations already present.

### 3. Transactional Application

Pending migrations execute inside a single SQLite transaction. If any script fails, the entire transaction rolls back and startup aborts. This guarantees atomic schema changes.

### 4. Safety Thresholds

A configurable limit prevents runaway upgrades:

| Environment Variable | Default | Purpose |
|---------------------|---------|---------|
| `OMNIROUTE_MAX_PENDING_MIGRATIONS` | 50 | Abort if pending count exceeds threshold |

The test [`migration-safety-abort-6260.test.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/migration-safety-abort-6260.test.ts) validates this protection.

### 5. Conditional FTS5 Support

Later migrations depend on SQLite's full-text search extension. The runner detects FTS5 capability at runtime and defers optional scripts when unavailable. This allows databases created on minimal drivers to upgrade later without corruption.

### 6. Idempotent Design

Migrations check for existing objects before creating them. For example, a column-addition migration first verifies the column doesn't exist, enabling safe re-execution.

## Running Migrations Manually

Normally `getDbInstance()` in [`src/lib/db/core.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/core.ts) triggers migrations automatically. For debugging or testing, invoke the runner directly:

```typescript
import { getDbInstance } from '@/src/lib/db/core';
import { runMigrations } from '@/src/lib/db/migrationRunner';

// Obtain SQLite adapter (creates DB file if missing)
const db = await getDbInstance();

// Apply all pending migrations
const appliedCount = runMigrations(db, { isNewDb: false });
console.log(`Applied ${appliedCount} migrations`);

```

## Inspecting Migration State

Query the ledger directly to verify which versions ran:

```typescript
const ledgerRows = await db.all<{ version: string }>(
  `SELECT version FROM omniroute_migrations ORDER BY version`,
);
console.log('Applied migrations:', ledgerRows.map(r => r.version));

```

## Creating Fresh Databases for Testing

Force a clean database with the `reset` option:

```typescript
const freshDb = await getDbInstance({ reset: true });
runMigrations(freshDb, { isNewDb: true }); // executes 001…157 sequentially

```

The `isNewDb: true` flag optimizes for empty databases, though the ledger remains the source of truth.

## Key Files and Tests

| Path | Role |
|------|------|
| [`src/lib/db/migrationRunner.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/migrationRunner.ts) | Core orchestrator with `runMigrations()` |
| [`src/lib/db/core.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/core.ts) | `getDbInstance()` and automatic migration triggers |
| `src/lib/db/migrations/` | 157 numbered SQL scripts (e.g., [`001_create_plugins.sql`](https://github.com/diegosouzapw/OmniRoute/blob/main/001_create_plugins.sql), [`157_exclusive_connection_leases.sql`](https://github.com/diegosouzapw/OmniRoute/blob/main/157_exclusive_connection_leases.sql)) |
| [`tests/unit/db-migration-runner.test.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/tests/unit/db-migration-runner.test.ts) | Unit tests for ordering, idempotency, FTS5 handling |
| [`tests/unit/migration-safety-abort-6260.test.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/tests/unit/migration-safety-abort-6260.test.ts) | Safety threshold validation |
| [`tests/unit/db-pre-migration-backup-retention-10421.test.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/tests/unit/db-pre-migration-backup-retention-10421.test.ts) | Backup pruning and ledger edge cases |

## Summary

- **154 migrations** are managed through numbered SQL files in `src/lib/db/migrations/`
- The **`omniroute_migrations` ledger table** tracks applied versions persistently
- **`runMigrations()` in [`src/lib/db/migrationRunner.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/migrationRunner.ts)** orchestrates discovery, filtering, and transactional execution
- **Safety thresholds** (`OMNIROUTE_MAX_PENDING_MIGRATIONS=50`) prevent accidental mass upgrades
- **FTS5 conditional logic** allows graceful degradation on limited SQLite builds
- **Idempotent scripts** support re-execution without errors

This design gives OmniRoute deterministic schema evolution across releases and environments while maintaining data integrity through transactional guarantees.

## Frequently Asked Questions

### How does OmniRoute prevent migrations from running out of order?

The migration runner sorts files lexicographically from `src/lib/db/migrations/` and cross-references each against the `omniroute_migrations` ledger before execution. Only versions absent from the ledger run, and they run in sorted sequence.

### What happens if a migration script fails halfway through?

All pending migrations execute within a single SQLite transaction. Any failure triggers a rollback, leaving the schema unchanged and aborting startup. This atomicity prevents partial schema updates.

### Can OmniRoute upgrade databases created on systems without FTS5?

Yes. The runner detects FTS5 availability at runtime and defers optional full-text search migrations. The database remains functional and can apply deferred migrations later when moved to an FTS5-capable environment.

### Where is the migration version history stored?

The `omniroute_migrations` table lives inside the same SQLite database file as application data. This co-location ensures version tracking travels with the database across backups and migrations.