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

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; 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, 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.

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, 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, 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.

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

# 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).
• 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 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, 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.

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