This blog is part of our Rails 5 series.

Database schemas change rapidly as project progresses. And it can be difficult to track purpose of each table and each column in a large project with multiple team members.

The solution for this problem is to document data models right from Rails migrations.

Solution in Rails 4

You can add comments in Rails 4.x migrations using gems like migration_comments and pg_comment.

Solution in Rails 5

Rails 5 allows to specify comments for tables, column and indexes in migrations.

These comments are stored in database itself.

Currently only MySQL and PostgreSQL supports adding comments.

We can add comments in migration as shown below.

class CreateProducts < ActiveRecord::Migration[5.0]
  def change
    create_table :products, comment: 'Products table' do |t|
      t.string :name, comment: 'Name of the product'
      t.string :barcode, comment: 'Barcode of the product'
      t.string :description, comment: 'Product details'
      t.float :msrp, comment: 'Maximum Retail Price'
      t.float :our_price, comment: 'Selling price'

      t.timestamps
    end

    add_index :products, :name,
              name: 'index_products_on_name',
              unique: true,
              comment: 'Index used to lookup product by name.'
  end
end

When we run above migration output will look as shown below.

  rails_5_app rake db:migrate:up VERSION=20160429081156
== 20160429081156 CreateProducts: migrating ===================================
-- create_table(:products, {:comment=>"Products table"})
   -> 0.0119s
-- add_index(:products, :name, {:name=>"index_products_on_name", :unique=>true, :comment=>"Index used to lookup product by name."})
   -> 0.0038s
== 20160429081156 CreateProducts: migrated (0.0159s) ==========================

The comments are also dumped in db/schema.rb file for PostgreSQL and MySQL.

db/schema.rb of application will have following content after running products table migration .

ActiveRecord::Schema.define(version: 20160429081156) do

  # These are extensions that must be enabled in order to support this database
  enable_extension "plpgsql"

  create_table "products", force: :cascade, comment: "Products table" do |t|
      t.string   "name",                     comment: "Name of the product"
      t.string   "barcode",                  comment: "Barcode of the product"
      t.string   "description",              comment: "Product details"
      t.float    "msrp",                     comment: "Maximum Retail Price"
      t.float    "our_price",                comment: "Selling price"
      t.datetime "created_at",  null: false
      t.datetime "updated_at",  null: false
      t.index ["name"], name: "index_products_on_name", unique: true, using: :btree, comment: "Index used to lookup product by name."
    end
end

We can view these comments with Database Administration Tools such as MySQL Workbench or PgAdmin III.

PgAdmin III will show database structure with comments as shown below.

-- Table: products

-- DROP TABLE products;

CREATE TABLE products
(
  id serial NOT NULL,
  name character varying, -- Name of the product
  barcode character varying, -- Barcode of the product
  description character varying, -- Product details with string data type
  msrp double precision, -- Maximum Retail price
  our_price double precision, -- Selling price
  created_at timestamp without time zone NOT NULL,
  updated_at timestamp without time zone NOT NULL,
  CONSTRAINT products_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE products
  OWNER TO postgres;
COMMENT ON TABLE products
  IS 'Products table';
COMMENT ON COLUMN products.name IS 'Name of the product';
COMMENT ON COLUMN products.barcode IS 'Barcode of the product';
COMMENT ON COLUMN products.description IS 'Product details with string data type';
COMMENT ON COLUMN products.msrp IS 'Maximum Retail price';
COMMENT ON COLUMN products.our_price IS 'Selling price';


-- Index: index_products_on_name

-- DROP INDEX index_products_on_name;

CREATE UNIQUE INDEX index_products_on_name
  ON products
  USING btree
  (name COLLATE pg_catalog."default");
COMMENT ON INDEX index_products_on_name
  IS 'Index used to lookup product by name.';

If we update comments through migrations, corresponding comments will be updated in db/schema.rb file.