strong_migrations

Strong Migrations

Catch unsafe migrations in development

  ✓  Detects potentially dangerous operations
  ✓  Prevents them from running by default
  ✓  Provides instructions on safer ways to do what you want

Supports PostgreSQL, MySQL, and MariaDB

:tangerine: Battle-tested at Instacart

Installation

Add this line to your application’s Gemfile:

gem "strong_migrations"

And run:

bundle install
rails generate strong_migrations:install

Strong Migrations sets a long statement timeout for migrations so you can set a short statement timeout for your application.

How It Works

When you run a migration that’s potentially dangerous, you’ll see an error message like:

=== Dangerous operation detected #strong_migrations ===

Active Record caches attributes, which causes problems
when removing columns. Be sure to ignore the column:

class User < ApplicationRecord
  self.ignored_columns += ["name"]
end

Deploy the code, then wrap this step in a safety_assured { ... } block.

class RemoveColumn < ActiveRecord::Migration[8.1]
  def change
    safety_assured { remove_column :users, :name }
  end
end

An operation is classified as dangerous if it either:

• Blocks reads or writes for more than a few seconds (after a lock is acquired)
• Has a good chance of causing application errors

Checks

Potentially dangerous operations:

• removing a column
• changing the type of a column
• renaming a column
• renaming a table
• creating a table with the force option
• adding an auto-incrementing column
• adding a stored generated column
• adding a foreign key
• adding a check constraint
• executing SQL directly
• backfilling data

Postgres-specific checks:

• adding an index non-concurrently
• adding a reference
• adding a unique constraint
• adding an exclusion constraint
• adding a json column
• adding a column with a volatile default value
• setting NOT NULL on an existing column
• renaming an enum value
• renaming a schema

MySQL and MariaDB-specific checks:

• using the COPY algorithm
• using shared or exclusive locking
• adding a column with an expression default value

Best practices:

• keeping non-unique indexes to three columns or less

You can also add custom checks or disable specific checks.

Removing a column

Bad

Active Record caches database columns at runtime, so if you drop a column, it can cause exceptions until your app reboots.

class RemoveSomeColumnFromUsers < ActiveRecord::Migration[8.1]
  def change
    remove_column :users, :some_column
  end
end

Good

1. Tell Active Record to ignore the column from its cache

class User < ApplicationRecord
  self.ignored_columns += ["some_column"]
end

1. Deploy the code
2. Write a migration to remove the column (wrap in safety_assured block)

class RemoveSomeColumnFromUsers < ActiveRecord::Migration[8.1]
  def change
    safety_assured { remove_column :users, :some_column }
  end
end

1. Deploy and run the migration
2. Remove the line added in step 1

Changing the type of a column

Bad

Changing the type of a column causes the entire table to be rewritten. During this time, reads and writes are blocked in Postgres, and writes are blocked in MySQL and MariaDB.

class ChangeSomeColumnType < ActiveRecord::Migration[8.1]
  def change
    change_column :users, :some_column, :new_type
  end
end

Some changes don’t require a table rewrite and are safe in Postgres:

Type	Safe Changes
cidr	Changing to inet
citext	Changing to text if not indexed, changing to string with no :limit if not indexed
datetime	Increasing or removing :precision, changing to timestamptz when session time zone is UTC
decimal	Increasing :precision at same :scale, removing :precision and :scale
interval	Increasing or removing :precision
numeric	Increasing :precision at same :scale, removing :precision and :scale
string	Increasing or removing :limit, changing to text, changing citext if not indexed
text	Changing to string with no :limit, changing to citext if not indexed
time	Increasing or removing :precision
timestamptz	Increasing or removing :limit, changing to datetime when session time zone is UTC

And some in MySQL and MariaDB:

Type	Safe Changes
string	Increasing :limit from under 63 up to 63, increasing :limit from over 63 to the max (the threshold can be different if using an encoding other than utf8mb4 - for instance, it’s 85 for utf8mb3 and 255 for latin1)

Good

A safer approach is to:

1. Create a new column
2. Write to both columns
3. Backfill data from the old column to the new column
4. Move reads from the old column to the new column
5. Stop writing to the old column
6. Drop the old column

Renaming a column

Bad

Renaming a column that’s in use will cause errors in your application.

class RenameSomeColumn < ActiveRecord::Migration[8.1]
  def change
    rename_column :users, :some_column, :new_name
  end
end

Good

A safer approach is to:

1. Create a new column
2. Write to both columns
3. Backfill data from the old column to the new column
4. Move reads from the old column to the new column
5. Stop writing to the old column
6. Drop the old column

Renaming a table

Bad

Renaming a table that’s in use will cause errors in your application.

class RenameUsersToCustomers < ActiveRecord::Migration[8.1]
  def change
    rename_table :users, :customers
  end
