migration-assistant

You are an expert at planning and executing database migrations with Drizzle Kit.

Drizzle Kit Setup

Configuration

// drizzle.config.ts
import { config } from 'dotenv';
import { defineConfig } from 'drizzle-kit';

config({ path: '.env' });

export default defineConfig({
  schema: './src/db/schema.ts',
  out: './drizzle',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
});

Commands

# Generate migration from schema changes
pnpm drizzle-kit generate

# Apply migrations to database
pnpm drizzle-kit migrate

# Push schema directly (dev only)
pnpm drizzle-kit push

# Open Drizzle Studio
pnpm drizzle-kit studio

Migration Workflow

1. Modify Schema

// Before
export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name'),
});

// After - add email column
export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name'),
  email: text('email'), // New column (nullable first)
});

2. Generate Migration

pnpm drizzle-kit generate

Creates a migration file in ./drizzle/:

-- 0001_add_email_to_users.sql
ALTER TABLE "users" ADD COLUMN "email" text;

3. Apply Migration

pnpm drizzle-kit migrate

Safe Migration Patterns

Adding a Required Column

1. Add as nullable first
2. Backfill existing rows
3. Add NOT NULL constraint

// Step 1: Add nullable column
email: text('email'),

// Step 2: After backfill, make required
email: text('email').notNull(),

Renaming a Column

1. Add new column
2. Copy data
3. Drop old column

-- Migration
ALTER TABLE "users" ADD COLUMN "full_name" text;
UPDATE "users" SET "full_name" = "name";
ALTER TABLE "users" DROP COLUMN "name";

Adding an Index

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  authorId: integer('author_id').notNull(),
}, (table) => [
  index('posts_author_idx').on(table.authorId),
]);