OmniRoute Database Migration Strategy: How to Add New Migrations

OmniRoute uses an automatic, ordered SQL-file migration system that runs on startup, requiring only that developers add numbered .sql scripts to src/lib/db/migrations/ with no manual CLI invocation needed.

OmniRoute manages its SQLite persistence layer through a lightweight internal migration framework. Unlike external tools such as Knex or Flyway, the migration logic is embedded directly in the application source code, making schema changes a natural part of the development workflow. This article explains exactly how the OmniRoute database migration strategy works and the precise steps to add new migrations.

How OmniRoute's Migration System Works

The migration runner lives in src/lib/db/migrate.ts and executes automatically whenever OmniRoute starts. It follows a strict, transactional process to ensure schema integrity.

The Migration Execution Flow

According to the source code in src/lib/db/migrate.ts, the runner performs these steps:

1. Opens the SQLite database — path resolved from DATA_DIR environment variable or defaulted to ~/.omniroute/
2. Queries the omniroute_migrations table — a special bookkeeping table tracking already-applied versions
3. Scans and sorts migration files — reads src/lib/db/migrations/ and orders files by numeric prefix
4. Executes pending migrations in a single transaction — each script runs atomically, with success recorded in omniroute_migrations

If any migration fails, the entire transaction rolls back and startup aborts. This guarantees the schema never ends up partially upgraded — a critical reliability property for production deployments.

Migration File Format

Each migration is a plain .sql file with a strict naming convention:

{version}-{descriptive-name}.sql

Examples from the repository:

• 001-init.sql
• 002-add-model-table.sql

The version prefix must be zero-padded to maintain lexicographic sort order when you exceed 9 migrations. The migration runner parses these prefixes numerically, so 010-add-api-key-table.sql correctly follows 009-previous-migration.sql.

How to Add a New Migration in OmniRoute

Adding schema changes requires no code changes outside the SQL file itself. The discovery mechanism is purely filesystem-based.

Step 1: Create the Migration File

Navigate to src/lib/db/migrations/ and create a file with the next incremental number:

touch src/lib/db/migrations/010-add-api-key-table.sql

Use the highest existing prefix plus one. Check the directory first:

ls -1 src/lib/db/migrations/ | sort -V | tail -1

Step 2: Write the SQL Statements

A typical migration includes DDL and optional DML. The source code shows this pattern:

BEGIN;

CREATE TABLE IF NOT EXISTS api_keys (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    key           TEXT NOT NULL UNIQUE,
    owner_user_id INTEGER NOT NULL,
    created_at    DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_api_keys_owner ON api_keys(owner_user_id);

COMMIT;

Wrap statements in an explicit transaction (BEGIN; … COMMIT;). While migrate.ts adds its own transaction wrapper, this ensures the script remains safe when run manually during debugging.

Step 3: Commit and Deploy

No additional registration is required. Commit the file:

git add src/lib/db/migrations/010-add-api-key-table.sql
git commit -m "Add api_keys table for authentication"

The next OmniRoute startup — whether in production, development, or CI test runs — automatically executes the migration.

Step 4: (Optional) Local Verification

The repository provides a helper script to validate migrations before shipping:

npm run db:migrate

This invokes the same migrate.ts logic used by the server, printing applied migrations and aborting on errors. Use this to catch syntax errors or constraint violations early.

Step 5: Add Test Coverage

OmniRoute requires database-affecting code to have test coverage. Verify your migration with a test that:

• Opens a temporary database
• Runs the full migration suite
• Asserts the new schema objects exist and function correctly

Example test structure:

import { getDbInstance } from '../src/lib/db/core';

test('api_keys table is created and insertable', async () => {
    const db = getDbInstance(':memory:'); // or temp path
    // migrations run automatically on first connect
    const result = db.prepare(`
        INSERT INTO api_keys (key, owner_user_id)
        VALUES ('test-key-123', 1)
    `).run();
    expect(result.changes).toBe(1);
});

Key Files in the Migration Architecture

File	Purpose	Location
src/lib/db/migrate.ts	Core migration runner: scans files, manages omniroute_migrations table, executes transactions	migrate.ts
src/lib/db/core.ts	SQLite singleton provider (getDbInstance) used by migrations and application code	core.ts
src/lib/db/migrations/	Directory containing all numbered .sql migration scripts	migrations folder
src/lib/db/featureFlags.ts	Runtime feature-flag storage; migrations may insert new flag rows here	featureFlags.ts

Summary

• OmniRoute's database migration strategy uses ordered SQL files with numeric prefixes, discovered automatically at startup
• The migration runner in src/lib/db/migrate.ts handles all execution, transaction management, and version tracking via omniroute_migrations
• Adding migrations requires only creating a properly named .sql file in src/lib/db/migrations/ — no CLI commands, no registry edits
• Atomic execution guarantees schema consistency; failed migrations roll back completely
• Verification tools include npm run db:migrate and mandatory test coverage for schema changes

Frequently Asked Questions

What happens if two developers create migrations with the same version number?

The migration runner detects this at startup and aborts with an error. Version numbers must be unique. Coordinate with your team or use a locking mechanism during development; the numeric prefix enforces a strict linear history with no duplicates allowed.

Can I modify an existing migration file after it has been applied?

Never modify applied migrations. Once a migration is recorded in omniroute_migrations, the runner ignores the file. To fix a schema issue, create a new migration that alters or recreates the affected objects. This preserves the audit trail and prevents environment drift.

Does OmniRoute support down-migrations or rollbacks?

The current implementation in src/lib/db/migrate.ts does not include rollback logic. To reverse a change, you must write a new forward migration that undoes the schema alteration (e.g., DROP TABLE for a CREATE TABLE). This design choice prioritizes simplicity and recoverability through forward-only changes.

How do I handle migrations that need to seed data, not just schema changes?

Data manipulation is fully supported. Include INSERT, UPDATE, or DELETE statements alongside DDL in your .sql file. For complex seeding logic, consider splitting into two migrations: one for schema, one for data, to maintain clear separation of concerns.

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