Migration Workflow

Database migrations allow you to evolve your schema over time while preserving data. This guide covers the complete migration workflow from development to production.

Migration Basics

Migrations are version-controlled SQL scripts that modify your database schema. Drizzle generates these automatically by comparing your TypeScript schema with the current database state.

Migration Configuration

The migration setup is defined in drizzle.config.ts: 
// drizzle.config.ts
import { defineConfig } from 'drizzle-kit'

export default defineConfig({
  schema: './src/db/models/*',      // Where your schema files are
  out: './drizzle/migrations',     // Where migrations are generated
  dialect: 'postgresql',           // Database dialect
  dbCredentials: {
    url: process.env.DATABASE_URL!
  }
})

Migration Files Structure

drizzle/
├── migrations/
│   ├── 0001_init_schema.sql
│   ├── 0002_add_user_avatar.sql
│   ├── 0003_create_projects_table.sql
│   └── meta/
│       ├── _journal.json
│       ├── 0001_snapshot.json
│       └── 0002_snapshot.json
└── schema.ts

Migration Development Workflow

Option 1: Push for Rapid Development

Use db:push for quick schema changes during development:
1

Modify Schema

Edit your schema files in /src/db/models/:
// /src/db/models/user-model.ts
export const user = pgTable('user', {
  id: uuid('id').default(sql`uuid_generate_v4()`).primaryKey(),
  name: text('name').notNull(),
  email: text('email').notNull().unique(),
  // Add new field
  avatar: text('avatar'),
  bio: text('bio'),
})
2

Push Changes

Apply changes directly to database:
pnpm db:push
This will:
  • Compare your schema with database
  • Apply changes immediately
  • Skip migration generation
3

Verify Changes

Check that changes were applied:
pnpm db:studio
# Or check connection
pnpm db:check
Development Only: Use db:push only in development. Always use proper migrations for staging and production.

Option 2: Generate Migrations

Use migrations for tracked, reversible changes:
1

Modify Schema

Make your schema changes as above.
2

Generate Migration

Create migration files:
pnpm db:generate
This creates files like:
-- drizzle/migrations/0004_add_user_avatar.sql
ALTER TABLE "user" ADD COLUMN "avatar" text;
ALTER TABLE "user" ADD COLUMN "bio" text;
3

Review Migration

Always review generated SQL before applying:
-- Check the generated migration file
cat drizzle/migrations/0004_add_user_avatar.sql
4

Apply Migration

Run the migration:
pnpm db:migrate

Production Workflow

Safe Production Deployment

1

Test in Staging

Always test migrations in a staging environment first:
# Staging environment
NODE_ENV=staging pnpm db:migrate
2

Backup Production

Create a backup before applying migrations:
# PostgreSQL backup
pg_dump $DATABASE_URL > backup_$(date +%Y%m%d_%H%M%S).sql
3

Apply to Production

Run migrations in production:
NODE_ENV=production pnpm db:migrate
4

Verify Deployment

Check that the migration succeeded:
NODE_ENV=production pnpm db:check

Common Migration Scenarios

Adding Columns

// Schema change
export const user = pgTable('user', {
  id: uuid('id').primaryKey(),
  name: text('name').notNull(),
  email: text('email').notNull(),
  // Add optional field
  phone: text('phone'), // nullable by default
})
Generated migration:
ALTER TABLE "user" ADD COLUMN "phone" text;

Dropping Columns

Safe column removal with staging: 
// Step 1: Remove from schema
export const user = pgTable('user', {
  id: uuid('id').primaryKey(),
  name: text('name').notNull(),
  email: text('email').notNull(),
  // Remove: deprecated_field: text('deprecated_field'),
})
-- Generated migration
ALTER TABLE "user" DROP COLUMN "deprecated_field";
Destructive Operation: Dropping columns permanently deletes data. Always backup first and consider a multi-step process for critical data.

Creating New Tables

// Add new table to schema
export const notifications = pgTable('notifications', {
  id: uuid('id').default(sql`uuid_generate_v4()`).primaryKey(),
  userId: uuid('user_id').references(() => user.id).notNull(),
  type: text('type').notNull(),
  message: text('message').notNull(),
  read: boolean('read').default(false).notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
})

// Add relations
export const notificationRelations = relations(notifications, ({ one }) => ({
  user: one(user, {
    fields: [notifications.userId],
    references: [user.id],
  }),
}))
Generated migration: 
CREATE TABLE "notifications" (
  "id" uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  "user_id" uuid NOT NULL,
  "type" text NOT NULL,
  "message" text NOT NULL,
  "read" boolean DEFAULT false NOT NULL,
  "created_at" timestamp DEFAULT now() NOT NULL
);

ALTER TABLE "notifications" ADD CONSTRAINT "notifications_user_id_user_id_fk"
FOREIGN KEY ("user_id") REFERENCES "user"("id") ON DELETE no action ON UPDATE no action;

Modifying Existing Columns

// Change column type or constraints
export const user = pgTable('user', {
  id: uuid('id').primaryKey(),
  name: text('name').notNull(),
  // Change: varchar(255) -> text, add unique constraint
  email: text('email').notNull().unique(),
})
Generated migration: 
ALTER TABLE "user" ALTER COLUMN "email" SET DATA TYPE text;
ALTER TABLE "user" ADD CONSTRAINT "user_email_unique" UNIQUE("email");

