The Ultimate Rails Migration Guide

Rails migrations are Ruby classes that modify your database schema over time. Instead of writing raw SQL, you use a Ruby DSL that's database-agnostic and version-controlled. To create a new migration, use the rails generate migration command (or rails g migration for short).

Basic Migration Generator

Create an empty migration file with a descriptive name:

rails generate migration AddEmailToUsers

This creates a timestamped file in db/migrate/:

# db/migrate/20260106120000_add_email_to_users.rb
class AddEmailToUsers < ActiveRecord::Migration[7.1]
  def change
  end
end

Generate with Column Definitions

Rails can automatically generate the migration body if you follow naming conventions. Use AddXxxToYyy or RemoveXxxFromYyy patterns:

# Add a single column
rails g migration AddEmailToUsers email:string

# Add multiple columns
rails g migration AddDetailsToUsers email:string age:integer

# Add column with index
rails g migration AddEmailToUsers email:string:index

# Add column with unique index
rails g migration AddEmailToUsers email:string:uniq

The generated migration includes the column definition:

class AddEmailToUsers < ActiveRecord::Migration[7.1]
  def change
    add_column :users, :email, :string
    add_index :users, :email, unique: true
  end
end

Common Error

If you see this error when generating a migration:

Another migration is already named add_email_to_users: db/migrate/20260105_add_email_to_users.rb

Fix: Use a unique, descriptive name for your migration. Rails prevents duplicate migration names even with different timestamps.

After creating migrations, you need to run them to apply the changes to your database. Rails tracks which migrations have been run in the schema_migrations table, so each migration only runs once.

Run All Pending Migrations

rails db:migrate

You'll see output like:

== 20260106120000 AddEmailToUsers: migrating ==================================
-- add_column(:users, :email, :string)
   -> 0.0012s
== 20260106120000 AddEmailToUsers: migrated (0.0013s) =========================

Run Migrations in a Specific Environment

# Production
RAILS_ENV=production rails db:migrate

# Test
RAILS_ENV=test rails db:migrate

Common Error: Pending Migrations

If you try to start your Rails server with pending migrations:

Migrations are pending. To resolve this issue, run:
        bin/rails db:migrate RAILS_ENV=development

ActiveRecord::PendingMigrationError

Fix: Run rails db:migrate to apply pending migrations before starting your server.

Common Error: Migration Failed

If a migration fails partway through:

== 20260106120000 AddEmailToUsers: migrating ==================================
-- add_column(:users, :email, :string)
rails aborted!
StandardError: An error has occurred, this and all later migrations canceled:

PG::DuplicateColumn: ERROR:  column "email" of relation "users" already exists

Fix: The column already exists. Either remove the existing column first, or modify your migration to check for existence:

def change
  add_column :users, :email, :string unless column_exists?(:users, :email)
end

Before running or rolling back migrations, you often want to see which migrations have been applied and which are pending. Rails provides the db:migrate:status command for this.

View Migration Status

rails db:migrate:status

Output shows each migration's status:

database: myapp_development

 Status   Migration ID    Migration Name
--------------------------------------------------
   up     20260101120000  Create users
   up     20260102120000  Add name to users
  down    20260106120000  Add email to users

Status meanings:

• up - Migration has been applied to the database
• down - Migration has not been run (pending)

Common Issue: Missing Migration File

If you see this in your status output:

 Status   Migration ID    Migration Name
--------------------------------------------------
   up     20260101120000  Create users
   up     20260102120000  ********** NO FILE **********
   up     20260103120000  Add name to users

This means a migration was run but the file no longer exists (perhaps deleted or on another branch). The migration is recorded in schema_migrations but Rails can't find the corresponding file.

Fix: If you're sure the migration should be removed from history, you can delete the record:

rails runner "ActiveRecord::SchemaMigration.where(version: '20260102120000').delete_all"

Check for Any Pending Migrations

In Rails console, you can programmatically check:

# Returns true if there are pending migrations
ActiveRecord::Migration.check_pending!

Rolling back a migration reverses the changes it made to your database. This is useful during development when you need to modify a migration you just created, or when you need to undo changes.

Rollback the Last Migration

rails db:rollback

Output:

== 20260106120000 AddEmailToUsers: reverting ==================================
-- remove_column(:users, :email, :string)
   -> 0.0015s
== 20260106120000 AddEmailToUsers: reverted (0.0234s) =========================

Rollback Multiple Migrations

# Rollback last 3 migrations
rails db:rollback STEP=3

Rollback to a Specific Version

# Rollback to (but not including) this migration
rails db:migrate VERSION=20260101120000

Common Error: IrreversibleMigration

If you try to rollback a migration that can't be reversed:

rails aborted!
ActiveRecord::IrreversibleMigration:

This migration uses remove_column, which is not automatically reversible.
To make the migration reversible you can either:
1. Define #up and #down methods in place of the #change method.
2. Use the #reversible method to define reversible behavior.

Fix: Some operations like remove_column in a change method lose information (the column type) needed to reverse. Specify the column type:

# This is reversible - Rails knows how to re-add the column
def change
  remove_column :users, :email, :string
end

# Or use up/down for complex cases
def up
  remove_column :users, :email
end

def down
  add_column :users, :email, :string
end

Common Error: Column Referenced by Foreign Key

PG::DependentObjectsStillExist: ERROR: cannot drop column email because other objects depend on it

Fix: Remove the foreign key constraint first, then the column:

def change
  remove_foreign_key :orders, :users
  remove_column :users, :email, :string
end

Sometimes you need to undo several migrations at once, either to return to a known good state or to rework a series of related changes.

Using STEP

# Rollback the last 3 migrations
rails db:rollback STEP=3

Using VERSION

Rollback all migrations after a specific version:

# Rollback to version 20260101120000 (this migration stays applied)
rails db:migrate VERSION=20260101120000

Redo Migrations

Rollback and re-run migrations in one command:

# Redo last migration
rails db:migrate:redo

# Redo last 3 migrations
rails db:migrate:redo STEP=3

Common Error: Partial Rollback Failure

If one migration in a series fails to rollback:

== 20260103120000 AddIndexToUsers: reverting ==================================
rails aborted!
ActiveRecord::IrreversibleMigration

Fix: You'll need to manually fix the problematic migration by adding proper up/down methods, then continue the rollback.

Sometimes you need to run or re-run a specific migration without affecting others. This is useful for debugging or when migrations get out of sync.

Run Up to a Specific Version

# Run all migrations up to and including this version
rails db:migrate VERSION=20260106120000

Run a Single Migration (Up or Down)

# Run a specific migration's "up" method
rails db:migrate:up VERSION=20260106120000

# Run a specific migration's "down" method
rails db:migrate:down VERSION=20260106120000

Re-run a Specific Migration

# Rollback and re-run a specific migration
rails db:migrate:redo VERSION=20260106120000

Common Error: Migration Already Run

== 20260106120000 AddEmailToUsers: migrating ==================================
   (0.0ms)  Migration 20260106120000 has already been run.

Fix: The migration is already in your schema_migrations table. Use db:migrate:redo to rollback and re-run, or manually remove the version:

rails runner "ActiveRecord::SchemaMigration.where(version: '20260106120000').delete_all"

Rails maintains separate databases for development, test, and production environments. Each environment tracks its own migration state independently.

Specify Environment

# Development (default)
rails db:migrate

# Test
RAILS_ENV=test rails db:migrate

# Production
RAILS_ENV=production rails db:migrate

Prepare Test Database

The test database is usually maintained automatically, but you can manually sync it:

# Load schema into test database
rails db:test:prepare

# Or reset test database completely
RAILS_ENV=test rails db:reset

Production Migrations

In production, you typically run migrations during deployment:

# Common deployment pattern
RAILS_ENV=production rails db:migrate

# Or with release phase (Heroku, Render, etc.)
# This runs automatically during deploy

Common Error: Wrong Database

PG::ConnectionBad: could not connect to server: Connection refused
        Is the server running on host "localhost" and accepting
        TCP/IP connections on port 5432?

Fix: Check your config/database.yml for the correct environment settings and ensure the database server is running.

The create_table method is the foundation of Rails migrations. It creates a new table with the columns you specify, plus automatic id, created_at, and updated_at columns by default.

Using the Generator

# Generate a model with migration
rails g model User name:string email:string age:integer

# Generate just the migration
rails g migration CreateUsers name:string email:string

Basic create_table

class CreateUsers < ActiveRecord::Migration[7.1]
  def change
    create_table :users do |t|
      t.string :name
      t.string :email
      t.integer :age
      t.boolean :admin, default: false
      t.text :bio

      t.timestamps  # adds created_at and updated_at
    end
  end
end

Common Column Types

create_table :products do |t|
  t.string :name           # VARCHAR(255)
  t.text :description      # TEXT
  t.integer :quantity      # INTEGER
  t.bigint :views          # BIGINT
  t.float :rating          # FLOAT
  t.decimal :price, precision: 10, scale: 2  # DECIMAL(10,2)
  t.boolean :active        # BOOLEAN
  t.date :release_date     # DATE
  t.time :available_at     # TIME
  t.datetime :published_at # DATETIME/TIMESTAMP
  t.binary :image_data     # BLOB/BYTEA
  t.json :metadata         # JSON (if supported)

  t.timestamps
end

Common Error: Table Already Exists

PG::DuplicateTable: ERROR:  relation "users" already exists
-- or --
Mysql2::Error: Table 'users' already exists

Fix: The table already exists. Either drop it first or use if_not_exists:

create_table :users, if_not_exists: true do |t|
  # ...
end

Sometimes you need to create a table conditionally, especially when working with existing databases or writing idempotent migrations.

Using if_not_exists Option

class CreateUsers < ActiveRecord::Migration[7.1]
  def change
    create_table :users, if_not_exists: true do |t|
      t.string :name
      t.string :email

      t.timestamps
    end
  end
end

Check Table Existence Manually

class CreateUsers < ActiveRecord::Migration[7.1]
  def change
    unless table_exists?(:users)
      create_table :users do |t|
        t.string :name
        t.timestamps
      end
    end
  end
end

Important Note on Reversibility

When using if_not_exists: true, Rails may not know whether it created the table, which can affect rollback behavior. For cleaner migrations, prefer checking explicitly:

def up
  return if table_exists?(:users)

  create_table :users do |t|
    t.string :name
    t.timestamps
  end
end

def down
  drop_table :users, if_exists: true
end

By default, Rails creates an auto-incrementing id column as the primary key. You can customize this to use a different column name, type, or disable it entirely.

Custom Primary Key Name

create_table :users, primary_key: :user_id do |t|
  t.string :name
  t.timestamps
end

String Primary Key

create_table :countries, id: false, primary_key: :code do |t|
  t.string :code, null: false, primary_key: true
  t.string :name

  t.timestamps
end

No Primary Key

For join tables or special cases:

create_table :users_roles, id: false do |t|
  t.references :user, null: false, foreign_key: true
  t.references :role, null: false, foreign_key: true
end

# Add composite index instead
add_index :users_roles, [:user_id, :role_id], unique: true

Using bigint Instead of integer

# Rails 5.1+ uses bigint by default, but you can be explicit:
create_table :users, id: :bigint do |t|
  t.string :name
  t.timestamps
end

Common Error: Primary Key Mismatch

ActiveRecord::MismatchedForeignKey: Column `user_id` on table `posts`
does not match column `id` on `users`, which has type `bigint`.

Fix: Ensure foreign key columns match the primary key type:

# If users.id is bigint, use bigint for the foreign key
t.bigint :user_id
# or
t.references :user, type: :bigint, foreign_key: true

UUIDs (Universally Unique Identifiers) are useful when you need globally unique IDs, want to hide record counts, or need to generate IDs before inserting records.

PostgreSQL with pgcrypto

# First, enable the extension (run once)
class EnableUuid < ActiveRecord::Migration[7.1]
  def change
    enable_extension 'pgcrypto'
  end
end

# Then create tables with UUID
class CreateUsers < ActiveRecord::Migration[7.1]
  def change
    create_table :users, id: :uuid do |t|
      t.string :name
      t.string :email

      t.timestamps
    end
  end
end

Set UUID as Default for All Tables

In config/application.rb:

config.generators do |g|
  g.orm :active_record, primary_key_type: :uuid
end

UUID Foreign Keys

create_table :posts, id: :uuid do |t|
  t.references :user, type: :uuid, foreign_key: true
  t.string :title

  t.timestamps
end

Common Error: Extension Not Enabled

PG::UndefinedFunction: ERROR:  function gen_random_uuid() does not exist

Fix: Enable the pgcrypto extension:

enable_extension 'pgcrypto'

Composite primary keys use multiple columns together as the primary key. Rails 7.1+ has built-in support for composite primary keys.

Create Table with Composite Primary Key

class CreateTravelRoutes < ActiveRecord::Migration[7.1]
  def change
    create_table :travel_routes, primary_key: [:origin, :destination] do |t|
      t.string :origin, null: false
      t.string :destination, null: false
      t.integer :distance
      t.decimal :price, precision: 10, scale: 2

      t.timestamps
    end
  end
end

In the Model

class TravelRoute < ApplicationRecord
  self.primary_key = [:origin, :destination]
end

# Query using composite key
route = TravelRoute.find(["NYC", "LAX"])

Foreign Keys to Composite Primary Keys

class CreateBookings < ActiveRecord::Migration[7.1]
  def change
    create_table :bookings do |t|
      t.string :route_origin, null: false
      t.string :route_destination, null: false
      t.datetime :departure_time

      t.timestamps
    end

    add_foreign_key :bookings, :travel_routes,
      column: [:route_origin, :route_destination],
      primary_key: [:origin, :destination]
  end
end

Join tables (also called junction tables or bridge tables) connect two tables in a many-to-many relationship. Rails provides a dedicated method for creating them.

Using create_join_table

class CreateJoinTableUsersRoles < ActiveRecord::Migration[7.1]
  def change
    create_join_table :users, :roles do |t|
      t.index [:user_id, :role_id]
      t.index [:role_id, :user_id]
    end
  end
end

This creates a table named roles_users (alphabetical order) with user_id and role_id columns.

Custom Table Name

create_join_table :users, :roles, table_name: :user_roles do |t|
  t.index [:user_id, :role_id], unique: true
end

With Additional Columns

create_join_table :users, :projects do |t|
  t.string :role  # e.g., "owner", "member", "viewer"
  t.datetime :joined_at
  t.index [:user_id, :project_id], unique: true
end

Manual Alternative

create_table :users_roles, id: false do |t|
  t.references :user, null: false, foreign_key: true
  t.references :role, null: false, foreign_key: true
end

add_index :users_roles, [:user_id, :role_id], unique: true

Database comments help document your schema directly in the database. They're visible in database tools and can be useful for teams.

Comment on Table Creation

create_table :users, comment: 'Registered user accounts' do |t|
  t.string :email, comment: 'Primary login identifier'
  t.string :name, comment: 'Display name, not unique'
  t.integer :status, comment: '0=pending, 1=active, 2=suspended'

  t.timestamps
end

Add Comment to Existing Table

change_table_comment :users, from: nil, to: 'Registered user accounts'

# Or without tracking the previous value (not reversible)
change_table_comment :users, 'Registered user accounts'

Add Comment to Existing Column

change_column_comment :users, :status, from: nil, to: '0=pending, 1=active, 2=suspended'

View Comments

Comments appear in db/schema.rb:

create_table "users", comment: "Registered user accounts", force: :cascade do |t|
  t.string "email", comment: "Primary login identifier"
  # ...
end

Note: Comments are supported in PostgreSQL and MySQL. SQLite does not support comments.

Use drop_table to remove a table from your database. Be careful—this permanently deletes all data in the table.

Basic Drop Table

class DropLegacyUsers < ActiveRecord::Migration[7.1]
  def change
    drop_table :legacy_users
  end
end

Reversible Drop (Recommended)

To make the migration reversible, provide the table schema:

class DropLegacyUsers < ActiveRecord::Migration[7.1]
  def change
    drop_table :legacy_users do |t|
      t.string :name
      t.string :email
      t.timestamps
    end
  end
end

Using up/down

class DropLegacyUsers < ActiveRecord::Migration[7.1]
  def up
    drop_table :legacy_users
  end

  def down
    create_table :legacy_users do |t|
      t.string :name
      t.string :email
      t.timestamps
    end
  end
end

Common Error: Foreign Key Constraint

PG::DependentObjectsStillExist: ERROR: cannot drop table users because other objects depend on it
-- or --
Mysql2::Error: Cannot delete or update a parent row: a foreign key constraint fails

Fix: Remove foreign key constraints first, or use CASCADE:

# Remove specific foreign key
remove_foreign_key :posts, :users
drop_table :users

# Or use cascade (PostgreSQL)
drop_table :users, force: :cascade

When writing idempotent migrations or cleaning up uncertain state, you may want to drop a table only if it exists.

