Database Migration Strategies

Overview

Database migration strategies define approaches for modifying database schemas in a controlled, repeatable manner across different environments. Migrations solve the problem of synchronizing database structure changes with application code changes, particularly in team environments where multiple developers modify the schema concurrently.

Traditional schema management relied on manual SQL scripts or direct database modifications, creating synchronization problems between development, staging, and production environments. A developer might add a column locally, forget to document the change, and deploy code that expects the column to exist, causing production failures. Migrations eliminate this category of errors by treating schema changes as versioned code artifacts.

A migration consists of two parts: instructions for applying a change (moving forward) and instructions for reverting the change (moving backward). This bidirectional nature enables testing changes in development, deploying to production, and rolling back if problems arise.

# Example migration structure
class AddEmailToUsers < ActiveRecord::Migration[7.0]
  def up
    add_column :users, :email, :string
  end
  
  def down
    remove_column :users, :email
  end
end

The migration system tracks which migrations have run using a schema version table in the database. When executing migrations, the system compares this table against available migration files and runs only the pending migrations in sequence. This mechanism prevents duplicate execution and maintains consistent state across all environments.

Ruby applications, particularly those using Rails, pioneered accessible migration patterns that influenced database tooling across multiple languages. The Rails migration DSL abstracts SQL differences between database systems, allowing the same migration code to work across PostgreSQL, MySQL, SQLite, and other databases.

Key Principles

Version Control Integration forms the foundation of migration strategies. Each migration receives a unique identifier, typically a timestamp, that establishes execution order. This versioning prevents conflicts when multiple developers create migrations simultaneously. Unlike sequential numbering where two developers might both create migration 005, timestamp-based versioning (20240315120000) ensures each migration has a distinct position in the sequence.

