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 for PostgreSQL, MySQL, and MariaDB

🍊 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[6.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
adding a column with a default value
backfilling data
changing the type of a column
renaming a column
renaming a table
creating a table with the force option
adding a check constraint
setting NOT NULL on an existing column
executing SQL directly

Postgres-specific checks:

adding an index non-concurrently
adding a reference
adding a foreign key
adding a json column

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[6.1]
  def change
    remove_column :users, :some_column
  end
end

Good

Tell Active Record to ignore the column from its cache

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

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

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

Deploy and run migration

Adding a column with a default value

Bad

In earlier versions of Postgres, MySQL, and MariaDB, adding a column with a default value to an existing table 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[6.1]
  def change
    add_column :users, :some_column, :text, default: "default_value"
  end
end

In Postgres 11+, MySQL 8.0.12+, and MariaDB 10.3.2+, this no longer requires a table rewrite and is safe.

Good

Instead, add the column without a default value, then change the default.

class AddSomeColumnToUsers < ActiveRecord::Migration[6.1]
  def up
    add_column :users, :some_column, :text
    change_column_default :users, :some_column, "default_value"
  end

  def down
    remove_column :users, :some_column
  end
end

See the next section for how to backfill.

Backfilling data

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[6.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 backfilling safely: batching, throttling, and running it outside a transaction. Use the Rails console or a separate migration with disable_ddl_transaction!.

class BackfillSomeColumn < ActiveRecord::Migration[6.1]
  disable_ddl_transaction!

  def up
    User.unscoped.in_batches do |relation|
      relation.update_all some_column: "default_value"
      sleep(0.01) # throttle
    end
  end
end

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[6.1]
  def change
    change_column :users, :some_column, :new_type
  end
end

A few changes don’t require a table rewrite (and are safe) in Postgres:

Increasing the length limit of a varchar column (or removing the limit)
Changing a varchar column to a text column
Changing a text column to a varchar column with no length limit
Increasing the precision of a decimal or numeric column
Making a decimal or numeric column unconstrained
Changing between timestamp and timestamptz columns when session time zone is UTC in Postgres 12+

And a few in MySQL and MariaDB:

Increasing the length limit of a varchar column from under 255 up to 255
Increasing the length limit of a varchar column from over 255 to the max limit

Good

A safer approach is to:

Create a new column
Write to both columns
Backfill data from the old column to the new column
Move reads from the old column to the new column
Stop writing to the old column
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[6.1]
  def change
    rename_column :users, :some_column, :new_name
  end
end

Good

A safer approach is to:

Create a new column
Write to both columns
Backfill data from the old column to the new column
Move reads from the old column to the new column
Stop writing to the old column
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[6.1]
  def change
    rename_table :users, :customers
  end
end

Good

A safer approach is to:

Create a new table
Write to both tables
Backfill data from the old table to new table
Move reads from the old table to the new table
Stop writing to the old table
Drop the old table

Creating a table with the force option

Bad

The force option can drop an existing table.

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

Good

Create tables without the force option.

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

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

Adding a check constraint

🐢 Safe by default available

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[6.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[6.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[6.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).

Setting NOT NULL on an existing column

🐢 Safe by default available

Bad

Setting NOT NULL on an existing column blocks reads and writes while every row is checked.

class SetSomeColumnNotNull < ActiveRecord::Migration[6.1]
  def change
    change_column_null :users, :some_column, false
  end
end

Good - Postgres

Instead, add a check constraint.

For Rails 6.1, use:

class SetSomeColumnNotNull < ActiveRecord::Migration[6.1]
  def change
    add_check_constraint :users, "some_column IS NOT NULL", name: "users_some_column_null", validate: false
  end
end

For Rails < 6.1, use:

class SetSomeColumnNotNull < ActiveRecord::Migration[6.0]
  def change
    safety_assured do
      execute 'ALTER TABLE "users" ADD CONSTRAINT "users_some_column_null" CHECK ("some_column" IS NOT NULL) NOT VALID'
    end
  end
end

Then validate it in a separate migration. A NOT NULL check constraint is functionally equivalent to setting NOT NULL on the column (but it won’t show up in schema.rb in Rails < 6.1). In Postgres 12+, once the check constraint is validated, you can safely set NOT NULL on the column and drop the check constraint.

For Rails 6.1, use:

class ValidateSomeColumnNotNull < ActiveRecord::Migration[6.1]
  def change
    validate_check_constraint :users, name: "users_some_column_null"

    # in Postgres 12+, you can then safely set NOT NULL on the column
    change_column_null :users, :some_column, false
    remove_check_constraint :users, name: "users_some_column_null"
  end
end

For Rails < 6.1, use:

class ValidateSomeColumnNotNull < ActiveRecord::Migration[6.0]
  def change
    safety_assured do
      execute 'ALTER TABLE "users" VALIDATE CONSTRAINT "users_some_column_null"'
    end

    # in Postgres 12+, you can then safely set NOT NULL on the column
    change_column_null :users, :some_column, false
    safety_assured do
      execute 'ALTER TABLE "users" DROP CONSTRAINT "users_some_column_null"'
    end
  end
end

Good - MySQL and MariaDB

Let us know if you have a safe way to do this.

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[6.1]
  def change
    safety_assured { execute "..." }
  end
end

Adding an index non-concurrently

🐢 Safe by default available

Bad

In Postgres, adding an index non-concurrently blocks writes.

class AddSomeIndexToUsers < ActiveRecord::Migration[6.1]
  def change
    add_index :users, :some_column
  end
end

Good

Add indexes concurrently.

class AddSomeIndexToUsers < ActiveRecord::Migration[6.1]
  disable_ddl_transaction!

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

If you forget disable_ddl_transaction!, the migration will fail. Also, note that indexes on new tables (those created in the same migration) don’t require this.

With gindex, you can generate an index migration instantly with:

rails g index table column

Adding a reference

🐢 Safe by default available

Bad

Rails adds an index non-concurrently to references by default, which blocks writes in Postgres.

class AddReferenceToUsers < ActiveRecord::Migration[6.1]
  def change
    add_reference :users, :city
  end
end

Good

Make sure the index is added concurrently.

class AddReferenceToUsers < ActiveRecord::Migration[6.1]
  disable_ddl_transaction!

  def change
    add_reference :users, :city, index: {algorithm: :concurrently}
  end
end

Adding a foreign key

🐢 Safe by default available

Bad

In Postgres, adding a foreign key blocks writes on both tables.

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

or

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

Good

Add the foreign key without validating existing rows, then validate them in a separate migration.

For Rails 5.2+, use:

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

Then:

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

For Rails < 5.2, use:

class AddForeignKeyOnUsers < ActiveRecord::Migration[5.1]
  def change
    safety_assured do
      execute 'ALTER TABLE "users" ADD CONSTRAINT "fk_rails_c1e9b98e31" FOREIGN KEY ("order_id") REFERENCES "orders" ("id") NOT VALID'
    end
  end
end

Then:

class ValidateForeignKeyOnUsers < ActiveRecord::Migration[5.1]
  def change
    safety_assured do
      execute 'ALTER TABLE "users" VALIDATE CONSTRAINT "fk_rails_c1e9b98e31"'
    end
  end
end

Adding a json column

Bad

In Postgres, there’s no equality operator for the json column type, which can cause errors for existing SELECT DISTINCT queries in your application.

class AddPropertiesToUsers < ActiveRecord::Migration[6.1]
  def change
    add_column :users, :properties, :json
  end
end

Good

Use jsonb instead.

class AddPropertiesToUsers < ActiveRecord::Migration[6.1]
  def change
    add_column :users, :properties, :jsonb
  end
end

Keeping non-unique indexes to three columns or less

Bad

Adding a non-unique index with more than three columns rarely improves performance.

class AddSomeIndexToUsers < ActiveRecord::Migration[6.1]
  def change
    add_index :users, [:a, :b, :c, :d]
  end
end

Good

Instead, start an index with columns that narrow down the results the most.

class AddSomeIndexToUsers < ActiveRecord::Migration[6.1]
  def change
    add_index :users, [:b, :d]
  end
end

For Postgres, be sure to add them concurrently.

Assuring Safety

To mark a step in the migration as safe, despite using a method that might otherwise be dangerous, wrap it in a safety_assured block.

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

Certain methods like execute and change_table cannot be inspected and are prevented from running by default. Make sure what you’re doing is really safe and use this pattern.

Safe by Default

Make operations safe by default.

adding and removing an index
adding a foreign key
adding a check constraint
setting NOT NULL on an existing column

Add to config/initializers/strong_migrations.rb:

StrongMigrations.safe_by_default = true

Custom Checks

Add your own custom checks with:

StrongMigrations.add_check do |method, args|
  if method == :add_index && args[0].to_s == "users"
    stop! "No more indexes on the users table"
  end
end

Use the stop! method to stop migrations.

Note: Since remove_column always requires a safety_assured block, it’s not possible to add a custom check for remove_column operations.

Opt-in Checks

Removing an index non-concurrently

Postgres supports removing indexes concurrently, but removing them non-concurrently shouldn’t be an issue for most applications. You can enable this check with:

StrongMigrations.enable_check(:remove_index)

Disable Checks

Disable specific checks with:

StrongMigrations.disable_check(:add_index)

Check the source code for the list of keys.

Down Migrations / Rollbacks

By default, checks are disabled when migrating down. Enable them with:

StrongMigrations.check_down = true

Custom Messages

To customize specific messages, create an initializer with:

StrongMigrations.error_messages[:add_column_default] = "Your custom instructions"

Check the source code for the list of keys.

Migration Timeouts

It’s extremely important to set a short lock timeout for migrations. This way, if a migration can’t acquire a lock in a timely manner, other statements won’t be stuck behind it. We also recommend setting a long statement timeout so migrations can run for a while.

Create config/initializers/strong_migrations.rb with:

StrongMigrations.lock_timeout = 10.seconds
StrongMigrations.statement_timeout = 1.hour

Or set the timeouts directly on the database user that runs migrations. For Postgres, use:

ALTER ROLE myuser SET lock_timeout = '10s';
ALTER ROLE myuser SET statement_timeout = '1h';

Note: If you use PgBouncer in transaction mode, you must set timeouts on the database user.

App Timeouts

We recommend adding timeouts to config/database.yml to prevent connections from hanging and individual queries from taking up too many resources in controllers, jobs, the Rails console, and other places.

For Postgres:

production:
  connect_timeout: 5
  variables:
    statement_timeout: 15s
    lock_timeout: 10s

Note: If you use PgBouncer in transaction mode, you must set the statement and lock timeouts on the database user as shown above.

For MySQL:

production:
  connect_timeout: 5
  read_timeout: 5
  write_timeout: 5
  variables:
    max_execution_time: 15000 # ms
    lock_wait_timeout: 10 # sec

For MariaDB:

production:
  connect_timeout: 5
  read_timeout: 5
  write_timeout: 5
  variables:
    max_statement_time: 15 # sec
    lock_wait_timeout: 10 # sec

For HTTP connections, Redis, and other services, check out this guide.

Existing Migrations

To mark migrations as safe that were created before installing this gem, create an initializer with:

StrongMigrations.start_after = 20170101000000

Use the version from your latest migration.

Target Version

If your development database version is different from production, you can specify the production version so the right checks run in development.

StrongMigrations.target_version = 10 # or "8.0.12", "10.3.2", etc

The major version works well for Postgres, while the full version is recommended for MySQL and MariaDB.

For safety, this option only affects development and test environments. In other environments, the actual server version is always used.

Analyze Tables

Analyze tables automatically (to update planner statistics) after an index is added. Create an initializer with:

StrongMigrations.auto_analyze = true

Faster Migrations

Only dump the schema when adding a new migration. If you use Git, add to the end of your Rakefile:

task :faster_migrations do
  ActiveRecord::Base.dump_schema_after_migration = Rails.env.development? &&
    `git status db/migrate/ --porcelain`.present?
end

task "db:migrate": "faster_migrations"

Schema Sanity

Columns can flip order in db/schema.rb when you have multiple developers. One way to prevent this is to alphabetize them. Add to the end of your Rakefile:

task "db:schema:dump": "strong_migrations:alphabetize_columns"

Permissions

We recommend using a separate database user for migrations when possible so you don’t need to grant your app user permission to alter tables.

Smaller Projects

You probably don’t need this gem for smaller projects, as operations that are unsafe at scale can be perfectly safe on smaller, low-traffic tables.

Additional Reading

Credits

Thanks to Bob Remeika and David Waller for the original code and Sean Huber for the bad/good readme format.

Contributing

Everyone is encouraged to help improve this project. Here are a few ways you can help:

Report bugs
Fix bugs and submit pull requests
Write, clarify, or fix documentation
Suggest or add new features

To get started with development:

git clone https://github.com/ankane/strong_migrations.git
cd strong_migrations
bundle install

# Postgres
createdb strong_migrations_test
bundle exec rake test

# MySQL and MariaDB
mysqladmin create strong_migrations_test
ADAPTER=mysql2 bundle exec rake test

Name		Name	Last commit message	Last commit date
Latest commit History 573 Commits
.github		.github
gemfiles		gemfiles
lib		lib
test		test
.gitignore		.gitignore
CHANGELOG.md		CHANGELOG.md
CONTRIBUTING.md		CONTRIBUTING.md
Gemfile		Gemfile
LICENSE.txt		LICENSE.txt
README.md		README.md
Rakefile		Rakefile
strong_migrations.gemspec		strong_migrations.gemspec

License

CharlesDelannoy/strong_migrations

Folders and files

Latest commit

History

Repository files navigation

Strong Migrations

Installation

How It Works

Checks

Removing a column

Bad

Good

Adding a column with a default value

Bad

Good

Backfilling data

Bad

Good

Changing the type of a column

Bad

Good

Renaming a column

Bad

Good

Renaming a table

Bad

Good

Creating a table with the force option

Bad

Good

Adding a check constraint

Bad

Good - Postgres

Good - MySQL and MariaDB

Setting NOT NULL on an existing column

Bad

Good - Postgres

Good - MySQL and MariaDB

Executing SQL directly

Adding an index non-concurrently

Bad

Good

Adding a reference

Bad

Good

Adding a foreign key

Bad

Good

Adding a json column

Bad

Good

Keeping non-unique indexes to three columns or less

Bad

Good

Assuring Safety

Safe by Default

Custom Checks

Opt-in Checks

Removing an index non-concurrently

Disable Checks

Down Migrations / Rollbacks

Custom Messages

Migration Timeouts

App Timeouts

Existing Migrations

Target Version

Analyze Tables

Faster Migrations

Schema Sanity

Permissions

Smaller Projects

Additional Reading

Credits

Contributing

About

Resources

License

Stars

Watchers

Packages