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?

score 70 · Accepted Answer · answered Jul 14 '10 at 22:17

70

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.

answered Jul 14 '10 at 22:17

Gabe Martin-Dempesy

7,687
4
33
24

2

Thanks for giving a valid reason for caring about the column order. I really didn't get it until I read your answer. If I look at the db outside the ORM that usually means the SQL command-line console or a UI where I can shuffle the columns around as I like. Didn't think about these new-fangled web UI things. :-) – clacke Jun 08 '11 at 09:22
Any idea if SQLite supports :after option? Can't get it working in neither change_column nor add_column – alexy2k Aug 05 '12 at 14:08
11

Besides, eg, `:after => :id`, you can also say `:first => true` to insert it as the first column. – Nathan Long Dec 03 '12 at 18:52
11

If you're wondering why this doesn't work for migrations run on PostgreSQL, it's because [MySQL supports AFTER as an ADD COLUMN option](https://dev.mysql.com/doc/refman/5.1/en/alter-table.html) and [PostgreSQL doesn't](http://www.postgresql.org/docs/9.1/static/sql-altertable.html) – Nathan Long Apr 22 '15 at 20:07

score 32 · Answer 2 · edited Jan 30 '19 at 19:03

32

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.

Type rails g migration AddGenderToUser gender:string

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

edited Jan 30 '19 at 19:03

Manish Shrivastava

30,617
13
97
101

answered Mar 30 '12 at 15:47

Tamik Soziev

14,307
5
43
55

2

Could you please add a link to the official documentation? – sekmo Feb 20 '18 at 15:12

score 8 · Answer 3 · answered Oct 04 '09 at 20:28

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).

Bill Karwin · Answer 4 · 2008-12-16T17:42:27.563

5

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

edited Dec 16 '08 at 17:42

answered Dec 16 '08 at 17:02

Bill Karwin

538,548
86
673
828

yup, this is what I do for my application that uses and older version of rails. – Vicer Apr 05 '11 at 02:16
@Ravistm Not surprising, since I answered this in 2008 — before Rails 1.0! Feel free to post your own answer that works with Rails 5.2. – Bill Karwin Sep 07 '19 at 07:29
i gave it a try with miner tweet which was not successful. – Ravistm Sep 07 '19 at 13:05

score 1 · Answer 5 · answered Dec 16 '08 at 17:49

1

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.

answered Dec 16 '08 at 17:49

Mike Woodhouse

51,832
12
88
127

It's not coincidental, it's standard SQL behavior that ALTER TABLE ADD COLUMN adds the column as the last ordinal position in the table. MySQL's "AFTER" syntax is an extension to standard SQL. – Bill Karwin Dec 17 '08 at 16:30
ANSI standard? Or de facto? Not quibbling, just curious. – Mike Woodhouse Dec 18 '08 at 09:39
I'm going by the book "SQL-99 Complete, Really" which describes it as ANSI standard behavior that additional columns are added as the rightmost column in a table. – Bill Karwin Dec 24 '08 at 21:16

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

5 Answers5

Linked