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

FreeLLMAPI handles database migrations using an idempotent, sequential migration system in 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

The migration process begins immediately after the database connection opens. The initDb function in server/src/db/index.ts instantiates the better-sqlite3 database, enables foreign-key enforcement, and invokes migrateDbSchema before returning the connection.

// 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.

// 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) 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:

// 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 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:

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.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}