Database Migrations

Guide to managing database schema changes using Drizzle ORM, including generating, reviewing, and applying migrations in development and production environments.

Database Migrations

This document explains the workflow for managing database schema changes in the OpenWiki system using Drizzle ORM.

Migration Setup

The system uses Drizzle Kit for migration management. Configuration is defined in /packages/db/drizzle.config.ts:

import { defineConfig } from "drizzle-kit";
import "varlock/auto-load";

export default defineConfig({
  dbCredentials: {
    url: process.env.DATABASE_URL || "",
  },
  dialect: "postgresql",
  out: "./src/migrations",
  schema: "./src/schema/index.ts",
  schemaFilter: ["public"],
});

Key settings:

dialect: PostgreSQL
out: Migration output directory (./src/migrations)
schema: Path to the schema definition (./src/schema/index.ts)
schemaFilter: Limits migrations to the public schema

Schema Definition

Database schemas are defined in TypeScript files under /packages/db/src/schema/. The main entry point is /packages/db/src/schema/index.ts, which re-exports all schema definitions.

Migration Workflow

1. Generate a Migration

After modifying the schema, generate a migration script:

bun run db:generate

This command:

Compares the current schema with the previous state
Generates a new SQL migration file in /packages/db/src/migrations/
Names the file with a timestamp and description (e.g., 20240101000000_add_user_table.ts)

2. Review the Migration

Inspect the generated migration file to ensure it accurately reflects the intended schema changes. The file contains:

up function: SQL statements to apply the migration
down function: SQL statements to rollback the migration (if supported)

3. Apply the Migration Locally

Run the migration against the local development database:

bun run db:migrate

This executes all pending migrations in order, updating the database schema and recording the migration in the _migrations table.

4. Apply the Migration in Production

The same migration command is used in production environments:

bun run db:migrate

Ensure the DATABASE_URL environment variable points to the production database before running.

Alternative: db:push

For rapid prototyping, the system supports pushing schema changes directly without generating migration files:

bun run db:push

Warning: This method bypasses versioned migrations and is not recommended for production as it does not create reproducible, auditable schema change records.

Database Studio

Inspect and interact with the database using Drizzle Studio:

bun run db:studio

This provides a web UI for viewing and editing data, useful during development.

Best Practices

Always generate migrations for production schema changes to maintain a versioned history.
Review generated migrations before applying, especially for complex changes like column renames or data migrations.
Test migrations on a copy of production data before deploying.
Keep migrations immutable once applied; never modify an existing migration file.
Use the down function for rollbacks during development, but note that production rollbacks should be handled with forward-migration fixes when possible.
Coordinate schema changes with code deployments to ensure compatibility.

Troubleshooting

Migration Fails to Apply

Check the error message for SQL syntax issues or constraint violations.
Verify the database connection string is correct.
Ensure no other processes are holding locks on the database.

Schema Drift

If the database schema diverges from the migration history:

Use db:push to synchronize (in development only).
Or, manually adjust the schema and generate a new migration to capture the difference.

Missing Migration Table

If the _migrations table is missing, run db:migrate to create it and apply all pending migrations.