Drizzle Kit Migration Workflow

This skill guides you through the proper database migration workflow using Drizzle Kit for the asset-forge project.

When to Use This Skill

Use this skill when:

• •User asks to "create a migration" or "generate a migration"
• •Database schema files in server/db/schema/ have been modified
• •User asks to "update the database" or "apply migrations"
• •User asks about the migration workflow

Workflow Steps

1. Generate Migration from Schema

After making changes to TypeScript schema files in packages/asset-forge/server/db/schema/, generate a migration:

cd ${WORKSPACE_DIR}/packages/asset-forge
bun run db:generate

What this does:

• •Reads all schema files from server/db/schema/
• •Compares with previous migration state
• •Generates SQL migration file in server/db/migrations/
• •Auto-generates migration name like 0001_clever_name.sql

Common errors:

• •TypeScript syntax errors in schema files → Fix with /check-types
• •Circular dependencies → Check foreign key references
• •Invalid column types → Verify Drizzle types

2. Review Generated SQL

ALWAYS review the SQL before applying! Use the Read tool to check:

# Find latest migration
cd ${WORKSPACE_DIR}/packages/asset-forge
ls -t server/db/migrations/*.sql | head -1

Review checklist:

• • SQL statements match intended schema changes
• • No unintended DROP TABLE or DROP COLUMN statements
• • Foreign keys reference existing tables
• • Indexes are properly named
• • Data migrations (if any) are correct

3. Apply Migration to Database

After reviewing, apply the migration:

cd ${WORKSPACE_DIR}/packages/asset-forge
bun run db:migrate

What this does:

• •Connects to database using DATABASE_URL
• •Runs pending migrations in order
• •Updates migration journal table
• •Applies schema changes

Troubleshooting:

• •Migration fails → Check database connection in .env
• •"Relation already exists" → Schema already in DB, might need to reset or create fix migration
• •Permission errors → Check database file permissions (SQLite) or user permissions (PostgreSQL)

4. Verify Changes

After successful migration, verify with Drizzle Studio:

cd ${WORKSPACE_DIR}/packages/asset-forge
bun run db:studio

Opens web UI at http://test-db-studio:4983 to inspect:

• •Tables created/modified correctly
• •Data integrity maintained
• •Indexes created
• •Foreign keys working

Important Notes

Auto-Generated vs Manual SQL

• •✅ ALWAYS use Drizzle Kit to generate migrations
• •❌ NEVER write SQL files manually in migrations folder
• •Why: Drizzle tracks schema state and ensures consistency

Migration Files Must Be Committed

After generating and applying migrations:

1. •Add both schema files AND migration SQL to git
2. •Commit together to keep schema + migrations in sync
3. •Never .gitignore the migrations folder

Development vs Production

• •Development: Can use bun run db:push to sync schema directly (skips migrations)
• •Production: ALWAYS use migrations (db:generate + db:migrate)

Rollback Strategy

Drizzle Kit doesn't have automatic rollback. To rollback:

1. •Create a new "down" migration that reverses changes
2. •Or restore database from backup
3. •Or manually write DROP/ALTER statements

Environment Requirements

Required in .env:

DATABASE_URL="postgresql://user:pass@host:port/dbname"
# or for SQLite:
DATABASE_URL="file:./local.db"

Related Commands

• •/migrate - Full migration workflow (generate → review → apply)
• •/migrate generate - Generate only
• •/migrate apply - Apply only
• •/db/studio - Launch Drizzle Studio
• •/check-types - Verify schema TypeScript

Example Workflow

// 1. User modifies schema
// packages/asset-forge/server/db/schema/teams.schema.ts
export const teams = pgTable('teams', {
  id: uuid('id').primaryKey().defaultRandom(),
  name: varchar('name', { length: 255 }).notNull(),
  // NEW: added description field
  description: text('description'),
  createdAt: timestamp('created_at').defaultNow().notNull(),
});

// 2. Generate migration
// bun run db:generate
// → Creates: 0004_amazing_hulk.sql

// 3. Review SQL
// ALTER TABLE "teams" ADD COLUMN "description" text;

// 4. Apply migration
// bun run db:migrate
// → Migration applied successfully

// 5. Commit changes
// git add server/db/schema/teams.schema.ts
// git add server/db/migrations/0004_amazing_hulk.sql
// git commit -m "feat: add description field to teams table"

Key Takeaways

1. •TypeScript → SQL: Schema changes in .ts files generate .sql migrations
2. •Review First: Always read the SQL before applying
3. •Commit Together: Schema files + migration files in same commit
4. •Production-Safe: Migrations are the safe way to evolve schema
5. •Drizzle Studio: Visual verification after applying migrations

Files to Know

• •packages/asset-forge/drizzle.config.ts - Drizzle Kit configuration
• •packages/asset-forge/server/db/schema/ - All schema definitions
• •packages/asset-forge/server/db/migrations/ - Generated SQL migrations
• •packages/asset-forge/server/db/migrations/meta/_journal.json - Migration tracking

Remember: Never skip the review step! Always inspect the generated SQL before running db:migrate.