# How the SQLite Database Layer Functions with 95 Domain Modules and 110 Migrations in OmniRoute

> Discover how OmniRoute's SQLite database layer efficiently manages 95 domain modules and 110 migrations using a singleton connection and an idempotent migration runner for robust schema evolution.

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

---

**OmniRoute uses a singleton `better-sqlite3` connection, 95 domain-specific TypeScript modules, and an idempotent migration runner to manage 110 schema versions in a single SQLite database with WAL journaling enabled.**

The OmniRoute open-source project implements a sophisticated data persistence strategy that balances simplicity with enterprise-grade organization. By leveraging SQLite as its single source of truth and wrapping it in a carefully architected TypeScript layer, the codebase supports nearly a hundred logical domains without sacrificing type safety or maintainability. This article examines how the database layer coordinates across [`src/lib/db/core.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/core.ts), [`migrationRunner.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/migrationRunner.ts), and the extensive module ecosystem to deliver reliable, testable data access.

## Core Architecture: Three Design Pillars

The OmniRoute database layer rests on three interconnected concepts: a shared connection manager, isolated domain modules, and a robust migration system. Each pillar addresses a specific challenge in scaling SQLite across a complex application surface.

### Singleton Database Connection with WAL Journaling

At the foundation sits `getDbInstance()` exported from [`src/lib/db/core.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/core.ts). This function implements the singleton pattern around a `better-sqlite3` instance configured with **Write-Ahead Logging (WAL)** mode for improved concurrent read performance.

```

src/lib/db/core.ts

```

All 95 domain modules import this single function. When any module needs database access, it either calls `getDbInstance()` directly or receives an optional `db` parameter that defaults to the singleton. This design eliminates connection lifecycle management overhead and ensures consistent transaction behavior across the application.

The core module also exports `rowToCamel()` for converting SQLite's snake_case column names to camelCase JavaScript objects, plus `encryptConnectionFields()` for masking sensitive data at rest.

### 95 Domain-Specific Modules for Logical Separation

Rather than clustering all database logic in monolithic files, OmniRoute distributes operations across **95 specialized modules** under `src/lib/db/`. Each module owns a logical table set and exposes type-safe CRUD helpers.

Prominent examples include:

- **[`providers.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/providers.ts)** – Manages AI provider connections and credential storage
- **[`quotaSnapshots.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/quotaSnapshots.ts)** – Tracks usage quotas and billing snapshots
- **[`compression.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/compression.ts)** – Handles prompt compression metadata and statistics

```

src/lib/db/providers.ts
src/lib/db/quotaSnapshots.ts
src/lib/db/compression.ts

```

Every module follows an identical pattern: prepared statements are declared at file scope, exported functions accept an optional `db` parameter defaulting to `getDbInstance()`, and implementations wrap `db.prepare().run()` or `db.prepare().get()` calls. This consistency makes the codebase navigable for contributors and enables straightforward unit testing through dependency injection of mock database instances.

### 110 Idempotent SQL Migrations with Version Tracking

Schema evolution is handled by **110 migration files** stored in `db/migrations/`. The [`migrationRunner.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/migrationRunner.ts) module orchestrates their execution:

```

src/lib/db/migrationRunner.ts

```

The runner implements four key behaviors:

1. **Lexical ordering** – Files are processed by name, enforcing sequential application
2. **Transaction wrapping** – Each migration runs inside `BEGIN…COMMIT` boundaries
3. **Version tracking** – Applied migrations are recorded in the hidden `_omniroute_migrations` table
4. **Idempotency** – All migrations use `CREATE TABLE IF NOT EXISTS` and `INSERT OR IGNORE` patterns, permitting safe re-execution on every startup

This design removes the need for complex migration state machines. The runner can execute unconditionally during application bootstrap without risk of duplicate schema modifications.

## Operational Workflow: From Bootstrap to Query Execution

Understanding how these components interact requires tracing the application lifecycle from startup through active request handling.

### Startup: Migration Runner Initialization

During server bootstrap, the system invokes `runMigrations()` from [`src/lib/db/migrationRunner.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/migrationRunner.ts). The process follows this sequence:

1. Query `_omniroute_migrations` for the highest applied version number
2. Scan `db/migrations/*.sql` for files lexically greater than the current version
3. Execute each pending migration within a database transaction
4. Insert the new version into the tracking table upon successful commit

Because migrations are idempotent, this process is safe to repeat. A deployment with no new migration files results in zero side effects.

### Runtime: Domain Module Data Access

Once initialized, request handlers interact with the database through domain modules. A typical provider registration flow demonstrates the pattern:

```typescript
import { getDbInstance } from "./core";
import { upsertProvider } from "./providers";

export async function registerProvider(data: ProviderInput) {
  const db = getDbInstance();           // singleton retrieval
  upsertProvider(db, data);             // domain-specific prepared statement
}

```

