# How to Create a Database Migration for New Features in OmniRoute's SQLite Schema

> Learn how to create a database migration for new features in OmniRoute's SQLite schema. Follow simple steps to add new features to your schema and ensure transactional updates.

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

---

**TLDR:** OmniRoute manages schema changes through numbered SQL files in `db/migrations/` that are automatically discovered and executed by the migration runner in [`src/lib/db/migrationRunner.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/migrationRunner.ts); to add a new feature, create a file with the next three-digit prefix, write idempotent SQL, and restart the server to apply the changes transactionally.

OmniRoute persists all application data in a SQLite database controlled by a version-controlled migration system. When extending the schema—whether adding tables, columns, indexes, or seed data—you must create a database migration for new features that the built-in runner can apply safely across environments. This guide explains the exact workflow using the source code implementation from the `diegosouzapw/OmniRoute` repository.

## Understanding OmniRoute's Migration Architecture

The migration system is a thin wrapper around **better-sqlite3** that ensures each script runs exactly once and atomically.

### The Migration Runner

In [`src/lib/db/migrationRunner.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/migrationRunner.ts), the `runMigrations()` function handles the orchestration:

- Scans the `db/migrations/` directory (or the path specified by `OMNIROUTE_MIGRATIONS_DIR`) for `.sql` files
- Queries the internal `_omniroute_migrations` table to identify already-applied scripts
- Executes pending migrations inside a transaction
- Records the filename and timestamp in `_omniroute_migrations` upon success

The runner relies on the singleton database instance provided by `getDbInstance()` in [`src/lib/db/core.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/core.ts).

### Migration File Conventions

All migrations reside in `db/migrations/` and follow strict naming:

- **Three-digit prefix:** Files are ordered lexicographically using a numeric prefix (e.g., [`001_initial_schema.sql`](https://github.com/diegosouzapw/OmniRoute/blob/main/001_initial_schema.sql), [`071_add_user_preferences.sql`](https://github.com/diegosouzapw/OmniRoute/blob/main/071_add_user_preferences.sql))
- **Pure SQL:** Scripts contain raw SQLite statements, making them portable across local development, Docker containers, and Fly.io deployments
- **Idempotent design:** Every statement must be safe to run multiple times without errors

## Step-by-Step: Create a Database Migration for New Features

Follow this sequence when extending the OmniRoute schema:

1. **Pick the next migration number** – Examine existing files in `db/migrations/` and increment the highest three-digit prefix (e.g., if the last file is [`070_update_routes.sql`](https://github.com/diegosouzapw/OmniRoute/blob/main/070_update_routes.sql), use `071_`).

2. **Create the `.sql` file** – Name it with the chosen prefix and a descriptive suffix, then place it in `db/migrations/`.

3. **Write idempotent SQL** – Guard schema changes with `IF NOT EXISTS` clauses and wrap logic in transactions to ensure safety on re-runs.

4. **Commit the file** – The migration runner detects new files automatically; no TypeScript code changes are required.

5. **Run the project** – Execute `npm run dev` to start the server. On startup, `runMigrations()` applies pending scripts inside a transaction.

6. **Verify the changes** – Query the schema via the SQLite CLI or check the built-in health endpoint at `/api/health`.

## Writing Idempotent SQLite Migration Scripts

Idempotency prevents failures if a migration is re-applied (e.g., during CI testing or container restarts). Always guard destructive or additive operations.

```sql
-- db/migrations/071_add_user_preferences.sql
BEGIN TRANSACTION;

-- Add a new column to the existing users table safely
ALTER TABLE users ADD COLUMN preferences TEXT DEFAULT '{}';

-- Create a new table only if it doesn't exist
CREATE TABLE IF NOT EXISTS feature_flags (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL UNIQUE,
  enabled INTEGER NOT NULL DEFAULT 0,
  created_at INTEGER NOT NULL DEFAULT (strftime('%s','now'))
);

COMMIT;

```

For complex alterations that SQLite's `ALTER TABLE` doesn't support natively, query `sqlite_master` to verify existence before creating temporary tables or recreating schemas.

## Testing and Verifying Your Migration

After creating the file, test the migration locally before committing:

```bash

# Start the development server to trigger the migration runner

npm run dev

# Inspect the updated schema using the SQLite CLI

sqlite3 ~/.omniroute/omniroute.db ".schema users"

# Verify data integrity or seed values

sqlite3 ~/.omniroute/omniroute.db "SELECT * FROM feature_flags LIMIT 5;"

```

If the migration fails, the transaction rolls back automatically, leaving the database in its previous state. Check the server logs for specific SQL error messages from `better-sqlite3`.

## Summary

- **Location:** Place all migration files in `db/migrations/` using a three-digit numeric prefix (e.g., [`072_feature_name.sql`](https://github.com/diegosouzapw/OmniRoute/blob/main/072_feature_name.sql)).
- **Idempotency:** Use `IF NOT EXISTS` for tables and indexes; wrap multi-statement migrations in `BEGIN TRANSACTION` / `COMMIT`.
- **Automatic Execution:** The `runMigrations()` function in [`src/lib/db/migrationRunner.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/migrationRunner.ts) applies new scripts on startup without manual intervention.
- **Tracking:** Applied migrations are recorded in the `_omniroute_migrations` table to prevent duplicate execution.
- **Portability:** Pure SQL migrations work consistently across local development, Docker, and production environments.

## Frequently Asked Questions

### Where does OmniRoute store migration files?

Migration files are stored in the `db/migrations/` directory at the project root. According to the source code in [`src/lib/db/migrationRunner.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/migrationRunner.ts), the runner scans this directory (or the path override specified by the `OMNIROUTE_MIGRATIONS_DIR` environment variable) for all `.sql` files and sorts them lexicographically to determine execution order.

### What makes a migration "idempotent" and why does it matter?

An idempotent migration produces the same result whether it runs once or multiple times. This matters because OmniRoute's runner executes migrations inside transactions during every container start or server restart in certain environments. Using `CREATE TABLE IF NOT EXISTS` instead of `CREATE TABLE`, for example, prevents "table already exists" errors on re-runs while ensuring the schema exists after the first run.

### How does OmniRoute prevent migrations from running twice?

The system maintains an internal ledger table named `_omniroute_migrations`. Before executing any script, `runMigrations()` queries this table to check if the filename already exists. If found, the runner skips the file; if not found, it executes the SQL and inserts a record with the filename and current timestamp, ensuring exactly-once semantics.

### Can I include data manipulation statements in a schema migration?

Yes. You can include `INSERT`, `UPDATE`, or `DELETE` statements alongside `CREATE` or `ALTER` commands within the same migration file. Because the runner wraps each migration in a transaction, schema changes and data back-fills succeed or fail atomically, preserving database integrity if any statement errors.