Database Migration Skill

Create schema changes with proper migrations.

CRITICAL: Never Write Migrations By Hand

ALWAYS use drizzle-kit generate to create migrations. NEVER write SQL migration files manually.

Why this matters:

• Drizzle tracks schema state via snapshot files in drizzle/meta/
• Hand-written migrations don't update snapshots
• This causes drizzle-kit generate to fail with confusing prompts about tables being "created or renamed"
• It breaks the entire migration system for future changes

If you're tempted to write SQL by hand (e.g., for data migrations), create a separate script in scripts/ instead.

Before Starting

1. Read current schema: src/lib/schema.ts
2. Read recent migrations: drizzle/*.sql (last 2-3)
3. Understand current state before making changes

Workflow

Step 1: Modify Schema First

Edit src/lib/schema.ts with your changes:

• Add new tables with pgTable()
• Add columns to existing tables
• Add/modify indexes

Step 2: Generate Migration

Run Drizzle Kit to generate migration SQL:

pnpm drizzle-kit generate

This creates a new file in drizzle/ like 0011_descriptive_name.sql.

Step 3: Review Generated Migration

Read the generated migration and verify:

• SQL looks correct
• No destructive changes (DROP without intent)
• Index names follow convention
• Column types match schema.ts

Step 4: Test Locally (Optional - Be Careful!)

WARNING: Only test migrations locally if POSTGRES_URL points to a LOCAL database. If it points to production, skip this step - migrations will run automatically on Vercel deploy.

To check your database URL:

echo $POSTGRES_URL  # Should be localhost or local container

If local database is configured:

pnpm build  # Runs migrations automatically
pnpm cli stats  # Verify queries work

Step 5: Verify in PR

• Migration SQL looks correct (review diff)
• schema.ts and migration are in same commit
• No IF NOT EXISTS clauses (signals potential drift)

Common Patterns

Adding a Column

// schema.ts
export const myTable = pgTable('my_table', {
  // existing columns...
  newColumn: varchar('new_column', { length: 255 }),
});

Adding an Index

export const myTable = pgTable('my_table', {
  // columns...
}, (table) => [
  index('idx_my_table_column').on(table.column),
]);

Adding a Table

export const newTable = pgTable('new_table', {
  id: serial('id').primaryKey(),
  // columns...
}, (table) => [
  // indexes...
]);

Anti-Patterns (NEVER Do This)

• Writing migration SQL by hand - This breaks drizzle's snapshot tracking and causes future generate commands to fail. ALWAYS use drizzle-kit generate.
• Using IF NOT EXISTS or IF EXISTS - These clauses signal you're writing SQL by hand. Generated migrations never include them.
• Editing schema.ts without running drizzle-kit generate
• Modifying existing migrations after they've been applied to prod
• Adding data migrations to schema migration files (use separate scripts)

Checklist

Before committing:

• schema.ts changes match generated migration
• Migration tested locally with pnpm build (only if local DB configured)
• No IF NOT EXISTS clauses (clean migration)
• Both schema.ts and migration in same commit