Writing DB migrations

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

Creating a Migration

Follow https://guides.rubyonrails.org/active_record_migrations.html#creating-a-standalone-migration

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.

Specifying the correct null value on a field

In an ActiveRecord migration, null: true and null: false are used to specify whether a column in a database table can have a null value or not.

null: true - means that the column can have a null value, which is a value that represents no data. If a column is defined as true, it can be left blank when a new record is created or updated.

null: false - means that the column cannot have a null value. If a column is defined as false, it must have a value when a new record is created or updated.

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.