Passionate about data

Data and its implications on software design and development.

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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.

1
2
3
4
5
6
7
8
9
10
11
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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.