fernandezbaptiste/database-migrations

Database Migration Patterns for Rails 8

Overview

Safe database migrations are critical for production stability:

• Zero-downtime deployments
• Reversible migrations
• Proper indexing
• Data integrity constraints
• Performance considerations

Quick Start

# Generate migration
bin/rails generate migration AddStatusToEvents status:integer

# Run migrations
bin/rails db:migrate

# Rollback
bin/rails db:rollback

# Check status
bin/rails db:migrate:status

Safety Checklist

Migration Safety:
- [ ] Migration is reversible (has down or uses change)
- [ ] Large tables use batching for updates
- [ ] Indexes added concurrently (if needed)
- [ ] Foreign keys have indexes
- [ ] NOT NULL added in two steps (for existing columns)
- [ ] Default values don't lock table
- [ ] Tested rollback locally

Safe Migration Patterns

Pattern 1: Add Column (Safe)

# db/migrate/20240115000001_add_status_to_events.rb
class AddStatusToEvents < ActiveRecord::Migration[8.0]
  def change
    add_column :events, :status, :integer, default: 0, null: false
  end
end

Pattern 2: Add Column with NOT NULL (Two-Step)

For existing tables with data, add NOT NULL in two migrations:

# Step 1: Add column with default (allows NULL temporarily)
# db/migrate/20240115000001_add_priority_to_tasks.rb
class AddPriorityToTasks < ActiveRecord::Migration[8.0]
  def change
    add_column :tasks, :priority, :integer, default: 0
  end
end

# Step 2: Add NOT NULL constraint after backfill
# db/migrate/20240115000002_add_not_null_to_tasks_priority.rb
class AddNotNullToTasksPriority < ActiveRecord::Migration[8.0]
  def change
    change_column_null :tasks, :priority, false
  end
end

Pattern 3: Add Index (Production Safe)

# db/migrate/20240115000001_add_index_to_events_status.rb
class AddIndexToEventsStatus < ActiveRecord::Migration[8.0]
  disable_ddl_transaction!

  def change
    add_index :events, :status, algorithm: :concurrently, if_not_exists: true
  end
end

Pattern 4: Add Foreign Key with Index

# db/migrate/20240115000001_add_account_to_events.rb
class AddAccountToEvents < ActiveRecord::Migration[8.0]
  def change
    add_reference :events, :account, null: false, foreign_key: true, index: true
  end
end

Pattern 5: Rename Column (Safe)

# db/migrate/20240115000001_rename_name_to_title_on_events.rb
class RenameNameToTitleOnEvents < ActiveRecord::Migration[8.0]
  def change
    rename_column :events, :name, :title
  end
end

Pattern 6: Remove Column (Safe)

First, remove references in code, then migrate:

# db/migrate/20240115000001_remove_legacy_field_from_events.rb
class RemoveLegacyFieldFromEvents < ActiveRecord::Migration[8.0]
  def change
    # safety_assured tells strong_migrations this is intentional
    safety_assured { remove_column :events, :legacy_field, :string }
  end
end

Pattern 7: Add Enum Column

# db/migrate/20240115000001_add_status_enum_to_orders.rb
class AddStatusEnumToOrders < ActiveRecord::Migration[8.0]
  def change
    # Use integer for Rails enum
    add_column :orders, :status, :integer, default: 0, null: false

    # Add index for queries
    add_index :orders, :status
  end
end

In model:

class Order < ApplicationRecord
  enum :status, { pending: 0, confirmed: 1, shipped: 2, delivered: 3, cancelled: 4 }
end

Dangerous Operations (Avoid)

DON'T: Change Column Type Directly

# DANGEROUS - can lose data or lock table
class ChangeColumnType < ActiveRecord::Migration[8.0]
  def change
    change_column :events, :budget, :decimal  # DON'T DO THIS
  end
end

DO: Add New Column, Migrate Data, Remove Old

# Step 1: Add new column
class AddBudgetDecimalToEvents < ActiveRecord::Migration[8.0]
  def change
    add_column :events, :budget_decimal, :decimal, precision: 10, scale: 2
  end
end

# Step 2: Backfill data (in a rake task or separate migration)
class BackfillEventsBudget < ActiveRecord::Migration[8.0]
  disable_ddl_transaction!

  def up
    Event.in_batches.update_all("budget_decimal = budget")
  end

  def down
    # Data migration, no rollback needed
  end
end

# Step 3: Remove old column (after code updated)
class RemoveOldBudgetFromEvents < ActiveRecord::Migration[8.0]
  def change
    safety_assured { remove_column :events, :budget, :integer }
    rename_column :events, :budget_decimal, :budget
  end
end

Data Migrations

Safe Backfill Pattern

class BackfillEventStatus < ActiveRecord::Migration[8.0]
  disable_ddl_transaction!

  def up
    Event.unscoped.in_batches(of: 1000) do |batch|
      batch.where(status: nil).update_all(status: 0)
      sleep(0.1) # Reduce database load
    end
  end

  def down
    # No rollback for data migration
  end
end

Using Background Job for Large Tables

# Migration just adds column
class AddProcessedAtToEvents < ActiveRecord::Migration[8.0]
  def change
    add_column :events, :processed_at, :datetime
  end
