# How OmniRoute Uses better-sqlite3 for Database Management: Domain Modules and Migration Process

> Discover how OmniRoute leverages better-sqlite3 for robust database management. Explore its domain modules and atomic migration process for efficient state persistence.

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

---

**OmniRoute persists all application state—including provider configurations, combo definitions, usage logs, and guard-rails—in a single SQLite file accessed through the high-performance `better-sqlite3` driver, implementing a singleton pattern with automatic failover to [`sql.js`](https://github.com/diegosouzapw/OmniRoute/blob/main/sql.js) and atomic migrations.**

The database architecture in OmniRoute centers on synchronous I/O performance while maintaining compatibility across diverse deployment environments. According to the OmniRoute source code, the system avoids connection leaks during Next.js hot-module-replacement cycles by storing the database instance in a global variable, and it safeguards data integrity through WAL-mode pragmas and versioned migrations.

## Singleton Database Instance and Driver Management

OmniRoute eliminates connection overhead and prevents memory leaks by treating the database as an application-wide singleton rather than creating connections per request.

### Global Instance Pattern

In [`src/lib/db/core.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/core.ts), the function `getDbInstance()` manages access to a single `better-sqlite3` connection stored in `globalThis.__omnirouteDb`. On first invocation, the function initializes the connection, executes the base schema, and runs pending migrations. Subsequent calls return the cached instance, ensuring that every domain module shares the same underlying connection across the process lifecycle.

```typescript
// src/lib/db/core.ts
import { getDbInstance } from "@/lib/db/core";

export function getProviderConnection(id: string) {
  const db = getDbInstance();  // Returns the singleton better-sqlite3 instance
  const stmt = db.prepare(
    `SELECT * FROM provider_connections WHERE id = ?`
  );
  const row = stmt.get(id);
  return row ? rowToCamel(row) : null;
}

```

### Native Driver Detection with JavaScript Fallback

The `tryOpenSync()` function in [`src/lib/db/adapters/driverFactory.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/adapters/driverFactory.ts) attempts to open the SQLite file using the native `better-sqlite3` binary. If the binary is missing or the ABI is incompatible—common in restricted environments or during cross-platform builds—the system automatically falls back to the pure-JavaScript [`sql.js`](https://github.com/diegosouzapw/OmniRoute/blob/main/sql.js) (Wasm) driver.

The async helper `ensureDbInitialized()` pre-loads the fallback driver during application startup, guaranteeing that the first synchronous database call never blocks on binary initialization.

```typescript
// Conceptual usage from driverFactory.ts
const db = tryOpenSync(sqliteFilePath) || await getSqlJsAdapter();

```

## Performance Configuration and WAL Mode

OmniRoute optimizes SQLite for concurrent read-write workloads typical of API routing applications.

### Write-Ahead Logging and Timeouts

Immediately after opening the connection, [`src/lib/db/core.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/core.ts) configures the database with performance-critical pragmas:

- **`journal_mode = WAL`**: Enables Write-Ahead Logging for improved concurrency and reduced write latency
- **`busy_timeout = 2000`**: Sets a 2-second timeout to prevent event-loop stalls under contention
- **`synchronous = NORMAL`**: Balances durability with performance

These settings ensure that the synchronous `better-sqlite3` API remains non-blocking for typical web request durations while maintaining ACID compliance.

## Domain Module Architecture

Each feature area interacts with the database through specialized domain modules that import the singleton and prepare statements for reuse.

### Prepared Statement Pattern

Domain modules such as [`src/lib/db/providers.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/providers.ts), [`combos.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/combos.ts), and [`usageHistory.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/usageHistory.ts) import `getDbInstance()` and cache prepared statements. Because `better-sqlite3` operates synchronously, these modules execute reads and writes without async/await overhead.

```typescript
// src/lib/db/combos.ts (representative domain module)
import { getDbInstance } from "@/lib/db/core";

export function insertCombo(name: string, data: string) {
  const db = getDbInstance();
  const stmt = db.prepare(
    `INSERT INTO combos (id, name, data, created_at, updated_at)
     VALUES (lower(hex(randomblob(16))), ?, ?, datetime('now'), datetime('now'))`
  );
  stmt.run(name, data);
}

```

This pattern centralizes SQL logic within domain boundaries while maintaining the performance benefits of the native driver.

## Schema Evolution and Migration System

OmniRoute separates initial schema creation from incremental changes through a two-phase initialization process.

### Base Schema Definition

The `SCHEMA_SQL` constant in [`src/lib/db/core.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/core.ts) contains the foundational `CREATE TABLE` statements for core entities including `provider_connections`, `combos`, `usage_history`, and `call_logs`. This script executes every time the database opens, ensuring that new installations start with the complete base structure.

### Versioned Migration Runner

The `runMigrations()` function in [`src/lib/db/migrationRunner.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/migrationRunner.ts) manages schema evolution through numbered `.sql` files stored in `db/migrations/`. During startup, the runner:

1. Queries the `_omniroute_migrations` tracking table to identify applied migrations
2. Creates a pre-migration backup using `VACUUM INTO` with a timestamped filename
3. Executes pending migration files inside individual transactions
4. Aborts if an unexpectedly large number of pending migrations are detected (indicating potential data loss)

```typescript
// Manual migration trigger (rarely needed)
import { getDbInstance } from "@/lib/db/core";
import { runMigrations } from "@/lib/db/migrationRunner";

const db = getDbInstance();
runMigrations(db, { isNewDb: false });

```

### Corruption Recovery

If `getDbInstance()` detects a corrupted database file or lock contention failure, the system automatically renames the problematic file to `*.probe-failed-<timestamp>`, attempts to capture a snapshot of critical tables, and restores from the most recent valid backup. If automatic restoration fails, the application throws a descriptive error directing operators to manual recovery procedures.

## Summary

- **Singleton Pattern**: `globalThis.__omnirouteDb` stores a single `better-sqlite3` connection managed by `getDbInstance()` in [`src/lib/db/core.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/core.ts), preventing connection leaks across Next.js HMR cycles.
- **Driver Flexibility**: `tryOpenSync()` in [`src/lib/db/adapters/driverFactory.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/adapters/driverFactory.ts) provides native performance with automatic fallback to [`sql.js`](https://github.com/diegosouzapw/OmniRoute/blob/main/sql.js) Wasm when binaries are unavailable.
- **Domain Modules**: Feature-specific files like [`providers.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/providers.ts) and [`combos.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/combos.ts) import the singleton and cache prepared statements for synchronous, high-performance queries.
- **Migration Safety**: [`src/lib/db/migrationRunner.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/migrationRunner.ts) applies numbered `.sql` migrations transactionally, creates pre-migration backups via `VACUUM INTO`, and tracks versions in `_omniroute_migrations`.
- **Performance Tuning**: WAL mode with 2-second `busy_timeout` and `synchronous = NORMAL` pragmas optimize the database for concurrent API workloads.

## Frequently Asked Questions

### How does OmniRoute handle database connections during Next.js hot reloading?

OmniRoute stores the database instance in `globalThis.__omnirouteDb` rather than module-level variables. During Next.js hot-module-replacement cycles, module scope resets but the global object persists, allowing `getDbInstance()` to return the existing connection instead of creating duplicates that would leak memory or lock the database file.

### What happens if the better-sqlite3 binary is not available in the deployment environment?

The `tryOpenSync()` function in [`src/lib/db/adapters/driverFactory.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/adapters/driverFactory.ts) catches binary loading failures and returns null, triggering fallback to [`sql.js`](https://github.com/diegosouzapw/OmniRoute/blob/main/sql.js). The `ensureDbInitialized()` async helper pre-initializes the Wasm driver during application startup, ensuring that synchronous database operations proceed without blocking when the native binary is unavailable.

### How does OmniRoute prevent data loss during schema migrations?

Before applying any pending migrations from `db/migrations/`, the `runMigrations()` function creates a backup using `VACUUM INTO` with a timestamped filename. It also validates the migration count against the `_omniroute_migrations` table, aborting if the gap suggests a corrupted tracking table. Each migration runs inside its own transaction, ensuring atomicity.

### Can domain modules use async/await with better-sqlite3?

No, `better-sqlite3` operates synchronously, and OmniRoute domain modules leverage this for deterministic performance. Modules import `getDbInstance()` and use `db.prepare()` to create cached statements that execute immediately with `.get()` or `.run()`, eliminating the overhead of promise chains for database operations.