Advanced Migration Patterns

Complex Data Migrations

Sometimes you need custom logic in migrations:
1

Generate Base Migration

pnpm db:generate
2

Edit Migration File

Add custom logic to the generated SQL:
-- drizzle/migrations/0005_complex_data_migration.sql

-- Generated schema changes
ALTER TABLE "projects" ADD COLUMN "slug" text;

-- Custom data migration
UPDATE "projects"
SET "slug" = LOWER(REPLACE("name", ' ', '-'))
WHERE "slug" IS NULL;

-- Add constraints after data migration
ALTER TABLE "projects" ALTER COLUMN "slug" SET NOT NULL;
ALTER TABLE "projects" ADD CONSTRAINT "projects_slug_unique" UNIQUE("slug");
3

Test Migration

Test on a copy of production data:
# Restore backup to test database
psql $TEST_DATABASE_URL < production_backup.sql

# Test migration
NODE_ENV=test pnpm db:migrate

Migration Rollbacks

Drizzle doesn't automatically generate rollback scripts, but you can create them manually: 
-- Migration: 0005_add_user_avatar.sql
ALTER TABLE "user" ADD COLUMN "avatar" text;

-- Rollback: 0005_rollback_add_user_avatar.sql (manual)
ALTER TABLE "user" DROP COLUMN "avatar";

Zero-Downtime Migrations

For large tables, use techniques to avoid locking: 
-- Step 1: Add nullable column
ALTER TABLE "large_table" ADD COLUMN "new_field" text;

-- Step 2: Populate in batches (separate deployment)
UPDATE "large_table" SET "new_field" = 'default_value'
WHERE "new_field" IS NULL AND "id" BETWEEN 1 AND 1000;

-- Step 3: Make NOT NULL (separate deployment)
ALTER TABLE "large_table" ALTER COLUMN "new_field" SET NOT NULL;

Migration Best Practices

Version Control

Always commit migration files: 
git add drizzle/migrations/
git commit -m "Add user avatar field migration"

Testing Migrations

Create a migration testing script: 
// scripts/test-migration.ts
import { drizzle } from 'drizzle-orm/postgres-js'
import { migrate } from 'drizzle-orm/postgres-js/migrator'
import postgres from 'postgres'

const testMigration = async () => {
  const client = postgres(process.env.TEST_DATABASE_URL!)
  const db = drizzle(client)

  try {
    await migrate(db, { migrationsFolder: './drizzle/migrations' })
    console.log('✅ Migration test successful')
  } catch (error) {
    console.error('❌ Migration test failed:', error)
    process.exit(1)
  } finally {
    await client.end()
  }
}

testMigration()

Migration Naming

Use descriptive names: 
# ✅ Good names
0001_initial_schema.sql
0002_add_user_avatar.sql
0003_create_projects_table.sql
0004_add_project_status_index.sql

# ❌ Avoid generic names
0001_migration.sql
0002_update.sql
0003_changes.sql

Documentation

Document complex migrations: 
-- Migration: Add user avatar support
-- Date: 2024-01-15
-- Author: developer@example.com
-- Description: Adds avatar URL field to users table for profile pictures
--              Includes data migration to set default avatar for existing users

ALTER TABLE "user" ADD COLUMN "avatar" text;

-- Set default avatar for existing users
UPDATE "user"
SET "avatar" = 'https://api.dicebear.com/7.x/avataaars/svg?seed=' || "email"
WHERE "avatar" IS NULL;

Troubleshooting Migrations

Common Issues

Migration fails with constraint error: 
# Error: column "email" of relation "user" already exists
Solutions: 
-- Add IF NOT EXISTS (PostgreSQL 9.6+)
ALTER TABLE "user" ADD COLUMN IF NOT EXISTS "email" text;

-- Or check in migration script
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_name = 'user' AND column_name = 'email'
  ) THEN
    ALTER TABLE "user" ADD COLUMN "email" text;
  END IF;
END $$;
Migration state mismatch: 
# Reset migration state (development only)
rm -rf drizzle/migrations
pnpm db:generate
pnpm db:push
Production migration failure: 
# Check migration status
SELECT * FROM __drizzle_migrations ORDER BY id;

# Manual rollback (if safe)
# Restore from backup and replay successful migrations

Migration Recovery

If a migration fails in production:
1

Assess Damage

# Check database state
psql $DATABASE_URL -c "\d+ user"

# Check migration status
psql $DATABASE_URL -c "SELECT * FROM __drizzle_migrations;"
2

Fix Forward

-- Create a fix migration instead of rolling back
-- Fix the issue that caused the failure
ALTER TABLE "user" DROP CONSTRAINT IF EXISTS "invalid_constraint";
3

Mark as Complete

-- If migration partially succeeded, mark as complete
UPDATE __drizzle_migrations
SET applied_at = NOW()
WHERE id = 'failed_migration_id';
A solid migration workflow is crucial for maintaining database integrity across environments. Always test migrations thoroughly before production deployment.

Next Steps