end

# Separate job for backfill
class BackfillProcessedAtJob < ApplicationJob
  def perform(start_id, end_id)
    Event.where(id: start_id..end_id, processed_at: nil)
         .update_all(processed_at: Time.current)
  end
end

# Rake task to enqueue
# lib/tasks/backfill.rake
namespace :backfill do
  task processed_at: :environment do
    Event.in_batches(of: 10_000) do |batch|
      BackfillProcessedAtJob.perform_later(batch.minimum(:id), batch.maximum(:id))
    end
  end
end

Index Strategies

Composite Indexes

# For queries: WHERE account_id = ? AND status = ?
add_index :events, [:account_id, :status]

# Order matters! This index helps:
# - WHERE account_id = ?
# - WHERE account_id = ? AND status = ?
# But NOT:
# - WHERE status = ?

Partial Indexes

# Index only active records
add_index :events, :event_date, where: "status = 0", name: "index_events_on_date_active"

# Index only non-null values
add_index :users, :reset_token, where: "reset_token IS NOT NULL"

Unique Indexes

# Unique constraint
add_index :users, :email, unique: true

# Unique within scope
add_index :event_vendors, [:event_id, :vendor_id], unique: true

Foreign Keys

Adding Foreign Keys

class AddForeignKeys < ActiveRecord::Migration[8.0]
  def change
    # With automatic index
    add_reference :events, :venue, foreign_key: true

    # To existing column
    add_foreign_key :events, :accounts

    # With specific column name
    add_foreign_key :events, :users, column: :organizer_id
  end
end

Foreign Key Options

# ON DELETE CASCADE (delete children when parent deleted)
add_foreign_key :comments, :posts, on_delete: :cascade

# ON DELETE NULLIFY (set to NULL when parent deleted)
add_foreign_key :posts, :users, column: :author_id, on_delete: :nullify

# ON DELETE RESTRICT (prevent parent deletion)
add_foreign_key :orders, :users, on_delete: :restrict

Strong Migrations Gem

Installation

# Gemfile
gem 'strong_migrations'

Configuration

# config/initializers/strong_migrations.rb
StrongMigrations.start_after = 20240101000000

# Target version for safe operations
StrongMigrations.target_version = 16  # PostgreSQL version

# Custom checks
StrongMigrations.add_check do |method, args|
  if method == :add_column && args[1] == :events
    stop! "Check with team before modifying events table"
  end
end

Handling Warnings

class AddColumnWithDefault < ActiveRecord::Migration[8.0]
  def change
    # Tell strong_migrations this is safe
    safety_assured do
      add_column :events, :priority, :integer, default: 0, null: false
    end
  end
end

Reversible Migrations

Using change (Automatic Reversal)

class CreateEvents < ActiveRecord::Migration[8.0]
  def change
    create_table :events do |t|
      t.string :name, null: false
      t.date :event_date
      t.references :account, null: false, foreign_key: true
      t.timestamps
    end

    add_index :events, [:account_id, :event_date]
  end
end

Using up/down (Manual Reversal)

class ChangeEventsStructure < ActiveRecord::Migration[8.0]
  def up
    # Complex change
    execute <<-SQL
      ALTER TABLE events ADD CONSTRAINT check_positive_budget
      CHECK (budget_cents >= 0)
    SQL
  end

  def down
    execute <<-SQL
      ALTER TABLE events DROP CONSTRAINT check_positive_budget
    SQL
  end
end

Irreversible Migrations

class DropLegacyTable < ActiveRecord::Migration[8.0]
  def up
    drop_table :legacy_events
  end

  def down
    raise ActiveRecord::IrreversibleMigration, "Cannot restore dropped table"
  end
end

Testing Migrations

Test Rollback

# Migrate and rollback
bin/rails db:migrate
bin/rails db:rollback
bin/rails db:migrate

# Check for issues
bin/rails db:migrate:status

Schema Check

# spec/db/schema_spec.rb
RSpec.describe "Database Schema" do
  it "has all foreign keys indexed" do
    foreign_keys = ActiveRecord::Base.connection.foreign_keys(:events)
    indexes = ActiveRecord::Base.connection.indexes(:events)

    foreign_keys.each do |fk|
      indexed = indexes.any? { |idx| idx.columns.first == fk.column }
      expect(indexed).to be(true), "Missing index for #{fk.column}"
    end
  end
end

Performance Tips

Avoid Table Locks

# DON'T - Locks entire table
add_index :large_table, :column

# DO - Non-blocking
disable_ddl_transaction!
add_index :large_table, :column, algorithm: :concurrently

Batch Operations

# DON'T - Updates all at once
Event.update_all(status: 0)

# DO - Updates in batches
Event.in_batches(of: 1000) do |batch|
  batch.update_all(status: 0)
end

Checklist

• [ ] Migration is reversible
• [ ] Indexes on foreign keys
• [ ] Concurrent index creation for large tables
• [ ] NOT NULL added safely (two-step)
• [ ] Data migrations use batching
• [ ] Tested rollback locally
• [ ] strong_migrations gem checks pass
• [ ] No table locks during deploy