Database Migration Skill

Create schema changes with proper migrations.

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 (Don't Do This)

• Editing schema.ts without running drizzle-kit generate
• Writing migration SQL by hand (use generate)
• Modifying existing migrations after they've been applied to prod
• Using IF NOT EXISTS in new migrations (signals drift)

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