Using if_exists Option

class DropLegacyTables < ActiveRecord::Migration[7.1]
  def change
    drop_table :legacy_users, if_exists: true
    drop_table :legacy_orders, if_exists: true
  end
end

Check Manually

class DropLegacyUsers < ActiveRecord::Migration[7.1]
  def change
    drop_table :legacy_users if table_exists?(:legacy_users)
  end
end

Important: Reversibility

Using if_exists: true without a block makes the migration irreversible because Rails doesn't know the table schema:

# NOT reversible
drop_table :users, if_exists: true

# Reversible - provide the schema
drop_table :users, if_exists: true do |t|
  t.string :name
  t.timestamps
end

When you run drop_table without specifying the table schema, Rails doesn't know how to recreate the table on rollback.

The Error

rails aborted!
ActiveRecord::IrreversibleMigration:

To avoid mistakes, drop_table is only reversible if given options or a block (to recreate the table).

Fix: Provide Table Schema

class DropUsers < ActiveRecord::Migration[7.1]
  def change
    # Pass a block with the table definition
    drop_table :users do |t|
      t.string :name, null: false
      t.string :email, null: false, index: { unique: true }
      t.string :password_digest
      t.boolean :admin, default: false

      t.timestamps
    end
  end
end

Alternative: Use up/down

class DropUsers < ActiveRecord::Migration[7.1]
  def up
    drop_table :users
  end

  def down
    create_table :users do |t|
      t.string :name, null: false
      t.string :email, null: false
      t.timestamps
    end

    add_index :users, :email, unique: true
  end
end

Copy Schema from schema.rb

The easiest way to get the exact schema is to copy the table definition from db/schema.rb before dropping.

Use add_column to add a new column to an existing table. This is one of the most common migration operations.

Using the Generator

# Rails recognizes the AddXxxToYyy pattern
rails g migration AddEmailToUsers email:string

# With multiple columns
rails g migration AddFieldsToUsers email:string age:integer admin:boolean

Basic add_column

class AddEmailToUsers < ActiveRecord::Migration[7.1]
  def change
    add_column :users, :email, :string
  end
end

With Options

add_column :users, :email, :string, null: false, default: ''
add_column :users, :age, :integer, limit: 2  # SMALLINT
add_column :users, :score, :decimal, precision: 5, scale: 2
add_column :users, :status, :integer, default: 0, null: false
add_column :users, :metadata, :jsonb, default: {}

Add Column with Index

class AddEmailToUsers < ActiveRecord::Migration[7.1]
  def change
    add_column :users, :email, :string
    add_index :users, :email, unique: true
  end
end

Common Error: Column Already Exists

PG::DuplicateColumn: ERROR: column "email" of relation "users" already exists

Fix: Check if column exists first:

add_column :users, :email, :string unless column_exists?(:users, :email)

You can add multiple columns in a single migration using multiple add_column calls or using change_table for a cleaner syntax.

Multiple add_column Calls

class AddProfileFieldsToUsers < ActiveRecord::Migration[7.1]
  def change
    add_column :users, :first_name, :string
    add_column :users, :last_name, :string
    add_column :users, :phone, :string
    add_column :users, :bio, :text
    add_column :users, :avatar_url, :string
  end
end

Using change_table

class AddProfileFieldsToUsers < ActiveRecord::Migration[7.1]
  def change
    change_table :users do |t|
      t.string :first_name
      t.string :last_name
      t.string :phone
      t.text :bio
      t.string :avatar_url
    end
  end
end

Using change_table with bulk: true

For databases that support it (MySQL), this combines changes into a single ALTER statement:

class AddProfileFieldsToUsers < ActiveRecord::Migration[7.1]
  def change
    change_table :users, bulk: true do |t|
      t.string :first_name
      t.string :last_name
      t.string :phone
      t.index :phone
    end
  end
end

Generator Shortcut

rails g migration AddProfileFieldsToUsers first_name:string last_name:string phone:string bio:text

Use remove_column to delete a column. To make it reversible, you must specify the column type.

Using the Generator

# Rails recognizes RemoveXxxFromYyy pattern
rails g migration RemoveEmailFromUsers email:string

Basic remove_column (Reversible)

class RemoveEmailFromUsers < ActiveRecord::Migration[7.1]
  def change
    # Specify type to make it reversible
    remove_column :users, :email, :string
  end
end

With All Column Options

# Include all options for perfect rollback
remove_column :users, :email, :string, null: false, default: '', index: true

Remove Multiple Columns

class RemoveLegacyFieldsFromUsers < ActiveRecord::Migration[7.1]
  def change
    remove_column :users, :legacy_id, :integer
    remove_column :users, :old_email, :string
    remove_column :users, :deprecated_flag, :boolean
  end
end

# Or using change_table
class RemoveLegacyFieldsFromUsers < ActiveRecord::Migration[7.1]
  def change
    change_table :users do |t|
      t.remove :legacy_id, type: :integer
      t.remove :old_email, type: :string
      t.remove :deprecated_flag, type: :boolean
    end
  end
end

Common Error: Irreversible Migration

ActiveRecord::IrreversibleMigration:
remove_column is only reversible if given a type.

Fix: Always include the column type:

# Bad - not reversible
remove_column :users, :email

# Good - reversible
remove_column :users, :email, :string

When refactoring your schema, you might need to remove the same column from several tables. Create a single migration that handles all the changes.

Remove Same Column from Multiple Tables

class RemoveLegacyTimestamps < ActiveRecord::Migration[7.1]
  def change
    # Remove deprecated_at from multiple tables
    remove_column :users, :deprecated_at, :datetime
    remove_column :posts, :deprecated_at, :datetime
    remove_column :comments, :deprecated_at, :datetime
  end
end

Using a Loop

class RemoveLegacyColumn < ActiveRecord::Migration[7.1]
  TABLES = [:users, :posts, :comments, :orders, :products]

  def change
    TABLES.each do |table|
      remove_column table, :legacy_id, :integer if column_exists?(table, :legacy_id)
    end
  end
end

With Different Column Types per Table

class CleanupLegacyColumns < ActiveRecord::Migration[7.1]
  def change
    remove_column :users, :old_email, :string
    remove_column :posts, :view_count, :integer
    remove_column :orders, :legacy_status, :string
  end
end

Reversible with Full Schema

class RemoveSoftDeleteColumns < ActiveRecord::Migration[7.1]
  def change
    remove_column :users, :deleted_at, :datetime, index: true
    remove_column :posts, :deleted_at, :datetime, index: true
    remove_column :comments, :deleted_at, :datetime, index: true
  end
end

Use change_column to modify a column's type, constraints, or options. Note that changing column types can cause data loss if the types are incompatible.

Basic Type Change

class ChangeAgeToString < ActiveRecord::Migration[7.1]
  def up
    change_column :users, :age, :string
  end

  def down
    change_column :users, :age, :integer
  end
end

Why change_column Needs up/down

change_column is not automatically reversible because Rails doesn't track the previous column state.

Using reversible

class ChangeStatusToInteger < ActiveRecord::Migration[7.1]
  def change
    reversible do |dir|
      dir.up do
        change_column :orders, :status, :integer, using: 'status::integer'
      end
      dir.down do
        change_column :orders, :status, :string
      end
    end
  end
end

String to Text

class ChangeBioToText < ActiveRecord::Migration[7.1]
  def up
    change_column :users, :bio, :text
  end

  def down
    change_column :users, :bio, :string, limit: 255
  end
end

Common Error: Data Truncation

PG::StringDataRightTruncation: ERROR: value too long for type character varying(50)

Fix: When shrinking column size, either clean up data first or use a larger limit:

# Clean up data first
User.where("LENGTH(name) > 50").update_all("name = LEFT(name, 50)")

# Then change the column
change_column :users, :name, :string, limit: 50

Use rename_column to change a column's name. This operation is automatically reversible.

Basic Rename

class RenameNameToFullName < ActiveRecord::Migration[7.1]
  def change
    rename_column :users, :name, :full_name
  end
end

Rename Multiple Columns

class RenameUserColumns < ActiveRecord::Migration[7.1]
  def change
    rename_column :users, :fname, :first_name
    rename_column :users, :lname, :last_name
    rename_column :users, :mail, :email
  end
end

After Renaming

Remember to update your code:

• Model validations and associations
• Controller parameters (strong params)
• Views and form fields
• API responses
• Tests and factories

Common Error: Index Name Mismatch

If the column had an index, the index name might now be inconsistent:

# After renaming email to primary_email, you might want to rename the index too
rename_index :users, 'index_users_on_email', 'index_users_on_primary_email'

When you rename a column that has a foreign key constraint, the constraint name doesn't automatically update. This is a known Rails behavior.

The Problem

# Original
add_reference :posts, :author, foreign_key: { to_table: :users }
# Creates FK named: fk_rails_xxx or posts_author_id_fk

# After renaming
rename_column :posts, :author_id, :creator_id
# FK constraint still references old column name internally

Fix: Recreate the Foreign Key

class RenameAuthorToCreator < ActiveRecord::Migration[7.1]
  def change
    # Remove old foreign key
    remove_foreign_key :posts, column: :author_id

    # Rename column
    rename_column :posts, :author_id, :creator_id

    # Add new foreign key
    add_foreign_key :posts, :users, column: :creator_id
  end
end

Check Foreign Key Names

# In Rails console
ActiveRecord::Base.connection.foreign_keys(:posts)

PostgreSQL: Rename Constraint Directly

class RenameAuthorToCreator < ActiveRecord::Migration[7.1]
  def change
    rename_column :posts, :author_id, :creator_id

    # Rename the constraint in PostgreSQL
    execute "ALTER TABLE posts RENAME CONSTRAINT fk_rails_xxx TO fk_rails_yyy"
  end
end

Rails provides database-agnostic types that map to native database types. Here's a comprehensive reference.

Common Types

create_table :examples do |t|
  # Strings
  t.string :name              # VARCHAR(255)
  t.text :description         # TEXT (unlimited length)

  # Numbers
  t.integer :count            # INTEGER (4 bytes)
  t.bigint :large_count       # BIGINT (8 bytes)
  t.float :rating             # FLOAT
  t.decimal :price, precision: 10, scale: 2  # DECIMAL

  # Boolean
  t.boolean :active           # BOOLEAN

  # Date/Time
  t.date :birth_date          # DATE
  t.time :start_time          # TIME
  t.datetime :published_at    # DATETIME/TIMESTAMP
  t.timestamp :processed_at   # Same as datetime

  # Binary
  t.binary :image_data        # BLOB/BYTEA

  # Special
  t.json :metadata            # JSON (native if supported)
  t.jsonb :settings           # JSONB (PostgreSQL only)

  t.timestamps                # created_at + updated_at
end

Database-Specific Types

# PostgreSQL
t.uuid :external_id
t.inet :ip_address
t.cidr :network
t.macaddr :mac_address
t.hstore :properties
t.jsonb :data
t.daterange :availability
t.tsrange :duration
t.point :location
t.line :path
t.polygon :area
t.interval :duration

# MySQL
t.unsigned_integer :count
t.unsigned_bigint :large_count

Type Modifiers

t.string :code, limit: 10           # VARCHAR(10)
t.decimal :amount, precision: 8, scale: 2
t.integer :age, limit: 2            # SMALLINT
t.integer :views, limit: 8          # BIGINT

# Array type (PostgreSQL)
t.string :tags, array: true, default: []
t.integer :scores, array: true

Use the limit option to specify the maximum character length for a string column. This maps to VARCHAR(n) in the database.

Specify String Length

class AddCodeToProducts < ActiveRecord::Migration[7.1]
  def change
    add_column :products, :sku, :string, limit: 20
    add_column :products, :country_code, :string, limit: 2
    add_column :products, :postal_code, :string, limit: 10
  end
end

In create_table

create_table :countries do |t|
  t.string :code, limit: 2, null: false    # ISO country code
  t.string :code3, limit: 3                # 3-letter code
  t.string :name, limit: 100
  t.string :phone_code, limit: 5

  t.timestamps
end

String vs Text

# Use string for short, fixed-ish length content
t.string :email, limit: 255           # VARCHAR(255)
t.string :phone, limit: 20            # VARCHAR(20)

# Use text for longer, variable content
t.text :bio                           # TEXT (unlimited)
t.text :description                   # TEXT

Change Existing Column Length

class IncreaseNameLength < ActiveRecord::Migration[7.1]
  def up
    change_column :users, :name, :string, limit: 500
  end

  def down
    change_column :users, :name, :string, limit: 255
  end
end

Database Differences

• PostgreSQL: VARCHAR(n) and TEXT perform identically
• MySQL: VARCHAR max is 65,535 bytes (varies with encoding)
• SQLite: Limit is advisory only (not enforced)

Boolean columns store true/false values. Always set a default to avoid NULL values, which can cause unexpected behavior.

Basic Boolean Column

class AddAdminToUsers < ActiveRecord::Migration[7.1]
  def change
    add_column :users, :admin, :boolean, default: false, null: false
  end
end

Multiple Boolean Flags

class AddFlagsToUsers < ActiveRecord::Migration[7.1]
  def change
    add_column :users, :active, :boolean, default: true, null: false
    add_column :users, :verified, :boolean, default: false, null: false
    add_column :users, :subscribed, :boolean, default: false, null: false
  end
end

Why Use default: false, null: false?

# Without defaults, you get three states: true, false, nil
user.admin?  # Could be true, false, or nil (confusing!)

# With default: false, null: false
user.admin?  # Always true or false, never nil

Add Boolean to Existing Table with Data

class AddVerifiedToUsers < ActiveRecord::Migration[7.1]
  def change
    # Add column with default (applies to new AND existing rows in PostgreSQL 11+)
    add_column :users, :verified, :boolean, default: false, null: false
  end
end

# For older PostgreSQL or MySQL, you might need:
class AddVerifiedToUsers < ActiveRecord::Migration[7.1]
  def up
    add_column :users, :verified, :boolean
    User.update_all(verified: false)
    change_column_null :users, :verified, false
    change_column_default :users, :verified, false
  end

  def down
    remove_column :users, :verified
  end
end

Database Representation

• PostgreSQL: Native BOOLEAN type
• MySQL: TINYINT(1) - 0 or 1
• SQLite: INTEGER - 0 or 1

Use decimal for precise numeric values like money, measurements, or percentages. Unlike float, decimal avoids floating-point rounding errors.

Understanding Precision and Scale

# precision: total number of digits
# scale: number of digits after decimal point

# DECIMAL(10, 2) can store: -99999999.99 to 99999999.99
t.decimal :price, precision: 10, scale: 2

Common Use Cases

class CreateProducts < ActiveRecord::Migration[7.1]
  def change
    create_table :products do |t|
      # Money (8 digits, 2 decimal places) - up to 999,999.99
      t.decimal :price, precision: 8, scale: 2, null: false

      # Percentage (5 digits, 2 decimal) - up to 100.00 or 999.99
      t.decimal :discount_rate, precision: 5, scale: 2, default: 0

      # Weight in kg (10 digits, 4 decimal) - high precision
      t.decimal :weight, precision: 10, scale: 4

      # Geographic coordinates
      t.decimal :latitude, precision: 10, scale: 6
      t.decimal :longitude, precision: 10, scale: 6

      t.timestamps
    end
  end
end

Why Not Float?

# Float has rounding errors
0.1 + 0.2  # => 0.30000000000000004

# Decimal is exact
BigDecimal("0.1") + BigDecimal("0.2")  # => 0.3

Common Error: Precision Exceeded

PG::NumericValueOutOfRange: ERROR: numeric field overflow
DETAIL: A field with precision 8, scale 2 must round to an absolute value less than 10^6.

Fix: Increase precision or check your data:

change_column :products, :price, :decimal, precision: 12, scale: 2

DateTime columns store both date and time. Rails provides datetime, timestamp, date, and time types.

DateTime vs Timestamp

# In Rails, these are typically equivalent:
t.datetime :published_at
t.timestamp :published_at

# timestamps helper adds both created_at and updated_at
t.timestamps

DateTime with Precision (Rails 7+)

# Microsecond precision (default in Rails 7+)
t.datetime :occurred_at, precision: 6

# Second precision only
t.datetime :occurred_at, precision: 0

# Millisecond precision
t.datetime :occurred_at, precision: 3

Different Time Types

create_table :events do |t|
  t.date :event_date          # Just the date: 2026-01-15
  t.time :start_time          # Just the time: 14:30:00
  t.datetime :starts_at       # Both: 2026-01-15 14:30:00

  t.timestamps
end

With Default Value

# Default to current time (database function)
t.datetime :published_at, default: -> { 'CURRENT_TIMESTAMP' }

# Or in PostgreSQL
t.datetime :published_at, default: -> { 'NOW()' }

Common Issue: Timezone Handling

