# OmniRoute Database Migration Strategy: How to Add New Migrations

> Discover OmniRoute's automatic database migration strategy. Learn how to add new SQL migrations effortlessly to src/lib/db/migrations/ without manual CLI commands.

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

---

**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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/001-init.sql)
- [`002-add-model-table.sql`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/010-add-api-key-table.sql) correctly follows [`009-previous-migration.sql`](https://github.com/diegosouzapw/OmniRoute/blob/main/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**:

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

```

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

```bash
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:

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

```bash
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:

```bash
npm run db:migrate

```

This invokes the same [`migrate.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/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:

```typescript
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`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/migrate.ts) | Core migration runner: scans files, manages `omniroute_migrations` table, executes transactions | [migrate.ts](https://github.com/diegosouzapw/OmniRoute/blob/release/v3.8.50/src/lib/db/migrate.ts) |
| [`src/lib/db/core.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/core.ts) | SQLite singleton provider (`getDbInstance`) used by migrations and application code | [core.ts](https://github.com/diegosouzapw/OmniRoute/blob/release/v3.8.50/src/lib/db/core.ts) |
| `src/lib/db/migrations/` | Directory containing all numbered `.sql` migration scripts | [migrations folder](https://github.com/diegosouzapw/OmniRoute/tree/release/v3.8.50/src/lib/db/migrations) |
| [`src/lib/db/featureFlags.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/featureFlags.ts) | Runtime feature-flag storage; migrations may insert new flag rows here | [featureFlags.ts](https://github.com/diegosouzapw/OmniRoute/blob/release/v3.8.50/src/lib/db/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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.