bobmatnyc/claude-mpm-skills

drizzle-migrations

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

소스 보기

사용 방법

  1. 설치 명령을 복사하고 자신의 에이전트 환경에 설치하기 전에 소스 패키지를 확인하세요.
  2. 필요한 도구, 인증 정보, 지원 환경은 원본 문서에서 확인하세요.
  3. 입력과 원하는 산출물을 설명하고 출처와 결과를 대조하세요.
npx skills add https://github.com/bobmatnyc/claude-mpm-skills --skill drizzle-migrations

시작 전 확인

  • 설치에는 모델 구독, API 한도, 파일 접근 권한이 포함되지 않습니다.
  • 별점 수와 설치 수는 활동 지표이며 독립적인 품질·보안 검증이 아닙니다.
원본 Skill 문서

원본 저장소의 제목, 예시, 코드, 표, 링크, 이미지를 유지해 표시합니다.

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

typescript
// 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

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

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

bash
# 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:

json
// 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:

typescript
// 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):

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

school/index.ts:

typescript
// 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:

yaml
# .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:

bash
# 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

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

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

Creating a Junction Table

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

typescript
export const providerSpecialties = pgTable('provider_specialties', {
  providerId: uuid('provider_id')
    .notNull()
    .references(() => providers.id, { onDelete: 'cascade' }),
  specialtyId: uuid('specialty_id')
    .notNull()
    .references(() => specialties.id, { onDelete: 'cascade' }),
}, (table) => ({
  pk: primaryKey(table.providerId, table.specialtyId),
}));

Modifying Column Type

sql
-- drizzle/0007_change_price_to_decimal.sql
ALTER TABLE services
ALTER COLUMN price TYPE DECIMAL(10, 2);

TypeScript:

typescript
import { decimal } from 'drizzle-orm/pg-core';

export const services = pgTable('services', {
  id: uuid('id').primaryKey(),
  name: text('name').notNull(),
  price: decimal('price', { precision: 10, scale: 2 }).notNull(),
});

Adding Constraints

sql
-- drizzle/0008_add_email_constraint.sql
ALTER TABLE users
ADD CONSTRAINT users_email_unique UNIQUE (email);

ALTER TABLE users
ADD CONSTRAINT users_email_format CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}$');

Configuration

drizzle.config.ts

typescript
import type { Config } from 'drizzle-kit';

export default {
  schema: './src/lib/db/schema/index.ts',
  out: './drizzle',
  driver: 'pg',
  dbCredentials: {
    connectionString: process.env.DATABASE_URL!,
  },
} satisfies Config;

package.json Scripts

json
{
  "scripts": {
    "db:generate": "drizzle-kit generate:pg",
    "db:push": "drizzle-kit push:pg",
    "db:studio": "drizzle-kit studio",
    "db:check": "drizzle-kit check:pg",
    "db:up": "drizzle-kit up:pg"
  }
}

Migration Testing Workflow

Local Testing

bash
# 1. Create migration
echo "CREATE TABLE test (...)" > drizzle/0009_test.sql

# 2. Generate TypeScript
pnpm db:generate

# 3. Push to local database
pnpm db:push

# 4. Verify schema
pnpm db:check

# 5. Test in application
pnpm dev
# Manually test affected features

# 6. Run tests
pnpm test

Rollback Strategy

sql
-- drizzle/0010_add_feature.sql (up migration)
CREATE TABLE new_feature (...);

-- drizzle/0010_add_feature_down.sql (down migration)
DROP TABLE new_feature;

Apply rollback:

bash
# Manually run down migration
psql $DATABASE_URL -f drizzle/0010_add_feature_down.sql

Best Practices

Do's

  • ✅ Write SQL migrations first
  • ✅ Use descriptive migration names
  • ✅ Add indexes for foreign keys
  • ✅ Include constraints in migrations
  • ✅ Test migrations on staging before production
  • ✅ Commit snapshots to version control
  • ✅ Organize schemas by domain
  • ✅ Use drizzle-kit check in CI

Don'ts

  • ❌ Never write TypeScript schema before SQL migration
  • ❌ Don't skip staging testing
  • ❌ Don't modify old migrations (create new ones)
  • ❌ Don't forget to add indexes
  • ❌ Don't use drizzle-kit push in production (use proper migrations)
  • ❌ Don't commit generated files without snapshots

Troubleshooting

Schema Drift Detected

Error: Schema drift detected

Solution:

bash
# Check what changed
pnpm drizzle-kit check

# Regenerate snapshots
pnpm drizzle-kit generate

# Review changes and commit
git add drizzle/meta/
git commit -m "Update schema snapshots"

Migration Fails on Staging

Error: Migration fails with data constraint violation

Solution:

  1. Rollback migration
  2. Create data migration script
  3. Run data migration first
  4. Then run schema migration
sql
-- First: Migrate data
UPDATE users SET status = 'active' WHERE status IS NULL;

-- Then: Add constraint
ALTER TABLE users
ALTER COLUMN status SET NOT NULL;

TypeScript Types Out of Sync

Error: TypeScript types don't match database

Solution:

bash
# Regenerate everything
pnpm db:generate
pnpm tsc --noEmit

# If still broken, check schema files
# Ensure column names match SQL exactly

Related Skills

  • universal-data-database-migration - Universal migration patterns
  • toolchains-typescript-data-drizzle - Drizzle ORM usage patterns
  • toolchains-typescript-core - TypeScript best practices
  • universal-debugging-verification-before-completion - Verification workflows

관련 작업

같은 저장소의 Skills

더 많은 Skills

모든 Skills