Database Migrations Guide

Step 1: Generate Migrations

bash
1bun run db:generate

This generates:

• packages/database/migrations/0046_meaningless_file_name.sql

And updates:

• packages/database/migrations/meta/_journal.json
• packages/database/src/core/migrations.json
• docs/development/database-schema.dbml

Custom Migrations (e.g. CREATE EXTENSION)

For migrations that don't involve Drizzle schema changes (e.g. enabling PostgreSQL extensions), use the --custom flag:

bash
1bunx drizzle-kit generate --custom --name=enable_pg_search

This generates an empty SQL file and properly updates _journal.json and snapshot. Then edit the generated SQL file to add your custom SQL:

sql
1-- Custom SQL migration file, put your code below! --
2CREATE EXTENSION IF NOT EXISTS pg_search;

Do NOT manually create migration files or edit _journal.json — always use drizzle-kit generate to ensure correct journal entries and snapshots.

Step 2: Optimize Migration SQL Filename

Rename auto-generated filename to be meaningful:

0046_meaningless_file_name.sql → 0046_user_add_avatar_column.sql

Step 3: Use Idempotent Clauses (Defensive Programming)

Always use defensive clauses to make migrations idempotent (safe to re-run):

CREATE TABLE

sql
1-- ✅ Good
2CREATE TABLE IF NOT EXISTS "agent_eval_runs" (
3  "id" text PRIMARY KEY NOT NULL,
4  "name" text,
5  "created_at" timestamp with time zone DEFAULT now() NOT NULL
6);
7
8-- ❌ Bad
9CREATE TABLE "agent_eval_runs" (...);

ALTER TABLE - Columns

sql
1-- ✅ Good
2ALTER TABLE "users" ADD COLUMN IF NOT EXISTS "avatar" text;
3ALTER TABLE "posts" DROP COLUMN IF EXISTS "deprecated_field";
4
5-- ❌ Bad
6ALTER TABLE "users" ADD COLUMN "avatar" text;

ALTER TABLE - Foreign Key Constraints

PostgreSQL has no ADD CONSTRAINT IF NOT EXISTS. Use DROP IF EXISTS + ADD:

sql
1-- ✅ Good: Drop first, then add (idempotent)
2ALTER TABLE "agent_eval_datasets" DROP CONSTRAINT IF EXISTS "agent_eval_datasets_user_id_users_id_fk";
3ALTER TABLE "agent_eval_datasets" ADD CONSTRAINT "agent_eval_datasets_user_id_users_id_fk"
4  FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;
5
6-- ❌ Bad: Will fail if constraint already exists
7ALTER TABLE "agent_eval_datasets" ADD CONSTRAINT "agent_eval_datasets_user_id_users_id_fk"
8  FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;

DROP TABLE / INDEX

sql
1-- ✅ Good
2DROP TABLE IF EXISTS "old_table";
3CREATE INDEX IF NOT EXISTS "users_email_idx" ON "users" ("email");
4CREATE UNIQUE INDEX IF NOT EXISTS "users_email_unique" ON "users" USING btree ("email");
5
6-- ❌ Bad
7DROP TABLE "old_table";
8CREATE INDEX "users_email_idx" ON "users" ("email");

Step 4: Regenerate Client After SQL Edits

After modifying the generated SQL (e.g., adding IF NOT EXISTS), regenerate the client:

bash
1bun run db:generate:client