# In config/application.rb, set your timezone:
config.time_zone = 'Eastern Time (US & Canada)'

# Store times in UTC (recommended)
config.active_record.default_timezone = :utc

Migration Compatibility Note

Rails 7 changed the default datetime precision from nil to 6 (microseconds). Old migrations might behave differently:

# Rails 6 migration creates datetime without precision
class OldMigration < ActiveRecord::Migration[6.1]
  # ...
end

# Rails 7 migration creates datetime with precision: 6
class NewMigration < ActiveRecord::Migration[7.1]
  # ...
end

Bigint columns store large integers (8 bytes vs 4 bytes for integer). Rails 5.1+ uses bigint for primary keys by default.

When to Use Bigint

• Primary keys (default in Rails 5.1+)
• Foreign keys to bigint primary keys
• Counters that might exceed 2 billion
• External IDs from other systems
• Unix timestamps in milliseconds

Integer vs Bigint Range

# Integer (4 bytes): -2,147,483,648 to 2,147,483,647
# Bigint (8 bytes):  -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

t.integer :small_count      # INTEGER
t.bigint :large_count       # BIGINT

# Or use limit
t.integer :count, limit: 8  # BIGINT

Create Table with Bigint Primary Key

# Rails 5.1+ does this by default
create_table :users do |t|
  t.string :name
  t.timestamps
end

# Explicitly specify
create_table :users, id: :bigint do |t|
  t.string :name
  t.timestamps
end

Foreign Keys to Bigint

# Reference column must match primary key type
create_table :posts do |t|
  t.references :user, type: :bigint, foreign_key: true
  # or
  t.bigint :user_id
  t.string :title

  t.timestamps
end

Common Error: Type Mismatch

ActiveRecord::MismatchedForeignKey: Column `user_id` on table `posts`
does not match column `id` on `users`, which has type `bigint(8)`.
Change the type of the `user_id` column to `bigint`.

Fix: Ensure foreign key columns match the primary key type:

# Change existing column
change_column :posts, :user_id, :bigint

# Or when creating reference
t.references :user, type: :bigint, foreign_key: true

JSON columns store structured data without a fixed schema. PostgreSQL's jsonb is preferred for performance as it stores data in binary format.

PostgreSQL JSONB (Recommended)

class AddMetadataToUsers < ActiveRecord::Migration[7.1]
  def change
    add_column :users, :settings, :jsonb, default: {}
    add_column :users, :preferences, :jsonb, default: {}

    # Add index for faster queries
    add_index :users, :settings, using: :gin
  end
end

MySQL JSON

class AddMetadataToUsers < ActiveRecord::Migration[7.1]
  def change
    add_column :users, :settings, :json
  end
end

In create_table

create_table :products do |t|
  t.string :name
  t.jsonb :specifications, default: {}
  t.jsonb :variants, default: []

  t.timestamps
end

Querying JSON Data

# In your model
class User < ApplicationRecord
  # Query JSON fields
  scope :with_dark_mode, -> { where("settings->>'theme' = ?", 'dark') }
  scope :newsletter_subscribers, -> { where("settings->>'newsletter' = ?", 'true') }
end

# PostgreSQL jsonb operators
User.where("settings @> ?", { theme: 'dark' }.to_json)
User.where("settings ? :key", key: 'newsletter')

JSON vs JSONB

• json: Stores exact text, preserves whitespace and key order
• jsonb: Binary format, faster queries, supports indexing, no duplicate keys

Common Error: Invalid JSON Default

PG::InvalidTextRepresentation: ERROR: invalid input syntax for type json

Fix: Use Ruby hash/array for defaults, not JSON strings:

# Correct
t.jsonb :settings, default: {}
t.jsonb :tags, default: []

# Wrong
t.jsonb :settings, default: '{}'

PostgreSQL supports native array columns, allowing you to store multiple values in a single field without a separate table.

Create Array Column

class AddTagsToArticles < ActiveRecord::Migration[7.1]
  def change
    add_column :articles, :tags, :string, array: true, default: []
    add_column :articles, :scores, :integer, array: true, default: []
    add_column :articles, :prices, :decimal, array: true, default: [], precision: 10, scale: 2
  end
end

In create_table

create_table :products do |t|
  t.string :name
  t.string :categories, array: true, default: []
  t.string :sizes, array: true, default: []
  t.integer :related_ids, array: true, default: []

  t.timestamps
end

# Add GIN index for array queries
add_index :products, :categories, using: :gin

Querying Arrays

# Find records containing a value
Product.where("'electronics' = ANY(categories)")

# Find records containing all values
Product.where("categories @> ARRAY[?]::varchar[]", ['electronics', 'gadgets'])

# Find records containing any of values
Product.where("categories && ARRAY[?]::varchar[]", ['electronics', 'clothing'])

Model Usage

class Product < ApplicationRecord
  # Works like a regular Ruby array
end

product = Product.new(tags: ['ruby', 'rails', 'web'])
product.tags << 'api'
product.save

Important Note

Array columns are PostgreSQL-specific. For MySQL or SQLite, use a JSON column or a separate join table instead.

There are two approaches: use integer columns with Rails enums (database-agnostic) or use native database enum types (PostgreSQL).

Option 1: Integer Column with Rails Enum (Recommended)

# Migration
class AddStatusToOrders < ActiveRecord::Migration[7.1]
  def change
    add_column :orders, :status, :integer, default: 0, null: false
  end
end

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

# Usage
order.pending?     # => true
order.shipped!     # updates status to shipped
Order.delivered    # scope for delivered orders

Option 2: PostgreSQL Native Enum

class AddStatusToOrders < ActiveRecord::Migration[7.1]
  def up
    # Create the enum type
    execute <<-SQL
      CREATE TYPE order_status AS ENUM ('pending', 'processing', 'shipped', 'delivered', 'cancelled');
    SQL

    add_column :orders, :status, :order_status, default: 'pending', null: false
  end

  def down
    remove_column :orders, :status

    execute <<-SQL
      DROP TYPE order_status;
    SQL
  end
end

Rails 7+ create_enum Helper

class AddStatusToOrders < ActiveRecord::Migration[7.1]
  def change
    create_enum :order_status, ['pending', 'processing', 'shipped', 'delivered', 'cancelled']

    add_column :orders, :status, :enum, enum_type: :order_status, default: 'pending', null: false
  end
end

Why Integer is Often Better

• Database-agnostic (works everywhere)
• Easy to add new values
• Better performance (smaller storage)
• No migration needed to add values

PostgreSQL enum types require special handling for reversibility because you can't easily drop a type that's in use.

The Error

ActiveRecord::IrreversibleMigration

-- or on rollback --

PG::DependentObjectsStillExist: ERROR: cannot drop type order_status because other objects depend on it

Fix: Use up/down Methods

class AddStatusToOrders < ActiveRecord::Migration[7.1]
  def up
    create_enum :order_status, ['pending', 'processing', 'shipped']
    add_column :orders, :status, :enum, enum_type: :order_status, default: 'pending'
  end

  def down
    remove_column :orders, :status
    execute "DROP TYPE order_status"
  end
end

Adding Values to Existing Enum

class AddCancelledToOrderStatus < ActiveRecord::Migration[7.1]
  def up
    execute <<-SQL
      ALTER TYPE order_status ADD VALUE 'cancelled';
    SQL
  end

  def down
    # PostgreSQL doesn't support removing enum values!
    # You'd need to recreate the type
    raise ActiveRecord::IrreversibleMigration
  end
end

Alternative: Use Integer Enums

To avoid these complications, use integer columns with Rails enums instead. Adding new values is simply a model change, no migration required:

# No migration needed to add 'refunded'
enum status: {
  pending: 0,
  processing: 1,
  shipped: 2,
  delivered: 3,
  cancelled: 4,
  refunded: 5  # Just add to the hash
}

Default values are set automatically when creating new records without explicitly providing a value for that column.

When Creating Column

class AddFieldsToUsers < ActiveRecord::Migration[7.1]
  def change
    add_column :users, :role, :string, default: 'member'
    add_column :users, :active, :boolean, default: true
    add_column :users, :credits, :integer, default: 0
    add_column :users, :settings, :jsonb, default: {}
  end
end

In create_table

create_table :products do |t|
  t.string :name
  t.string :status, default: 'draft'
  t.integer :stock, default: 0
  t.decimal :price, default: 0.0, precision: 10, scale: 2
  t.boolean :featured, default: false

  t.timestamps
end

Add Default to Existing Column

class AddDefaultToStatus < ActiveRecord::Migration[7.1]
  def change
    change_column_default :orders, :status, from: nil, to: 'pending'
  end
end

Default with null: false

# Best practice: combine default with null: false
add_column :users, :locale, :string, default: 'en', null: false

Common Issue: Existing Records

Adding a default doesn't update existing records (they keep NULL or their current value). To update existing records:

class AddDefaultStatus < ActiveRecord::Migration[7.1]
  def change
    add_column :orders, :status, :string, default: 'pending'

    # Update existing records
    reversible do |dir|
      dir.up do
        Order.where(status: nil).update_all(status: 'pending')
      end
    end
  end
end

Use change_column_default to modify the default value of an existing column.

Reversible Change (Recommended)

class ChangeDefaultStatus < ActiveRecord::Migration[7.1]
  def change
    change_column_default :orders, :status, from: 'new', to: 'pending'
  end
end

Non-Reversible Change

# If you don't know the previous default
class ChangeDefaultStatus < ActiveRecord::Migration[7.1]
  def up
    change_column_default :orders, :status, 'pending'
  end

  def down
    change_column_default :orders, :status, 'new'
  end
end

Change Multiple Defaults

class UpdateDefaults < ActiveRecord::Migration[7.1]
  def change
    change_column_default :users, :role, from: 'user', to: 'member'
    change_column_default :users, :credits, from: 100, to: 0
    change_column_default :users, :active, from: nil, to: true
  end
end

Dynamic Default (Database Function)

class ChangeDefaultTimestamp < ActiveRecord::Migration[7.1]
  def up
    change_column_default :events, :occurred_at, -> { 'CURRENT_TIMESTAMP' }
  end

  def down
    change_column_default :events, :occurred_at, nil
  end
end

Set the default to nil to remove it, allowing the column to have no default value.

Remove Default (Reversible)

class RemoveDefaultFromStatus < ActiveRecord::Migration[7.1]
  def change
    change_column_default :orders, :status, from: 'pending', to: nil
  end
end

Remove Default (Non-Reversible)

class RemoveDefaultFromStatus < ActiveRecord::Migration[7.1]
  def up
    change_column_default :orders, :status, nil
  end

  def down
    change_column_default :orders, :status, 'pending'
  end
end

Using change_column

# This also works but changes more than just the default
class RemoveDefaultFromStatus < ActiveRecord::Migration[7.1]
  def up
    change_column :orders, :status, :string, default: nil
  end

  def down
    change_column :orders, :status, :string, default: 'pending'
  end
end

Use a lambda/proc to specify a database function as the default value. This is evaluated by the database, not Ruby.

Current Timestamp

class AddTimestampDefaults < ActiveRecord::Migration[7.1]
  def change
    add_column :events, :occurred_at, :datetime, default: -> { 'CURRENT_TIMESTAMP' }

    # PostgreSQL specific
    add_column :events, :created_date, :date, default: -> { 'CURRENT_DATE' }
  end
end

UUID Generation

# PostgreSQL with pgcrypto
class AddExternalId < ActiveRecord::Migration[7.1]
  def change
    enable_extension 'pgcrypto' unless extension_enabled?('pgcrypto')

    add_column :orders, :external_id, :uuid, default: -> { 'gen_random_uuid()' }, null: false
  end
end

In create_table

create_table :audit_logs, id: :uuid, default: -> { 'gen_random_uuid()' } do |t|
  t.string :action
  t.jsonb :data
  t.datetime :logged_at, default: -> { 'NOW()' }

  t.timestamps
end

MySQL Examples

# MySQL current timestamp
add_column :events, :created_at, :datetime, default: -> { 'CURRENT_TIMESTAMP' }

# MySQL UUID (requires MySQL 8.0+)
add_column :orders, :uuid, :string, limit: 36, default: -> { 'UUID()' }

Important Note

The lambda syntax tells Rails to use the expression as-is in SQL, not as a Ruby value. Without the lambda, Rails would try to evaluate it as Ruby code.

This is a common confusion. change_column_default only changes the default value and shouldn't affect other options. However, change_column can inadvertently reset options if you don't specify them.

The Problem with change_column

# Original column
add_column :users, :status, :string, limit: 20, null: false, default: 'active'

# This LOSES limit and null constraint!
change_column :users, :status, :string, default: 'pending'

# After: status is string with default 'pending', but limit is 255 and null is allowed

Solution: Use change_column_default

# This only changes the default, preserves everything else
change_column_default :users, :status, from: 'active', to: 'pending'

If You Must Use change_column

# Specify ALL options you want to keep
change_column :users, :status, :string, limit: 20, null: false, default: 'pending'

Check Current Column Definition

# In Rails console
ActiveRecord::Base.connection.columns(:users).find { |c| c.name == 'status' }

# Shows: #<Column name: "status", type: :string, limit: 20, null: false, default: "active">

Use change_column_null to add or remove a NOT NULL constraint. If the column has existing NULL values, you must handle them first.

Add NOT NULL to New Column

add_column :users, :email, :string, null: false

Add NOT NULL to Existing Column

class MakeEmailNotNull < ActiveRecord::Migration[7.1]
  def change
    change_column_null :users, :email, false
  end
end

With Existing NULL Values

class MakeEmailNotNull < ActiveRecord::Migration[7.1]
  def change
    # Provide a default for existing NULL values
    change_column_null :users, :email, false, '[email protected]'
  end
end

# Or handle manually
class MakeEmailNotNull < ActiveRecord::Migration[7.1]
  def up
    # Update NULLs first
    User.where(email: nil).update_all(email: '[email protected]')

    # Then add constraint
    change_column_null :users, :email, false
  end

  def down
    change_column_null :users, :email, true
  end
end

Common Error: NULL Values Exist

PG::NotNullViolation: ERROR: column "email" of relation "users" contains null values
-- or --
Mysql2::Error: Invalid use of NULL value

Fix: Update or delete rows with NULL values before adding the constraint:

def up
  # Option 1: Set a default value
  User.where(email: nil).update_all(email: '[email protected]')

  # Option 2: Delete rows with NULL
  User.where(email: nil).delete_all

  # Then add constraint
  change_column_null :users, :email, false
end

Remove NOT NULL Constraint

class AllowNullEmail < ActiveRecord::Migration[7.1]
  def change
    change_column_null :users, :email, true
  end
end

Use change_column_null with true to remove a NOT NULL constraint and allow NULL values.

Remove NOT NULL Constraint

class AllowNullOnEmail < ActiveRecord::Migration[7.1]
  def change
    change_column_null :users, :email, true
  end
end

Multiple Columns

class AllowNullOnOptionalFields < ActiveRecord::Migration[7.1]
  def change
    change_column_null :users, :phone, true
    change_column_null :users, :bio, true
    change_column_null :users, :avatar_url, true
  end
end

With Reversibility Consideration

class AllowNullOnEmail < ActiveRecord::Migration[7.1]
  def up
    change_column_null :users, :email, true
  end

  def down
    # Fill in NULLs before re-adding constraint
    User.where(email: nil).find_each do |user|
      user.update_column(:email, "user_#{user.id}@placeholder.com")
    end
    change_column_null :users, :email, false
  end
end

Adding a NOT NULL column to a table with existing rows requires providing a default value, either in the column definition or by updating rows first.

Option 1: With Default Value (Simplest)

class AddStatusToOrders < ActiveRecord::Migration[7.1]
  def change
    # PostgreSQL 11+ applies default to existing rows automatically
    add_column :orders, :status, :string, null: false, default: 'pending'
  end
end

Option 2: Three-Step Process

class AddStatusToOrders < ActiveRecord::Migration[7.1]
  def up
    # 1. Add column allowing NULL
    add_column :orders, :status, :string

    # 2. Backfill existing records
    Order.update_all(status: 'pending')
    # Or for large tables:
    # Order.in_batches.update_all(status: 'pending')

    # 3. Add NOT NULL constraint
    change_column_null :orders, :status, false
  end

  def down
    remove_column :orders, :status
  end
end

Option 3: Using SQL for Performance

class AddStatusToOrders < ActiveRecord::Migration[7.1]
  def up
    add_column :orders, :status, :string

    # Direct SQL is faster for large tables
    execute "UPDATE orders SET status = 'pending' WHERE status IS NULL"

    change_column_null :orders, :status, false
    change_column_default :orders, :status, 'pending'
  end

  def down
    remove_column :orders, :status
  end
end

For Very Large Tables

