Is there a way to control column order in ActiveRecord migrations?

Question

I'd like to add a column to a table using a migration. Is there a way to add it in a particular position, rather than as the last column? I could find any "order" option in the API docs.

Answer 1

I don't believe that it is possible to do this with migrations "natively". The reason for this is that many databases do not support the addition of columns at arbitrary locations. The hoops you need to go through to do this with databases that do not support it are non trivial.

If your DB does support it and you do not wish to support multiple databases then you can use an execute statement directly within your migration.

Answer 2

Indeed this isn't part of the migrations API, but if you're willing to give up vendor neutrality (and your DB supports it), it's probably a pretty easy thing to implement.

If you're using MySQL, here's a monkey-patch to the MysqlAdapter that will add options :after and :first to add_column and change_column. (See here for the relevant MySQL ALTER TABLE syntax.)

ActiveRecord::ConnectionAdapters::MysqlAdapter.class_eval do
  def add_column_options!(sql, options)
    super
    if options[:after]
      sql << " AFTER #{quote_column_name(options[:after])}"
    elsif options[:first]
      sql << " FIRST"
    end
  end
end

So now

a.add_column :foos, :bar, :string, :null => false, :limit => 20, :after => "baz"

will execute

ALTER TABLE `foos` ADD `bar` varchar(20) NOT NULL AFTER `baz`

and

a.add_column :foos, :bar, :string, :null => false, :limit => 20, :first => true

will execute

ALTER TABLE `foos` ADD `bar` varchar(20) NOT NULL FIRST

Likewise for change_column.