README.md in strong_migrations-2.0.0 vs README.md in strong_migrations-2.0.1

- old
+ new

@@ -41,11 +41,11 @@ self.ignored_columns += ["name"] end Deploy the code, then wrap this step in a safety_assured { ... } block. -class RemoveColumn < ActiveRecord::Migration[7.1] +class RemoveColumn < ActiveRecord::Migration[7.2] def change safety_assured { remove_column :users, :name } end end ``` @@ -96,11 +96,11 @@ #### Bad Active Record caches database columns at runtime, so if you drop a column, it can cause exceptions until your app reboots. ```ruby -class RemoveSomeColumnFromUsers < ActiveRecord::Migration[7.1] +class RemoveSomeColumnFromUsers < ActiveRecord::Migration[7.2] def change remove_column :users, :some_column end end ``` @@ -117,11 +117,11 @@ 2. Deploy the code 3. Write a migration to remove the column (wrap in `safety_assured` block) ```ruby - class RemoveSomeColumnFromUsers < ActiveRecord::Migration[7.1] + class RemoveSomeColumnFromUsers < ActiveRecord::Migration[7.2] def change safety_assured { remove_column :users, :some_column } end end ``` @@ -134,11 +134,11 @@ #### 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. ```ruby -class ChangeSomeColumnType < ActiveRecord::Migration[7.1] +class ChangeSomeColumnType < ActiveRecord::Migration[7.2] def change change_column :users, :some_column, :new_type end end ``` @@ -180,11 +180,11 @@ #### Bad Renaming a column that’s in use will cause errors in your application. ```ruby -class RenameSomeColumn < ActiveRecord::Migration[7.1] +class RenameSomeColumn < ActiveRecord::Migration[7.2] def change rename_column :users, :some_column, :new_name end end ``` @@ -205,11 +205,11 @@ #### Bad Renaming a table that’s in use will cause errors in your application. ```ruby -class RenameUsersToCustomers < ActiveRecord::Migration[7.1] +class RenameUsersToCustomers < ActiveRecord::Migration[7.2] def change rename_table :users, :customers end end ``` @@ -230,11 +230,11 @@ #### Bad The `force` option can drop an existing table. ```ruby -class CreateUsers < ActiveRecord::Migration[7.1] +class CreateUsers < ActiveRecord::Migration[7.2] def change create_table :users, force: true do |t| # ... end end @@ -244,11 +244,11 @@ #### Good Create tables without the `force` option. ```ruby -class CreateUsers < ActiveRecord::Migration[7.1] +class CreateUsers < ActiveRecord::Migration[7.2] def change create_table :users do |t| # ... end end @@ -262,11 +262,11 @@ #### 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. ```ruby -class AddIdToCitiesUsers < ActiveRecord::Migration[7.1] +class AddIdToCitiesUsers < ActiveRecord::Migration[7.2] def change add_column :cities_users, :id, :primary_key end end ``` @@ -282,11 +282,11 @@ #### 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. ```ruby -class AddSomeColumnToUsers < ActiveRecord::Migration[7.1] +class AddSomeColumnToUsers < ActiveRecord::Migration[7.2] def change add_column :users, :some_column, :virtual, type: :string, as: "...", stored: true end end ``` @@ -302,11 +302,11 @@ #### Bad Adding a check constraint blocks reads and writes in Postgres and blocks writes in MySQL and MariaDB while every row is checked. ```ruby -class AddCheckConstraint < ActiveRecord::Migration[7.1] +class AddCheckConstraint < ActiveRecord::Migration[7.2] def change add_check_constraint :users, "price > 0", name: "price_check" end end ``` @@ -314,21 +314,21 @@ #### Good - Postgres Add the check constraint without validating existing rows: ```ruby -class AddCheckConstraint < ActiveRecord::Migration[7.1] +class AddCheckConstraint < ActiveRecord::Migration[7.2] def change add_check_constraint :users, "price > 0", name: "price_check", validate: false end end ``` Then validate them in a separate migration. ```ruby -class ValidateCheckConstraint < ActiveRecord::Migration[7.1] +class ValidateCheckConstraint < ActiveRecord::Migration[7.2] def change validate_check_constraint :users, name: "price_check" end end ``` @@ -340,11 +340,11 @@ ### 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: ```ruby -class ExecuteSQL < ActiveRecord::Migration[7.1] +class ExecuteSQL < ActiveRecord::Migration[7.2] def change safety_assured { execute "..." } end end ``` @@ -354,11 +354,11 @@ #### 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](https://wework.github.io/data/2015/11/05/add-columns-with-default-values-to-large-tables-in-rails-postgres/). ```ruby -class AddSomeColumnToUsers < ActiveRecord::Migration[7.1] +class AddSomeColumnToUsers < ActiveRecord::Migration[7.2] def change add_column :users, :some_column, :text User.update_all some_column: "default_value" end end @@ -369,11 +369,11 @@ #### 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!`. ```ruby -class BackfillSomeColumn < ActiveRecord::Migration[7.1] +class BackfillSomeColumn < ActiveRecord::Migration[7.2] disable_ddl_transaction! def up User.unscoped.in_batches do |relation| relation.update_all some_column: "default_value" @@ -390,11 +390,11 @@ #### Bad In Postgres, adding an index non-concurrently blocks writes. ```ruby -class AddSomeIndexToUsers < ActiveRecord::Migration[7.1] +class AddSomeIndexToUsers < ActiveRecord::Migration[7.2] def change add_index :users, :some_column end end ``` @@ -402,11 +402,11 @@ #### Good Add indexes concurrently. ```ruby -class AddSomeIndexToUsers < ActiveRecord::Migration[7.1] +class AddSomeIndexToUsers < ActiveRecord::Migration[7.2] disable_ddl_transaction! def change add_index :users, :some_column, algorithm: :concurrently end @@ -428,11 +428,11 @@ #### Bad Rails adds an index non-concurrently to references by default, which blocks writes in Postgres. ```ruby -class AddReferenceToUsers < ActiveRecord::Migration[7.1] +class AddReferenceToUsers < ActiveRecord::Migration[7.2] def change add_reference :users, :city end end ``` @@ -440,11 +440,11 @@ #### Good Make sure the index is added concurrently. ```ruby -class AddReferenceToUsers < ActiveRecord::Migration[7.1] +class AddReferenceToUsers < ActiveRecord::Migration[7.2] disable_ddl_transaction! def change add_reference :users, :city, index: {algorithm: :concurrently} end @@ -458,21 +458,21 @@ #### Bad In Postgres, adding a foreign key blocks writes on both tables. ```ruby -class AddForeignKeyOnUsers < ActiveRecord::Migration[7.1] +class AddForeignKeyOnUsers < ActiveRecord::Migration[7.2] def change add_foreign_key :users, :orders end end ``` or ```ruby -class AddReferenceToUsers < ActiveRecord::Migration[7.1] +class AddReferenceToUsers < ActiveRecord::Migration[7.2] def change add_reference :users, :order, foreign_key: true end end ``` @@ -480,21 +480,21 @@ #### Good Add the foreign key without validating existing rows: ```ruby -class AddForeignKeyOnUsers < ActiveRecord::Migration[7.1] +class AddForeignKeyOnUsers < ActiveRecord::Migration[7.2] def change add_foreign_key :users, :orders, validate: false end end ``` Then validate them in a separate migration. ```ruby -class ValidateForeignKeyOnUsers < ActiveRecord::Migration[7.1] +class ValidateForeignKeyOnUsers < ActiveRecord::Migration[7.2] def change validate_foreign_key :users, :orders end end ``` @@ -504,11 +504,11 @@ #### Bad In Postgres, adding a unique constraint creates a unique index, which blocks reads and writes. ```ruby -class AddUniqueConstraint < ActiveRecord::Migration[7.1] +class AddUniqueConstraint < ActiveRecord::Migration[7.2] def change add_unique_constraint :users, :some_column end end ``` @@ -516,11 +516,11 @@ #### Good Create a unique index concurrently, then use it for the constraint. ```ruby -class AddUniqueConstraint < ActiveRecord::Migration[7.1] +class AddUniqueConstraint < ActiveRecord::Migration[7.2] disable_ddl_transaction! def up add_index :users, :some_column, unique: true, algorithm: :concurrently add_unique_constraint :users, using_index: "index_users_on_some_column" @@ -537,11 +537,11 @@ #### Bad In Postgres, adding an exclusion constraint blocks reads and writes while every row is checked. ```ruby -class AddExclusionConstraint < ActiveRecord::Migration[7.1] +class AddExclusionConstraint < ActiveRecord::Migration[7.2] def change add_exclusion_constraint :users, "number WITH =", using: :gist end end ``` @@ -555,11 +555,11 @@ #### 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. ```ruby -class AddPropertiesToUsers < ActiveRecord::Migration[7.1] +class AddPropertiesToUsers < ActiveRecord::Migration[7.2] def change add_column :users, :properties, :json end end ``` @@ -567,11 +567,11 @@ #### Good Use `jsonb` instead. ```ruby -class AddPropertiesToUsers < ActiveRecord::Migration[7.1] +class AddPropertiesToUsers < ActiveRecord::Migration[7.2] def change add_column :users, :properties, :jsonb end end ``` @@ -583,11 +583,11 @@ #### Bad In Postgres, setting `NOT NULL` on an existing column blocks reads and writes while every row is checked. ```ruby -class SetSomeColumnNotNull < ActiveRecord::Migration[7.1] +class SetSomeColumnNotNull < ActiveRecord::Migration[7.2] def change change_column_null :users, :some_column, false end end ``` @@ -595,21 +595,21 @@ #### Good Instead, add a check constraint. ```ruby -class SetSomeColumnNotNull < ActiveRecord::Migration[7.1] +class SetSomeColumnNotNull < ActiveRecord::Migration[7.2] def change add_check_constraint :users, "some_column IS NOT NULL", name: "users_some_column_null", validate: false end end ``` Then validate it in a separate migration. Once the check constraint is validated, you can safely set `NOT NULL` on the column and drop the check constraint. ```ruby -class ValidateSomeColumnNotNull < ActiveRecord::Migration[7.1] +class ValidateSomeColumnNotNull < ActiveRecord::Migration[7.2] def change validate_check_constraint :users, name: "users_some_column_null" change_column_null :users, :some_column, false remove_check_constraint :users, name: "users_some_column_null" end @@ -621,11 +621,11 @@ #### Bad Adding a column with a volatile default value to an existing table causes the entire table to be rewritten. During this time, reads and writes are blocked. ```ruby -class AddSomeColumnToUsers < ActiveRecord::Migration[7.1] +class AddSomeColumnToUsers < ActiveRecord::Migration[7.2] def change add_column :users, :some_column, :uuid, default: "gen_random_uuid()" end end ``` @@ -633,11 +633,11 @@ #### Good Instead, add the column without a default value, then change the default. ```ruby -class AddSomeColumnToUsers < ActiveRecord::Migration[7.1] +class AddSomeColumnToUsers < ActiveRecord::Migration[7.2] def up add_column :users, :some_column, :uuid change_column_default :users, :some_column, from: nil, to: "gen_random_uuid()" end @@ -684,11 +684,11 @@ #### Bad Adding a non-unique index with more than three columns rarely improves performance. ```ruby -class AddSomeIndexToUsers < ActiveRecord::Migration[7.1] +class AddSomeIndexToUsers < ActiveRecord::Migration[7.2] def change add_index :users, [:a, :b, :c, :d] end end ``` @@ -696,11 +696,11 @@ #### Good Instead, start an index with columns that narrow down the results the most. ```ruby -class AddSomeIndexToUsers < ActiveRecord::Migration[7.1] +class AddSomeIndexToUsers < ActiveRecord::Migration[7.2] def change add_index :users, [:d, :b] end end ``` @@ -710,11 +710,11 @@ ## 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. ```ruby -class MySafeMigration < ActiveRecord::Migration[7.1] +class MySafeMigration < ActiveRecord::Migration[7.2] def change safety_assured { remove_column :users, :some_column } end end ``` @@ -932,17 +932,20 @@ 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 -- [Rails Migrations with No Downtime](https://pedro.herokuapp.com/past/2011/7/13/rails_migrations_with_no_downtime/) - [PostgreSQL at Scale: Database Schema Changes Without Downtime](https://medium.com/braintree-product-technology/postgresql-at-scale-database-schema-changes-without-downtime-20d3749ed680) -- [An Overview of DDL Algorithms in MySQL](https://mydbops.wordpress.com/2020/03/04/an-overview-of-ddl-algorithms-in-mysql-covers-mysql-8/) +- [MySQL InnoDB Online DDL Operations](https://dev.mysql.com/doc/refman/en/innodb-online-ddl-operations.html) - [MariaDB InnoDB Online DDL Overview](https://mariadb.com/kb/en/innodb-online-ddl-overview/) ## Credits Thanks to Bob Remeika and David Waller for the [original code](https://github.com/foobarfighter/safe-migrations) and [Sean Huber](https://github.com/LendingHome/zero_downtime_migrations) for the bad/good readme format. + +## History + +View the [changelog](https://github.com/ankane/strong_migrations/blob/master/CHANGELOG.md) ## Contributing Everyone is encouraged to help improve this project. Here are a few ways you can help: