Writing DB migrations - TISTATechnologies/caseflow GitHub Wiki

Some rules for writing migrations. Also check out Record associations and Foreign Keys.

Index creation should go in its own migration file

Because we turn transactions off for migrations that contain indexes, due to how long they can take to create in production, we must ensure that they finish successfully or they will block deployments and leave the database in a broken state.

In addition, we must set the statement_timeout to a long value.

Good example (but see below for simpler version):

class ReferenceLegacyIssueInLegacyIssueOptins < ActiveRecord::Migration[5.1]
  disable_ddl_transaction!

  def change
    ActiveRecord::Base.connection.execute "SET statement_timeout = 1800000" # 30 minutes

    add_index :legacy_issue_optins, :legacy_issue_id, algorithm: :concurrently

  ensure
    # always restore the timeout value
    ActiveRecord::Base.connection.execute "SET statement_timeout = 30000" # 30 seconds
  end
end

To make this simple, use the Caseflow::Migration class which handles all the timeouts for you:

class ReferenceLegacyIssueInLegacyIssueOptins < Caseflow::Migration
  def change
    add_safe_index :legacy_issue_optins, :legacy_issue_id
  end
end

In addition to increasing the timeout, Caseflow::Migration also creates the indexes concurrent if using the add_safe_index method, which allows most activity against the table to continue while the index is being created, limiting production impact.

Comments should be updated with a migration

The PR checklist for Database Changes includes an action item, Column comments updated. If you update the comments for a column, do not edit the comments in schema.rb directly. Instead, use a migration to update the column comments.

Editing the schema directly without a migration will cause other developers' schemas to get out of sync.

Here is an example of a migration to update a column's comments.