Note: Please see the updated ColumnComments v.1.2 post for the latest with bug fixes.

ColumnComments

Here’s a small plugin that may be useful for documenting your database. Based on Dave Thomas’s AnnotateModels plugin, this plugin goes one step further and allows you to store comments on each column in the database (MySQL only).

Why?

Since the Rails philosophy uses the database as the “authoritative source” for the data model, it’s been troublesome to know where to store helpful developer comments. If you write them in the model files (where it would make sense to do it), you repeat a lot of work because the models don’t store column information.

Dave Thomas solved part of this problem by introducing the AnnotateModels plugin–with it you can take the basic information about each table and auto-generate a comment-header inside each model file. But still, where would you put comments? If you edit the auto-generated header then you run the risk of accidentally (or by necessity) overwriting your own comments when you re-generate the headers.

Enter ColumnComments

The ColumnComments plugin makes the necessary modifications to ActiveRecord to allow for an optional :comment in your table migrations. In addition, it includes the same feature provided by the AnnotateModels plugin (rake annotate_models), but also includes the database-stored comments in the auto-generated header.

Here’s an example of the output:

# Schema as of Thu Apr 27 12:07:32 MDT 2006 (schema version 10)
#
#  id                                      integer(11)         not null
#  login                                   string(255)
#    A user-generated login name.
#
#  salted_password                         string(40)          default(), not null
#    Encrypted using SHA1 and the "salt" column
#
#  email                                   string(60)          default(), not null
#    Mandatory email address for each user

Adding Comments to the Database

There are two times when you might want to add comments to your columns: while you create a new table or after a table already exists.

While You Create a New Table

  def self.up
    create_table "users" do |t|
      t.column "first_name", :string, :comment => "The member's given name."
      t.column "last_name", :string, :comment => "The member's family name."
    end
  end

After a Table Already Exists

  def self.up
    column_comment "tags", "id", "The unique ID of any tag in the system."
  end

Or, if you want to mass-assign comments (for example, just after you install the plugin on an already-built app):

  def self.up
      column_comments({
        :users => {:first_name => "User's given name", :last_name => "Family name"},
        :tags  => {:id => "Tag IDentifier"}})
  end

Get It From Our Subversion Repository

syncPEOPLE maintains an open source subversion repository for plugins and other useful code that we’ve open-sourced. ColumnComments is released under the MIT license and can be installed from:

svn://syncid.textdriven.com/svn/opensource/column_comments/trunk