drizzle-migrations

Drizzle ORM Database Migrations (TypeScript)

Migration-first database development workflow using Drizzle ORM for TypeScript/JavaScript projects.

When to Use This Skill

Use this skill when:

• Working with Drizzle ORM in TypeScript/JavaScript projects
• Need to create or modify database schema
• Want migration-first development workflow
• Setting up new database tables or columns
• Need to ensure schema consistency across environments

Core Principle: Migration-First Development

Critical Rule: Schema changes ALWAYS start with migrations, never code-first.

Why Migration-First?

• ✅ SQL migrations are the single source of truth
• ✅ Prevents schema drift between environments
• ✅ Enables rollback and versioning
• ✅ Forces explicit schema design decisions
• ✅ TypeScript types generated from migrations
• ✅ CI/CD can validate schema changes

Anti-Pattern (Code-First)

❌ WRONG: Writing TypeScript schema first

// DON'T DO THIS FIRST
export const users = pgTable('users', {
  id: uuid('id').primaryKey(),
  email: text('email').notNull(),
});

Correct Pattern (Migration-First)

✅ CORRECT: Write SQL migration first

-- drizzle/0001_add_users_table.sql
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email TEXT NOT NULL UNIQUE,
  created_at TIMESTAMP DEFAULT NOW()
);

Complete Migration Workflow

Step 1: Design Schema in SQL Migration

Create descriptive SQL migration file:

-- drizzle/0001_create_school_calendars.sql
CREATE TABLE school_calendars (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  school_id UUID NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  academic_year TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

-- Add indexes for query performance
CREATE INDEX idx_school_calendars_school_id ON school_calendars(school_id);
CREATE INDEX idx_school_calendars_academic_year ON school_calendars(academic_year);

-- Add constraints
ALTER TABLE school_calendars
  ADD CONSTRAINT check_date_range
  CHECK (end_date > start_date);