44

I learned that add_column has an :after option to set where the column gets inserted. Too bad I learned about it :after adding a bunch.

How can I write a migration to simply reorder columns?

emersonthis
  • 32,822
  • 59
  • 210
  • 375
  • You could dump you Database to save the Data ; re-order your Schema.rb to put the timestamps columns at the end of the create_table's block ; run `rake db:drop db:create db:migrate` and then re-import your Data – MrYoshiji Sep 19 '13 at 15:37
  • Now, that's an undocumented feature : [You have to read core sources](https://github.com/rails/rails/blob/7860bf11d862a6eb1bd9ada85bcd97f17a2cb536/activerecord/lib/active_record/connection_adapters/abstract/schema_definitions.rb#L278) to find it. Just for reference, I've checked rename_column code : it does not offer this feature (sorry it doesn't help). – kik Sep 19 '13 at 15:51
  • 1
    Use SQL in a migration. http://stackoverflow.com/questions/4095481/easy-way-to-re-order-columns – Zabba Sep 19 '13 at 16:27
  • @Zabba That's not a bad idea. Could you submit it as an answer, preferably with some code to show the syntax for executing raw MySQL in a migration. – emersonthis Sep 19 '13 at 16:30
  • I just pointed out another answer on SO, but Stefan shows a nice way to do it without SQL.. – Zabba Sep 19 '13 at 17:01
  • @MrYoshiji: This didn't work for me. The `rake` commands re-order the columns, that's correct. But when I re-import my legacy data using an SQL dump the column sort order gets messed up again. – Tintin81 Feb 19 '17 at 11:13
  • @MrYoshiji: Good overall point except that you should not modify the `schema.rb` directly. Create a migration instead. I quote here the rails documentation on `schema.rb` [rails 5.1]: "*This file is auto-generated from the current state of the database. Instead of editing this file, please use the migrations feature of Active Record*". So: 1/ erase the relevant migrations 2/ run `rake db:drop db:create` (this will empty the `schema.rb`), 3/ create a migration, 4/ edit it so as to add the columns in the right order, an then 5/ run it with `rake db:migrate`. It worked for me. – Varus Septimus Jul 14 '17 at 20:50

1 Answers1

67

When using MySQL, you can call change_column, but you have to repeat the column type (just copy and paste it from your other migration):

def up
  change_column :your_table, :some_column, :integer, after: :other_column
end

Or if you have to reorder multiple columns in one table:

def up
  change_table :your_table do |t|
    t.change :some_column, :integer, after: :other_column
    # ...
  end
end

change_column calls ALTER TABLE under the hood. From the MySQL documentation:

You can also use FIRST and AFTER in CHANGE or MODIFY operations to reorder columns within a table.

Note that this approach doesn't work with PostgreSQL. (see Alter column positions)

Stefan
  • 109,145
  • 14
  • 143
  • 218
  • Good catch. I was not aware it was doable through ALTER TABLE. – kik Sep 19 '13 at 17:11
  • @OlivierElMekki I've added a link to the MySQL documentation – Stefan Sep 19 '13 at 18:55
  • @Stefan Can I get away with using `def change` instead of `up` and `down`? – emersonthis Sep 19 '13 at 19:27
  • 2
    @Emerson Rails currently can't reverse `change_column`. Do you really have to revert the column order? – Stefan Sep 19 '13 at 19:49
  • @Stefan Nope. I don't need to revert it. I just wanted to make sure it wouldn't break anything. If that's the case, should I use `up` instead of `change`? – emersonthis Sep 19 '13 at 19:56
  • You can use `up` for a "one way" migration. Using `change` will raise an exception upon rollback if Rails can't reverse the migration definition unless you're [using `reversible`](http://guides.rubyonrails.org/migrations.html#using-reversible). – Stefan Sep 19 '13 at 20:07
  • 9
    None of this works with postgres- it's impossible to reorder columns http://stackoverflow.com/a/31683264/165673 – Yarin Jan 23 '16 at 16:34
  • @Yarin Thanks! That saved me some precious time – Anwar Apr 22 '17 at 15:11