class AddStatusToOrders < ActiveRecord::Migration[7.1]
  disable_ddl_transaction!

  def up
    add_column :orders, :status, :string

    # Update in batches to avoid locking
    Order.in_batches(of: 10_000) do |batch|
      batch.update_all(status: 'pending')
    end

    change_column_null :orders, :status, false
  end

  def down
    remove_column :orders, :status
  end
end

Indexes improve query performance by allowing the database to find rows faster. Use add_index to create an index on one or more columns.

Basic Index

class AddIndexToUsersEmail < ActiveRecord::Migration[7.1]
  def change
    add_index :users, :email
  end
end

When Adding a Column

class AddEmailToUsers < ActiveRecord::Migration[7.1]
  def change
    add_column :users, :email, :string
    add_index :users, :email
  end
end

# Or combine with generator
# rails g migration AddEmailToUsers email:string:index

Using References (Automatically Adds Index)

class AddUserToPosts < ActiveRecord::Migration[7.1]
  def change
    add_reference :posts, :user, foreign_key: true
    # Automatically creates index on user_id
  end
end

In create_table

create_table :users do |t|
  t.string :email, index: true
  t.string :username, index: { unique: true }
  t.references :organization, index: true, foreign_key: true

  t.timestamps
end

Common Error: Index Already Exists

PG::DuplicateTable: ERROR: relation "index_users_on_email" already exists

Fix: Check if index exists first:

add_index :users, :email unless index_exists?(:users, :email)

Unique indexes enforce uniqueness at the database level, preventing duplicate values even if application validations fail.

Basic Unique Index

class AddUniqueIndexToUsersEmail < ActiveRecord::Migration[7.1]
  def change
    add_index :users, :email, unique: true
  end
end

Unique Index on Multiple Columns

# Ensure combination is unique
class AddUniqueIndexToMemberships < ActiveRecord::Migration[7.1]
  def change
    add_index :memberships, [:user_id, :organization_id], unique: true
  end
end

Unique Index with Conditions (Partial)

# Only enforce uniqueness for active records
class AddUniqueIndexToActiveUsers < ActiveRecord::Migration[7.1]
  def change
    add_index :users, :email, unique: true, where: "deleted_at IS NULL"
  end
end

Common Error: Duplicate Values Exist

PG::UniqueViolation: ERROR: could not create unique index "index_users_on_email"
DETAIL: Key (email)=([email protected]) is duplicated.

Fix: Remove duplicates before adding the unique index:

class AddUniqueIndexToUsersEmail < ActiveRecord::Migration[7.1]
  def up
    # Find and handle duplicates first
    duplicates = User.group(:email).having('COUNT(*) > 1').pluck(:email)
    duplicates.each do |email|
      users = User.where(email: email).order(:created_at)
      users.offset(1).destroy_all  # Keep the first, remove others
    end

    add_index :users, :email, unique: true
  end

  def down
    remove_index :users, :email
  end
end

Composite indexes (multi-column indexes) improve queries that filter or sort by multiple columns together.

Basic Composite Index

class AddIndexToOrdersStatusDate < ActiveRecord::Migration[7.1]
  def change
    add_index :orders, [:status, :created_at]
  end
end

Column Order Matters

# This index helps queries that:
# - Filter by status alone
# - Filter by status AND user_id
# But NOT queries that only filter by user_id

add_index :orders, [:status, :user_id]

# For queries filtering only by user_id, add a separate index
add_index :orders, :user_id

Common Composite Index Patterns

class AddIndexesToOrders < ActiveRecord::Migration[7.1]
  def change
    # For: Order.where(user_id: x).order(created_at: :desc)
    add_index :orders, [:user_id, :created_at]

    # For: Order.where(status: x, user_id: y)
    add_index :orders, [:status, :user_id]

    # For polymorphic associations
    add_index :comments, [:commentable_type, :commentable_id]
  end
end

Unique Composite Index

# User can only have one role per organization
add_index :memberships, [:user_id, :organization_id, :role], unique: true

# Or simpler: user can only be in org once
add_index :memberships, [:user_id, :organization_id], unique: true

With Custom Name

# If auto-generated name is too long
add_index :orders, [:user_id, :status, :created_at],
          name: 'idx_orders_user_status_date'

Partial indexes only include rows that match a condition. They're smaller and faster for queries that match the same condition.

Basic Partial Index

class AddPartialIndexToOrders < ActiveRecord::Migration[7.1]
  def change
    # Only index non-archived orders
    add_index :orders, :status, where: "archived = false"
  end
end

Unique Partial Index (Soft Delete)

# Enforce unique email only for non-deleted users
class AddUniqueEmailIndex < ActiveRecord::Migration[7.1]
  def change
    add_index :users, :email, unique: true, where: "deleted_at IS NULL"
  end
end

Index Only Active Records

class AddPartialIndexes < ActiveRecord::Migration[7.1]
  def change
    # Index only active users
    add_index :users, :email, where: "active = true", name: 'idx_active_users_email'

    # Index only pending orders
    add_index :orders, :created_at, where: "status = 'pending'", name: 'idx_pending_orders'

    # Index only recent records
    add_index :logs, :action, where: "created_at > '2025-01-01'", name: 'idx_recent_logs'
  end
end

PostgreSQL Specific Conditions

# JSONB conditions
add_index :users, :email, where: "settings->>'notifications' = 'true'"

# NULL checks
add_index :orders, :shipped_at, where: "shipped_at IS NOT NULL"
add_index :orders, :user_id, where: "cancelled_at IS NULL"

Benefits of Partial Indexes

• Smaller index size
• Faster index updates
• Better query performance for matching queries
• Can enforce partial uniqueness

Rails auto-generates index names, but they can exceed database limits (63 characters for PostgreSQL). Use the name option for custom names.

Why Custom Names?

• Auto-generated names can be too long
• More readable names for debugging
• Match existing index names when adopting legacy schemas

Custom Index Name

class AddIndexToOrders < ActiveRecord::Migration[7.1]
  def change
    add_index :orders, [:user_id, :status, :created_at, :updated_at],
              name: 'idx_orders_user_status_timestamps'
  end
end

Common Error: Name Too Long

ArgumentError: Index name 'index_user_organization_memberships_on_user_id_and_organization_id_and_role_id' is too long; the limit is 63 characters

Fix: Use a shorter custom name:

add_index :user_organization_memberships,
          [:user_id, :organization_id, :role_id],
          name: 'idx_user_org_memberships_composite'

Removing Index by Name

# When removing, reference by name
remove_index :orders, name: 'idx_orders_user_status_timestamps'

Check Index Name

# In Rails console
ActiveRecord::Base.connection.indexes(:orders).map(&:name)

Use remove_index to delete an index. You can reference it by column(s) or by name.

Remove by Column

class RemoveIndexFromUsersEmail < ActiveRecord::Migration[7.1]
  def change
    remove_index :users, :email
  end
end

Remove Composite Index

class RemoveIndexFromOrders < ActiveRecord::Migration[7.1]
  def change
    remove_index :orders, [:user_id, :status]
  end
end

Remove by Name

class RemoveIndexFromOrders < ActiveRecord::Migration[7.1]
  def change
    remove_index :orders, name: 'idx_orders_user_status'
  end
end

Making Removal Reversible

# For simple indexes, Rails can reverse automatically
remove_index :users, :email  # Reversible

# For indexes with options, specify them for reversibility
remove_index :users, :email, unique: true, where: "active = true"

Check If Index Exists First

class RemoveIndexIfExists < ActiveRecord::Migration[7.1]
  def change
    remove_index :users, :email if index_exists?(:users, :email)
  end
end

Common Error: Index Not Found

ArgumentError: Index name 'index_users_on_email' on table 'users' does not exist

Fix: Check the actual index name in your database or schema.rb:

# In Rails console
ActiveRecord::Base.connection.indexes(:users)

Use index_exists? to check for an index before adding or removing it, making your migrations idempotent.

Check by Column

class AddIndexSafely < ActiveRecord::Migration[7.1]
  def change
    add_index :users, :email unless index_exists?(:users, :email)
  end
end

Check by Name

class AddIndexSafely < ActiveRecord::Migration[7.1]
  def change
    unless index_exists?(:users, name: 'index_users_on_email')
      add_index :users, :email
    end
  end
end

Check Composite Index

if index_exists?(:orders, [:user_id, :status])
  # Index exists
end

Check Unique Index

# Check if a unique index exists on email
index_exists?(:users, :email, unique: true)

List All Indexes

# In Rails console or migration
ActiveRecord::Base.connection.indexes(:users).each do |index|
  puts "#{index.name}: #{index.columns.join(', ')} (unique: #{index.unique})"
end

Safe Add/Remove Pattern

class ManageIndexes < ActiveRecord::Migration[7.1]
  def change
    # Safe add
    add_index :users, :email unless index_exists?(:users, :email)

    # Safe remove
    remove_index :users, :legacy_field if index_exists?(:users, :legacy_field)
  end
end

Expression indexes (indexes on functions or expressions) can't be automatically reversed because Rails doesn't store the expression used to create them.

The Error

ActiveRecord::IrreversibleMigration:
This migration uses remove_index to remove an expression index, but
add_index was not given an expression.

Problem: Expression Index

# These are not automatically reversible
add_index :users, 'LOWER(email)', name: 'index_users_on_lower_email'
add_index :users, "(data->>'name')", name: 'index_users_on_data_name'

Fix: Use up/down Methods

class AddLowerEmailIndex < ActiveRecord::Migration[7.1]
  def up
    execute <<-SQL
      CREATE INDEX index_users_on_lower_email ON users (LOWER(email));
    SQL
  end

  def down
    execute <<-SQL
      DROP INDEX index_users_on_lower_email;
    SQL
  end
end

Alternative: Use reversible

class AddLowerEmailIndex < ActiveRecord::Migration[7.1]
  def change
    reversible do |dir|
      dir.up do
        execute "CREATE INDEX index_users_on_lower_email ON users (LOWER(email))"
      end
      dir.down do
        execute "DROP INDEX index_users_on_lower_email"
      end
    end
  end
end

Common Expression Indexes

# Case-insensitive search
execute "CREATE INDEX idx_users_lower_email ON users (LOWER(email))"

# JSONB field
execute "CREATE INDEX idx_users_settings_theme ON users ((settings->>'theme'))"

# Date extraction
execute "CREATE INDEX idx_orders_year ON orders (EXTRACT(YEAR FROM created_at))"

# GIN index for full-text search
execute "CREATE INDEX idx_posts_search ON posts USING GIN (to_tsvector('english', title || ' ' || body))"

Databases have limits on identifier names (PostgreSQL: 63 characters, MySQL: 64). Rails auto-generates index names that may exceed these limits.

The Error

Index name 'index_user_organization_role_permissions_on_user_id_and_organization_id_and_role_id'
on table 'user_organization_role_permissions' is too long; the limit is 63 characters

Fix: Use Custom Name

class AddIndexToPermissions < ActiveRecord::Migration[7.1]
  def change
    add_index :user_organization_role_permissions,
              [:user_id, :organization_id, :role_id],
              name: 'idx_user_org_role_perms'
  end
end

Common Long Name Scenarios

# Polymorphic association - auto name too long
add_index :comments, [:commentable_type, :commentable_id],
          name: 'idx_comments_commentable'

# Join table with long names
add_index :user_organization_memberships, [:user_id, :organization_id],
          name: 'idx_user_org_memberships', unique: true

# Multiple columns
add_index :audit_logs, [:auditable_type, :auditable_id, :action, :created_at],
          name: 'idx_audit_logs_composite'

Rails 7.1+ Index Name Changes

Rails 7.1 changed how index names are generated for rename_table. Older migrations may have different index names than newer ones:

# In migration version 7.0 and earlier
rename_table :users, :members
# Index names stay as index_users_on_*

# In migration version 7.1+
rename_table :users, :members
# Index names become index_members_on_*

Use add_reference (or add_belongs_to) to add a foreign key column with an index. This is the preferred way to create associations.

Basic Reference

class AddUserToPosts < ActiveRecord::Migration[7.1]
  def change
    add_reference :posts, :user, foreign_key: true
  end
end

# Creates:
# - user_id column (bigint)
# - index on user_id
# - foreign key constraint to users table

Using Generator

# These are equivalent
rails g migration AddUserToPosts user:references
rails g migration AddUserToPosts user:belongs_to

Reference Options

add_reference :posts, :user,
  foreign_key: true,       # Add FK constraint
  null: false,             # NOT NULL
  index: true,             # Add index (default)
  type: :uuid              # Match UUID primary key

Without Foreign Key

# Just add the column and index, no FK constraint
add_reference :posts, :user, foreign_key: false

# Or without index
add_reference :posts, :user, index: false

Custom Column Name

# Creates author_id instead of user_id
add_reference :posts, :author, foreign_key: { to_table: :users }

In create_table

create_table :posts do |t|
  t.references :user, foreign_key: true
  # or
  t.belongs_to :user, foreign_key: true

  t.string :title
  t.timestamps
end

Polymorphic associations allow a model to belong to multiple other models using a single association. This requires two columns: one for the ID and one for the type.

Add Polymorphic Reference

class AddCommentableToComments < ActiveRecord::Migration[7.1]
  def change
    add_reference :comments, :commentable, polymorphic: true
  end
end

# Creates:
# - commentable_id (bigint)
# - commentable_type (string)
# - composite index on [commentable_type, commentable_id]

In create_table

create_table :comments do |t|
  t.references :commentable, polymorphic: true, null: false
  t.text :body

  t.timestamps
end

Model Setup

class Comment < ApplicationRecord
  belongs_to :commentable, polymorphic: true
end

class Post < ApplicationRecord
  has_many :comments, as: :commentable
end

class Photo < ApplicationRecord
  has_many :comments, as: :commentable
end

Polymorphic with NOT NULL

add_reference :comments, :commentable, polymorphic: true, null: false

# Or in create_table
t.references :commentable, polymorphic: true, null: false

Note on Foreign Keys

PostgreSQL doesn't support foreign keys on polymorphic associations because the reference can point to multiple tables. The foreign_key: true option is ignored for polymorphic references.

Foreign key constraints ensure referential integrity at the database level, preventing orphaned records.

Add Foreign Key to Existing Column

class AddForeignKeyToPosts < ActiveRecord::Migration[7.1]
  def change
    add_foreign_key :posts, :users
  end
end

With Custom Column Name

# posts.author_id references users.id
add_foreign_key :posts, :users, column: :author_id

With Custom Primary Key

# posts.user_uuid references users.uuid
add_foreign_key :posts, :users, column: :user_uuid, primary_key: :uuid

On Delete/Update Actions

# Delete posts when user is deleted
add_foreign_key :posts, :users, on_delete: :cascade

# Set to NULL when user is deleted
add_foreign_key :posts, :users, on_delete: :nullify

# Prevent deletion if posts exist
add_foreign_key :posts, :users, on_delete: :restrict

# Options: :cascade, :nullify, :restrict, :no_action

With Reference Helper

# Combined column + index + foreign key
add_reference :posts, :user, foreign_key: true

# With on_delete
add_reference :posts, :user, foreign_key: { on_delete: :cascade }

Validate Later (Large Tables)

# Add constraint without validating existing data
add_foreign_key :posts, :users, validate: false

# Later, validate in a separate migration
validate_foreign_key :posts, :users

Use remove_foreign_key to drop a foreign key constraint while keeping the column.

Remove by Table

class RemoveForeignKeyFromPosts < ActiveRecord::Migration[7.1]
  def change
    remove_foreign_key :posts, :users
  end
end

Remove by Column

# When column name doesn't match convention
remove_foreign_key :posts, column: :author_id

Remove by Name

# When you know the constraint name
remove_foreign_key :posts, name: 'fk_rails_5b5ddfd518'

Making Removal Reversible

class RemoveForeignKey < ActiveRecord::Migration[7.1]
  def change
    # Provide to_table for reversibility
    remove_foreign_key :posts, :users, column: :author_id
  end
end

# Or with full options
class RemoveForeignKey < ActiveRecord::Migration[7.1]
  def up
    remove_foreign_key :posts, :users
  end

  def down
    add_foreign_key :posts, :users, on_delete: :cascade
  end
end

Find Foreign Key Name

# In Rails console
ActiveRecord::Base.connection.foreign_keys(:posts)
# Returns array of ForeignKeyDefinition objects

Self-referential associations link a table to itself, common for hierarchies like categories, comments (replies), or employee managers.

Parent-Child Hierarchy

class AddParentToCategories < ActiveRecord::Migration[7.1]
  def change
    add_reference :categories, :parent,
                  foreign_key: { to_table: :categories },
                  null: true  # Root categories have no parent
  end
end

Manager-Employee Relationship

class AddManagerToEmployees < ActiveRecord::Migration[7.1]
  def change
    add_reference :employees, :manager,
                  foreign_key: { to_table: :employees },
                  null: true
  end
end

