Database Migration Guide

This guide covers database migration best practices for Elite Events.

Overview

All database schema changes must be tracked through Prisma migrations. This ensures:

• Reproducible deployments across environments
• Auditable change history
• Safe rollback capabilities
• Team collaboration on schema changes

When to Create Migrations

ALWAYS create a migration when:

• Adding or removing tables
• Adding, removing, or renaming columns
• Changing column types or constraints
• Adding or removing indexes
• Modifying foreign key relationships
• Changing enum values

NEVER use db push in production. It's only for rapid prototyping in development.

Creating a Migration

Development Workflow

# 1. Make changes to prisma/schema.prisma

# 2. Create a migration
npx prisma migrate dev --name descriptive_name

# 3. Review generated SQL in prisma/migrations/[timestamp]_name/

# 4. Commit migration files to git
git add prisma/migrations
git commit -m "Add migration: descriptive_name"

Migration Naming Convention

Format: action_target_detail

Production Deployment

Deploy Migrations

# Deploy pending migrations to production
npx prisma migrate deploy

This command:

• Applies all pending migrations
• Does NOT modify the schema file
• Fails safely if migrations conflict

CI/CD Integration

Add to your deployment pipeline:

# Example GitHub Actions step
- name: Deploy database migrations
  run: npx prisma migrate deploy
  env:
    DATABASE_URL: ${{ secrets.DATABASE_URL }}

Rollback Strategy

Before Deployment

1. Test in staging first - Always apply migrations to staging before production
2. Keep rollback SQL ready - For critical changes, prepare rollback scripts
3. Use feature flags - For risky schema changes, gate features behind flags

Handling Failed Migrations

# Check migration status
npx prisma migrate status

# If a migration failed, fix the issue and re-run
npx prisma migrate deploy

Manual Rollback (Emergency Only)

If you need to manually rollback:

1. Backup the database first
2. Run the reverse SQL operations
3. Mark the migration as rolled back in _prisma_migrations table

-- Example: Remove a column that was added
ALTER TABLE Product DROP COLUMN new_column;

-- Update migration status
UPDATE _prisma_migrations
SET rolled_back_at = NOW()
WHERE migration_name = '20251201_add_new_column';

Safe Migration Patterns

Adding Columns

// Safe: Adding nullable column
model User {
  phone String?  // Nullable, no default needed
}

// Safe: Adding with default
model Product {
  viewCount Int @default(0)
}

Removing Columns

1. First deploy: Mark column as deprecated (stop writing to it)
2. Second deploy: Remove column from schema after data is migrated

// Step 1: Keep column but stop using it
model Product {
  @deprecated("Use newField instead")
  oldField String?
}

// Step 2 (later deployment): Remove column
model Product {
  // oldField removed
}

Renaming Columns

Use a three-step process:

1. Add new column
2. Migrate data
3. Remove old column

-- Step 1: Add new column
ALTER TABLE Product ADD COLUMN new_name VARCHAR(255);

-- Step 2: Copy data
UPDATE Product SET new_name = old_name;

-- Step 3: Remove old column (separate migration)
ALTER TABLE Product DROP COLUMN old_name;

Index Management

Adding Indexes

model Product {
  @@index([categoryId, isActive])  // Composite index
  @@index([slug])                   // Single column index
}

Analyzing Index Usage

-- Check if indexes are being used (MySQL)
SHOW INDEX FROM Product;

-- Analyze query plan
EXPLAIN SELECT * FROM Product WHERE categoryId = 1 AND isActive = true;

Migration Best Practices

1. Small, focused migrations - One logical change per migration
2. Descriptive names - Future you will thank present you
3. Test with production data volume - 100 rows != 1 million rows
4. Consider locks - Large table alterations may lock tables
5. Off-peak deployments - Run migrations during low-traffic periods
6. Monitor after deployment - Watch for query performance changes

Troubleshooting

"Migration failed to apply cleanly"

# Check what went wrong
npx prisma migrate status

# If safe, reset and reapply (DEV ONLY!)
npx prisma migrate reset

"Drift detected"

The database schema differs from migrations:

# See the difference
npx prisma migrate diff

# Create migration to fix drift
npx prisma migrate dev --name fix_drift

Migration Takes Too Long

For large tables, consider:

• Running during maintenance window
• Using pt-online-schema-change for MySQL
• Breaking into smaller migrations

Related Documentation

• Query Optimization Guide
• Prisma Documentation