# How OmniRoute's Database Layer Functions with Migrations and Modules

> Discover how OmniRoute's database layer works with migrations and modules. Learn about its SQLite adapter, versioned migration runner, and domain-specific modules for efficient database operations across environments.

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

---

**OmniRoute implements a singleton SQLite adapter pattern orchestrated through [`src/lib/db/core.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/core.ts), utilizing a versioned migration runner in [`src/lib/db/migrationRunner.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/usageLogs.ts) and [`settings.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/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:

```typescript
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`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/usageLogs.ts):

```typescript
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`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/settings.ts):

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

```typescript
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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/usageLogs.ts) and [`settings.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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.