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);

Naming Convention:

• Use sequential numbers: 0001_, 0002_, etc.
• Descriptive names: create_school_calendars, add_user_roles
• Format: XXXX_descriptive_name.sql

Step 2: Generate TypeScript Definitions

Drizzle Kit generates TypeScript types from SQL:

# Generate TypeScript schema and snapshots
pnpm drizzle-kit generate

# Or using npm
npm run db:generate

What This Creates:

1. TypeScript schema files (if using drizzle-kit push)
2. Snapshot files in drizzle/meta/XXXX_snapshot.json
3. Migration metadata

Step 3: Create Schema Snapshot

Snapshots enable schema drift detection:

// drizzle/meta/0001_snapshot.json (auto-generated)
{
  "version": "5",
  "dialect": "postgresql",
  "tables": {
    "school_calendars": {
      "name": "school_calendars",
      "columns": {
        "id": {
          "name": "id",
          "type": "uuid",
          "primaryKey": true,
          "notNull": true,
          "default": "gen_random_uuid()"
        },
        "school_id": {
          "name": "school_id",
          "type": "uuid",
          "notNull": true
        }
      }
    }
  }
}

Snapshots in Version Control:

• ✅ Commit snapshots to git
• ✅ Enables drift detection in CI
• ✅ Documents schema history

Step 4: Implement TypeScript Schema

Now write TypeScript schema that mirrors SQL migration:

// src/lib/db/schema/school/calendar.ts
import { pgTable, uuid, date, text, timestamp } from 'drizzle-orm/pg-core';
import { schools } from './school';

export const schoolCalendars = pgTable('school_calendars', {
  id: uuid('id').primaryKey().defaultRandom(),
  schoolId: uuid('school_id')
    .notNull()
    .references(() => schools.id, { onDelete: 'cascade' }),
  startDate: date('start_date').notNull(),
  endDate: date('end_date').notNull(),
  academicYear: text('academic_year').notNull(),
  createdAt: timestamp('created_at').defaultNow(),
  updatedAt: timestamp('updated_at').defaultNow(),
});

// Type inference
export type SchoolCalendar = typeof schoolCalendars.$inferSelect;
export type NewSchoolCalendar = typeof schoolCalendars.$inferInsert;

Key Points:

• Column names match SQL exactly: school_id → 'school_id'
• TypeScript property names use camelCase: schoolId
• Constraints and indexes defined in SQL, not TypeScript
• Foreign keys reference other tables

Step 5: Organize Schemas by Domain

Structure schemas for maintainability:

src/lib/db/schema/
├── index.ts              # Export all schemas
├── school/
│   ├── index.ts
│   ├── district.ts
│   ├── holiday.ts
│   ├── school.ts
│   └── calendar.ts
├── providers.ts
├── cart.ts
└── users.ts

index.ts (export all):

// src/lib/db/schema/index.ts
export * from './school';
export * from './providers';
export * from './cart';
export * from './users';

school/index.ts:

// src/lib/db/schema/school/index.ts
export * from './district';
export * from './holiday';
export * from './school';
export * from './calendar';

Step 6: Add Quality Check to CI

Validate schema consistency in CI/CD:

# .github/workflows/quality.yml
name: Quality Checks

on:
  pull_request:
    branches: [main, develop]
  push:
    branches: [main]

jobs:
  quality:
    runs-on: ubuntu-latest

    steps:
      - uses: actions/checkout@v4

      - name: Setup Node.js
        uses: actions/setup-node@v4
        with:
          node-version: '20'
          cache: 'pnpm'

      - name: Install dependencies
        run: pnpm install --frozen-lockfile

      - name: Check database schema drift
        run: pnpm drizzle-kit check

      - name: Verify migrations (dry-run)
        run: pnpm drizzle-kit push --dry-run
        env:
          DATABASE_URL: ${{ secrets.STAGING_DATABASE_URL }}

      - name: Run type checking
        run: pnpm tsc --noEmit

      - name: Lint code
        run: pnpm lint

CI Checks Explained:

• drizzle-kit check: Validates snapshots match schema
• drizzle-kit push --dry-run: Tests migration without applying
• Type checking: Ensures TypeScript compiles
• Linting: Enforces code style

Step 7: Test on Staging

Before production, test migration on staging:

# 1. Run migration on staging
STAGING_DATABASE_URL="..." pnpm drizzle-kit push

# 2. Verify schema
pnpm drizzle-kit check

# 3. Test affected API routes
curl https://staging.example.com/api/schools/calendars

# 4. Check for data integrity issues
# Run queries to verify data looks correct

# 5. Monitor logs for errors
# Check application logs for migration-related errors

Staging Checklist:

• [ ] Migration runs without errors
• [ ] Schema drift check passes
• [ ] API routes using new schema work correctly
• [ ] No data integrity issues
• [ ] Application logs show no errors
• [ ] Query performance acceptable

Common Migration Patterns

Adding a Column

-- drizzle/0005_add_user_phone.sql
ALTER TABLE users
ADD COLUMN phone TEXT;

-- Add index if querying by phone
CREATE INDEX idx_users_phone ON users(phone);

TypeScript:

export const users = pgTable('users', {
  id: uuid('id').primaryKey(),
  email: text('email').notNull(),
  phone: text('phone'), // New column
});

Creating a Junction Table

-- drizzle/0006_create_provider_specialties.sql
CREATE TABLE provider_specialties (
  provider_id UUID NOT NULL REFERENCES providers(id) ON DELETE CASCADE,
  specialty_id UUID NOT NULL REFERENCES specialties(id) ON DELETE CASCADE,
  PRIMARY KEY (provider_id, specialty_id)
);

CREATE INDEX idx_provider_specialties_provider ON provider_specialties(provider_id);
CREATE INDEX idx_provider_specialties_specialty ON provider_specialties(specialty_id);

TypeScript:

export const providerSpecialties = pgTable('provider_specialties', {