Automatically adding columns to Rails migrations

Many projects need addition of identical columns to all the tables created by the project. Audit columns are an example of such a requirement. The requirement is to add columns such as created_by, created_date, modified_by and modified_date to all the tables, these columns store, who created the row, when the row was created, who modified the row last and when was it modified. created_by and created_date are required to be present when the row is inserted and thus are required to be not nullable. Adding these columns to each and every table is a lot of work for developers.

When creating the new migration, we would add all the required columns for every migration we create as shown below

class CreateCustomer < ActiveRecord::Migration
  def change
    execute 'CREATE SEQUENCE SEQ_CUSTOMER'
    create_table :customer, primary_key: 'customer_id', sequence_name: 'seq_customer', id: false do |t|
      t.integer :customer_id, limit: 8
      t.string :name, null: false
      t.string :email, limit: 200
      t.datetime :date_joined, null: false
      t.string :active_flag, default: 'Y', limit: 1
      t.string :created_by, null: false
      t.datetime :created_date, null:false
      t.string :modified_by, null:true
      t.datetime :modified_date, null:true
    end
    execute 'ALTER TABLE CUSTOMER MODIFY DATE_JOINED DEFAULT SYSDATE'
  end
end

Adding the four columns for every table is repetitive and could be easily forgotten, creating follow-on migrations and data loss. Adding a ActiveRecord method like audit_columns that automatically adds the default columns when a new table is being created saves the developers from remembering about the four columns and also enables them to have a standard definition of these columns.

module ActiveRecordTableDefinitionExtension
  extend ActiveSupport::Concern
  included do
    def audit_columns(options={})
      column(:created_by, :string, null: false)
      column(:created_date, :datetime, null:false)
      column(:modified_by, :string)
      column(:modified_date, :datetime )
    end
  end
end

Once the ActiveSupport method is defined, we can add the new customer table and reference the audit_columns method, which will automatically add the four columns when the migration is run

class CreateCustomer < ActiveRecord::Migration
  def change
    execute 'CREATE SEQUENCE SEQ_CUSTOMER'
    create_table :customer, primary_key: 'customer_id', sequence_name: 'seq_customer', id: false do |t|
      t.integer :customer_id, limit: 8
      t.string :name, null: false
      t.string :email, limit: 200
      t.datetime :date_joined, null: false
      t.string :active_flag, default: 'Y', limit: 1
      t.audit_columns
    end
    execute 'ALTER TABLE CUSTOMER MODIFY DATE_JOINED DEFAULT SYSDATE'
  end
end

We still have to run the DEFAULT SYSDATE command as a raw sql as we want the default to be a function and not a literal date. Similar techniques can be applied when other types of columns are required to be present on a large number of tables.

Share