How OpenCode Manages SQLite Database Migrations with Drizzle ORM

OpenCode uses Drizzle ORM with Drizzle Kit to define schemas in TypeScript, generate timestamped migration files, and automatically apply them at runtime through a lazy-loaded database client that supports both development and bundled production modes.

OpenCode is an AI-powered coding assistant that stores application data in a local SQLite file. To manage schema evolution reliably, the project adopts Drizzle ORM for type-safe database access and Drizzle Kit for migration generation. This article examines the complete migration lifecycle in the anomalyco/opencode repository, from schema definition to runtime execution and safety checks.

Schema Definition with Drizzle ORM

All database tables in OpenCode are defined as TypeScript modules using Drizzle's SQLite-specific helpers. These schema files follow the naming convention *.sql.ts and are located throughout the src directory.

In packages/opencode/src/storage/schema.sql.ts, tables are constructed using sqliteTable:

import { sqliteTable, text, integer } from "drizzle-orm/sqlite-core"

export const project = sqliteTable("project", {
  id: integer("id").primaryKey(),
  name: text("name").notNull(),
  createdAt: integer("created_at", { mode: "timestamp" }).notNull()
})

Drizzle Kit discovers these files using a glob pattern defined in the configuration: ./src/**/*.sql.ts.

Migration Generation with Drizzle Kit

OpenCode uses Drizzle Kit to generate incremental migrations based on schema changes. The tool configuration resides in packages/opencode/drizzle.config.ts:

import { defineConfig } from "drizzle-kit"

export default defineConfig({
  dialect: "sqlite",
  schema: "./src/**/*.sql.ts",
  out: "./migration",
  dbCredentials: {
    url: "/home/thdxr/.local/share/opencode/opencode.db"
  }
})

To generate a new migration, developers run:

bun run db generate --name add-project-table

Drizzle Kit creates a timestamped directory under migration/ (e.g., 20240914123000_add-project-table/) containing:

• migration.sql: The raw DDL statements to apply the schema change
• snapshot.json: A complete schema snapshot for drift detection

Runtime Migration Execution

At application startup, OpenCode automatically applies pending migrations through a lazy-initialized database client defined in packages/opencode/src/storage/db.ts.

The Database Client Singleton

The Database.Client() function implements a singleton pattern that initializes the SQLite connection only on first access:

import { Database as BunDatabase } from "bun:sqlite"
import { drizzle } from "drizzle-orm/bun-sqlite"
import { migrate } from "drizzle-orm/bun-sqlite/migrator"

export namespace Database {
  let client: ReturnType<typeof drizzle> | undefined
  
  export function Client() {
    if (!client) {
      const sqlite = new BunDatabase("/home/thdxr/.local/share/opencode/opencode.db")
      client = drizzle({ client: sqlite })
      
      // Apply migrations on initialization
      runMigrations(client)
    }
    return client
  }
}

Migration Loading Strategies

OpenCode supports two modes for loading migration files:

Development Mode: Migrations are read directly from the migration/ directory on disk. The loader parses folder names (format: YYYYMMDDhhmmss_slug) to compute UTC timestamps and sorts them chronologically.

Production Mode: Migrations are baked into the bundle via the global OPENCODE_MIGRATIONS constant, allowing the application to run without access to the source migration directory.

function runMigrations(db: ReturnType<typeof drizzle>) {
  // Check for bundled migrations first
  if (typeof globalThis.OPENCODE_MIGRATIONS !== "undefined") {
    const entries = globalThis.OPENCODE_MIGRATIONS
    await migrate(db, entries)
    console.log(`Applied ${entries.length} migrations (mode: bundled)`)
  } else {
    // Load from disk in development
    const entries = loadMigrationsFromDisk("./migration")
    await migrate(db, entries)
    console.log(`Applied ${entries.length} migrations (mode: dev)`)
  }
}

Migration Safety and Drift Detection

To prevent schema drift in CI/CD pipelines, OpenCode includes a safety check script at packages/opencode/script/check-migrations.ts. This utility runs drizzle-kit check to compare the current schema definition against the applied migration history:

bun run script/check-migrations.ts

If the checker detects any discrepancy between the schema files and the migration snapshots, it exits with a non-zero status, failing the build and preventing deployment of an inconsistent database state.

Summary

• Schema Definition: OpenCode uses *.sql.ts files with Drizzle's sqliteTable helpers, discovered via the glob pattern ./src/**/*.sql.ts.
• Migration Generation: Drizzle Kit generates timestamped migration folders containing migration.sql and snapshot.json based on the drizzle.config.ts configuration.
• Runtime Execution: A lazy singleton Database.Client() initializes the SQLite connection and applies pending migrations using drizzle-orm/bun-sqlite/migrator, supporting both disk-based and bundled migration sources.
• Safety Checks: The script/check-migrations.ts utility prevents schema drift by validating migrations against the current schema definition in CI pipelines.

Frequently Asked Questions

How does OpenCode handle database connections during migration?

OpenCode implements a lazy singleton pattern in packages/opencode/src/storage/db.ts. The Database.Client() function creates the SQLite connection and Drizzle client only on first access, immediately running migrate() to apply any pending migrations before returning the client for use.

Can OpenCode run migrations in production without the migration folder?

Yes. OpenCode supports bundling migrations into the binary via the global OPENCODE_MIGRATIONS constant. In production builds, the migration loader checks for this constant first and uses the embedded SQL entries instead of reading from the migration/ directory on disk.

What command generates a new migration in OpenCode?

Developers run bun run db generate --name <migration-name> to create a new migration. Drizzle Kit reads the current schema from ./src/**/*.sql.ts, compares it against the previous snapshot, and generates a timestamped folder under ./migration/ containing the SQL changes and a new schema snapshot.

How does OpenCode prevent schema drift between code and database?

The repository includes packages/opencode/script/check-migrations.ts, which executes drizzle-kit check to compare the current schema definition against the migration history. This script runs in CI pipelines and exits with a non-zero status if any drift is detected, ensuring the database state always matches the code.

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