# How OpenCode Manages SQLite Database Migrations with Drizzle ORM

> Discover how OpenCode manages SQLite database migrations using Drizzle ORM and Drizzle Kit. Learn to define schemas in TypeScript, generate migrations, and apply them at runtime.

- Repository: [Anomaly/opencode](https://github.com/anomalyco/opencode)
- Tags: how-to-guide
- Published: 2026-02-16

---

**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`](https://github.com/anomalyco/opencode/blob/main/packages/opencode/src/storage/schema.sql.ts), tables are constructed using `sqliteTable`:

```typescript
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`](https://github.com/anomalyco/opencode/blob/main/packages/opencode/drizzle.config.ts):

```typescript
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:

```bash
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`](https://github.com/anomalyco/opencode/blob/main/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:

```typescript
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.

```typescript
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`](https://github.com/anomalyco/opencode/blob/main/packages/opencode/script/check-migrations.ts). This utility runs `drizzle-kit check` to compare the current schema definition against the applied migration history:

```bash
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`](https://github.com/anomalyco/opencode/blob/main/migration.sql) and [`snapshot.json`](https://github.com/anomalyco/opencode/blob/main/snapshot.json) based on the [`drizzle.config.ts`](https://github.com/anomalyco/opencode/blob/main/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`](https://github.com/anomalyco/opencode/blob/main/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`](https://github.com/anomalyco/opencode/blob/main/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`](https://github.com/anomalyco/opencode/blob/main/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.