# Model
class Employee < ApplicationRecord
  belongs_to :manager, class_name: 'Employee', optional: true
  has_many :subordinates, class_name: 'Employee', foreign_key: :manager_id
end

Reply to Comment

class AddParentToComments < ActiveRecord::Migration[7.1]
  def change
    add_reference :comments, :parent,
                  foreign_key: { to_table: :comments, on_delete: :cascade }
  end
end

# Model
class Comment < ApplicationRecord
  belongs_to :parent, class_name: 'Comment', optional: true
  has_many :replies, class_name: 'Comment', foreign_key: :parent_id
end

In create_table

create_table :categories do |t|
  t.string :name
  t.references :parent, foreign_key: { to_table: :categories }

  t.timestamps
end

Composite foreign keys reference multiple columns in the parent table. Rails 7.1+ has built-in support for composite foreign keys.

Rails 7.1+ Composite Foreign Key

class AddCompositeForeignKey < ActiveRecord::Migration[7.1]
  def change
    add_foreign_key :bookings, :travel_routes,
                    column: [:origin, :destination],
                    primary_key: [:origin, :destination]
  end
end

With create_table

create_table :bookings do |t|
  t.string :origin, null: false
  t.string :destination, null: false
  t.datetime :departure_time

  t.timestamps
end

add_foreign_key :bookings, :travel_routes,
                column: [:origin, :destination],
                primary_key: [:origin, :destination]

Pre-Rails 7.1: Use SQL

class AddCompositeForeignKey < ActiveRecord::Migration[7.0]
  def up
    execute <<-SQL
      ALTER TABLE bookings
      ADD CONSTRAINT fk_bookings_routes
      FOREIGN KEY (origin, destination)
      REFERENCES travel_routes (origin, destination);
    SQL
  end

  def down
    execute <<-SQL
      ALTER TABLE bookings
      DROP CONSTRAINT fk_bookings_routes;
    SQL
  end
end

Model Association

class Booking < ApplicationRecord
  belongs_to :travel_route,
             foreign_key: [:origin, :destination],
             primary_key: [:origin, :destination]
end

This is a migration version compatibility issue. Rails 5.1+ uses bigint for primary keys by default, but older migration versions still use integer for references.

The Problem

# In a Rails 6.0 migration
class AddUserToPosts < ActiveRecord::Migration[6.0]
  def change
    add_reference :posts, :user, foreign_key: true
    # Creates user_id as INTEGER, not BIGINT!
  end
end

The Error

ActiveRecord::MismatchedForeignKey: Column `user_id` on table `posts`
does not match column `id` on `users`, which has type `bigint`.

Fix: Specify Type Explicitly

class AddUserToPosts < ActiveRecord::Migration[6.0]
  def change
    add_reference :posts, :user, type: :bigint, foreign_key: true
  end
end

Fix Existing Column

class FixUserIdType < ActiveRecord::Migration[7.1]
  def up
    # Remove FK first
    remove_foreign_key :posts, :users if foreign_key_exists?(:posts, :users)

    # Change column type
    change_column :posts, :user_id, :bigint

    # Re-add FK
    add_foreign_key :posts, :users
  end

  def down
    remove_foreign_key :posts, :users
    change_column :posts, :user_id, :integer
    add_foreign_key :posts, :users
  end
end

Best Practice

Always use the latest migration version to get sensible defaults, or explicitly specify type: :bigint for references.

This is a known issue with migration version compatibility. Different Rails versions generate different index names for polymorphic associations.

The Issue

# In older migrations
class AddCommentable < ActiveRecord::Migration[6.0]
  def change
    add_reference :comments, :commentable, polymorphic: true
  end
end

# Might create: index_comments_on_commentable_type_and_commentable_id
# Or: index_comments_on_commentable

Index Name Varies by Version

• Rails 6.0: index_comments_on_commentable_type_and_commentable_id
• Rails 7.0+: index_comments_on_commentable

Fix: Specify Index Name

class AddCommentable < ActiveRecord::Migration[7.1]
  def change
    add_reference :comments, :commentable, polymorphic: true,
                  index: { name: 'idx_comments_on_commentable' }
  end
end

Fix Existing Index

class RenamePolymorphicIndex < ActiveRecord::Migration[7.1]
  def change
    rename_index :comments,
                 'index_comments_on_commentable_type_and_commentable_id',
                 'index_comments_on_commentable'
  end
end

Check Current Index Name

# In Rails console
ActiveRecord::Base.connection.indexes(:comments).map(&:name)

The change method is the modern way to write reversible migrations. Rails automatically figures out how to reverse most operations.

Basic change Method

class AddEmailToUsers < ActiveRecord::Migration[7.1]
  def change
    add_column :users, :email, :string
    add_index :users, :email, unique: true
  end
end

# Rails knows to:
# - UP: add_column, add_index
# - DOWN: remove_index, remove_column

Reversible Operations in change

# These are automatically reversible:
add_column / remove_column (with type)
add_index / remove_index
add_reference / remove_reference
add_timestamps / remove_timestamps
add_foreign_key / remove_foreign_key
create_table / drop_table (with block)
create_join_table / drop_join_table
rename_column
rename_table
rename_index
change_column_default (with from/to)
change_column_null

When change Doesn't Work

# These need up/down:
change_column  # Can't know previous type
remove_column without type
execute (raw SQL)
Custom reversibility logic

Using reversible Inside change

class UpdateData < ActiveRecord::Migration[7.1]
  def change
    add_column :users, :status, :string, default: 'active'

    reversible do |dir|
      dir.up do
        User.update_all(status: 'active')
      end
      dir.down do
        # Nothing needed for down
      end
    end
  end
end

When to Use up/down Instead

class ChangeColumnType < ActiveRecord::Migration[7.1]
  # change_column is not reversible
  def up
    change_column :users, :age, :string
  end

  def down
    change_column :users, :age, :integer
  end
end

Use up and down methods when the migration isn't automatically reversible, or when you need different logic for each direction.

Operations That Need up/down

# change_column - Rails can't know the previous type
class ChangeColumnType < ActiveRecord::Migration[7.1]
  def up
    change_column :users, :phone, :string, limit: 20
  end

  def down
    change_column :users, :phone, :string, limit: 255
  end
end

Data Transformations

class SplitName < ActiveRecord::Migration[7.1]
  def up
    add_column :users, :first_name, :string
    add_column :users, :last_name, :string

    User.find_each do |user|
      parts = user.name.to_s.split(' ', 2)
      user.update_columns(first_name: parts[0], last_name: parts[1])
    end

    remove_column :users, :name
  end

  def down
    add_column :users, :name, :string

    User.find_each do |user|
      user.update_column(:name, "#{user.first_name} #{user.last_name}".strip)
    end

    remove_column :users, :first_name
    remove_column :users, :last_name
  end
end

Raw SQL

class CreateView < ActiveRecord::Migration[7.1]
  def up
    execute <<-SQL
      CREATE VIEW active_users AS
      SELECT * FROM users WHERE active = true;
    SQL
  end

  def down
    execute "DROP VIEW active_users;"
  end
end

Different Index Strategies

class ChangeIndex < ActiveRecord::Migration[7.1]
  def up
    remove_index :orders, :user_id
    add_index :orders, [:user_id, :created_at]
  end

  def down
    remove_index :orders, [:user_id, :created_at]
    add_index :orders, :user_id
  end
end

The reversible helper lets you define custom up/down behavior within a change method.

Basic reversible Usage

class AddStatusWithData < ActiveRecord::Migration[7.1]
  def change
    add_column :orders, :status, :string

    reversible do |dir|
      dir.up do
        execute "UPDATE orders SET status = 'pending' WHERE status IS NULL"
      end
      # dir.down is optional - nothing to undo here
    end

    change_column_null :orders, :status, false
  end
end

Custom Logic Both Directions

class ChangeColumnWithCast < ActiveRecord::Migration[7.1]
  def change
    reversible do |dir|
      dir.up do
        change_column :orders, :amount, :decimal, precision: 10, scale: 2
      end
      dir.down do
        change_column :orders, :amount, :integer
      end
    end
  end
end

Raw SQL in Both Directions

class CreateFunction < ActiveRecord::Migration[7.1]
  def change
    reversible do |dir|
      dir.up do
        execute <<-SQL
          CREATE FUNCTION update_timestamp()
          RETURNS TRIGGER AS $$
          BEGIN
            NEW.updated_at = NOW();
            RETURN NEW;
          END;
          $$ LANGUAGE plpgsql;
        SQL
      end
      dir.down do
        execute "DROP FUNCTION update_timestamp();"
      end
    end
  end
end

Combining with Regular change Operations

class ComplexMigration < ActiveRecord::Migration[7.1]
  def change
    # This is automatically reversible
    add_column :users, :settings, :jsonb, default: {}

    # This needs custom handling
    reversible do |dir|
      dir.up do
        User.where(legacy_settings: nil).update_all(settings: {})
      end
    end

    # This is also automatically reversible
    remove_column :users, :legacy_settings, :text
  end
end

Use raise ActiveRecord::IrreversibleMigration in the down method to explicitly prevent rollback of destructive or one-way migrations.

Raise IrreversibleMigration

class DropLegacyData < ActiveRecord::Migration[7.1]
  def up
    drop_table :legacy_users
    drop_table :legacy_orders
  end

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

In change Method

class DestructiveMigration < ActiveRecord::Migration[7.1]
  def change
    drop_table :temporary_data

    reversible do |dir|
      dir.down do
        raise ActiveRecord::IrreversibleMigration
      end
    end
  end
end

With Helpful Message

class TruncateAuditLogs < ActiveRecord::Migration[7.1]
  def up
    execute "TRUNCATE TABLE audit_logs"
  end

  def down
    raise ActiveRecord::IrreversibleMigration, <<~MSG
      Cannot reverse truncation of audit_logs.
      Data has been permanently deleted.
      If you need to rollback, restore from a backup.
    MSG
  end
end

Common Irreversible Operations

• Dropping tables without schema definition
• Deleting or truncating data
• Lossy type conversions (text → integer)
• Removing columns that had unique data

Use the revert method to undo the changes from a previous migration class, useful when you need to undo and redo changes differently.

Revert Entire Migration

class RevertAddEmailIndex < ActiveRecord::Migration[7.1]
  def change
    revert AddEmailToUsers
  end
end

Revert Specific Operations

class PartialRevert < ActiveRecord::Migration[7.1]
  def change
    revert do
      # Only revert these specific operations
      add_column :users, :temporary_field, :string
      add_index :users, :temporary_field
    end
  end
end

Revert and Replace

class ReplaceIndexStrategy < ActiveRecord::Migration[7.1]
  def change
    # Undo the old index
    revert AddUserIndex

    # Add new improved index
    add_index :users, [:email, :created_at], name: 'idx_users_email_created'
  end
end

Important: Migration Must Be Reversible

The migration you're reverting must use reversible operations. If it uses up/down, the down method will be executed.

# This works because AddEmailToUsers uses `change`
revert AddEmailToUsers

# For up/down migrations, `revert` runs the `down` method

The down_only helper (proposed for Rails 8) lets you specify code that only runs during rollback. Until then, use reversible.

Current Approach with reversible

class AddColumnWithBackfill < ActiveRecord::Migration[7.1]
  def change
    add_column :users, :full_name, :string

    reversible do |dir|
      dir.up do
        User.update_all("full_name = first_name || ' ' || last_name")
      end
      # Nothing needed for down - column removal handles it
    end
  end
end

Proposed down_only Syntax (Future Rails)

# This syntax may be available in future Rails versions
class RemoveColumn < ActiveRecord::Migration[8.0]
  def change
    remove_column :users, :legacy_field, :string

    down_only do
      # Only runs during rollback
      User.update_all(legacy_field: 'default_value')
    end
  end
end

Current Workaround

class Migration < ActiveRecord::Migration[7.1]
  def change
    remove_column :users, :old_field, :string

    reversible do |dir|
      dir.down do
        # This runs only during rollback
        User.where(old_field: nil).update_all(old_field: '')
      end
    end
  end
end

Helper Method Pattern

# In migration
def down_only(&block)
  reversible do |dir|
    dir.down(&block)
  end
end

You can use ActiveRecord or raw SQL to update data during migrations. Be cautious with large tables and consider performance implications.

Using ActiveRecord

class BackfillStatus < ActiveRecord::Migration[7.1]
  def up
    User.where(status: nil).update_all(status: 'active')
  end

  def down
    # Usually nothing needed
  end
end

Using Raw SQL (Faster)

class BackfillStatus < ActiveRecord::Migration[7.1]
  def up
    execute "UPDATE users SET status = 'active' WHERE status IS NULL"
  end

  def down
    # Nothing needed
  end
end

Batch Updates (Large Tables)

class BackfillData < ActiveRecord::Migration[7.1]
  def up
    User.in_batches(of: 10_000) do |batch|
      batch.update_all(status: 'active')
    end
  end

  def down
    # Nothing needed
  end
end

With Progress Output

class BackfillData < ActiveRecord::Migration[7.1]
  def up
    total = User.where(status: nil).count
    say "Updating #{total} users..."

    User.where(status: nil).find_each.with_index do |user, index|
      user.update_column(:status, 'active')
      say_with_time "Progress: #{index + 1}/#{total}" if (index + 1) % 1000 == 0
    end
  end

  def down
    # Nothing needed
  end
end

Conditional Updates

class SetDefaultRoles < ActiveRecord::Migration[7.1]
  def up
    execute <<-SQL
      UPDATE users
      SET role = CASE
        WHEN admin = true THEN 'admin'
        WHEN moderator = true THEN 'moderator'
        ELSE 'member'
      END
      WHERE role IS NULL
    SQL
  end

  def down
    # Nothing needed
  end
end

Adding a NOT NULL column to a table with existing data requires either a default value or backfilling before adding the constraint.

Option 1: With Default (Simplest)

class AddStatusToOrders < ActiveRecord::Migration[7.1]
  def change
    # PostgreSQL 11+ applies default to existing rows
    add_column :orders, :status, :string, default: 'pending', null: false
  end
end

Option 2: Backfill Then Constrain

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

    # Step 2: Backfill existing records
    reversible do |dir|
      dir.up do
        Order.where(status: nil).update_all(status: 'pending')
      end
    end

    # Step 3: Add NOT NULL constraint
    change_column_null :orders, :status, false

    # Step 4: Add default for new records
    change_column_default :orders, :status, 'pending'
  end
end

Option 3: For Very Large Tables

class AddStatusToOrders < ActiveRecord::Migration[7.1]
  disable_ddl_transaction!

  def up
    # Add column without lock
    add_column :orders, :status, :string

    # Backfill in batches
    Order.in_batches(of: 50_000) do |batch|
      batch.where(status: nil).update_all(status: 'pending')
    end

    # Add constraint with validation
    change_column_null :orders, :status, false
    change_column_default :orders, :status, 'pending'
  end

  def down
    remove_column :orders, :status
  end
end

Using SQL for Speed

class AddStatusToOrders < ActiveRecord::Migration[7.1]
  def up
    add_column :orders, :status, :string

    execute "UPDATE orders SET status = 'pending' WHERE status IS NULL"

    change_column_null :orders, :status, false
    change_column_default :orders, :status, 'pending'
  end

  def down
    remove_column :orders, :status
  end
end

Using models in migrations can be risky because the model may change after the migration is written, causing failures when running old migrations.

The Problem

# This migration worked when written...
class BackfillData < ActiveRecord::Migration[7.1]
  def up
    User.where(status: nil).update_all(status: 'active')
  end
end

# But later, if you rename status to state:
# - Old migration still references :status
# - Running migrations on fresh DB fails

Solution 1: Define Migration-Local Model

class BackfillData < ActiveRecord::Migration[7.1]
  class User < ApplicationRecord
    # Isolated model that won't change
  end

  def up
    User.where(status: nil).update_all(status: 'active')
  end
end

Solution 2: Use Raw SQL

class BackfillData < ActiveRecord::Migration[7.1]
  def up
    execute "UPDATE users SET status = 'active' WHERE status IS NULL"
  end
end

Solution 3: Reset Column Information

class AddAndBackfill < ActiveRecord::Migration[7.1]
  def up
    add_column :users, :full_name, :string

    User.reset_column_information  # Refresh model's column cache

    User.find_each do |user|
      user.update_column(:full_name, "#{user.first_name} #{user.last_name}")
    end
  end
end

Best Practices

• Prefer raw SQL for simple updates
• Define local model classes for complex logic
• Call reset_column_information after schema changes
• Avoid callbacks/validations: use update_column/update_all

Data migrations modify data rather than schema. They require extra care to avoid data loss, long locks, and deployment issues.

Separate Schema and Data Migrations

# Migration 1: Schema change (runs during deploy)
class AddStatusColumn < ActiveRecord::Migration[7.1]
  def change
    add_column :orders, :status, :string
  end
end

