Rails Database Migration Cheatsheet

• Dangerous Migrations (For Large Scale Production Applications)
• Cheatsheet
• Common Commands
  • Generate a migration
  • Run all pending rails migrations
  • Database rollbacks
• Breaking Down Common Database Migration Examples
  • Creating a new table
  • Adding a foreign key reference to an existing table
  • Adding a foreign key reference with a custom column name
  • Alternative syntax for creating a foreign key reference
  • Adding or removing a column from a table

Rails Guides: ActiveRecord Migrations

Dangerous Migrations (For Large Scale Production Applications)

Certain migrations can be dangerous to perform in production because of how long they can take or how prone they are to causing exceptions if done improperly.

During a long running migration, the database tables invovled are locked, thus blocking requests to the database from the application.

The strong_migrations gem is a helpful tool for catching “dangerous” database changes. The gems’ README examples do a great job of explaining why certain migrations are “dangerous” at scale.

An alternative to using the gem is to perform any long-running/request-blocking migrations during periods of low request volume. Or to put the application in maintenance mode.

This does not avoid the other advantage of the gem, which is catching unsafe migrations that might need matching applciation layer changes.

Cheatsheet

Comprehensive rails migration cheatsheet

Common Commands

Generate a migration

bin/rails g migration AddColumnToTable

Run all pending rails migrations

bin/rails db:migrate

To migrate a specific migration out of order

rake db:migrate:up VERSION=20100905201547

Database rollbacks

Important, after creating a new migration, perform a rollback to make sure the migration is reversible

bin/rails db:rollback

Rollback a certain number of migrations

rake db:rollback STEP=5

Rollback only one specific migration (out of order) use:

rake db:migrate:down VERSION=20100905201547

Breaking Down Common Database Migration Examples

Creating a new table

class CreateUserTable < ActiveRecord::Migration[7.0]
  def change
    create_table :users do |t|
      t.string :full_name, null: false
      t.string :email, null: false
      t.string :encrypted_password, null: false
      t.boolean :admin, null: false, default: false

      t.timestamps null: false
    end

    add_index :users, :email, unique: true
    add_reference :users, :organization, 
      foreign_key: {on_delete: :cascade},
      index: true, null: false
  end
end

Adding a foreign key reference to an existing table

class AddForeignKeyReference < ActiveRecord::Migration[7.0]
  def change
    add_reference :users, :organization, 
              foreign_key: { on_delete: :cascade }, 
              null: false
  end
end

• add_reference :users denotes the table that the foreign key column will be added to
  • :organization denotes the resource the foreign key will target.
  • Rails magic will pluralize this input to infer the table name organizations and add the suffix _id to infer the column name organization_id
  • Scroll down to see how to add a foreign key to a table but specify the column name
• foreign_key: { on_delete: cascade } tells the database to also delete child records (users) with foreign keys equal to a deleted parent record id (organization)
  • If this bevaviour is not wanted, then use foreign_key: true to only add the referential db constraint that checks to make sure that the value is a valid id from the parent table
• It is not necessary to specify index: true when creating a reference. Rails will add the creation SQL to the end query by default
  • Creating an index for the foreign key is a performance optimization that for all the SQL queries generated by ActiveRecord when using association code like user.organization

Adding a foreign key reference with a custom column name

class AddForeignKeyReference < ActiveRecord::Migration[7.0]
  def change
    add_reference :users, :foo_bar_org, 
      foreign_key: { to_table: :organizations, on_delete: :cascade }, 
      null: false
  end
end

Alternative syntax for creating a foreign key reference

class CreateUserTable < ActiveRecord::Migration[7.0]
  def change
    create_table :users do |t|
      t.string :full_name, null: false
      
      # yada yada yada ...
      
      t.references :organization, foreign_key: {on_delete: :cascade}, 
                                  index: true, null: false

      t.timestamps null: false
    end
  end
end

Adding or removing a column from a table

Adding a column

class AddPartNumberToProducts < ActiveRecord::Migration[7.0]
  def change
    add_column :products, :part_number, :string
  end
end

Removing a column

class RemovePartNumberFromProducts < ActiveRecord::Migration[7.0]
  def change
    remove_column :products, :part_number, :string
  end
end