# OmniRoute Database Layer Architecture: Modular SQLite with TypeScript

> Discover the modular SQLite database layer architecture in OmniRoute. Learn about its type-safe abstraction, singleton connection, entity CRUD modules, and migration system.

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

---

**OmniRoute implements its database layer as a modular, type-safe SQLite abstraction under `src/lib/db/`, using a singleton connection pattern, entity-specific CRUD modules, and a migration-driven schema evolution system.**

All persistent state in OmniRoute flows through a single SQLite database accessed via a thin TypeScript-based data access layer. This architecture centralizes database operations within `src/lib/db/` while enforcing strict separation between raw SQL and business logic, giving the routing engine a lean yet robust persistence mechanism for providers, API keys, and usage telemetry.

## Core Connection Management: The Singleton Pattern

At the heart of the database layer sits [`src/lib/db/core.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/core.ts), which exports `getDbInstance()` to manage the SQLite connection lifecycle. This function lazily creates—or re-opens—a `better-sqlite3` connection wrapped in a `SqliteAdapter` object, ensuring that every module shares the same database handle without creating multiple connections.

```typescript
import { getDbInstance } from '@/lib/db/core';

// All database access begins here
const db = getDbInstance();
const row = db.prepare('SELECT * FROM providers WHERE id = ?').get(providerId);

```

Every other module in `src/lib/db/` imports this singleton function rather than instantiating connections directly. This design guarantees connection pooling by default and provides a single point of failure for connection debugging or telemetry injection.

## Entity-Module Organization

The database layer follows a strict **module-per-entity** pattern where each logical table lives in its own file. Rather than scattering queries across handlers, OmniRoute co-locates all data access logic for a specific domain within dedicated modules:

- **[`src/lib/db/providers.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/providers.ts)** – CRUD operations for provider discovery and routing configuration
- **[`src/lib/db/apiKeys.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/apiKeys.ts)** – API key storage, usage limits, and policy enforcement  
- **[`src/lib/db/usageLogs.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/usageLogs.ts)** – Per-request analytics and billing telemetry
- **[`src/lib/db/settings.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/settings.ts)** – Global configuration and feature-flag persistence

Each module exposes typed helper functions such as `getProvider()`, `upsertApiKey()`, or `deleteUsageLog()`, hiding raw SQL behind strongly-typed interfaces. These modules also export companion TypeScript interfaces like `ProviderRow` and `ApiKeyRow` that mirror the database schema, providing compile-time safety for callers throughout the application.

```typescript
import { getDbInstance } from '@/lib/db/core';
import { ProviderRow } from '@/lib/db/providers';

export async function getProvider(id: string): Promise<ProviderRow | null> {
  const db = getDbInstance();
  return db.prepare('SELECT * FROM providers WHERE id = ?')
           .get(id) as ProviderRow | null;
}

```

## Schema Evolution and Migrations

Database schema versioning lives in [`src/lib/db/migrationRunner.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/migrationRunner.ts), which executes sequential SQL migrations located in `src/lib/db/migrations/`. The runner ensures the database version matches the codebase on every startup, preventing drift between deployed application code and the underlying schema.

```typescript
import { runMigrations } from '@/lib/db/migrationRunner';

// Called during application initialization
await runMigrations(); // Ensures schema is up-to-date

```

Each migration file follows the incremental `.sql` naming convention and contains atomic DDL statements. This approach allows OmniRoute to evolve its database layer across releases without downtime or manual intervention, critical for maintaining state consistency in production routing clusters.

## Type Safety and Utility Patterns

Beyond basic CRUD, the database layer centralizes common data patterns in utility modules. Helpers like `rowToCamel` convert snake_case column names to camelCase JavaScript properties, while `TTLCache` implementations optimize repeated lookups for configuration data. JSON column handling and soft-delete logic are also standardized here, ensuring that modules like [`src/lib/db/apiKeys.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/apiKeys.ts) remain focused on domain logic rather than serialization boilerplate.

Type definitions such as `ApiKeyRow` and `ProviderRow` are defined alongside their respective modules, creating a tight coupling between the SQL schema and TypeScript contracts. This eliminates the need for a heavy ORM while maintaining strict compile-time verification of database interactions.

## Isolation of Business Logic from Persistence

A strict architectural constraint governs the database layer: **handlers, services, and API routes never issue raw SQL**. Instead, they exclusively consume the exported functions from the `src/lib/db/` modules. This guarantees a single point of change for schema updates—when the `providers` table structure changes, only [`src/lib/db/providers.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/providers.ts) requires modification, leaving the routing logic untouched.

Feature-flag-driven tables in files like [`src/lib/db/featureFlags.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/featureFlags.ts) and [`src/lib/db/piiSanitizer.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/piiSanitizer.ts) extend this isolation further. These tables are only accessed when corresponding feature flags are enabled, keeping the core schema lean and ensuring that experimental features do not impact baseline database performance.

```typescript
import { upsertApiKey } from '@/lib/db/apiKeys';

// Business logic calls typed functions, never raw SQL
await upsertApiKey({
  id: 'key-123',
  name: 'User CLI',
  hashedSecret: hash('super-secret'),
  scopes: ['chat:write'],
});

```

## Summary

- **Singleton connection**: `getDbInstance()` in [`src/lib/db/core.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/core.ts) manages a single `better-sqlite3` adapter shared across the application
- **Modular entities**: Each table (providers, API keys, usage logs) has its own module with typed CRUD helpers under `src/lib/db/`
- **Migration safety**: [`migrationRunner.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/migrationRunner.ts) automates schema evolution using versioned `.sql` files in `src/lib/db/migrations/`
- **Type safety**: Row types like `ProviderRow` co-located with modules provide compile-time guarantees without ORM overhead
- **Clean architecture**: Business logic remains completely isolated from SQL implementation details

## Frequently Asked Questions

### Does OmniRoute use an ORM for its database layer?

No. OmniRoute intentionally avoids heavy ORM frameworks in favor of a thin data-access layer. The codebase uses `better-sqlite3` directly through prepared statements wrapped in typed helper functions, providing the performance benefits of raw SQL with the type safety of TypeScript interfaces.

### How does OmniRoute handle database schema changes?

Schema changes are managed through sequential SQL migrations stored in `src/lib/db/migrations/`. The [`migrationRunner.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/migrationRunner.ts) script executes these files on startup, comparing the current database version against the codebase expectations and applying pending migrations atomically.

### Where are TypeScript types for database rows defined?

Row types like `ApiKeyRow`, `ProviderRow`, and `UsageLogRow` are defined alongside their respective database modules in `src/lib/db/`. For example, `ProviderRow` lives in [`src/lib/db/providers.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/providers.ts), ensuring tight coupling between the SQL schema and TypeScript contracts.

### Can OmniRoute run with a different database engine like PostgreSQL?

Currently, no. The database layer is tightly coupled to SQLite through `better-sqlite3` and the `SqliteAdapter` in [`core.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/core.ts). While the modular structure abstracts specific SQL dialects within the CRUD helpers, the connection management and migration system assume SQLite file-based semantics throughout the codebase.