database-migration - SKILL.md Agent Skill

name: database-migration description: Handle database schema changes using Drizzle ORM. Use when modifying database tables, columns, relationships, or any schema changes. Includes migration generation, application, and verification steps.

Database Migration Workflow

This skill guides you through the complete database migration process using Drizzle ORM.

When to Use

Adding new tables to the database
Modifying existing table columns
Changing relationships or constraints
Adding indexes or foreign keys
Any schema changes in <schema-file>

Prerequisites

Node.js installed
Database connection configured
Drizzle Kit available in project

Step-by-Step Process

1. Modify the Schema

Edit <schema-file> with your schema changes:

// Example: Adding a new column
export const users = pgTable("users", {
  id: text("id").primaryKey(),
  email: text("email").notNull(),
  newField: text("new_field"), // New field added
  createdAt: timestamp("created_at").defaultNow().notNull(),
});

2. Generate Migration

Run the migration generation command:

<generate-command>

This command:

Analyzes your schema changes
Creates a new migration file in <migrations-dir>
Names it with a timestamp and description

Example output:

✔ Loaded env from .env
✔ Pulling schema from database...
✔ Pulling migration state...
✔ Loaded all config
✔ Pulling existing migrations...
◐ Pulling schema from database...0001_new_field_migration.sql migrated!

3. Review the Migration

Check the generated migration file in <migrations-dir>:

-- Example: migrations/0001_add_new_field.sql
ALTER TABLE "users" ADD COLUMN "new_field" text;

Important: Review the SQL to ensure it matches your intentions.

4. Apply the Migration

Run the migration to update your database:

<migrate-command>

This applies all pending migrations to your database.

5. Verify Changes

Open Drizzle Studio to verify:

<studio-command>

Check:

New columns appear
Data is preserved
Constraints are applied

6. Commit Changes

Always commit both files together:

git add <schema-file> <migrations-dir>/
git commit -m "feat: add new_field to users table"

Common Scenarios

Adding a New Table

export const newTable = pgTable("new_table", {
  id: text("id").primaryKey().$defaultFn(() => crypto.randomUUID()),
  name: text("name").notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
});

Adding a Foreign Key

export const posts = pgTable("posts", {
  id: text("id").primaryKey(),
  userId: text("user_id")
    .notNull()
    .references(() => users.id, { onDelete: "cascade" }),
  // other fields...
});

Making a Field Required

// Before
description: text("description"),

// After
description: text("description").notNull(),

Important Rules

Never edit migration files manually - Always regenerate if needed
Always commit schema and migrations together - They must stay in sync
Test migrations on development first - Never run untested migrations in production
Review generated SQL - Ensure it matches your expectations
Use transactions - Drizzle handles this, but be aware for complex migrations

Troubleshooting

Migration Drift Detected

If you get a drift warning:

# Reset and regenerate (DEVELOPMENT ONLY)
<generate-command>

Migration Fails

Check the error message
Review the generated SQL
Ensure database is running
Check for data conflicts

Rollback

Drizzle doesn't support automatic rollback. To undo:

Manually write a down migration
Or restore from backup

Acceptance Criteria

✅ Schema file modified ✅ Migration generated successfully ✅ Migration SQL reviewed ✅ Migration applied without errors ✅ Changes verified in database ✅ Both files committed together

Project-Specific Placeholders

<schema-file>: Path to schema definition file
<migrations-dir>: Directory where migrations are stored
<generate-command>: Command to generate migrations
<migrate-command>: Command to apply migrations
<studio-command>: Command to open database studio
Names it with a timestamp and description