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:

• Add both schema files AND migration SQL to git

• Commit together to keep schema + migrations in sync

• 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:

• Create a new "down" migration that reverses changes

• Or restore database from backup

• 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

• TypeScript → SQL: Schema changes in .ts files generate .sql migrations

• Review First: Always read the SQL before applying

• Commit Together: Schema files + migration files in same commit

• Production-Safe: Migrations are the safe way to evolve schema

• 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.