end

Good

A safer approach is to:

1. Create a new table
2. Write to both tables
3. Backfill data from the old table to the new table
4. Move reads from the old table to the new table
5. Stop writing to the old table
6. Drop the old table

Creating a table with the force option

Bad

The force option can drop an existing table.

class CreateUsers < ActiveRecord::Migration[8.1]
  def change
    create_table :users, force: true do |t|
      # ...
    end
  end
end

Good

Create tables without the force option.

class CreateUsers < ActiveRecord::Migration[8.1]
  def change
    create_table :users do |t|
      # ...
    end
  end
end

If you intend to drop an existing table, run drop_table first.

Adding an auto-incrementing column

Bad

Adding an auto-incrementing column (serial/bigserial in Postgres and AUTO_INCREMENT in MySQL and MariaDB) causes the entire table to be rewritten. During this time, reads and writes are blocked in Postgres, and writes are blocked in MySQL and MariaDB.

class AddIdToCitiesUsers < ActiveRecord::Migration[8.1]
  def change
    add_column :cities_users, :id, :primary_key
  end
end

With MySQL and MariaDB, this can also generate different values on replicas if using statement-based replication.

Good

Create a new table and migrate the data with the same steps as renaming a table.

Adding a stored generated column

Bad

Adding a stored generated column causes the entire table to be rewritten. During this time, reads and writes are blocked in Postgres, and writes are blocked in MySQL and MariaDB.

class AddSomeColumnToUsers < ActiveRecord::Migration[8.1]
  def change
    add_column :users, :some_column, :virtual, type: :string, as: "...", stored: true
  end
end

Good

Add a non-generated column and use callbacks or triggers instead (or a virtual generated column with MySQL and MariaDB).

Adding a foreign key

:turtle: Safe by default available for Postgres

Bad

Adding a foreign key blocks writes on both tables.

class AddForeignKeyOnUsers < ActiveRecord::Migration[8.1]
  def change
    add_foreign_key :users, :orders
  end
end

or

class AddReferenceToUsers < ActiveRecord::Migration[8.1]
  def change
    add_reference :users, :order, foreign_key: true
  end
end

Good - Postgres

Add the foreign key without validating existing rows:

class AddForeignKeyOnUsers < ActiveRecord::Migration[8.1]
  def change
    add_foreign_key :users, :orders, validate: false
  end
end

Then validate them in a separate migration.

class ValidateForeignKeyOnUsers < ActiveRecord::Migration[8.1]
  def change
    validate_foreign_key :users, :orders
  end
end

Good - MySQL and MariaDB

If you are 100% sure all rows are valid and migrations do not use a connection pooler, you can add the foreign key without validating existing rows:

class AddForeignKeyOnUsers < ActiveRecord::Migration[8.1]
  def up
    safety_assured do
      begin
        execute "SET SESSION foreign_key_checks = 0"
        add_foreign_key :users, :orders
      ensure
        execute "SET SESSION foreign_key_checks = 1"
      end
    end
  end

  def down
    remove_foreign_key :users, :orders
  end
end

Adding a check constraint

:turtle: Safe by default available for Postgres

Bad

Adding a check constraint blocks reads and writes in Postgres and blocks writes in MySQL and MariaDB while every row is checked.

class AddCheckConstraint < ActiveRecord::Migration[8.1]
  def change
    add_check_constraint :users, "price > 0", name: "price_check"
  end
end

Good - Postgres

Add the check constraint without validating existing rows:

class AddCheckConstraint < ActiveRecord::Migration[8.1]
  def change
    add_check_constraint :users, "price > 0", name: "price_check", validate: false
  end
end

Then validate them in a separate migration.

class ValidateCheckConstraint < ActiveRecord::Migration[8.1]
  def change
    validate_check_constraint :users, name: "price_check"
  end
end

Good - MySQL and MariaDB

Let us know if you have a safe way to do this (check constraints can be added with NOT ENFORCED, but enforcing blocks writes).

Executing SQL directly

Strong Migrations can’t ensure safety for raw SQL statements. Make really sure that what you’re doing is safe, then use:

class ExecuteSQL < ActiveRecord::Migration[8.1]
  def change
    safety_assured { execute "..." }
  end
end

Backfilling data

Note: Strong Migrations does not detect dangerous backfills.

Bad

Active Record creates a transaction around each migration, and backfilling in the same transaction that alters a table keeps the table locked for the duration of the backfill.

class AddSomeColumnToUsers < ActiveRecord::Migration[8.1]
  def change
    add_column :users, :some_column, :text
    User.update_all some_column: "default_value"
  end
end

Also, running a single query to update data can cause issues for large tables.

Good

There are three keys to backfilli