README.md in strong_migrations-0.6.3 vs README.md in strong_migrations-0.6.4
- old
+ new
@@ -1,9 +1,11 @@
# Strong Migrations
Catch unsafe migrations in development
+ ✓ Detects potentially dangerous operations<br /> ✓ Prevents them from running by default<br /> ✓ Provides instructions on safer ways to do what you want
+
Supports for PostgreSQL, MySQL, and MariaDB
:tangerine: Battle-tested at [Instacart](https://www.instacart.com/opensource)
[](https://travis-ci.org/ankane/strong_migrations)
@@ -14,42 +16,40 @@
```ruby
gem 'strong_migrations'
```
-## How It Works
+We highly recommend [setting timeouts](#timeouts). You can [mark existing migrations as safe](#existing-migrations) as well.
-Strong Migrations detects potentially dangerous operations in migrations, prevents them from running by default, and provides instructions on safer ways to do what you want.
+## Checks
-
+Potentially dangerous operations:
-## Dangerous Operations
+- [removing a column](#removing-a-column)
+- [adding a column with a default value](#adding-a-column-with-a-default-value)
+- [backfilling data](#backfilling-data)
+- [changing the type of a column](#renaming-or-changing-the-type-of-a-column)
+- [renaming a column](#renaming-or-changing-the-type-of-a-column)
+- [renaming a table](#renaming-a-table)
+- [creating a table with the force option](#creating-a-table-with-the-force-option)
+- [using change_column_null with a default value](#using-change_column_null-with-a-default-value)
+- [executing SQL directly](#executing-SQL-directly)
-The following operations can cause downtime or errors:
+Postgres-specific checks:
-- [[+]](#removing-a-column) removing a column
-- [[+]](#adding-a-column-with-a-default-value) adding a column with a default value
-- [[+]](#backfilling-data) backfilling data
-- [[+]](#adding-an-index) adding an index non-concurrently
-- [[+]](#adding-a-reference) adding a reference
-- [[+]](#adding-a-foreign-key) adding a foreign key
-- [[+]](#renaming-or-changing-the-type-of-a-column) changing the type of a column
-- [[+]](#renaming-or-changing-the-type-of-a-column) renaming a column
-- [[+]](#renaming-a-table) renaming a table
-- [[+]](#creating-a-table-with-the-force-option) creating a table with the `force` option
-- [[+]](#setting-not-null-on-an-existing-column) setting `NOT NULL` on an existing column
-- [[+]](#adding-a-json-column) adding a `json` column
+- [adding an index non-concurrently](#adding-an-index)
+- [removing an index non-concurrently](#removing-an-index)
+- [adding a reference](#adding-a-reference)
+- [adding a foreign key](#adding-a-foreign-key)
+- [adding a json column](#adding-a-json-column)
+- [setting NOT NULL on an existing column](#setting-not-null-on-an-existing-column)
-Optional checks:
-
-- [[+]](#removing-an-index) removing an index non-concurrently
-
Best practices:
-- [[+]](#keeping-non-unique-indexes-to-three-columns-or-less) keeping non-unique indexes to three columns or less
+- [keeping non-unique indexes to three columns or less](#keeping-non-unique-indexes-to-three-columns-or-less)
-## The Zero Downtime Way
+You can also add [custom checks](#custom-checks) or [disable specific checks](#disable-checks).
### Removing a column
#### Bad
@@ -86,11 +86,11 @@
4. Deploy and run migration
### Adding a column with a default value
-Note: This operation is safe in Postgres 11+, MySQL 8.0.12+, and MariaDB 10.3.2+
+Note: This operation is safe in Postgres 11+, MySQL 8.0.12+, and MariaDB 10.3.2+.
#### Bad
Adding a column with a default value to an existing table causes the entire table to be rewritten.
@@ -153,304 +153,314 @@
end
end
end
```
-### Adding an index
+### Renaming or changing the type of a column
#### Bad
-In Postgres, adding an index non-concurrently locks the table.
-
```ruby
-class AddSomeIndexToUsers < ActiveRecord::Migration[6.0]
+class RenameSomeColumn < ActiveRecord::Migration[6.0]
def change
- add_index :users, :some_column
+ rename_column :users, :some_column, :new_name
end
end
```
-#### Good
+or
-Add indexes concurrently.
-
```ruby
-class AddSomeIndexToUsers < ActiveRecord::Migration[6.0]
- disable_ddl_transaction!
-
+class ChangeSomeColumnType < ActiveRecord::Migration[6.0]
def change
- add_index :users, :some_column, algorithm: :concurrently
+ change_column :users, :some_column, :new_type
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.
+A few changes are safe in Postgres:
-With [gindex](https://github.com/ankane/gindex), you can generate an index migration instantly with:
+- Changing between `varchar` and `text` columns
+- 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+
-```sh
-rails g index table column
-```
+And a few in MySQL and MariaDB:
-### Adding a reference
+- Increasing the length of a `varchar` column from under 255 up to 255
+- Increasing the length of a `varchar` column over 255
-#### Bad
+#### Good
-Rails adds an index non-concurrently to references by default, which is problematic for Postgres.
+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
+
```ruby
-class AddReferenceToUsers < ActiveRecord::Migration[6.0]
+class RenameUsersToCustomers < ActiveRecord::Migration[6.0]
def change
- add_reference :users, :city
+ rename_table :users, :customers
end
end
```
#### Good
-Make sure the index is added concurrently.
+A safer approach is to:
-```ruby
-class AddReferenceToUsers < ActiveRecord::Migration[6.0]
- disable_ddl_transaction!
+1. Create a new table
+2. Write to both tables
+3. Backfill data from the old table to 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
- def change
- add_reference :users, :city, index: {algorithm: :concurrently}
- end
-end
-```
+### Creating a table with the force option
-### Adding a foreign key
-
#### Bad
-In Postgres, new foreign keys are validated by default, which acquires a `ShareRowExclusiveLock` that can be [expensive on large tables](https://travisofthenorth.com/blog/2017/2/2/postgres-adding-foreign-keys-with-zero-downtime).
+The `force` option can drop an existing table.
```ruby
-class AddForeignKeyOnUsers < ActiveRecord::Migration[6.0]
+class CreateUsers < ActiveRecord::Migration[6.0]
def change
- add_foreign_key :users, :orders
+ create_table :users, force: true do |t|
+ # ...
+ end
end
end
```
#### Good
-Instead, validate it in a separate migration with a more agreeable `RowShareLock`. This approach is documented by Postgres to have “[the least impact on other work](https://www.postgresql.org/docs/current/sql-altertable.html).”
+Create tables without the `force` option.
-For Rails 5.2+, use:
-
```ruby
-class AddForeignKeyOnUsers < ActiveRecord::Migration[6.0]
+class CreateUsers < ActiveRecord::Migration[6.0]
def change
- add_foreign_key :users, :orders, validate: false
+ create_table :users do |t|
+ # ...
+ end
end
end
```
-Then validate it in a separate migration.
+### Using change_column_null with a default value
+#### Bad
+
+This generates a single `UPDATE` statement to set the default value.
+
```ruby
-class ValidateForeignKeyOnUsers < ActiveRecord::Migration[6.0]
+class ChangeSomeColumnNull < ActiveRecord::Migration[6.0]
def change
- validate_foreign_key :users, :orders
+ change_column_null :users, :some_column, false, "default_value"
end
end
```
-For Rails < 5.2, use:
+#### Good
+Backfill the column [safely](#backfilling-data). Then use:
+
```ruby
-class AddForeignKeyOnUsers < ActiveRecord::Migration[5.1]
+class ChangeSomeColumnNull < ActiveRecord::Migration[6.0]
def change
- safety_assured do
- execute 'ALTER TABLE "users" ADD CONSTRAINT "fk_rails_c1e9b98e31" FOREIGN KEY ("order_id") REFERENCES "orders" ("id") NOT VALID'
- end
+ change_column_null :users, :some_column, false
end
end
```
-Then validate it in a separate migration.
+Note: In Postgres, `change_column_null` is still [not safe](#setting-not-null-on-an-existing-column) with this method.
+### 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 ValidateForeignKeyOnUsers < ActiveRecord::Migration[5.1]
+class ExecuteSQL < ActiveRecord::Migration[6.0]
def change
- safety_assured do
- execute 'ALTER TABLE "users" VALIDATE CONSTRAINT "fk_rails_c1e9b98e31"'
- end
+ safety_assured { execute "..." }
end
end
```
-### Renaming or changing the type of a column
+### Adding an index
#### Bad
+In Postgres, adding an index non-concurrently locks the table.
+
```ruby
-class RenameSomeColumn < ActiveRecord::Migration[6.0]
+class AddSomeIndexToUsers < ActiveRecord::Migration[6.0]
def change
- rename_column :users, :some_column, :new_name
+ add_index :users, :some_column
end
end
```
-or
+#### Good
+Add indexes concurrently.
+
```ruby
-class ChangeSomeColumnType < ActiveRecord::Migration[6.0]
+class AddSomeIndexToUsers < ActiveRecord::Migration[6.0]
+ disable_ddl_transaction!
+
def change
- change_column :users, :some_column, :new_type
+ add_index :users, :some_column, algorithm: :concurrently
end
end
```
-A few changes are safe in Postgres:
+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.
-- Changing between `varchar` and `text` columns
-- 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+
+With [gindex](https://github.com/ankane/gindex), you can generate an index migration instantly with:
-And a few in MySQL and MariaDB:
+```sh
+rails g index table column
+```
-- Increasing the length of a `varchar` column from under 255 up to 255
-- Increasing the length of a `varchar` column over 255
+### Removing an index
-#### Good
+Note: This check is [opt-in](#opt-in-checks).
-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
+In Postgres, removing an index non-concurrently locks the table for a brief period.
+
```ruby
-class RenameUsersToCustomers < ActiveRecord::Migration[6.0]
+class RemoveSomeIndexFromUsers < ActiveRecord::Migration[6.0]
def change
- rename_table :users, :customers
+ remove_index :users, :some_column
end
end
```
#### Good
-A safer approach is to:
+Remove indexes concurrently.
-1. Create a new table
-2. Write to both tables
-3. Backfill data from the old table to 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
+```ruby
+class RemoveSomeIndexFromUsers < ActiveRecord::Migration[6.0]
+ disable_ddl_transaction!
-### Creating a table with the force option
+ def change
+ remove_index :users, column: :some_column, algorithm: :concurrently
+ end
+end
+```
+### Adding a reference
+
#### Bad
-The `force` option can drop an existing table.
+Rails adds an index non-concurrently to references by default, which is problematic for Postgres.
```ruby
-class CreateUsers < ActiveRecord::Migration[6.0]
+class AddReferenceToUsers < ActiveRecord::Migration[6.0]
def change
- create_table :users, force: true do |t|
- # ...
- end
+ add_reference :users, :city
end
end
```
#### Good
-Create tables without the `force` option.
+Make sure the index is added concurrently.
```ruby
-class CreateUsers < ActiveRecord::Migration[6.0]
+class AddReferenceToUsers < ActiveRecord::Migration[6.0]
+ disable_ddl_transaction!
+
def change
- create_table :users do |t|
- # ...
- end
+ add_reference :users, :city, index: {algorithm: :concurrently}
end
end
```
-### Setting `NOT NULL` on an existing column
+### Adding a foreign key
#### Bad
-In Postgres, setting `NOT NULL` on an existing column requires an `AccessExclusiveLock`, which is expensive on large tables.
+In Postgres, new foreign keys are validated by default, which acquires a `ShareRowExclusiveLock` that can be [expensive on large tables](https://travisofthenorth.com/blog/2017/2/2/postgres-adding-foreign-keys-with-zero-downtime).
```ruby
-class SetSomeColumnNotNull < ActiveRecord::Migration[6.0]
+class AddForeignKeyOnUsers < ActiveRecord::Migration[6.0]
def change
- change_column_null :users, :some_column, false
+ add_foreign_key :users, :orders
end
end
```
+or
+
+```ruby
+class AddReferenceToUsers < ActiveRecord::Migration[6.0]
+ def change
+ add_reference :users, :order, foreign_key: true
+ end
+end
+```
+
#### Good
-Instead, add a constraint:
+Instead, validate it in a separate migration with a more agreeable `RowShareLock`. This approach is documented by Postgres to have “[the least impact on other work](https://www.postgresql.org/docs/current/sql-altertable.html).”
+For Rails 5.2+, use:
+
```ruby
-class SetSomeColumnNotNull < ActiveRecord::Migration[6.0]
+class AddForeignKeyOnUsers < 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
+ add_foreign_key :users, :orders, validate: false
end
end
```
Then validate it in a separate migration.
```ruby
-class ValidateSomeColumnNotNull < ActiveRecord::Migration[6.0]
+class ValidateForeignKeyOnUsers < ActiveRecord::Migration[6.0]
def change
- safety_assured do
- execute 'ALTER TABLE "users" VALIDATE CONSTRAINT "users_some_column_null"'
- end
+ validate_foreign_key :users, :orders
end
end
```
-Note: This is not 100% the same as `NOT NULL` column constraint. Here’s a [good explanation](https://medium.com/doctolib/adding-a-not-null-constraint-on-pg-faster-with-minimal-locking-38b2c00c4d1c).
+For Rails < 5.2, use:
-### Using change_column_null with a default value
-
-#### Bad
-
-This generates a single `UPDATE` statement to set the default value.
-
```ruby
-class ChangeSomeColumnNull < ActiveRecord::Migration[6.0]
+class AddForeignKeyOnUsers < ActiveRecord::Migration[5.1]
def change
- change_column_null :users, :some_column, false, "default_value"
+ safety_assured do
+ execute 'ALTER TABLE "users" ADD CONSTRAINT "fk_rails_c1e9b98e31" FOREIGN KEY ("order_id") REFERENCES "orders" ("id") NOT VALID'
+ end
end
end
```
-#### Good
+Then validate it in a separate migration.
-Backfill the column [safely](#backfilling-data). Then use:
-
```ruby
-class ChangeSomeColumnNull < ActiveRecord::Migration[6.0]
+class ValidateForeignKeyOnUsers < ActiveRecord::Migration[5.1]
def change
- change_column_null :users, :some_column, false
+ safety_assured do
+ execute 'ALTER TABLE "users" VALIDATE CONSTRAINT "fk_rails_c1e9b98e31"'
+ end
end
end
```
-Note: In Postgres, `change_column_null` is still [not safe](#setting-not-null-on-an-existing-column) with this method.
-
### 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.
@@ -473,53 +483,51 @@
add_column :users, :properties, :jsonb
end
end
```
-## Optional Checks
+### Setting NOT NULL on an existing column
-Some operations rarely cause issues in practice, but can be checked if desired. Enable checks with:
+#### Bad
-```ruby
-StrongMigrations.enable_check(:remove_index)
-```
+In Postgres, setting `NOT NULL` on an existing column requires an `AccessExclusiveLock`, which is expensive on large tables.
-To start a check only after a specific migration, use:
-
```ruby
-StrongMigrations.enable_check(:remove_index, start_after: 20170101000000)
+class SetSomeColumnNotNull < ActiveRecord::Migration[6.0]
+ def change
+ change_column_null :users, :some_column, false
+ end
+end
```
-### Removing an index
+#### Good
-#### Bad
+Instead, add a constraint:
-In Postgres, removing an index non-concurrently locks the table for a brief period.
-
```ruby
-class RemoveSomeIndexFromUsers < ActiveRecord::Migration[6.0]
+class SetSomeColumnNotNull < ActiveRecord::Migration[6.0]
def change
- remove_index :users, :some_column
+ safety_assured do
+ execute 'ALTER TABLE "users" ADD CONSTRAINT "users_some_column_null" CHECK ("some_column" IS NOT NULL) NOT VALID'
+ end
end
end
```
-#### Good
+Then validate it in a separate migration.
-Remove indexes concurrently.
-
```ruby
-class RemoveSomeIndexFromUsers < ActiveRecord::Migration[6.0]
- disable_ddl_transaction!
-
+class ValidateSomeColumnNotNull < ActiveRecord::Migration[6.0]
def change
- remove_index :users, column: :some_column, algorithm: :concurrently
+ safety_assured do
+ execute 'ALTER TABLE "users" VALIDATE CONSTRAINT "users_some_column_null"'
+ end
end
end
```
-## Best Practices
+Note: This is not 100% the same as `NOT NULL` column constraint. Here’s a [good explanation](https://medium.com/doctolib/adding-a-not-null-constraint-on-pg-faster-with-minimal-locking-38b2c00c4d1c).
### Keeping non-unique indexes to three columns or less
#### Bad
@@ -575,103 +583,117 @@
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
+
+Some operations rarely cause issues in practice, but can be checked if desired. Enable checks with:
+
+```ruby
+StrongMigrations.enable_check(:remove_index)
+```
+
+To start a check only after a specific migration, use:
+
+```ruby
+StrongMigrations.enable_check(:remove_index, start_after: 20170101000000)
+```
+
## Disable Checks
Disable specific checks with:
```ruby
StrongMigrations.disable_check(:add_index)
```
Check the [source code](https://github.com/ankane/strong_migrations/blob/master/lib/strong_migrations.rb) for the list of keys.
-## Existing Migrations
+## Custom Messages
-To mark migrations as safe that were created before installing this gem, create an initializer with:
+To customize specific messages, create an initializer with:
```ruby
-StrongMigrations.start_after = 20170101000000
+StrongMigrations.error_messages[:add_column_default] = "Your custom instructions"
```
-Use the version from your latest migration.
+Check the [source code](https://github.com/ankane/strong_migrations/blob/master/lib/strong_migrations.rb) for the list of keys.
-## Dangerous Tasks
+## Timeouts
-For safety, dangerous database tasks are disabled in production - `db:drop`, `db:reset`, `db:schema:load`, and `db:structure:load`. To get around this, use:
+It’s a good idea to set a long statement timeout and a short lock timeout for migrations. This way, migrations can run for a while, and if a migration can’t acquire a lock in a timely manner, other statements won’t be stuck behind it.
-```sh
-SAFETY_ASSURED=1 rails db:drop
+Create `config/initializers/strong_migrations.rb` with:
+
+```ruby
+StrongMigrations.statement_timeout = 1.hour
+StrongMigrations.lock_timeout = 10.seconds
```
-## Faster Migrations
+Or set the timeouts directly on the database user that runs migrations. For Postgres, use:
-Only dump the schema when adding a new migration. If you use Git, create an initializer with:
-
-```ruby
-ActiveRecord::Base.dump_schema_after_migration = Rails.env.development? &&
- `git status db/migrate/ --porcelain`.present?
+```sql
+ALTER ROLE myuser SET statement_timeout = '1h';
+ALTER ROLE myuser SET lock_timeout = '10s';
```
-## Schema Sanity
+Note: If you use PgBouncer in transaction mode, you must set timeouts on the database user.
-Columns can flip order in `db/schema.rb` when you have multiple developers. One way to prevent this is to [alphabetize them](https://www.pgrs.net/2008/03/12/alphabetize-schema-rb-columns/). Add to the end of your `Rakefile`:
+## Existing Migrations
+To mark migrations as safe that were created before installing this gem, create an initializer with:
+
```ruby
-task "db:schema:dump": "strong_migrations:alphabetize_columns"
+StrongMigrations.start_after = 20170101000000
```
-## Custom Messages
+Use the version from your latest migration.
-To customize specific messages, create an initializer with:
+## Target Version
+If your development database version is different from production, you can specify the production version so the right checks are run in development.
+
```ruby
-StrongMigrations.error_messages[:add_column_default] = "Your custom instructions"
+StrongMigrations.target_postgresql_version = "10"
+StrongMigrations.target_mysql_version = "8.0.12"
+StrongMigrations.target_mariadb_version = "10.3.2"
```
-Check the [source code](https://github.com/ankane/strong_migrations/blob/master/lib/strong_migrations.rb) for the list of keys.
+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:
```ruby
StrongMigrations.auto_analyze = true
```
-## Target Version
+## Faster Migrations
-If your development database version is different from production, you can specify the production version so the right checks are run in development.
+Only dump the schema when adding a new migration. If you use Git, create an initializer with:
```ruby
-StrongMigrations.target_postgresql_version = "10"
-StrongMigrations.target_mysql_version = "8.0.12"
-StrongMigrations.target_mariadb_version = "10.3.2"
+ActiveRecord::Base.dump_schema_after_migration = Rails.env.development? &&
+ `git status db/migrate/ --porcelain`.present?
```
-For safety, this option only affects development and test environments. In other environments, the actual server version is always used.
+## Schema Sanity
-## Timeouts
+Columns can flip order in `db/schema.rb` when you have multiple developers. One way to prevent this is to [alphabetize them](https://www.pgrs.net/2008/03/12/alphabetize-schema-rb-columns/). Add to the end of your `Rakefile`:
-It’s a good idea to set a long statement timeout and a short lock timeout for migrations. This way, migrations can run for a while, and if a migration can’t acquire a lock in a timely manner, other statements won’t be stuck behind it.
-
-You can use:
-
```ruby
-StrongMigrations.statement_timeout = 1.hour
-StrongMigrations.lock_timeout = 10.seconds
+task "db:schema:dump": "strong_migrations:alphabetize_columns"
```
-Or set the timeouts directly on the database user that runs migrations. For Postgres, use:
+## Dangerous Tasks
-```sql
-ALTER ROLE myuser SET statement_timeout = '1h';
-ALTER ROLE myuser SET lock_timeout = '10s';
-```
+For safety, dangerous database tasks are disabled in production - `db:drop`, `db:reset`, `db:schema:load`, and `db:structure:load`. To get around this, use:
-Note: If you use PgBouncer in transaction mode, you must set timeouts on the database user.
+```sh
+SAFETY_ASSURED=1 rails db:drop
+```
## Permissions
We recommend using a [separate database user](https://ankane.org/postgres-users) for migrations when possible so you don’t need to grant your app user permission to alter tables.