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