---
description: Example of creating and applying database migrations
version: 1.0.0
lastUpdated: 2025-01-10
tags: [example, database, migration, drizzle]
---

# Example: Database Migration

This document demonstrates how to create and apply database schema changes safely.

---

## Scenario 1: Adding a New Column

### Step 1: Modify Schema

```typescript
// File: packages/database/src/schema.ts
import { ulid } from 'ulid';

export const projects = pgTable('projects', {
  id: varchar('id', { length: 191 }).primaryKey(), // ULID stored as varchar
  name: text('name').notNull(),
  description: text('description'),
  
  // ✨ NEW COLUMN
  githubUrl: text('github_url'),
  
  userId: varchar('user_id', { length: 191 }).references(() => users.id).notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
});

// When inserting, generate ULID
const projectId = ulid();
await db.insert(projects).values({
  id: projectId,
  userId: ctx.user.id,
  // ...
});
```

### Step 2: Generate Migration

```bash
cd packages/database
pnpm db:generate
```

**Generated SQL** (`migrations/0001_add_github_url.sql`):

```sql
-- Migration: Add github_url column to projects table
ALTER TABLE "projects" ADD COLUMN "github_url" text;
```

### Step 3: Review Migration

```bash
# Check the generated SQL file
cat migrations/0001_add_github_url.sql

# Verify it matches expectations:
# - Column name correct
# - Type correct
# - Nullable/Not null correct
# - No unexpected changes
```

### Step 4: Apply Migration

```bash
# Development
pnpm db:migrate

# Production (with backup first!)
# 1. Backup database
pg_dump -U postgres dbname > backup.sql

# 2. Apply migration
pnpm db:migrate
```

### Step 5: Verify

```bash
# Open Drizzle Studio
pnpm db:studio

# Or check directly in PostgreSQL
psql -U postgres -d dbname -c "\d projects"
```

---

## Scenario 2: Adding a New Table with Relations

### Step 1: Define Schema

```typescript
// File: packages/database/src/schema.ts
import { ulid } from 'ulid';

// New table: webhooks
export const webhooks = pgTable('webhooks', {
  id: varchar('id', { length: 191 }).primaryKey(), // ULID stored as varchar
  url: text('url').notNull(),
  secret: text('secret').notNull(),
  events: text('events').array().notNull(), // Array of event types
  projectId: varchar('project_id', { length: 191 })
    .references(() => projects.id, { onDelete: 'cascade' })
    .notNull(),
  isActive: boolean('is_active').default(true).notNull(),
  lastTriggeredAt: timestamp('last_triggered_at'),
  createdAt: timestamp('created_at').defaultNow().notNull(),
}, (table) => ({
  projectIdIdx: index('webhooks_project_id_idx').on(table.projectId),
}));

// Relations
export const webhooksRelations = relations(webhooks, ({ one }) => ({
  project: one(projects, {
    fields: [webhooks.projectId],
    references: [projects.id],
  }),
}));

// Update projects relations
export const projectsRelations = relations(projects, ({ many }) => ({
  webhooks: many(webhooks),
  // ... other relations
}));

// Types
export type Webhook = InferSelectModel<typeof webhooks>;
export type NewWebhook = InferInsertModel<typeof webhooks>;
```

### Step 2: Generate Migration

```bash
pnpm db:generate
```

**Generated SQL**:

```sql
-- Create webhooks table
CREATE TABLE IF NOT EXISTS "webhooks" (
  "id" varchar(191) PRIMARY KEY, -- ULID stored as varchar
  "url" text NOT NULL,
  "secret" text NOT NULL,
  "events" text[] NOT NULL,
  "project_id" varchar(191) NOT NULL,
  "is_active" boolean DEFAULT true NOT NULL,
  "last_triggered_at" timestamp,
  "created_at" timestamp DEFAULT now() NOT NULL
);

-- Create foreign key
ALTER TABLE "webhooks" ADD CONSTRAINT "webhooks_project_id_projects_id_fk"
  FOREIGN KEY ("project_id") REFERENCES "projects"("id") ON DELETE CASCADE;

-- Create index
CREATE INDEX IF NOT EXISTS "webhooks_project_id_idx" ON "webhooks" ("project_id");
```

### Step 3: Apply and Verify

```bash
pnpm db:migrate
pnpm db:studio
```

---

## Scenario 3: Renaming a Column (Breaking Change)

⚠️ **Caution**: This is a breaking change that requires coordination.

### Option A: Direct Rename (Requires Downtime)

```sql
-- Manual migration: migrations/XXXX_rename_column.sql
ALTER TABLE "projects" RENAME COLUMN "github_url" TO "repository_url";
```

### Option B: Gradual Migration (No Downtime)

```typescript
// Step 1: Add new column
export const projects = pgTable('projects', {
  // ... existing columns
  githubUrl: text('github_url'), // Keep old column
  repositoryUrl: text('repository_url'), // Add new column
});
```

```sql
-- Migration 1: Add new column
ALTER TABLE "projects" ADD COLUMN "repository_url" text;

-- Migration 2: Copy data
UPDATE "projects" SET "repository_url" = "github_url" WHERE "github_url" IS NOT NULL;
```

```typescript
// Step 2: Update code to use new column
// Update all references in codebase

// Step 3: After deployment, remove old column
export const projects = pgTable('projects', {
  // ... existing columns
  repositoryUrl: text('repository_url'),
  // githubUrl removed
});
```