# Migration 2: Data backfill (can run separately)
class BackfillStatus < ActiveRecord::Migration[7.1]
  def up
    Order.in_batches.update_all(status: 'pending')
  end
end

# Migration 3: Add constraint (after backfill complete)
class AddStatusConstraint < ActiveRecord::Migration[7.1]
  def change
    change_column_null :orders, :status, false
  end
end

Use Batching for Large Tables

class BackfillSafely < ActiveRecord::Migration[7.1]
  disable_ddl_transaction!

  def up
    Order.in_batches(of: 10_000) do |batch|
      batch.update_all(status: 'pending')
      sleep(0.1)  # Give database a breather
    end
  end
end

Idempotent Migrations

class SafeBackfill < ActiveRecord::Migration[7.1]
  def up
    # Only update records that need it
    Order.where(status: nil).in_batches.update_all(status: 'pending')
  end

  def down
    # Explicitly do nothing or document why
  end
end

Consider Using a Data Migration Gem

# Gems like 'data_migrate' separate data migrations
# from schema migrations, allowing independent execution

# Schema migrations: db/migrate/
# Data migrations: db/data/

Production Safety Checklist

• Test on production-like data volume
• Run during low-traffic periods
• Have a rollback plan
• Monitor database load during execution
• Use disable_ddl_transaction! if needed

Use execute to run raw SQL statements when Rails' DSL doesn't support what you need.

Basic execute

class RunRawSql < ActiveRecord::Migration[7.1]
  def up
    execute "UPDATE users SET status = 'active' WHERE confirmed_at IS NOT NULL"
  end

  def down
    execute "UPDATE users SET status = NULL WHERE status = 'active'"
  end
end

Multi-line SQL with Heredoc

class CreateComplexIndex < ActiveRecord::Migration[7.1]
  def up
    execute <<-SQL
      CREATE INDEX CONCURRENTLY idx_users_search
      ON users
      USING GIN (to_tsvector('english', name || ' ' || email));
    SQL
  end

  def down
    execute "DROP INDEX idx_users_search;"
  end
end

With reversible

class CreateView < ActiveRecord::Migration[7.1]
  def change
    reversible do |dir|
      dir.up do
        execute <<-SQL
          CREATE VIEW active_users AS
          SELECT id, name, email, created_at
          FROM users
          WHERE active = true;
        SQL
      end
      dir.down do
        execute "DROP VIEW active_users;"
      end
    end
  end
end

Query Results

class DataMigration < ActiveRecord::Migration[7.1]
  def up
    # execute returns result object
    results = execute("SELECT id, name FROM users WHERE status IS NULL")

    results.each do |row|
      puts "User #{row['id']}: #{row['name']}"
    end
  end
end

Database-Specific SQL

class PostgresSpecific < ActiveRecord::Migration[7.1]
  def up
    return unless postgresql?

    execute "CREATE EXTENSION IF NOT EXISTS pg_trgm;"
  end

  private

  def postgresql?
    ActiveRecord::Base.connection.adapter_name.downcase.include?('postgresql')
  end
end

Database views are virtual tables based on a SELECT query. They're useful for complex queries, data abstraction, and security.

Create View with SQL

class CreateActiveUsersView < ActiveRecord::Migration[7.1]
  def up
    execute <<-SQL
      CREATE VIEW active_users AS
      SELECT id, name, email, created_at
      FROM users
      WHERE active = true AND deleted_at IS NULL;
    SQL
  end

  def down
    execute "DROP VIEW active_users;"
  end
end

Using reversible

class CreateOrderSummaryView < ActiveRecord::Migration[7.1]
  def change
    reversible do |dir|
      dir.up do
        execute <<-SQL
          CREATE VIEW order_summaries AS
          SELECT
            users.id AS user_id,
            users.name AS user_name,
            COUNT(orders.id) AS order_count,
            SUM(orders.total) AS total_spent
          FROM users
          LEFT JOIN orders ON orders.user_id = users.id
          GROUP BY users.id, users.name;
        SQL
      end
      dir.down do
        execute "DROP VIEW order_summaries;"
      end
    end
  end
end

Materialized View (PostgreSQL)

class CreateMaterializedView < ActiveRecord::Migration[7.1]
  def up
    execute <<-SQL
      CREATE MATERIALIZED VIEW daily_stats AS
      SELECT
        DATE(created_at) AS date,
        COUNT(*) AS order_count,
        SUM(total) AS revenue
      FROM orders
      GROUP BY DATE(created_at);

      CREATE UNIQUE INDEX idx_daily_stats_date ON daily_stats (date);
    SQL
  end

  def down
    execute "DROP MATERIALIZED VIEW daily_stats;"
  end
end

Using View in Rails Model

class ActiveUser < ApplicationRecord
  self.table_name = 'active_users'
  self.primary_key = 'id'

  # Read-only by default
  def readonly?
    true
  end
end

Stored procedures are precompiled SQL code stored in the database. They're database-specific—use functions for PostgreSQL.

PostgreSQL Function

class CreateArchiveFunction < ActiveRecord::Migration[7.1]
  def up
    execute <<-SQL
      CREATE OR REPLACE FUNCTION archive_old_orders(days_old INTEGER)
      RETURNS INTEGER AS $$
      DECLARE
        archived_count INTEGER;
      BEGIN
        WITH moved AS (
          DELETE FROM orders
          WHERE created_at < NOW() - (days_old || ' days')::INTERVAL
          RETURNING *
        )
        INSERT INTO archived_orders SELECT * FROM moved;

        GET DIAGNOSTICS archived_count = ROW_COUNT;
        RETURN archived_count;
      END;
      $$ LANGUAGE plpgsql;
    SQL
  end

  def down
    execute "DROP FUNCTION IF EXISTS archive_old_orders(INTEGER);"
  end
end

MySQL Stored Procedure

class CreateArchiveProcedure < ActiveRecord::Migration[7.1]
  def up
    execute <<-SQL
      CREATE PROCEDURE archive_old_orders(IN days_old INT)
      BEGIN
        INSERT INTO archived_orders
        SELECT * FROM orders
        WHERE created_at < DATE_SUB(NOW(), INTERVAL days_old DAY);

        DELETE FROM orders
        WHERE created_at < DATE_SUB(NOW(), INTERVAL days_old DAY);
      END
    SQL
  end

  def down
    execute "DROP PROCEDURE IF EXISTS archive_old_orders;"
  end
end

Calling from Rails

# PostgreSQL function
result = ActiveRecord::Base.connection.execute("SELECT archive_old_orders(90)")
archived_count = result.first['archive_old_orders']

# MySQL procedure
ActiveRecord::Base.connection.execute("CALL archive_old_orders(90)")

Triggers automatically execute code in response to database events (INSERT, UPDATE, DELETE). They're useful for audit logs and data integrity.

PostgreSQL Trigger

class CreateAuditTrigger < ActiveRecord::Migration[7.1]
  def up
    # Create the trigger function
    execute <<-SQL
      CREATE OR REPLACE FUNCTION audit_user_changes()
      RETURNS TRIGGER AS $$
      BEGIN
        INSERT INTO user_audits (user_id, action, old_data, new_data, created_at)
        VALUES (
          COALESCE(NEW.id, OLD.id),
          TG_OP,
          row_to_json(OLD),
          row_to_json(NEW),
          NOW()
        );
        RETURN NEW;
      END;
      $$ LANGUAGE plpgsql;
    SQL

    # Create the trigger
    execute <<-SQL
      CREATE TRIGGER users_audit_trigger
      AFTER INSERT OR UPDATE OR DELETE ON users
      FOR EACH ROW
      EXECUTE FUNCTION audit_user_changes();
    SQL
  end

  def down
    execute "DROP TRIGGER IF EXISTS users_audit_trigger ON users;"
    execute "DROP FUNCTION IF EXISTS audit_user_changes();"
  end
end

Auto-Update Timestamp Trigger

class CreateUpdatedAtTrigger < ActiveRecord::Migration[7.1]
  def up
    execute <<-SQL
      CREATE OR REPLACE FUNCTION update_updated_at()
      RETURNS TRIGGER AS $$
      BEGIN
        NEW.updated_at = NOW();
        RETURN NEW;
      END;
      $$ LANGUAGE plpgsql;

      CREATE TRIGGER set_updated_at
      BEFORE UPDATE ON orders
      FOR EACH ROW
      EXECUTE FUNCTION update_updated_at();
    SQL
  end

  def down
    execute "DROP TRIGGER IF EXISTS set_updated_at ON orders;"
    execute "DROP FUNCTION IF EXISTS update_updated_at();"
  end
end

MySQL Trigger

class CreateMySQLTrigger < ActiveRecord::Migration[7.1]
  def up
    execute <<-SQL
      CREATE TRIGGER before_user_update
      BEFORE UPDATE ON users
      FOR EACH ROW
      BEGIN
        INSERT INTO user_audits (user_id, old_name, new_name, changed_at)
        VALUES (OLD.id, OLD.name, NEW.name, NOW());
      END
    SQL
  end

  def down
    execute "DROP TRIGGER IF EXISTS before_user_update;"
  end
end

PostgreSQL schemas are namespaces that organize database objects. Useful for multi-tenant applications or separating concerns.

Create Schema (Rails 7.1+)

class CreateTenantSchema < ActiveRecord::Migration[7.1]
  def change
    create_schema :tenant_acme
  end
end

Create Schema with Raw SQL

class CreateTenantSchema < ActiveRecord::Migration[7.1]
  def up
    execute "CREATE SCHEMA IF NOT EXISTS tenant_acme;"
  end

  def down
    execute "DROP SCHEMA IF EXISTS tenant_acme CASCADE;"
  end
end

Create Table in Schema

class CreateTenantUsers < ActiveRecord::Migration[7.1]
  def up
    execute "CREATE SCHEMA IF NOT EXISTS tenant_acme;"

    execute <<-SQL
      CREATE TABLE tenant_acme.users (
        id BIGSERIAL PRIMARY KEY,
        name VARCHAR(255),
        email VARCHAR(255),
        created_at TIMESTAMP,
        updated_at TIMESTAMP
      );
    SQL
  end

  def down
    execute "DROP TABLE IF EXISTS tenant_acme.users;"
    execute "DROP SCHEMA IF EXISTS tenant_acme;"
  end
end

Using Schema in Rails

# In model
class Tenant::User < ApplicationRecord
  self.table_name = 'tenant_acme.users'
end

# Or set schema search path
ActiveRecord::Base.connection.schema_search_path = 'tenant_acme,public'

Multi-Tenant Pattern

class CreateTenantSchema < ActiveRecord::Migration[7.1]
  def change
    reversible do |dir|
      dir.up do
        execute "CREATE SCHEMA IF NOT EXISTS #{tenant_schema};"
        # Copy structure from template
        execute "CREATE TABLE #{tenant_schema}.users (LIKE public.users INCLUDING ALL);"
      end
      dir.down do
        execute "DROP SCHEMA IF EXISTS #{tenant_schema} CASCADE;"
      end
    end
  end

  private

  def tenant_schema
    ENV['TENANT_SCHEMA'] || 'tenant_default'
  end
end

The create_schema method was not automatically reversible in earlier Rails versions. Rails 7.1+ added drop_schema support.

The Error (Pre-Rails 7.1)

ActiveRecord::IrreversibleMigration:
create_schema is not reversible.

Fix: Use up/down

class CreateAnalyticsSchema < ActiveRecord::Migration[7.0]
  def up
    execute "CREATE SCHEMA analytics;"
  end

  def down
    execute "DROP SCHEMA analytics CASCADE;"
  end
end

Rails 7.1+ Reversible Version

class CreateAnalyticsSchema < ActiveRecord::Migration[7.1]
  def change
    create_schema :analytics
    # Automatically reversed with drop_schema
  end
end

With reversible Helper

class CreateSchema < ActiveRecord::Migration[7.0]
  def change
    reversible do |dir|
      dir.up { execute "CREATE SCHEMA analytics;" }
      dir.down { execute "DROP SCHEMA analytics CASCADE;" }
    end
  end
end

Warning: CASCADE Drops Everything

Using CASCADE in the down method will drop all objects in the schema. Be careful in production!

Rails 6+ supports multiple databases. Each database has its own migrations directory and migration commands.

Database Configuration

# config/database.yml
production:
  primary:
    database: myapp_production
    adapter: postgresql
  analytics:
    database: myapp_analytics
    adapter: postgresql
    migrations_paths: db/analytics_migrate

Run All Migrations

# Run migrations for all databases
rails db:migrate

# This runs migrations in:
# - db/migrate (primary)
# - db/analytics_migrate (analytics)

Run Migrations for Specific Database

# Only primary database
rails db:migrate:primary

# Only analytics database
rails db:migrate:analytics

Check Status

# Status for all databases
rails db:migrate:status

# Status for specific database
rails db:migrate:status:analytics

Rollback

# Rollback primary
rails db:rollback:primary

# Rollback analytics
rails db:rollback:analytics STEP=2

Directory Structure

db/
├── migrate/                    # Primary database
│   └── 20260106_create_users.rb
├── analytics_migrate/          # Analytics database
│   └── 20260106_create_events.rb
├── schema.rb                   # Primary schema
└── analytics_schema.rb         # Analytics schema

Use the --database flag when generating migrations to target a specific database in a multi-database setup.

Generate for Specific Database

# Generate migration for primary (default)
rails g migration CreateUsers name:string

# Generate migration for analytics database
rails g migration CreateEvents --database=analytics

File Location

# Primary: db/migrate/20260106_create_users.rb
# Analytics: db/analytics_migrate/20260106_create_events.rb

In the Migration File

# db/analytics_migrate/20260106_create_events.rb
class CreateEvents < ActiveRecord::Migration[7.1]
  def change
    create_table :events do |t|
      t.string :name
      t.jsonb :properties
      t.timestamps
    end
  end
end

Generate Model for Specific Database

rails g model Event name:string --database=analytics

Model Configuration

# app/models/analytics_record.rb
class AnalyticsRecord < ApplicationRecord
  self.abstract_class = true
  connects_to database: { writing: :analytics, reading: :analytics }
end

# app/models/event.rb
class Event < AnalyticsRecord
  # Uses analytics database
end

Rails provides commands to check migration status across all configured databases.

Check All Databases

rails db:migrate:status

Output:

database: myapp_development

 Status   Migration ID    Migration Name
--------------------------------------------------
   up     20260101120000  Create users
   up     20260102120000  Add email to users

database: myapp_analytics_development

 Status   Migration ID    Migration Name
--------------------------------------------------
   up     20260101120000  Create events
  down    20260106120000  Add properties to events

Check Specific Database

rails db:migrate:status:primary
rails db:migrate:status:analytics

Programmatic Check

# In Rails console or code
ActiveRecord::Base.configurations.configs_for(env_name: Rails.env).each do |config|
  ActiveRecord::Base.establish_connection(config)
  context = ActiveRecord::MigrationContext.new(config.migrations_paths)

  puts "Database: #{config.name}"
  puts "Pending: #{context.open.pending_migrations.map(&:version)}"
end

In Application Code

# Check if any migrations are pending
ActiveRecord::Migration.check_all_pending!

This usually happens due to incorrect migrations_paths configuration or migration files in the wrong directory.

Common Causes

• Migration file in wrong directory
• Missing migrations_paths in database.yml
• Incorrect database name in command

Fix: Check migrations_paths

# config/database.yml
development:
  primary:
    database: myapp_development
    migrations_paths: db/migrate  # Explicit path
  analytics:
    database: myapp_analytics_development
    migrations_paths: db/analytics_migrate  # Must be different!

Fix: Check File Location

# Verify migration is in correct directory
ls db/migrate/               # Primary migrations
ls db/analytics_migrate/     # Analytics migrations

Fix: Specify Database in Generator

# Always specify database when generating
rails g migration CreateEvents --database=analytics

Debug: Check Which DB Migration Runs On

class CreateEvents < ActiveRecord::Migration[7.1]
  def change
    puts "Running on: #{ActiveRecord::Base.connection_db_config.name}"
    create_table :events do |t|
      t.timestamps
    end
  end
end

Common Error

Table 'events' already exists in primary database
-- when it should be in analytics database

Fix: Move the migration file to the correct directory and ensure migrations_paths is configured.

The pending migration check might not detect secondary database migrations due to configuration issues or middleware behavior.

The Problem

# Server starts without error even though analytics DB has pending migrations
# ActiveRecord::PendingMigrationError not raised

Fix: Check All Databases in Middleware

# config/application.rb
config.active_record.migration_error = :page_load

# This should check all databases, but verify your Rails version

Fix: Manual Check

# config/initializers/check_pending_migrations.rb
if Rails.env.development?
  ActiveRecord::Migration.check_all_pending!
end

Rails 7.1+ Behavior

# Rails 7.1+ checks all databases by default
# If still not working, ensure migrations_paths is set

