Docs /ai-agents/examples/database-migration

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

// 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

cd packages/database
pnpm db:generate

Generated SQL (migrations/0001_add_github_url.sql):

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

Step 3: Review Migration

# 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

# 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

# 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

// 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

pnpm db:generate

Generated 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

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)

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

Option B: Gradual Migration (No Downtime)

// 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
});

-- 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;

// 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
});

-- 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

-- 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

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

Scenario 5: Adding Indexes for Performance

Identify Need

-- 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

// 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

pnpm db:generate
pnpm db:migrate

Generated 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

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

Pattern 2: Foreign Key with Cascade

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

Pattern 3: Composite Index

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

Pattern 4: Unique Constraint

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

Pattern 5: Check Constraint

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

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

Rollback Strategy

Automatic Rollback (Transaction)

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

Manual Rollback

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

Emergency Rollback

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

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

Testing Migrations

// 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

Reading this with an agent? /docs/ai-agents/examples/database-migration.md serves the raw markdown.

All docs