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:

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:

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:

Rollback migration
Create data migration script
Run data migration first
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

drizzle-migrations

NPX Install

Tags

SKILL.md Content

Drizzle ORM Database Migrations (TypeScript)

When to Use This Skill

Core Principle: Migration-First Development

Why Migration-First?

Anti-Pattern (Code-First)

Correct Pattern (Migration-First)

Complete Migration Workflow

Step 1: Design Schema in SQL Migration

Step 2: Generate TypeScript Definitions

Step 3: Create Schema Snapshot

Step 4: Implement TypeScript Schema

Step 5: Organize Schemas by Domain

Step 6: Add Quality Check to CI

Step 7: Test on Staging

Common Migration Patterns

Adding a Column

Creating a Junction Table

Modifying Column Type

Adding Constraints

Configuration

drizzle.config.ts

package.json Scripts

Migration Testing Workflow

Local Testing

Rollback Strategy

Best Practices

Do's

Don'ts

Troubleshooting

Schema Drift Detected

Migration Fails on Staging

TypeScript Types Out of Sync

Related Skills