# How OmniRoute Handles Data Persistence for Its Services: SQLite Architecture Explained

> Explore OmniRoute's data persistence strategy. Learn how it uses SQLite with better-sqlite3 for encrypted storage, automatic migrations, and domain-specific modules.

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

---

**OmniRoute persists all runtime state and configuration in a single SQLite database using the `better-sqlite3` driver, with a modular architecture that supports encrypted storage, automatic migrations, and domain-specific data modules.**

OmniRoute implements a lightweight, self-contained persistence layer that eliminates the need for external database servers. According to the diegosouzapw/OmniRoute source code, the application stores all service data—including provider configurations, routing combos, and quota pools—in a local SQLite file managed through a singleton connection pattern.

## Core Database Connection

The persistence layer centers on [`src/lib/db/core.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/core.ts), which exports a **`getDbInstance()`** singleton function that creates and configures a single `better-sqlite3` connection for the entire Next.js process.

This module configures several critical settings:
- **WAL journaling mode** for concurrent read/write performance
- **Optional encryption** support for sensitive data at rest
- **`rowToCamel` helper** that automatically maps snake_case database columns to camelCase JavaScript objects used throughout the codebase

The database file location is determined by the **`DATA_DIR`** environment variable, defaulting to `~/.omniroute/`. On first launch, the system creates the SQLite file automatically; subsequent restarts open the existing file, preserving all service data across application lifecycles.

## Schema Management and Migrations

OmniRoute handles structural changes through a versioned migration system. The initial schema definition lives as the **`SCHEMA_SQL`** constant within [`src/lib/db/core.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/core.ts), while incremental changes are stored as numbered SQL files in the `db/migrations/` directory (for example, [`001_initial_schema.sql`](https://github.com/diegosouzapw/OmniRoute/blob/main/001_initial_schema.sql) and [`115_bifrost_service.sql`](https://github.com/diegosouzapw/OmniRoute/blob/main/115_bifrost_service.sql)).

The **`runMigrations()`** function in [`src/lib/db/migrationRunner.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/migrationRunner.ts) executes pending migrations within a transaction, tracking completed migrations in a hidden **`_omniroute_migrations`** table to prevent duplicate application.

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

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

```

## Domain-Specific Data Modules

Rather than direct SQL queries scattered throughout the codebase, OmniRoute organizes data access into focused modules that wrap the singleton database instance.

### Provider Management

The [`src/lib/db/providers.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/providers.ts) module handles LLM provider registrations, exposing functions like **`insertProvider()`** to persist API endpoints and authentication headers.

### Combo Routing

Combo definitions and routing metadata are managed by [`src/lib/db/combo.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/combo.ts), which provides **`getComboById()`** and **`insertCombo()`** functions to retrieve and store routing configurations.

### Quota Pools

Per-model usage limits are tracked in [`src/lib/db/quotaPools.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/quotaPools.ts), offering **`getQuotaPool()`** and related helpers to monitor consumption across different AI models.

### Compression Settings

Prompt-compression configurations reside in [`src/lib/db/compressionCombos.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/compressionCombos.ts), maintaining specialized settings for reducing token usage.

### Centralized Exports

All domain modules are re-exported through [`src/lib/db/localDb.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/localDb.ts), providing a single import point for application code while maintaining clean separation of concerns.

## Security and Maintenance Utilities

Beyond basic CRUD operations, OmniRoute includes specialized utilities for data protection and maintenance.

### Encryption Layer

The **[`src/lib/db/encryption.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/encryption.ts)** module encrypts sensitive columns—such as API keys and authentication tokens—before writing to the database, ensuring secrets remain protected even if the database file is accessed directly.

### Backup Routines

**[`src/lib/db/backup.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/backup.ts)** implements routines to dump the entire database state, enabling administrators to create recovery points without external tooling.

### Database Optimization

The **[`src/lib/db/vacuumScheduler.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/vacuumScheduler.ts)** module runs periodic `VACUUM` operations to reclaim storage space and optimize query performance, preventing database bloat over time.

## Configuration and Storage Location

The persistence layer requires no external server configuration. By setting the **`DATA_DIR`** environment variable, administrators can relocate the SQLite file to custom paths (such as mounted volumes or network storage). The default location uses the user's home directory at `~/.omniroute/`, making the installation portable and easy to inspect.

## Working with the Database

All request-handling code ultimately calls one of the DB helpers to read or write state. Because the connection is a singleton, the entire Next.js process shares the same persistent store.

Inserting a new provider record:

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

const db = getDbInstance();
insertProvider(db, {
  providerId: 'openai',
  name: 'OpenAI',
  authHeader: 'Bearer <YOUR_KEY>',
  configJson: JSON.stringify({ baseUrl: 'https://api.openai.com/v1' }),
});

```

Fetching a combo definition:

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

const db = getDbInstance();
const combo = getComboById(db, 'my-combo-123');
console.log('Combo targets:', combo?.targets);

```

## Summary

- OmniRoute uses a **single SQLite database** accessed via `better-sqlite3` in [`src/lib/db/core.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/core.ts), configured with WAL mode and optional encryption.
- Schema changes are managed through **incremental SQL migrations** tracked in [`src/lib/db/migrationRunner.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/migrationRunner.ts) and the `_omniroute_migrations` table.
- **Domain-specific modules** (providers, combos, quota pools, compression) provide type-safe wrappers around the database connection.
- Sensitive data is protected via **[`src/lib/db/encryption.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/encryption.ts)**, while **[`src/lib/db/backup.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/backup.ts)** and **[`src/lib/db/vacuumScheduler.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/vacuumScheduler.ts)** handle maintenance.
- The **singleton connection pattern** ensures all parts of the Next.js application share the same persistent state without external database dependencies.

## Frequently Asked Questions

### Where does OmniRoute store its data by default?

OmniRoute stores all data in a single SQLite file located at `~/.omniroute/` by default. This location can be customized by setting the `DATA_DIR` environment variable before starting the application, allowing you to place the database on network storage or persistent volumes in containerized environments.

### How does OmniRoute handle database schema updates?

Schema updates are handled automatically through the migration system in [`src/lib/db/migrationRunner.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/migrationRunner.ts). When the application starts, it checks the `_omniroute_migrations` table and applies any pending SQL files from the `db/migrations/` directory within a transaction, ensuring atomic schema changes without manual intervention.

### Can OmniRoute encrypt sensitive data like API keys?

Yes. The [`src/lib/db/encryption.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/encryption.ts) module encrypts sensitive fields—such as API keys and authentication tokens—before they are written to the SQLite database. This ensures that even if the database file is compromised, the encrypted secrets remain protected and require the application's encryption key to decrypt.

### What happens if the database file is deleted or corrupted?

If the database file is deleted, OmniRoute will create a new empty database on the next startup, applying the initial schema from `SCHEMA_SQL` and any migration files. However, all runtime state, provider configurations, and quota pools will be lost unless restored from a backup created via [`src/lib/db/backup.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/backup.ts). Regular backups are recommended for production deployments.