Verify Configuration

# Check that Rails sees both migration paths
rails runner "puts ActiveRecord::Base.configurations.configs_for(env_name: 'development').map(&:migrations_paths)"

Fix: database_tasks Option

# Ensure database_tasks is not false
development:
  primary:
    database: myapp_dev
    database_tasks: true  # Default, but be explicit
  analytics:
    database: myapp_analytics_dev
    database_tasks: true  # Must be true for migrations to run

Common Gotcha

# This DISABLES migration tasks for analytics!
analytics:
  database: analytics_db
  database_tasks: false  # No migrations will run or be detected

When using multiple databases, migrations run in timestamp order across all databases by default. This can cause issues if migrations have dependencies.

The Problem

# These run in timestamp order:
20260106100000_create_users.rb (primary)
20260106100001_create_events.rb (analytics)  # Might need users first!
20260106100002_add_user_to_events.rb (analytics)

Fix: Run Databases Separately

# Run primary first
rails db:migrate:primary

# Then analytics
rails db:migrate:analytics

Fix: Use Later Timestamps

# Ensure dependent migrations have later timestamps
# Primary: 20260106100000_create_users.rb
# Analytics: 20260106110000_create_events.rb (1 hour later)

For db:prepare

Rails 7.1+ fixed ordering for db:prepare:

# This now handles ordering correctly
rails db:prepare

Deployment Strategy

# In deployment script
#!/bin/bash
set -e

# Run primary database migrations first
rails db:migrate:primary

# Then other databases
rails db:migrate:analytics

# Or for all databases in correct order (Rails 7.1+)
rails db:prepare

Setting database_tasks: false on the primary database can prevent migrations from running on other databases.

The Problem

# config/database.yml
production:
  primary:
    database: external_db
    database_tasks: false  # Can't run migrations on external DB
  internal:
    database: internal_db
    migrations_paths: db/internal_migrate
    # Migrations don't run here either!

The Error

rails db:migrate
# Nothing happens, or
# ActiveRecord::ConnectionNotEstablished

Fix: Run Specific Database

# Explicitly run internal database migrations
rails db:migrate:internal

Fix: Reorder Databases

# Put the database with database_tasks last
production:
  internal:
    database: internal_db
    migrations_paths: db/internal_migrate
  primary:  # Still called primary, but listed second
    database: external_db
    database_tasks: false

Fix: Use Different Primary

production:
  primary:
    database: internal_db
    migrations_paths: db/migrate
  external:
    database: external_db
    database_tasks: false  # Now this is secondary

When to Use database_tasks: false

• External databases you don't control
• Read replicas
• Databases managed by other applications

Rails can dump your database schema in two formats: Ruby DSL (schema.rb) or raw SQL (structure.sql).

schema.rb (Default)

# db/schema.rb
ActiveRecord::Schema[7.1].define(version: 2026_01_06_120000) do
  create_table "users", force: :cascade do |t|
    t.string "name"
    t.string "email"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.index ["email"], name: "index_users_on_email", unique: true
  end
end

structure.sql

-- db/structure.sql
CREATE TABLE users (
    id bigint NOT NULL,
    name character varying,
    email character varying,
    created_at timestamp(6) NOT NULL,
    updated_at timestamp(6) NOT NULL
);

CREATE UNIQUE INDEX index_users_on_email ON users (email);

Comparison

Feature	schema.rb	structure.sql
Database-agnostic	Yes	No
Views, triggers, functions	No	Yes
Custom types (enums)	Limited	Yes
Readable	Yes	Less so

When to Use structure.sql

• Using database views
• Using stored procedures or functions
• Using database triggers
• Using native enum types
• Using extensions (pgcrypto, postgis, etc.)

Configure Rails to use SQL format for schema dumps when you need database-specific features that schema.rb can't represent.

Change Configuration

# config/application.rb
config.active_record.schema_format = :sql

Generate structure.sql

# Dump current schema to structure.sql
rails db:schema:dump

# Or specifically
rails db:structure:dump

Update .gitignore

# .gitignore
# Remove or comment out:
# /db/schema.rb

# Add (if you don't want to track SQL):
# /db/structure.sql

Loading Schema

# Load structure.sql into database
rails db:schema:load

# For test database
rails db:test:prepare

Mixed Approach (Not Recommended)

You can keep both files, but it's confusing:

# This generates both files
config.active_record.schema_format = :ruby
# Then manually run: rails db:structure:dump

Per-Database Format (Rails 7+)

# config/database.yml
production:
  primary:
    schema_format: ruby
  analytics:
    schema_format: sql

In multi-database setups, you can dump schemas for individual databases or skip certain databases entirely.

Dump Specific Database

# Dump primary only
rails db:schema:dump:primary

# Dump analytics only
rails db:schema:dump:analytics

Skip Database from Schema Dump

# config/database.yml
production:
  primary:
    database: myapp_db
  external:
    database: external_db
    database_tasks: false  # Skips all tasks including dump

Custom Rake Task for Selective Dump

# lib/tasks/db.rake
namespace :db do
  namespace :schema do
    desc "Dump only internal databases"
    task dump_internal: :environment do
      %w[primary analytics].each do |db|
        Rake::Task["db:schema:dump:#{db}"].invoke
      end
    end
  end
end

Output Files

db/
├── schema.rb              # Primary database
├── analytics_schema.rb    # Analytics database
└── structure.sql          # If using SQL format

CI/CD Consideration

# In CI, you might want to verify schema is up to date
rails db:schema:dump
git diff --exit-code db/schema.rb || (echo "Schema out of date!" && exit 1)

Several issues can prevent schema.rb from updating after running migrations.

Common Causes

• Running migrations in production mode
• File permission issues
• Schema dump disabled
• Running migrations via console/script

Force Schema Dump

# Manually dump schema
rails db:schema:dump

# Or structure.sql
rails db:structure:dump

Check Configuration

# config/environments/production.rb
# This disables schema dump in production (correct behavior)
config.active_record.dump_schema_after_migration = false

# config/environments/development.rb
# This should be true (default)
config.active_record.dump_schema_after_migration = true

Running in Wrong Environment

# This might not update schema.rb
RAILS_ENV=production rails db:migrate

# This should update schema.rb
rails db:migrate  # defaults to development

Browser-Based Migration

Running migrations via the browser (development error page) may not update schema.rb. Run rails db:migrate from command line instead.

File Permissions

# Check schema.rb is writable
ls -la db/schema.rb

# Fix permissions if needed
chmod 644 db/schema.rb

Migration versioning ensures old migrations behave the same way they did when written, even after Rails upgrades. The version number (e.g., [7.1]) locks in the behavior from that Rails version.

How It Works

# Rails 6.0 behavior
class AddEmailToUsers < ActiveRecord::Migration[6.0]
  def change
    add_reference :posts, :user  # Creates INTEGER column
  end
end

# Rails 7.1 behavior
class AddEmailToUsers < ActiveRecord::Migration[7.1]
  def change
    add_reference :posts, :user  # Creates BIGINT column
  end
end

What Changes Between Versions

• 5.1: Primary keys became bigint by default
• 6.0: References defaulted to integer (for compatibility)
• 7.0: DateTime precision defaults changed
• 7.1: Index naming for rename_table changed

Why It Matters

# If you wrote this migration in 2020 (Rails 6.0)
class CreateUsers < ActiveRecord::Migration[6.0]
  def change
    create_table :users do |t|
      t.timestamps  # Creates datetime without precision
    end
  end
end

# Running it in 2026 (Rails 8.0) still creates datetime without precision
# because of the [6.0] version lock

Best Practice

# Always use current Rails version for new migrations
rails g migration AddFeature
# Generates: class AddFeature < ActiveRecord::Migration[7.1]

Don't Change Version of Existing Migrations

# BAD: Changing version can break existing databases
class OldMigration < ActiveRecord::Migration[7.1]  # Was [6.0]

# GOOD: Keep original version
class OldMigration < ActiveRecord::Migration[6.0]

If a migration version doesn't match its behavior expectations, you might see unexpected results after a Rails upgrade.

Common Scenario

# This migration was written for Rails 6, but missing version
class AddFields < ActiveRecord::Migration  # No version!
  def change
    add_column :users, :last_login, :datetime
  end
end

# In Rails 6: datetime without precision
# In Rails 7: datetime WITH precision (different behavior!)

The Error

StandardError: Directly inheriting from ActiveRecord::Migration is not supported.
Please specify the Rails release the migration was written for:

  class AddFields < ActiveRecord::Migration[7.1]

Fix: Add Version Number

# Add the version from when migration was written
class AddFields < ActiveRecord::Migration[6.0]
  def change
    add_column :users, :last_login, :datetime
  end
end

Check Compatibility Module

# See what compatibility changes exist
ActiveRecord::Migration::Compatibility::V6_0.instance_methods(false)
# Returns methods with version-specific behavior

Upgrading Strategy

# 1. Keep old migrations at their original version
class OldMigration < ActiveRecord::Migration[6.0]

# 2. Write new migrations with current version
class NewMigration < ActiveRecord::Migration[7.1]

# 3. Never change version of run migrations

Rails 7 changed the default datetime precision from nil to 6 (microseconds). This can cause issues when mixing old and new migrations.

The Change

# Rails 6 (Migration[6.x])
t.datetime :created_at  # TIMESTAMP (no precision)

# Rails 7 (Migration[7.x])
t.datetime :created_at  # TIMESTAMP(6) with microseconds

Problem: Schema Mismatch

# Fresh db:schema:load creates different columns than
# running migrations sequentially if versions are mixed

Fix: Explicit Precision in Old Migrations

class CreateUsers < ActiveRecord::Migration[6.0]
  def change
    create_table :users do |t|
      t.string :name
      # Explicitly set precision to match Rails 7 behavior
      t.timestamps precision: 6
    end
  end
end

Fix: Match Existing Schema

class AddPublishedAt < ActiveRecord::Migration[7.1]
  def change
    # Match existing timestamps (no precision) if needed
    add_column :posts, :published_at, :datetime, precision: nil
  end
end

Global Configuration

# config/application.rb
# Force all datetimes to have precision 6
config.active_record.default_precision = 6

Check Current Precision

# In Rails console
col = User.columns_hash['created_at']
puts col.precision  # nil or 6

Rails 7.1 changed how index names are generated during rename_table. This can cause issues with existing migrations.

The Change

# Rails 7.0 and earlier
rename_table :users, :members
# Index stays: index_users_on_email

# Rails 7.1+
rename_table :users, :members
# Index becomes: index_members_on_email

The Error

ArgumentError: Index name 'index_users_on_email' on table 'members' does not exist
-- when rolling back a migration

Fix: Use Migration Version

# Keep old behavior by using old version
class RenameUsersToMembers < ActiveRecord::Migration[7.0]
  def change
    rename_table :users, :members
    # Index keeps old name: index_users_on_email
  end
end

Fix: Rename Index Separately

class RenameUsersToMembers < ActiveRecord::Migration[7.1]
  def change
    rename_table :users, :members

    # Explicitly rename index if needed
    rename_index :members, 'index_users_on_email', 'index_members_on_email'
  end
end

Fix Existing Database

class FixIndexNames < ActiveRecord::Migration[7.1]
  def change
    # Rename old-style index names to new-style
    if index_exists?(:members, :email, name: 'index_users_on_email')
      rename_index :members, 'index_users_on_email', 'index_members_on_email'
    end
  end
end

Check Index Names

# In Rails console
ActiveRecord::Base.connection.indexes(:members).each do |idx|
  puts "#{idx.name}: #{idx.columns}"
end

This error occurs when there are migrations that haven't been run against your database. It's raised when starting the Rails server in development.

The Error

Migrations are pending. To resolve this issue, run:
        bin/rails db:migrate RAILS_ENV=development

ActiveRecord::PendingMigrationError

Fix: Run Migrations

# Run pending migrations
rails db:migrate

# For specific environment
RAILS_ENV=production rails db:migrate

Check What's Pending

rails db:migrate:status

Disable Check Temporarily

# config/environments/development.rb
config.active_record.migration_error = false  # Not recommended

Common Causes

• Pulled code with new migrations
• Switched branches with different migrations
• Fresh clone without running setup
• Multiple databases with pending migrations

For Multi-Database

# Check all databases
rails db:migrate:status

# Run for all databases
rails db:migrate

Rails requires unique migration names, even with different timestamps. This prevents confusion about which migration does what.

The Error

Another migration is already named add_email_to_users: db/migrate/20260105_add_email_to_users.rb

Fix: Use Unique Name

# Instead of
rails g migration AddEmailToUsers

# Use a more specific name
rails g migration AddEmailFieldToUsers
rails g migration AddEmailColumnToUsersTable
rails g migration AddUserEmailAddress

If Migration Already Exists

# Option 1: Delete old migration (if not run)
rm db/migrate/20260105_add_email_to_users.rb

# Option 2: Rename the file and class
mv db/migrate/20260106_add_email_to_users.rb \
   db/migrate/20260106_add_primary_email_to_users.rb
# Then edit the file to rename the class

Check for Duplicates

# Find migrations with similar names
ls db/migrate/ | grep -i email

Multi-Database Consideration

Different databases can have migrations with the same name since they're in different directories:

# These are OK
db/migrate/20260106_add_email.rb
db/analytics_migrate/20260106_add_email.rb

This occurs when running a migration that creates a table that already exists in your database.

The Error

PG::DuplicateTable: ERROR: relation "users" already exists
-- or --
Mysql2::Error: Table 'users' already exists
-- or --
SQLite3::SQLException: table users already exists

Cause: Migration Already Applied

# Check if table exists but migration not recorded
rails db:migrate:status

# Look for "NO FILE" entries or missing entries

Fix: Mark Migration as Run

# If table exists and is correct, mark migration as run
rails runner "ActiveRecord::SchemaMigration.create!(version: '20260106120000')"

Fix: Use if_not_exists

class CreateUsers < ActiveRecord::Migration[7.1]
  def change
    create_table :users, if_not_exists: true do |t|
      t.string :name
      t.timestamps
    end
  end
end

Fix: Drop and Recreate

# WARNING: This deletes data!
rails runner "ActiveRecord::Base.connection.drop_table(:users, if_exists: true)"
rails db:migrate

Fix: Reset Database (Development Only)

# WARNING: Drops all data!
rails db:reset

Deadlocks can occur when migrations lock tables while other processes are trying to access them, or when the CheckPending middleware conflicts with autoloading.

The Error

ActiveRecord::StatementInvalid: PG::TRDeadlockDetected: ERROR: deadlock detected
-- or --
Mysql2::Error: Deadlock found when trying to get lock

Fix: Disable DDL Transactions

class AddIndexConcurrently < ActiveRecord::Migration[7.1]
  disable_ddl_transaction!

  def change
    add_index :users, :email, algorithm: :concurrently
  end
end

Fix: Run During Low Traffic

# Schedule migrations during maintenance window
RAILS_ENV=production rails db:migrate

Fix: Use Timeouts

class SafeMigration < ActiveRecord::Migration[7.1]
  def change
    # Set statement timeout (PostgreSQL)
    execute "SET statement_timeout = '5s'"

    add_column :users, :new_field, :string

    execute "SET statement_timeout = DEFAULT"
  end
end

CheckPending Middleware Deadlock

# config/environments/development.rb
# If you're seeing deadlocks in development with classic autoloader
config.active_record.migration_error = false  # Disable auto-check
# Then run migrations manually

Avoid Long Locks

# BAD: Locks table for entire update
class BackfillData < ActiveRecord::Migration[7.1]
  def up
    User.update_all(status: 'active')  # Locks users table
  end
end

# GOOD: Process in batches
class BackfillData < ActiveRecord::Migration[7.1]
  disable_ddl_transaction!

  def up
    User.in_batches(of: 1000) do |batch|
      batch.update_all(status: 'active')
      sleep(0.1)  # Give other queries a chance
    end
  end
end

This error typically occurs with multi-database setups or when connection handling conflicts during migration checks.

The Error

ActiveRecord::ConnectionNotEstablished:
No connection pool for 'ApplicationRecord' found.

-- or --

No connection pool for 'ActiveRecord::Base' found for the 'primary' shard.

Common Causes

• CheckPending middleware issues
• Thread accessing wrong connection
• Multi-database configuration issues
• Connection closed during migration

Fix: Ensure Connection in Migration

class DataMigration < ActiveRecord::Migration[7.1]
  def up
    # Ensure we have a connection
    ActiveRecord::Base.connection_pool.with_connection do
      User.update_all(status: 'active')
    end
  end
end

Fix: Multi-Database Connection

class AnalyticsMigration < ActiveRecord::Migration[7.1]
  def up
    # Use specific connection
    AnalyticsRecord.connection.execute("UPDATE events SET processed = true")
  end
end

Fix: Check Configuration

