How OmniRoute's Database Layer Functions with Migrations and Modules

OmniRoute implements a singleton SQLite adapter pattern orchestrated through src/lib/db/core.ts, utilizing a versioned migration runner in src/lib/db/migrationRunner.ts and domain-specific modules to isolate database operations across Node.js, edge, and build environments.

OmniRoute persists all application state to a single SQLite file using a centralized database layer that separates connection management from business logic. The system employs a lazy-initialized singleton pattern to handle diverse JavaScript runtimes while maintaining schema integrity through numbered SQL migrations. This architecture stores data in ~/.omniroute/storage.sqlite by default and organizes functional access into isolated TypeScript modules under src/lib/db/.

Singleton Database Connection and Runtime Handling

The Core Singleton Pattern

The database layer centers on getDbInstance() exported from src/lib/db/core.ts. This function implements lazy initialization, checking getDb() for an existing cached connection before instantiating a new SqliteAdapter. The adapter abstracts the underlying driver selection: better-sqlite3 for native Node.js environments or sql.js as an in-browser fallback. During server startup, ensureDbInitialized() invokes this singleton to verify the database file exists and trigger the migration runner.

Environment-Specific Adapters

OmniRoute adapts its database behavior based on runtime context to prevent crashes and optimize performance. When isCloud detects an edge environment, the system uses an in-memory :memory: database rather than a persistent file. During Next.js static builds (isBuildPhase), getDbInstance() returns a no-op stub that accepts queries but never executes them, avoiding attempts to load native SQLite bindings that would crash the build process.

Versioned SQL Migration System

Migration Runner Mechanics

Schema evolution is handled by runMigrations() in src/lib/db/migrationRunner.ts. This function resolves the src/lib/db/migrations/ directory, numerically sorts all .sql files, and executes them sequentially within a single transaction. Applied versions are recorded in the internal _omniroute_migrations table, ensuring idempotent migrations. The runner skips any scripts already present in this tracking table.

Data Safety and Backup Protocols

Before applying migrations, createHealthCheckBackup() (implemented in src/lib/db/core.ts) generates a timestamped database copy in the db_backups/ directory. Additionally, the migration runner checks the OMNIROUTE_MAX_PENDING_MIGRATIONS environment variable as a safety guard. If the number of pending migrations exceeds this threshold, the process aborts to prevent accidental mass-migrations that could indicate configuration errors.

Modular Domain Access Pattern

Functional Module Structure

Database operations are organized into domain-specific modules located alongside the core files in src/lib/db/. Modules such as usageLogs.ts and settings.ts import the shared getDbInstance() singleton and interact with the database through the adapter's prepare(), run(), get(), and all() methods. This structure isolates raw SQL to the database layer while keeping business logic tidy and testable.

Practical Database Operations

To obtain a database handle in any module:

import { getDbInstance } from "@/lib/db/core";

const db = getDbInstance(); // Returns SqliteAdapter (native or sql.js)

Inserting records through a domain module follows this pattern, as implemented in src/lib/db/usageLogs.ts:

import { getDbInstance } from "@/lib/db/core";

export function insertUsageLog(userId: string, tokens: number): void {
  const db = getDbInstance();
  const stmt = db.prepare(
    `INSERT INTO usage_logs (user_id, token_count, ts) VALUES (?, ?, datetime('now'))`
  );
  stmt.run(userId, tokens);
}

Reading configuration values with type safety, as seen in src/lib/db/settings.ts:

import { getDbInstance } from "@/lib/db/core";

export function getFeatureFlag(flag: string): boolean {
  const db = getDbInstance();
  const row = db.prepare(
    `SELECT value FROM feature_flags WHERE name = ?`
  ).get(flag);
  return row ? Boolean(row.value) : false;
}

For testing or manual administration, you can trigger migrations programmatically:

import { getDbInstance } from "@/lib/db/core";
import { runMigrations } from "@/lib/db/migrationRunner";

async function applyAllMigrations() {
  const db = getDbInstance();
  await runMigrations(db);
}

Summary

• Singleton Pattern: getDbInstance() in src/lib/db/core.ts ensures all modules share a single database connection, preventing duplicate file handles.
• Versioned Migrations: The runner in src/lib/db/migrationRunner.ts applies numbered .sql files from src/lib/db/migrations/ and tracks progress in _omniroute_migrations.
• Safety Mechanisms: Automatic backups via createHealthCheckBackup() and OMNIROUTE_MAX_PENDING_MIGRATIONS guards protect against data loss.
• Modular Architecture: Domain modules like usageLogs.ts and settings.ts isolate SQL operations while consuming the shared singleton.
• Runtime Adaptation: The system automatically selects between file-based, in-memory, or stub adapters based on whether it runs in Node.js, cloud edge, or build phases.

Frequently Asked Questions

Where does OmniRoute store its SQLite database file?

By default, OmniRoute creates storage.sqlite in the writable data directory at ~/.omniroute/, as determined by the path resolution logic in src/lib/db/core.ts. When running in cloud or edge environments detected via isCloud, the system switches to an in-memory :memory: database instead of a persistent file.

How does the migration system track which SQL scripts have already executed?

The migration runner maintains an internal _omniroute_migrations table that records each successfully applied migration version. When runMigrations() executes from src/lib/db/migrationRunner.ts, it compares the numbered .sql files in src/lib/db/migrations/ against this table and only applies pending scripts within a single transaction.

What prevents OmniRoute from crashing during Next.js static builds?

During the build phase (isBuildPhase), the getDbInstance() function returns a no-op stub adapter that never executes actual queries. This prevents attempts to load native SQLite bindings like better-sqlite3 that would cause crashes in the build environment, allowing the application to import database modules safely during static site generation.

How do individual modules access the database without creating multiple connections?

All domain modules import the getDbInstance() singleton from src/lib/db/core.ts, which caches and returns a single SqliteAdapter instance across the entire application. This shared connection pattern ensures that all modules operate within the same connection context and prevents resource exhaustion from duplicate file handles.

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