```sql
-- Migration 3: Drop old column
ALTER TABLE "projects" DROP COLUMN "github_url";
```

---

## Scenario 4: Adding NOT NULL Constraint to Existing Column

⚠️ **Careful**: This can fail if existing rows have NULL values.

### Safe Approach

```sql
-- Step 1: Backfill NULL values
UPDATE "projects" SET "description" = '' WHERE "description" IS NULL;

-- Step 2: Add NOT NULL constraint
ALTER TABLE "projects" ALTER COLUMN "description" SET NOT NULL;

-- Step 3: Optionally set default for future inserts
ALTER TABLE "projects" ALTER COLUMN "description" SET DEFAULT '';
```

### In Drizzle Schema

```typescript
export const projects = pgTable('projects', {
  // ... other columns
  description: text('description').notNull().default(''),
});
```

---

## Scenario 5: Adding Indexes for Performance

### Identify Need

```sql
-- Slow query
SELECT * FROM "projects" WHERE "user_id" = '...' ORDER BY "created_at" DESC;

-- Check query plan
EXPLAIN ANALYZE SELECT * FROM "projects" WHERE "user_id" = '...' ORDER BY "created_at" DESC;
```

### Add Index

```typescript
// File: packages/database/src/schema.ts
export const projects = pgTable('projects', {
  // ... columns
}, (table) => ({
  userIdCreatedAtIdx: index('projects_user_id_created_at_idx')
    .on(table.userId, table.createdAt.desc()),
}));
```

### Generate and Apply

```bash
pnpm db:generate
pnpm db:migrate
```

**Generated SQL**:

```sql
CREATE INDEX IF NOT EXISTS "projects_user_id_created_at_idx" 
  ON "projects" ("user_id", "created_at" DESC);
```

---

## Best Practices

### ✅ Do

1. **Always review generated SQL** before applying
2. **Backup production database** before migrations
3. **Test migrations in development** first
4. **Use transactions** for complex migrations
5. **Add indexes** for frequently queried columns
6. **Use cascading deletes** where appropriate
7. **Document breaking changes** in migration files

### ❌ Don't

1. **Never manually edit schema.ts** without generating migration
2. **Don't drop columns** without a deprecation period
3. **Don't add NOT NULL** without backfilling data
4. **Don't skip migration review**
5. **Don't apply untested migrations** to production
6. **Don't create indexes blindly** (monitor query performance first)

---

## Common Migration Patterns

### Pattern 1: Add Column with Default

```typescript
newColumn: text('new_column').notNull().default('default_value'),
```

### Pattern 2: Foreign Key with Cascade

```typescript
foreignId: varchar('foreign_id', { length: 191 }) // ULID stored as varchar
  .references(() => otherTable.id, { onDelete: 'cascade' })
  .notNull(),
```

### Pattern 3: Composite Index

```typescript
}, (table) => ({
  compositeIdx: index('table_col1_col2_idx').on(table.col1, table.col2),
}));
```

### Pattern 4: Unique Constraint

```typescript
email: text('email').notNull().unique(),
// Or composite unique
}, (table) => ({
  uniqueNameProject: unique('unique_name_per_project').on(table.name, table.projectId),
}));
```

### Pattern 5: Check Constraint

```typescript
import { check } from 'drizzle-orm/pg-core';

}, (table) => ({
  checkPositivePrice: check('price_positive', sql`price > 0`),
}));
```

---

## Rollback Strategy

### Automatic Rollback (Transaction)

```typescript
// Drizzle migrations run in a transaction by default
// If any part fails, entire migration is rolled back
```

### Manual Rollback

```sql
-- Create rollback migration
-- migrations/XXXX_rollback_add_column.sql
ALTER TABLE "projects" DROP COLUMN "github_url";
```

### Emergency Rollback

```bash
# Restore from backup
pg_restore -U postgres -d dbname backup.sql

# Or use point-in-time recovery (cloud providers)
```

---

## Testing Migrations

```typescript
// File: packages/database/src/__tests__/migrations.test.ts
import { describe, it, expect } from 'vitest';
import { db } from '../client';
import { projects } from '../schema';

describe('migrations', () => {
  it('should have applied all migrations', async () => {
    // Test that schema is correct
    const result = await db.select().from(projects).limit(1);
    expect(result).toBeDefined();
  });
  
  it('should allow inserting data with new schema', async () => {
    const [project] = await db.insert(projects).values({
      name: 'Test Project',
      description: 'Test description',
      userId: 'test-user-id',
      githubUrl: 'https://github.com/test/repo', // New column
    }).returning();
    
    expect(project.githubUrl).toBe('https://github.com/test/repo');
  });
});
```

---

## Summary

**Migration Workflow**:
1. Modify `schema.ts`
2. Run `pnpm db:generate`
3. Review generated SQL
4. Test in development
5. Backup production
6. Apply with `pnpm db:migrate`
7. Verify in Drizzle Studio

**Safety Checklist**:
- [ ] Reviewed generated SQL
- [ ] Tested in development
- [ ] Considered backward compatibility
- [ ] Backed up production database
- [ ] Planned rollback strategy
- [ ] Updated TypeScript types
- [ ] Updated code references
- [ ] Documented breaking changes

---

**Version**: 1.0.0  
**Last Updated**: 2025-01-10