# config/database.yml
development:
  primary:
    database: myapp_dev
    pool: 5  # Ensure pool is set
  analytics:
    database: analytics_dev
    pool: 5

Fix: Advisory Lock Issues

# If you see advisory lock issues
class Migration < ActiveRecord::Migration[7.1]
  # Rails 7+ handles this better, but for older versions:
  def change
    ActiveRecord::Base.connection.reconnect!
    # ... migration code
  end
end

Sometimes error messages reference a different column than the one causing the actual problem. This is usually due to constraint or foreign key issues.

The Misleading Error

PG::NotNullViolation: ERROR: column "status" of relation "orders" contains null values
-- But you're adding a column "priority", not "status"!

Common Causes

• Multiple changes in one migration
• Triggers updating other columns
• Generated columns or computed values
• After-save callbacks in models

Fix: Isolate the Problem

# Split into separate migrations
class AddPriority < ActiveRecord::Migration[7.1]
  def change
    add_column :orders, :priority, :integer
  end
end

class AddStatusConstraint < ActiveRecord::Migration[7.1]
  def change
    change_column_null :orders, :status, false
  end
end

Debug: Check What's Actually Happening

class DebugMigration < ActiveRecord::Migration[7.1]
  def up
    say "Before changes:"
    say Order.columns.map(&:name).inspect

    add_column :orders, :priority, :integer

    say "After add_column:"
    Order.reset_column_information
    say Order.columns.map(&:name).inspect
  end
end

Check for Triggers

# PostgreSQL: check for triggers
execute "SELECT * FROM pg_trigger WHERE tgrelid = 'orders'::regclass"

Disable Triggers Temporarily

class Migration < ActiveRecord::Migration[7.1]
  def up
    execute "ALTER TABLE orders DISABLE TRIGGER ALL"
    add_column :orders, :priority, :integer
    execute "ALTER TABLE orders ENABLE TRIGGER ALL"
  end
end

When a migration fails partway through, your database can be in an inconsistent state. Here's how to recover.

PostgreSQL: Transaction Rollback (Automatic)

PostgreSQL runs migrations in a transaction by default, so failures automatically rollback:

== 20260106120000 AddFields: migrating ===
-- add_column(:users, :email, :string)
   -> 0.0012s
-- add_index(:users, :email)
rails aborted!
PG::UniqueViolation: duplicate key value
# Transaction rolled back automatically!

MySQL: No Transaction (Manual Recovery)

# MySQL doesn't wrap DDL in transactions
# You may need to manually fix:

# 1. Check what was applied
rails db:migrate:status

# 2. Manually undo partial changes
rails runner "ActiveRecord::Base.connection.remove_column(:users, :email)"

# 3. Fix migration and retry
rails db:migrate

Clear Failed Migration from History

# If migration is marked as run but failed
rails runner "ActiveRecord::SchemaMigration.where(version: '20260106120000').delete_all"

Fix and Re-run

# Make migration idempotent
class AddEmailToUsers < ActiveRecord::Migration[7.1]
  def change
    add_column :users, :email, :string unless column_exists?(:users, :email)

    unless index_exists?(:users, :email)
      add_index :users, :email, unique: true
    end
  end
end

Disable Transaction for Partial Success

class LargeMigration < ActiveRecord::Migration[7.1]
  disable_ddl_transaction!

  def up
    # Changes are committed as they happen
    # Be careful - partial state possible on failure!
  end
end

Production Recovery Checklist

1. Don't panic - assess current state
2. Check db:migrate:status
3. Review database schema directly
4. Fix migration to be idempotent
5. Test fix on staging first
6. Apply fix to production

Rails 5+ requires specifying a version number in migration class definitions. This error occurs when using old-style migrations without a version.

The Error

StandardError: Directly inheriting from ActiveRecord::Migration is not supported.
Please specify the Rails release the migration was written for:

  class AddEmailToUsers < ActiveRecord::Migration[7.1]

The Problem

# Old style (Rails 4 and earlier)
class AddEmailToUsers < ActiveRecord::Migration  # No version!
  def change
    add_column :users, :email, :string
  end
end

Fix: Add Version Number

# Add appropriate version
class AddEmailToUsers < ActiveRecord::Migration[7.1]
  def change
    add_column :users, :email, :string
  end
end

Choosing the Right Version

# Use the Rails version when migration was written:
# - Rails 5.0 app: ActiveRecord::Migration[5.0]
# - Rails 6.1 app: ActiveRecord::Migration[6.1]
# - Rails 7.1 app: ActiveRecord::Migration[7.1]

# If unsure about old migration, use oldest supported:
class OldMigration < ActiveRecord::Migration[5.0]

Bulk Fix Old Migrations

# Find all migrations without version
grep -l "< ActiveRecord::Migration$" db/migrate/*.rb

# You can use sed to fix (careful with this!)
# sed -i 's/< ActiveRecord::Migration$/< ActiveRecord::Migration[5.0]/' db/migrate/*.rb

Available Versions

# Check available compatibility versions
ActiveRecord::Migration.descendants
# Or look at Rails source for Compatibility modules

Rails 7.1 changed how rename_table handles index names during rollback, which can break existing migrations.

The Error

ArgumentError: wrong number of arguments (given 3, expected 2)
-- when rolling back a migration with rename_table

The Cause

# This migration worked in Rails 7.0
class RenameUsers < ActiveRecord::Migration[7.0]
  def change
    rename_table :users, :members
  end
end

# But in Rails 7.1, the rollback behavior changed

Fix: Keep Original Migration Version

# DON'T change the version of existing migrations
class RenameUsers < ActiveRecord::Migration[7.0]  # Keep as 7.0!
  def change
    rename_table :users, :members
  end
end

Fix: Use up/down for Control

class RenameUsers < ActiveRecord::Migration[7.1]
  def up
    rename_table :users, :members
  end

  def down
    rename_table :members, :users
  end
end

Fix: Handle Index Names Explicitly

class RenameUsers < ActiveRecord::Migration[7.1]
  def change
    rename_table :users, :members

    # Rename indexes to match new table name
    rename_index :members, 'index_users_on_email', 'index_members_on_email'
  end
end

Check Your Rails Version

rails -v
# Rails 7.1.0+: new behavior
# Rails 7.0.x and below: old behavior

Zero-downtime migrations allow database changes without stopping your application. They require careful planning and often multiple deploy cycles.

Key Principles

• Never lock tables for long periods
• Make changes backward-compatible
• Deploy in stages
• Use concurrent operations where possible

Add Column Safely

class AddFieldSafely < ActiveRecord::Migration[7.1]
  def change
    # This is safe - no lock in PostgreSQL 11+
    add_column :users, :new_field, :string

    # DON'T do this - locks table while updating all rows
    # add_column :users, :new_field, :string, default: 'value', null: false
  end
end

Add Index Without Locking

class AddIndexConcurrently < ActiveRecord::Migration[7.1]
  disable_ddl_transaction!

  def change
    add_index :users, :email, algorithm: :concurrently
  end
end

Rename Column (Multi-Deploy)

# Deploy 1: Add new column
class AddNewColumn < ActiveRecord::Migration[7.1]
  def change
    add_column :users, :full_name, :string
  end
end

# Deploy 2: Update code to write to both columns
# Deploy 3: Backfill data
class BackfillFullName < ActiveRecord::Migration[7.1]
  disable_ddl_transaction!

  def up
    User.in_batches.update_all('full_name = name')
  end
end

# Deploy 4: Update code to read from new column only
# Deploy 5: Remove old column
class RemoveOldColumn < ActiveRecord::Migration[7.1]
  def change
    remove_column :users, :name, :string
  end
end

Remove Column Safely

# Deploy 1: Ignore column in model
class User < ApplicationRecord
  self.ignored_columns = [:old_field]
end

# Deploy 2: Remove column (after deploy 1 is stable)
class RemoveOldField < ActiveRecord::Migration[7.1]
  def change
    remove_column :users, :old_field, :string
  end
end

Tools for Safe Migrations

# Gemfile
gem 'strong_migrations'  # Catches unsafe migrations

Adding an index to a large table can lock the table for extended periods, causing downtime. Use concurrent indexing to avoid this.

PostgreSQL: Concurrent Index

class AddIndexToLargeTable < ActiveRecord::Migration[7.1]
  disable_ddl_transaction!  # Required for CONCURRENTLY

  def change
    add_index :orders, :user_id, algorithm: :concurrently
  end
end

Why disable_ddl_transaction?

CREATE INDEX CONCURRENTLY cannot run inside a transaction. disable_ddl_transaction! prevents Rails from wrapping the migration in a transaction.

Unique Concurrent Index

class AddUniqueIndex < ActiveRecord::Migration[7.1]
  disable_ddl_transaction!

  def change
    add_index :users, :email, unique: true, algorithm: :concurrently
  end
end

Handle Failed Concurrent Index

# If concurrent index creation fails, it leaves an INVALID index
# Check for invalid indexes:
# SELECT * FROM pg_indexes WHERE indexdef LIKE '%INVALID%'

class RetryIndex < ActiveRecord::Migration[7.1]
  disable_ddl_transaction!

  def up
    # Remove invalid index if exists
    remove_index :orders, :user_id, if_exists: true

    # Try again
    add_index :orders, :user_id, algorithm: :concurrently
  end
end

MySQL: pt-online-schema-change

# MySQL doesn't have CONCURRENTLY
# Use Percona tools for large tables:
pt-online-schema-change \
  --alter "ADD INDEX idx_user_id (user_id)" \
  D=myapp,t=orders

Estimate Time

# Check table size before adding index
size = ActiveRecord::Base.connection.execute(
  "SELECT pg_size_pretty(pg_total_relation_size('orders'))"
).first['pg_size_pretty']
puts "Table size: #{size}"

Removing a column while the application is running can cause errors if code still references that column. Use a multi-deploy strategy.

The Danger

# If you remove column while old code is running:
ActiveRecord::StatementInvalid: PG::UndefinedColumn:
ERROR: column "old_field" does not exist

Safe Removal Process

Step 1: Ignore Column in Model

# app/models/user.rb
class User < ApplicationRecord
  self.ignored_columns += ['old_field']
end

Step 2: Deploy and Verify

# Deploy the code change
# Wait for all servers to update
# Verify no errors related to the column

Step 3: Remove Column in Migration

class RemoveOldFieldFromUsers < ActiveRecord::Migration[7.1]
  def change
    remove_column :users, :old_field, :string
  end
end

Step 4: Remove ignored_columns (Optional)

# After migration is deployed everywhere
class User < ApplicationRecord
  # Can remove this line now
  # self.ignored_columns += ['old_field']
end

Using strong_migrations Gem

# Gemfile
gem 'strong_migrations'

# This will warn you about unsafe column removal
class RemoveColumn < ActiveRecord::Migration[7.1]
  def change
    safety_assured { remove_column :users, :old_field }
  end
end

Batch Column Removal

# app/models/user.rb
class User < ApplicationRecord
  self.ignored_columns += [
    'legacy_field1',
    'legacy_field2',
    'deprecated_at'
  ]
end

# Migration (after code deployed)
class RemoveLegacyFields < ActiveRecord::Migration[7.1]
  def change
    remove_column :users, :legacy_field1, :string
    remove_column :users, :legacy_field2, :integer
    remove_column :users, :deprecated_at, :datetime
  end
end

Testing migrations ensures they work correctly for both fresh databases and existing data. Here are strategies for testing migrations.

Manual Testing Checklist

# 1. Run migration
rails db:migrate

# 2. Verify schema
rails db:migrate:status
cat db/schema.rb | grep -A 20 "create_table :users"

# 3. Test rollback
rails db:rollback

# 4. Run again
rails db:migrate

# 5. Test with production data (on staging)
RAILS_ENV=staging rails db:migrate

Automated Migration Tests

# test/migrations/add_email_to_users_test.rb
require 'test_helper'

class AddEmailToUsersTest < ActiveSupport::TestCase
  def setup
    # Run migration up
    ActiveRecord::Migration.suppress_messages do
      AddEmailToUsers.migrate(:up)
    end
  end

  def teardown
    ActiveRecord::Migration.suppress_messages do
      AddEmailToUsers.migrate(:down)
    end
  end

  test "adds email column" do
    User.reset_column_information
    assert User.column_names.include?('email')
  end

  test "email has correct type" do
    column = User.columns_hash['email']
    assert_equal :string, column.type
  end
end

Test Data Migration

# test/migrations/backfill_status_test.rb
require 'test_helper'

class BackfillStatusTest < ActiveSupport::TestCase
  test "backfills nil status to pending" do
    # Setup: create user with nil status
    user = User.create!(name: 'Test', status: nil)

    # Run migration
    BackfillStatus.migrate(:up)

    # Verify
    user.reload
    assert_equal 'pending', user.status
  end
end

CI Pipeline Testing

# .github/workflows/test.yml
- name: Test migrations from scratch
  run: |
    rails db:create
    rails db:migrate
    rails db:seed

- name: Test migration rollback
  run: |
    rails db:rollback STEP=5
    rails db:migrate

Test Against Production Schema

# Dump production schema (safely!)
pg_dump -s production_db > production_schema.sql

# Load into test database
psql test_db < production_schema.sql

# Run pending migrations
RAILS_ENV=test rails db:migrate

Squashing migrations combines many migration files into fewer files, typically based on the current schema. Consider squashing when migrations become unwieldy.

Signs You Need to Squash

• Hundreds of migration files
• Fresh db:migrate takes too long
• Old migrations reference deleted models
• Migration history is confusing
• Starting a major new version

How to Squash

# 1. Ensure all environments are migrated
rails db:migrate:status  # All should be "up"

# 2. Delete old migrations (AFTER all DBs migrated!)
rm db/migrate/*.rb

# 3. Generate schema migration
rails db:schema:dump  # Ensure schema.rb is current

# 4. Create initial migration from schema
rails g migration InitialSchema

New Initial Migration

# db/migrate/20260106000000_initial_schema.rb
class InitialSchema < ActiveRecord::Migration[7.1]
  def change
    # Copy content from schema.rb's block
    create_table :users do |t|
      t.string :name
      t.string :email
      t.timestamps
    end

    add_index :users, :email, unique: true

    create_table :posts do |t|
      t.references :user, foreign_key: true
      t.string :title
      t.timestamps
    end

    # ... rest of schema
  end
end

Alternative: Use schema:load

# For fresh databases, use schema load instead of migrations
rails db:schema:load  # Much faster than running all migrations

Cautions

• All team members must be on same migration state
• All deployed environments must be migrated
• Can't squash if anyone has pending migrations
• Keep backups before deleting migrations

Keep Some History

# Option: Keep recent migrations, squash old ones
# Keep: Last 6 months of migrations
# Squash: Everything older into InitialSchema

# Move old migrations to archive
mkdir db/migrate_archive
mv db/migrate/2024*.rb db/migrate_archive/

Good migration names make it easy to understand what each migration does without reading the code. Rails recognizes certain patterns for auto-generation.

Auto-Generated Patterns

# AddXxxToYyy - adds columns to table
rails g migration AddEmailToUsers email:string
# Creates: add_column :users, :email, :string

# RemoveXxxFromYyy - removes columns
rails g migration RemoveAgeFromUsers age:integer
# Creates: remove_column :users, :age, :integer

# CreateXxx - creates table
rails g migration CreateProducts name:string price:decimal
# Creates: create_table :products

# AddXxxRefToYyy - adds reference
rails g migration AddUserRefToPosts user:references
# Creates: add_reference :posts, :user

Descriptive Names

# Good: Clear and specific
AddEmailIndexToUsers
CreateOrdersProductsJoinTable
RemoveDeprecatedFieldsFromUsers
ChangeStatusTypeInOrders
AddUniqueConstraintToUsersEmail

# Bad: Vague or unclear
UpdateUsers
FixDatabase
Changes
V2

Action-First Naming

# Start with verb describing the action
Add...
Remove...
Create...
Drop...
Change...
Rename...
Enable...      # For extensions
Backfill...    # For data migrations

Data Migration Names

# Clearly indicate data migrations
BackfillUserStatuses
MigrateOrdersToNewFormat
PopulateDefaultSettings
CleanupOrphanedRecords

Multi-Part Migration Names

# For related changes
AddFieldsToUsers           # Multiple fields
AddIndexesToOrders         # Multiple indexes
RemoveLegacyColumnsFromUsers

Avoid

# Don't use dates in names (timestamp handles this)
Add20260106Email           # Bad

# Don't use generic names
UpdateDatabase             # Too vague
FixBug                     # Not descriptive
Temp                       # What is this?

# Don't use abbreviations
AddUsrEmailIdx             # Hard to read
AddUserEmailIndex          # Better

File Naming

# Rails generates: timestamp_snake_case_name.rb
20260106120000_add_email_to_users.rb
20260106120001_create_orders.rb
20260106120002_add_user_ref_to_posts.rb