# How FreeLLMAPI Handles Database Migrations with better-sqlite3 for Schema Updates

> FreeLLMAPI uses better-sqlite3 for efficient, idempotent database migrations ensuring schema consistency. Learn how FreeLLMAPI handles schema updates on server startup.

- Repository: [Tashfeen/freellmapi](https://github.com/tashfeenahmed/freellmapi)
- Tags: how-to-guide
- Published: 2026-06-25

---

**FreeLLMAPI handles database migrations using an idempotent, sequential migration system in [`server/src/db/migrations.ts`](https://github.com/tashfeenahmed/freellmapi/blob/main/server/src/db/migrations.ts) that runs synchronous SQL statements via better-sqlite3 during server startup to ensure schema consistency across versions.**

FreeLLMAPI is an open-source LLM proxy server that persists runtime state—models, API keys, request logs, and rate-limit counters—to a local SQLite database. According to the source code in the `tashfeenahmed/freellmapi` repository, the application uses **better-sqlite3** to execute database migrations synchronously before the server accepts any connections, guaranteeing that schema updates are atomic and safe for long-running desktop installations.

## The Migration Entry Point

### Database Initialization in [`server/src/db/index.ts`](https://github.com/tashfeenahmed/freellmapi/blob/main/server/src/db/index.ts)

The migration process begins immediately after the database connection opens. The `initDb` function in [`server/src/db/index.ts`](https://github.com/tashfeenahmed/freellmapi/blob/main/server/src/db/index.ts) instantiates the **better-sqlite3** database, enables foreign-key enforcement, and invokes `migrateDbSchema` before returning the connection.

```typescript
// server/src/db/index.ts
export function initDb(dbPath?: string): Database.Database {
  // ...
  db = new Database(resolvedPath);
  db.pragma('foreign_keys = ON');
  migrateDbSchema(db);          // ← runs all schema-and-data migrations
  // ...
}

```

This design ensures that every database instance—whether a fresh file or an existing `data/freeapi.db`—is migrated to the current schema version before the server processes API requests.

## Idempotent Incremental Migration Strategy

The `migrateDbSchema` function acts as a single entry point that executes a series of specialized functions in strict order. Each function is designed to be **idempotent**, checking the current schema state before attempting modifications.

### Schema Creation and Validation

The `createTables` function issues a comprehensive `CREATE TABLE IF NOT EXISTS` block that defines the initial schema for models, API keys, requests, and rate-limit counters. It also creates indexes and delegates to helper functions that verify columns exist on older database versions.

```typescript
// From migrations.ts - ensures tables exist without error
function createTables(db: Database.Database) {
  db.prepare(`
    CREATE TABLE IF NOT EXISTS models (
      id TEXT PRIMARY KEY,
      platform TEXT NOT NULL,
      -- ...
    )
  `).run();
  // ... additional table creation
}

```

### Data Seeding and Encryption Setup

Immediately after schema creation, two critical initialization steps occur:

- **`initEncryptionKey`** generates or loads the AES-256-GCM key used to encrypt API keys at rest.
- **`seedModels`** populates the initial model catalog using `INSERT OR IGNORE`, ensuring that new free-tier models appear automatically while preserving existing user data.

### Versioned Migration Chains

Schema evolution is handled through a chain of idempotent migration functions (V1 through V25). These functions perform three types of operations:

- **Schema changes**: Adding columns via `ALTER TABLE`, such as `ensureRequestRequestedModelColumn` which adds `requested_model` to the requests table only when missing.
- **Data fixes**: Updating model IDs, correcting stale rate limits, or disabling deprecated free-tier models.
- **Insertions**: Adding newly available models with `INSERT OR IGNORE`.

Each migration checks `PRAGMA table_info` before altering structure, preventing failures on repeated runs.

### Auxiliary Migrations

Final housekeeping steps include `migrateEmbeddingsV1`, `migrateMediaV1`, `ensureUnifiedKey`, and `migrateProfilesInit`. These create auxiliary tables, add columns for unified API key support, and seed the default user profile. After all migrations complete, `applyModelPricing` (imported from [`model-pricing.ts`](https://github.com/tashfeenahmed/freellmapi/blob/main/model-pricing.ts)) writes pricing rows that drive the "estimated savings" analytics.

## Why better-sqlite3 Powers the Migration System

The choice of **better-sqlite3** over async alternatives is deliberate and specific to the migration architecture:

### Synchronous Execution During Startup

Migrations run on the main thread during server initialization. The synchronous API eliminates callback complexity and ensures that the database is fully ready before the HTTP server binds to its port.

### Prepared Statements and Transactions

The code leverages `db.prepare(sql).run()` for compiled statement reuse, particularly during batch operations. Large seeding operations are wrapped in `db.transaction(() => { … })` blocks, guaranteeing atomicity and preventing partial state if the process terminates unexpectedly.

## Step-by-Step Upgrade Flow

When a user upgrades FreeLLMAPI, the following sequence executes automatically:

1. **Server startup** triggers `initDb`, opening `data/freeapi.db` or creating it if absent.
2. **Foreign keys are enabled** via `db.pragma`.
3. **`migrateDbSchema` executes** sequentially:
   - Checks for table existence before creation.
   - Alters tables only when `PRAGMA table_info` confirms columns are missing.
   - Updates model configurations (e.g., changing Google Gemini rate limits from 250 to 20 requests per day).
   - Inserts new free-tier models with `INSERT OR IGNORE`.
4. **Pricing data is refreshed** via `applyModelPricing`.
5. **Server accepts connections** with a fully migrated schema.

This process occurs before any API request is processed, ensuring the schema is always current.

## Adding Custom Schema Migrations

To extend the schema, add a new migration function that checks column existence before altering:

```typescript
// In server/src/db/migrations.ts
function ensureApiKeysLastUsedColumn(db: Database.Database) {
  const cols = db.prepare('PRAGMA table_info(api_keys)').all() as { name: string }[];
  if (!cols.some(c => c.name === 'last_used_at')) {
    db.prepare('ALTER TABLE api_keys ADD COLUMN last_used_at TEXT')
      .run();
  }
}

// Register it in migrateDbSchema()
export function migrateDbSchema(db: Database.Database) {
  createTables(db);
  initEncryptionKey(db);
  // ... existing migrations
  ensureApiKeysLastUsedColumn(db);   // ← new step
  applyModelPricing(db);
}

```

## Summary

- **Migration entry point**: `migrateDbSchema` in [`server/src/db/migrations.ts`](https://github.com/tashfeenahmed/freellmapi/blob/main/server/src/db/migrations.ts) is called automatically by `initDb` before server startup.
- **Idempotent design**: Every migration uses `CREATE ... IF NOT EXISTS`, `ALTER TABLE ... ADD COLUMN` with existence checks, and `INSERT OR IGNORE` to ensure safe re-runs.
- **Synchronous execution**: better-sqlite3's synchronous API allows migrations to complete on the main thread without async complexity.
- **Atomic batches**: Large data seeds use `db.transaction()` to ensure atomic commits.
- **Versioned chains**: Schema changes are organized into numbered functions (V1-V25) that handle structural changes, data corrections, and new model insertions.

## Frequently Asked Questions

### How does FreeLLMAPI ensure migrations don't corrupt existing data?

Each migration function queries the current schema using `PRAGMA table_info` before executing `ALTER TABLE` statements. Combined with `CREATE TABLE IF NOT EXISTS` and `INSERT OR IGNORE`, the system ensures that running `migrateDbSchema` on an already-migrated database is a safe no-op that never duplicates data or drops existing columns.

### Can I run migrations manually without starting the full server?

Yes. You can import `migrateDbSchema` directly and apply it to any better-sqlite3 instance. This is useful for testing or CLI tooling:

```typescript
import { getDb } from './server/src/db/index.js';
import { migrateDbSchema } from './server/src/db/migrations.js';

const db = getDb();          // assumes initDb() already called
migrateDbSchema(db);         // applies all pending migrations

```

### Why did the developers choose better-sqlite3 over async SQLite libraries?

better-sqlite3 provides a synchronous API that aligns with the server's startup requirements. Because migrations must complete before the HTTP server begins accepting requests, the blocking nature of better-sqlite3 simplifies error handling and eliminates the need for async/await boilerplate during the critical initialization phase. Additionally, its prepared statement caching improves performance for batch insert operations during seeding.

### How are new provider models added through migrations?

New free-tier models are introduced via versioned migration functions that execute `INSERT OR IGNORE` statements into the `models` table. These functions also update existing rows to reflect changed rate limits (e.g., adjusting requests-per-day values) using standard `UPDATE` statements, ensuring that existing installations automatically receive catalog updates without manual intervention.