Atomicity requires that migrations execute completely or not at all. Partial execution leaves the database in an inconsistent state where some changes applied but others failed. Database transactions wrap migration statements, ensuring rollback on failure. However, some database operations cannot execute within transactions (MySQL's ALTER TABLE operations, for instance), requiring careful migration design.

# Wrapped in transaction automatically
class CreateOrders < ActiveRecord::Migration[7.0]
  def change
    create_table :orders do |t|
      t.references :user, foreign_key: true
      t.decimal :total, precision: 10, scale: 2
      t.timestamps
    end
  end
end

Idempotency ensures migrations produce the same result regardless of how many times they execute. Check for existence before creating tables or columns, and verify absence before dropping them. This property enables safe retry after failures and simplifies rollback scenarios.

Reversibility means every migration can undo its changes. Adding a column must include logic to remove that column. Creating an index must support dropping the index. Reversibility enables confident deployment because failures can be rolled back to a known-good state. Some operations (dropping columns or tables) pose data loss risks during reversal, requiring careful planning.

Environment Parity maintains identical schema structure across development, staging, and production environments. The same migration files execute in each environment, producing identical schemas. This principle prevents environment-specific bugs where code works in development but fails in production due to schema differences.

Separation of Schema and Data distinguishes between structure changes (adding tables, modifying columns) and data changes (populating new fields, transforming existing data). Schema migrations modify table structure. Data migrations modify table contents. Mixing these concerns complicates rollback and error handling. A migration that adds a column and populates it with values faces challenges: rolling back deletes both the column and the data, potentially losing information.

Forward Compatibility during deployment requires that new code works with both old and new schemas. During deployment, some application servers run new code while the database still has the old schema. The application must handle the transition period. This requirement influences how changes are sequenced: add optional columns before requiring them, maintain old columns while transitioning to new ones, and remove deprecated columns in later migrations.

Testing Requirement treats migrations as code requiring the same rigor as application logic. Tests verify migrations execute successfully, produce expected schema changes, and rollback cleanly. Testing catches SQL syntax errors, constraint violations, and data transformation problems before production deployment.

Implementation Approaches

Sequential Numbering assigns incrementing integers to migrations: 001, 002, 003. Each migration knows its position in the sequence. This approach works well for single developers or very small teams where only one person modifies the schema at a time. However, conflicts arise when multiple developers create migrations concurrently. If two developers both create migration 005, merging their branches requires renumbering one migration, potentially causing issues if either migration already ran in a shared environment.

Timestamp-Based Versioning uses the creation timestamp as the migration identifier: 20240315120000, 20240315120115. Each migration receives a unique identifier based on when it was created, eliminating numbering conflicts. When two developers create migrations simultaneously, each has a different timestamp. Merging branches combines both migrations without renumbering. The migration system sorts by timestamp to determine execution order. This approach has become the standard in modern frameworks.

Up/Down Migration Pattern explicitly defines forward and backward operations. The up method contains changes to apply. The down method contains changes to revert. This separation makes rollback logic explicit and reviewable.

class AddIndexToUsers < ActiveRecord::Migration[7.0]
  def up
    add_index :users, :email
  end
  
  def down
    remove_index :users, :email
  end
end

Explicit up/down methods provide clarity but require writing rollback logic for every change. Developers sometimes write only the up method, leaving down empty, which breaks rollback capability.

Reversible Change Pattern defines only forward operations when the framework can automatically infer the reverse. Adding a column can be automatically reversed by removing that column. Creating a table reverses by dropping the table. This pattern reduces code duplication and ensures rollback logic stays synchronized with forward logic.

class CreateProducts < ActiveRecord::Migration[7.0]
  def change
    create_table :products do |t|
      t.string :name
      t.decimal :price
      t.timestamps
    end
  end
end

The change method works for many operations but cannot automatically reverse complex data transformations or custom SQL statements. These cases require explicit up/down methods.

Online Migration Strategy applies schema changes without taking the application offline. This approach requires careful sequencing because the database must remain usable while migrations execute. Add new columns as nullable or with defaults to avoid locking tables while backfilling values. Use background jobs for data transformations instead of executing them within the migration. Create new indexes concurrently to prevent blocking writes.

Offline Migration Strategy takes the application offline during schema changes. This approach simplifies migrations because no concurrent activity occurs. Tables can be locked, data transformed, and constraints added without considering concurrent access. However, downtime impacts users and becomes unacceptable for high-availability systems.

Expand-Contract Pattern splits breaking changes across multiple deployments. The expand phase adds new schema elements (columns, tables) without removing old ones. The application code deploys to use both old and new schemas. The contract phase removes old schema elements after confirming the new schema works correctly. This three-phase approach (expand, migrate code, contract) eliminates downtime for most changes.

For example, renaming a column:

1. Expand: Add the new column alongside the old one
2. Deploy code that writes to both columns and reads from the new column
3. Backfill the new column with data from the old column
4. Deploy code that no longer references the old column
5. Contract: Remove the old column

Blue-Green Deployment Integration maintains two complete environments: blue (current production) and green (new version). Migrations run on the green environment. After verification, traffic switches from blue to green. If problems arise, traffic switches back to blue. This strategy requires databases support either separate schemas or multi-version compatibility.

Ruby Implementation

Ruby provides multiple migration frameworks, with Active Record migrations being the most common. Active Record ships with Rails but can run standalone. Sequel offers an alternative with different design choices. Understanding both frameworks illuminates different approaches to the same problem.

Active Record Migration Structure defines migrations as classes inheriting from ActiveRecord::Migration. The version number in the class declaration indicates which Active Record features are available. Migrations sit in db/migrate directory with filenames containing timestamp and description.

# db/migrate/20240315120000_create_articles.rb
class CreateArticles < ActiveRecord::Migration[7.0]
  def change
    create_table :articles do |t|
      t.string :title, null: false
      t.text :body
      t.references :author, foreign_key: { to_table: :users }
      t.integer :view_count, default: 0
      t.timestamps
    end
    
    add_index :articles, :title
    add_index :articles, [:author_id, :created_at]
  end
end

The change method specifies operations that Active Record can automatically reverse. The references method creates a foreign key column with appropriate index. The timestamps method adds created_at and updated_at columns.

Running Active Record Migrations uses rake tasks that inspect db/migrate for new migrations and execute them in order. The schema_migrations table tracks which versions have run.

# Run all pending migrations
rake db:migrate

# Rollback the last migration
rake db:rollback

# Rollback 3 migrations
rake db:rollback STEP=3

# Migrate to a specific version
rake db:migrate VERSION=20240315120000

# Show migration status
rake db:migrate:status

Sequel Migration Structure offers more flexibility than Active Record. Sequel supports both simple numbered migrations and timestamped migrations. The migration block receives a database connection for executing operations.

# db/migrate/001_create_articles.rb
Sequel.migration do
  change do
    create_table(:articles) do
      primary_key :id
      String :title, null: false
      Text :body
      foreign_key :author_id, :users
      Integer :view_count, default: 0
      DateTime :created_at
      DateTime :updated_at
      
      index :title
      index [:author_id, :created_at]
    end
  end
end

Sequel's syntax differs slightly but provides similar functionality. Column types are capitalized constants (String, Integer, Text). The foreign_key method creates the reference and constraint.

Reversible Blocks handle operations where automatic reversal is impossible or unclear. The up and down blocks explicitly define forward and reverse operations.

class AddCustomTimestamps < ActiveRecord::Migration[7.0]
  def change
    reversible do |dir|
      dir.up do
        execute <<-SQL
          ALTER TABLE articles 
          ADD COLUMN published_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        SQL
      end
      
      dir.down do
        remove_column :articles, :published_at
      end
    end
  end
end

Data Migrations modify data rather than schema. These require careful handling because they might execute for extended periods on large tables and cannot always be automatically reversed.

class NormalizePhoneNumbers < ActiveRecord::Migration[7.0]
  def up
    User.find_each do |user|
      next if user.phone.blank?
      
      normalized = user.phone.gsub(/\D/, '')
      user.update_column(:phone, normalized)
    end
  end
  
  def down
    # Cannot reverse - original format is lost
    raise ActiveRecord::IrreversibleMigration
  end
end

The find_each method loads records in batches to avoid loading millions of records into memory. The update_column method bypasses validations and callbacks for performance. The down method raises an exception because the original phone number format cannot be reconstructed.

Conditional Migration Execution checks current state before applying changes. This pattern makes migrations idempotent and safe to retry after failures.

class AddEmailToUsersIfNotExists < ActiveRecord::Migration[7.0]
  def change
    unless column_exists?(:users, :email)
      add_column :users, :email, :string
    end
    
    unless index_exists?(:users, :email)
      add_index :users, :email
    end
  end
end

Transaction Control wraps migrations in transactions by default, but some operations require explicit transaction control. Creating indexes concurrently cannot occur within a transaction. Multiple separate operations might need individual transactions.

class AddConcurrentIndex < ActiveRecord::Migration[7.0]
  disable_ddl_transaction!
  
  def change
    add_index :articles, :published_at, algorithm: :concurrently
  end
end

The disable_ddl_transaction! method prevents automatic transaction wrapping. The concurrent index creation runs without blocking writes but takes longer to complete.

Common Patterns

Additive Changes First adds new elements before removing old ones. This pattern maintains backward compatibility during deployment. Add a new column before removing the old column. Create a new table before dropping the old table. Deploy code changes between the addition and removal steps.

# Step 1: Add new column
class AddUserEmail < ActiveRecord::Migration[7.0]
  def change
    add_column :users, :email_address, :string
  end
end

# Deploy code that writes to both user_email and email_address

# Step 2: Backfill data
class BackfillEmailAddresses < ActiveRecord::Migration[7.0]
  def up
    User.where(email_address: nil).find_each do |user|
      user.update_column(:email_address, user.user_email)
    end
  end
  
  def down
    # No-op or clear the column
  end
end

# Deploy code that only reads from email_address

# Step 3: Remove old column
class RemoveUserEmail < ActiveRecord::Migration[7.0]
  def change
    remove_column :users, :user_email
  end
end

Null Column Defaults add nullable columns before enforcing constraints. A migration that adds a NOT NULL column requires backfilling existing rows, which locks the table. Add the column as nullable, backfill values through a data migration or background job, then add the NOT NULL constraint in a separate migration.

# Migration 1: Add nullable column
class AddStatusToOrders < ActiveRecord::Migration[7.0]
  def change
    add_column :orders, :status, :string
  end
end

# Migration 2: Set default values
class SetDefaultOrderStatus < ActiveRecord::Migration[7.0]
  def up
    Order.where(status: nil).update_all(status: 'pending')
  end
  
  def down
    # Optionally clear the values
  end
end

# Migration 3: Add constraint
class AddStatusConstraint < ActiveRecord::Migration[7.0]
  def change
    change_column_null :orders, :status, false
  end
end

Index Creation Strategy creates indexes outside business hours or using concurrent creation. Standard index creation locks the table, preventing writes. Concurrent index creation avoids locks but takes longer.

class AddArticleSearchIndex < ActiveRecord::Migration[7.0]
  disable_ddl_transaction!
  
  def change
    add_index :articles, 
              [:title, :body], 
              algorithm: :concurrently,
              name: 'idx_articles_search'
  end
end

Foreign Key Pattern establishes referential integrity through explicit foreign keys. Add the reference column, optionally add an index, then add the foreign key constraint. This sequence allows data verification before enforcing the constraint.

class AddUserReferencesToOrders < ActiveRecord::Migration[7.0]
  def change
    add_reference :orders, :user, null: true, index: true
    
    # Backfill in separate migration if needed
    
    add_foreign_key :orders, :users
  end
end

Unique Constraint Application checks for duplicates before adding constraints. Unique constraints fail if duplicate values exist. Query for duplicates, resolve them through data migration or manual intervention, then add the constraint.

class AddUniqueEmailConstraint < ActiveRecord::Migration[7.0]
  def change
    # First, find and fix duplicates
    reversible do |dir|
      dir.up do
        # Mark duplicates before constraint
        execute <<-SQL
          UPDATE users u1
          SET email = email || '_' || id
          WHERE id NOT IN (
            SELECT MIN(id)
            FROM users u2
            WHERE u2.email = u1.email
            GROUP BY email
          )
        SQL
      end
    end
    
    add_index :users, :email, unique: true
  end
end

Column Type Changes require careful sequencing for large tables. Changing a column type can lock the table for extended periods. The safer approach creates a new column, migrates data, switches application code, and removes the old column.

# Migration 1: Add new column
class AddPriceDecimal < ActiveRecord::Migration[7.0]
  def change
    add_column :products, :price_decimal, :decimal, precision: 10, scale: 2
  end
end

# Migration 2: Copy data
class MigratePriceToDecimal < ActiveRecord::Migration[7.0]
  def up
    Product.find_each do |product|
      product.update_column(:price_decimal, product.price_integer / 100.0)
    end
  end
  
  def down
    Product.find_each do |product|
      product.update_column(:price_integer, (product.price_decimal * 100).to_i)
    end
  end
end

# Migration 3: Remove old column
class RemovePriceInteger < ActiveRecord::Migration[7.0]
  def change
    remove_column :products, :price_integer
  end
end

Table Splitting divides a large table into multiple tables. Create the new tables, migrate data through background jobs, update application code to use new tables, then remove old table.

class SplitUserProfile < ActiveRecord::Migration[7.0]
  def change
    create_table :user_profiles do |t|
      t.references :user, foreign_key: true, null: false
      t.string :bio
      t.string :avatar_url
      t.date :birth_date
      t.timestamps
    end
    
    # Data migration in separate migration
    # Then remove columns from users table
  end
end

Practical Examples

Adding Column With Default shows the safe pattern for adding columns to existing tables. Large tables require special handling because adding columns with defaults can lock the table.

class AddActiveToUsers < ActiveRecord::Migration[7.0]
  def change
    # PostgreSQL 11+ handles this efficiently
    add_column :users, :active, :boolean, default: true, null: false
  end
end

For older PostgreSQL versions or very large tables, split into steps:

# Step 1: Add nullable column
class AddActiveToUsers < ActiveRecord::Migration[7.0]
  def change
    add_column :users, :active, :boolean
  end
end

# Step 2: Backfill in batches
class BackfillActiveUsers < ActiveRecord::Migration[7.0]
  disable_ddl_transaction!
  
  def up
    User.in_batches(of: 1000) do |batch|
      batch.update_all(active: true)
      sleep(0.01) # Brief pause between batches
    end
  end
  
  def down
    # Optional: reset values
  end
end

# Step 3: Add constraints
class AddActiveConstraints < ActiveRecord::Migration[7.0]
  def change
    change_column_null :users, :active, false
    change_column_default :users, :active, true
  end
end

Renaming Column Safely avoids breaking deployments where old code expects the old column name. The three-phase approach ensures zero downtime.

# Phase 1: Add new column
class AddEmailToUsers < ActiveRecord::Migration[7.0]
  def change
    add_column :users, :email, :string
    add_index :users, :email
  end
end

# Deploy code that writes to both columns:
# before_save :sync_email_columns
# def sync_email_columns
#   self.email = email_address
#   self.email_address = email
# end

# Phase 2: Copy data
class CopyEmailAddressToEmail < ActiveRecord::Migration[7.0]
  def up
    User.where(email: nil).find_each do |user|
      user.update_column(:email, user.email_address)
    end
  end
  
  def down
    # No-op
  end
end

# Deploy code that uses only email column

# Phase 3: Remove old column
class RemoveEmailAddress < ActiveRecord::Migration[7.0]
  def change
    remove_column :users, :email_address
  end
end

Creating Join Table establishes many-to-many relationships with proper indexing and constraints.

class CreateUserGroups < ActiveRecord::Migration[7.0]
  def change
    create_join_table :users, :groups do |t|
      t.index :user_id
      t.index :group_id
      t.index [:user_id, :group_id], unique: true
      t.timestamps
    end
    
    add_foreign_key :groups_users, :users
    add_foreign_key :groups_users, :groups
  end
end

The unique composite index prevents duplicate memberships. Separate indexes on user_id and group_id optimize common queries. Foreign keys ensure referential integrity.

Polymorphic Association creates tables that reference multiple other tables through a type/id combination.

class CreateComments < ActiveRecord::Migration[7.0]
  def change
    create_table :comments do |t|
      t.text :body, null: false
      t.references :commentable, polymorphic: true, null: false
      t.references :user, foreign_key: true
      t.timestamps
    end
    
    add_index :comments, [:commentable_type, :commentable_id]
  end
end

The commentable_type stores the class name (Article, Product), while commentable_id stores the foreign key. The composite index optimizes queries for all comments on a specific record.

Adding JSONB Column creates schemaless storage within PostgreSQL tables.

class AddMetadataToArticles < ActiveRecord::Migration[7.0]
  def change
    add_column :articles, :metadata, :jsonb, default: {}, null: false
    
    add_index :articles, :metadata, using: :gin
  end
end

The GIN index enables efficient queries against JSON content. The default empty hash prevents null handling issues.

Table Inheritance splits data across parent and child tables for single-table inheritance alternatives.

class CreateAccountTypes < ActiveRecord::Migration[7.0]
  def change
    create_table :accounts do |t|
      t.string :type, null: false
      t.string :name
      t.timestamps
    end
    
    create_table :business_accounts do |t|
      t.references :account, foreign_key: true, null: false
      t.string :tax_id
      t.integer :employee_count
      t.timestamps
    end
    
    create_table :personal_accounts do |t|
      t.references :account, foreign_key: true, null: false
      t.date :birth_date
      t.string :ssn_encrypted
      t.timestamps
    end
    
    add_index :accounts, :type
  end
end

This pattern provides better data organization than single-table inheritance for types with significantly different attributes.

Enum Column Management creates constrained string or integer columns for status values.

class AddOrderStatus < ActiveRecord::Migration[7.0]
  def change
    # Using string enum for readability
    add_column :orders, :status, :string, default: 'pending', null: false
    
    # Create check constraint
    reversible do |dir|
      dir.up do
        execute <<-SQL
          ALTER TABLE orders
          ADD CONSTRAINT check_order_status
          CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled'))
        SQL
      end
      
      dir.down do
        execute 'ALTER TABLE orders DROP CONSTRAINT check_order_status'
      end
    end
    
    add_index :orders, :status
  end
end

Check constraints enforce valid values at the database level, preventing invalid data even from direct SQL modifications.

Composite Primary Keys establish multi-column primary keys for specific use cases.

class CreateDailyMetrics < ActiveRecord::Migration[7.0]
  def change
    create_table :daily_metrics, primary_key: [:metric_date, :metric_type] do |t|
      t.date :metric_date, null: false
      t.string :metric_type, null: false
      t.integer :value
      t.timestamps
    end
    
    add_index :daily_metrics, :metric_date
    add_index :daily_metrics, :metric_type
  end
end

Concurrent Index Removal drops indexes without locking tables for writes.

class RemoveUnusedIndexes < ActiveRecord::Migration[7.0]
  disable_ddl_transaction!
  
  def change
    remove_index :articles, 
                 name: 'index_articles_on_legacy_field',
                 algorithm: :concurrently
  end
end

Common Pitfalls

Locking Large Tables occurs when migrations block concurrent access during execution. Adding columns with NOT NULL and defaults, creating indexes without the concurrent option, and changing column types all acquire locks that prevent reads or writes. On tables with millions of rows, these operations can lock the table for minutes or hours.

# PROBLEM: Locks table during entire operation
class AddRequiredColumn < ActiveRecord::Migration[7.0]
  def change
    add_column :users, :status, :string, null: false, default: 'active'
  end
end

# SOLUTION: Multi-step approach
class AddRequiredColumnSafely < ActiveRecord::Migration[7.0]
  def change
    add_column :users, :status, :string
  end
end

# Then backfill through background job
# Then add constraints in separate migration

Irreversible Data Loss happens when migrations destroy data without safe rollback paths. Dropping columns, dropping tables, and data transformations that lose information cannot be reversed. Migrations must preserve data during removal operations or explicitly mark themselves as irreversible.

# PROBLEM: Rollback loses all data
class RemoveUserProfiles < ActiveRecord::Migration[7.0]
  def change
    drop_table :user_profiles
  end
end

# SOLUTION: Preserve structure for potential rollback
class RemoveUserProfiles < ActiveRecord::Migration[7.0]
  def up
    drop_table :user_profiles
  end
  
  def down
    create_table :user_profiles do |t|
      # Recreate structure for rollback
      t.references :user, foreign_key: true
      t.text :bio
      t.timestamps
    end
  end
end

Migration Order Dependencies arise when migrations assume specific execution order or when merged branches contain conflicting migrations. Timestamp collisions from different time zones, dependencies between migrations, and race conditions during parallel execution cause failures.

# PROBLEM: Migration assumes previous migration ran
class AddForeignKey < ActiveRecord::Migration[7.0]
  def change
    # Assumes user_id column exists from previous migration
    add_foreign_key :orders, :users
  end
end

# SOLUTION: Check prerequisites or combine operations
class AddForeignKey < ActiveRecord::Migration[7.0]
  def change
    unless column_exists?(:orders, :user_id)
      add_reference :orders, :user, foreign_key: true
    else
      add_foreign_key :orders, :users unless foreign_key_exists?(:orders, :users)
    end
  end
end

Production vs Development Differences occur when migrations work in development but fail in production due to data volume, database version differences, or permission restrictions. Development databases contain minimal test data while production has millions of rows. Development might use SQLite while production uses PostgreSQL.

# PROBLEM: Works on empty dev database, fails on production
class ChangeColumnType < ActiveRecord::Migration[7.0]
  def change
    change_column :products, :price, :decimal
  end
end

# SOLUTION: Test with production-scale data or use safer approach
# Create new column, migrate data gradually, switch column names

Mixing Schema and Data Changes combines structure modifications with data transformations in a single migration. When rollback occurs, schema changes reverse but data changes may not, leaving inconsistent state.

# PROBLEM: Schema and data changes together
class AddAndPopulateSlug < ActiveRecord::Migration[7.0]
  def change
    add_column :articles, :slug, :string
    
    Article.find_each do |article|
      article.update_column(:slug, article.title.parameterize)
    end
    
    add_index :articles, :slug, unique: true
  end
end

# SOLUTION: Separate concerns
class AddSlugColumn < ActiveRecord::Migration[7.0]
  def change
    add_column :articles, :slug, :string
  end
end

class PopulateSlugs < ActiveRecord::Migration[7.0]
  def up
    Article.find_each do |article|
      article.update_column(:slug, article.title.parameterize)
    end
  end
  
  def down
    # Clear slugs or mark as irreversible
  end
end

class AddSlugIndex < ActiveRecord::Migration[7.0]
  def change
    add_index :articles, :slug, unique: true
  end
end

Not Testing Rollbacks leaves the down method untested until production rollback becomes necessary. Many migrations define only up methods or create down methods that fail when executed. Testing rollback in development catches these issues.

# PROBLEM: Untested rollback
class ModifyUsers < ActiveRecord::Migration[7.0]
  def up
    add_column :users, :full_name, :string
    User.update_all("full_name = first_name || ' ' || last_name")
    remove_column :users, :first_name
    remove_column :users, :last_name
  end
  
  def down
    # This rollback loses the split between first and last names
    add_column :users, :first_name, :string
    add_column :users, :last_name, :string
    # Cannot reconstruct first_name and last_name from full_name
  end
end

Transaction Isolation Issues cause race conditions when migrations execute while the application serves requests. Changes become visible partway through migration execution, leading to errors when code expects complete schema changes.

# PROBLEM: Application code sees partial migration state
class SplitNameFields < ActiveRecord::Migration[7.0]
  def change
    add_column :users, :first_name, :string
    add_column :users, :last_name, :string
    # Between these steps, application code might try to use the incomplete schema
    remove_column :users, :name
  end
end

# SOLUTION: Use expand-contract pattern with backward-compatible intermediate state

Assuming Database Features writes migrations using features not available in all target databases. PostgreSQL array columns, MySQL's FULLTEXT indexes, and database-specific functions fail when the application switches database systems.

# PROBLEM: PostgreSQL-specific feature
class AddTagsArray < ActiveRecord::Migration[7.0]
  def change
    add_column :articles, :tags, :string, array: true, default: []
  end
end

# SOLUTION: Use database-agnostic approach or check database type
class AddTags < ActiveRecord::Migration[7.0]
  def change
    if connection.adapter_name == 'PostgreSQL'
      add_column :articles, :tags, :string, array: true, default: []
    else
      # Alternative approach for other databases
      create_table :article_tags do |t|
        t.references :article, foreign_key: true
        t.string :tag
      end
    end
  end
end

Missing Indexes on Foreign Keys creates performance problems when joining tables or deleting parent records. Rails automatically creates indexes on foreign key columns when using references, but manual foreign key addition often forgets the index.

# PROBLEM: Foreign key without index
class AddUserToOrders < ActiveRecord::Migration[7.0]
  def change
    add_column :orders, :user_id, :integer
    add_foreign_key :orders, :users
  end
end

# SOLUTION: Always index foreign keys
class AddUserToOrders < ActiveRecord::Migration[7.0]
  def change
    add_reference :orders, :user, foreign_key: true, index: true
  end
end

String Length Limits vary across databases, causing migrations to fail when moving between systems. MySQL's VARCHAR requires explicit length limits while PostgreSQL accepts unlimited text.

# PROBLEM: No length specified
class AddEmail < ActiveRecord::Migration[7.0]
  def change
    add_column :users, :email, :string
  end
end

# BETTER: Explicit reasonable limit
class AddEmail < ActiveRecord::Migration[7.0]
  def change
    add_column :users, :email, :string, limit: 255
  end
end

Reference

Common Migration Methods

Column Type Reference

Migration Command Reference

Best Practices Checklist

Migration State Verification

Common Migration Patterns