The `upsertProvider()` function in [`providers.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/providers.ts) internally calls `db.prepare()` with parameterized SQL, protecting against injection while maintaining execution plan caching through `better-sqlite3`'s prepared statement reuse.

### Administrative: Manual Migration Control

For operational flexibility, OmniRoute exposes a CLI command `omniroute db:migrate` that directly invokes `runMigrations()`. This allows database administrators to apply schema changes without full application restarts, useful for zero-downtime deployments and recovery scenarios.

## Practical Code Examples

### Initializing the Database Connection

```typescript
import { getDbInstance } from "./core";

const db = getDbInstance(); // Creates SQLite file if absent, enables WAL mode
const tables = db.prepare(
  "SELECT name FROM sqlite_master WHERE type='table'"
).all();
console.log("DB ready, tables:", tables);

```

### Inserting Data Through Domain Modules

```typescript
import { upsertProvider } from "./providers";

await upsertProvider({
  providerId: "openai",
  apiKey: "sk-*****",
  config: JSON.stringify({ model: "gpt-4o" })
});

```

The `upsertProvider()` helper handles null coalescing, field encryption, and timestamp generation internally.

### Applying Migrations Manually

```typescript
import { runMigrations } from "./migrationRunner";

await runMigrations(); // Applies any pending *.sql files in db/migrations

```

### Retrieving Aggregated Statistics

```typescript
import { getDatabaseStats } from "./stats";

const stats = getDatabaseStats(); // Returns token counts, table sizes, index health
console.log(stats);

```

## Design Benefits and Trade-offs

OmniRoute's architecture delivers specific advantages for its use case:

- **Type safety** – Domain modules export TypeScript interfaces that propagate through the application
- **Testability** – Optional `db` parameters enable injection of in-memory SQLite instances for unit tests
- **Developer experience** – 95 focused files are more navigable than monolithic ORM configurations
- **Operational simplicity** – Single-file SQLite deployment eliminates external database dependencies

The trade-off is manual migration management. With 110 migrations accumulated, new contributors must understand lexical ordering conventions. The idempotent design mitigates but does not eliminate this cognitive load.

## Summary

OmniRoute's SQLite database layer demonstrates how a lightweight database engine can support substantial application complexity through disciplined architecture:

- **`getDbInstance()` in [`src/lib/db/core.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/core.ts)** provides a singleton `better-sqlite3` connection with WAL journaling and utility helpers
- **95 domain modules** isolate table-specific operations while maintaining type safety and testability
- **[`migrationRunner.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/migrationRunner.ts) applies 110 idempotent SQL migrations**, tracking versions in `_omniroute_migrations` for reliable schema evolution
- **Optional database parameters** throughout the module layer enable dependency injection without sacrificing convenience

This pattern suits applications requiring embedded databases with complex domain logic, offering a middle path between heavyweight ORMs and ad-hoc query construction.

## Frequently Asked Questions

### How does OmniRoute prevent migration conflicts with 110 schema versions?

OmniRoute prevents conflicts through strict lexical ordering and idempotent SQL patterns. Each migration file in `db/migrations/` uses `IF NOT EXISTS` clauses and `INSERT OR IGNORE` statements, making them safe to re-execute. The [`migrationRunner.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/migrationRunner.ts) module records completed versions in `_omniroute_migrations` and skips already-applied files, ensuring deterministic schema state regardless of startup timing or deployment strategy.

### Can domain modules use different database instances for testing?

Yes. Every domain module function accepts an optional `db` parameter that defaults to `getDbInstance()`. Test suites can pass in-memory SQLite instances created via `new Database(":memory:")` to run isolated tests without filesystem dependencies. This design pattern appears consistently across all 95 modules including [`providers.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/providers.ts) and [`quotaSnapshots.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/quotaSnapshots.ts).

### What happens if a migration fails during application startup?

The [`migrationRunner.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/migrationRunner.ts) wrapper executes each migration inside a transaction (`BEGIN...COMMIT`). If any statement fails, SQLite automatically rolls back the transaction, leaving the database in its pre-migration state. The error propagates to the bootstrap process, preventing the server from starting with a partially migrated schema. Administrators can then inspect the failing SQL file and remediate before retrying.

### Why does OmniRoute use WAL mode for SQLite?

WAL (Write-Ahead Logging) mode in [`src/lib/db/core.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/core.ts) improves read concurrency by allowing readers to proceed without blocking on writers. Since OmniRoute serves multiple concurrent API requests that frequently read quota snapshots and provider configurations while occasionally writing usage statistics, WAL mode reduces lock contention compared to traditional rollback journal mode. This is particularly valuable given the singleton connection pattern shared across 95 domain modules.