drizzle-migrations

仓库: bobmatnyc/claude-mpm-skills
安装量: 458
排名: #2244

安装

npx skills add https://github.com/bobmatnyc/claude-mpm-skills --skill 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:

TypeScript schema files (if using drizzle-kit push) Snapshot files in drizzle/meta/XXXX_snapshot.json 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', { 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 -- drizzle/0007_change_price_to_decimal.sql ALTER TABLE services ALTER COLUMN price TYPE DECIMAL(10, 2);

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 -- 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 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 { "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

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

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:

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:

Rollback migration Create data migration script Run data migration first Then run schema migration -- 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:

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

← 返回排行榜