Tutorial :In a Rails Migration (MySQL), can you specify what position a new column should be?



Question:

If I'm adding a column via MySQL, I can specify where in the table that column will be using the AFTER modifier. But if I do the add_column via a Rails migration, the column will be created at the end of the table.

Is there any functionality for rails migrations to specify the position of an added column?


Solution:1

This is now possible in Rails 2.3.6+ by passing the :after parameter

https://rails.lighthouseapp.com/projects/8994/tickets/3286-patch-add-support-for-mysql-column-positioning-to-migrations

To everyone that doesn't see the advantage in having this feature: do you never look at your database outside of the ORM? If I'm viewing in any sort of UI, I like having things like foreign keys, status columns, flags, etc, all grouped together. This doesn't impact the application, but definitely speeds up my ability to review data.


Solution:2

Sure you can.

  • Short answer:

    add_column :users, :gender, :string, :after => :column_name  
  • Long answer:

Here is an example, let's say you want to add a column called "gender" after column "username" to "users" table.

  1. Type rails g migration AddGenderToUser gender:string
  2. Add "after => :username" in migration that was created so it looks like this:

    class AddSlugToDictionary < ActiveRecord::Migration    def change      add_column :users, :gender, :string, :after => :username    end  end  


Solution:3

I created a patch that adds this additional functionality to the ActiveRecord Mysql adapter. It works for master and 2-3-stable.

https://rails.lighthouseapp.com/projects/8994/tickets/3286-patch-add-support-for-mysql-column-positioning-to-migrations

It might be mysql specific, but it doesn't make your migrations any less portable (other adapters would just ignore the extra positioning options).


Solution:4

There does not seem to be a position option for the add_column method in migrations. But migrations support executing literal SQL. I'm no Rails developer, but something like the following:

class AddColumnAfterOtherColumn < ActiveRecord::Migration    def self.up      execute "ALTER TABLE table_name ADD COLUMN column_name INTEGER         AFTER other_column"    end      def self.down      remove_column :table_name, :column_name    end  end  


Solution:5

There is no way within Rails to specify the position of a column. In fact, I think it's only coincidental (and therefore not to be relied on) that columns are created in the order they are named in a migration.

The order of columns within a table is almost relevant and should be so: the common "reason" given is to be able to see a particular subset when executing a "SELECT *", but that's really not a good reason.

Any other reason is probably a design smell, but I'd love to know a valid reason why I'm wrong!

On some platforms, there is a (miniscule) space and performance saving to be obtained by putting the columns with the highest probability of being NULL to the end (because the DMBS will not use any disk space for "trailing" NULL values, but I think you'd have to be running on 1980's hardware to notice.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »