add a database column with Rails migration and populate it based on another column

Question

I'm writing a migration to add a column to a table. The value of the column is dependent on the value of two more existing columns. What is the best/fastest way to do this? Currently I have this but not sure if it's the best way since the groups table is can be very large.

class AddColorToGroup < ActiveRecord::Migration
  def self.up
    add_column :groups, :color, :string
    Groups = Group.all.each do |g|
      c = "red" if g.is_active && is_live 
      c = "green" if g.is_active
      c = "orange"
      g.update_attribute(:type, c)
    end
  end

  def self.down

  end
end

I don't like seeing empty `down` methods like this. It gives a false sense of security that when yo roll back you actually rolled back, when in reality you did no such thing. The `down` method should put things back to how they were before `up` was run, and if you can't do that then leave it out entirely, so that when you try to roll back you are clearly informed that you can't — Toby 1 Kenobi, Mar 30 '22 at 04:48

score 19 · Answer 1 · answered Mar 18 '13 at 06:06

It's generally a bad idea to reference your models from your migrations like this. The problem is that the migrations run in order and change the database state as they go, but your models are not versioned at all. There's no guarantee that the model as it existed when the migration was written will still be compatible with the migration code in the future.

For example, if you change the behavior of the is_active or is_live attributes in the future, then this migration might break. This older migration is going to run first, against the new model code, and may fail. In your basic example here, it might not crop up, but this has burned me in deployment before when fields were added and validations couldn't run (I know your code is skipping validations, but in general this is a concern).

My favorite solution to this is to do all migrations of this sort using plain SQL. It looks like you've already considered that, so I'm going to assume you already know what to do there.

Another option, if you have some hairy business logic or just want the code to look more Railsy, is to include a basic version of the model as it exists when the migration is written in the migration file itself. For example, you could put this class in the migration file:

class Group < ActiveRecord::Base
end

In your case, that alone is probably sufficient to guarantee that the model will not break. Assuming active and live are boolean fields in the table at this time (and thus would be whenever this migration was run in the future), you won't need any more code at all. If you had more complex business logic, you could include it in this migration-specific version of model.

You might even consider copying whole methods from your model into the migration version. If you do that, bear in mind that you shouldn't reference any external models or libraries in your app from there, either, if there's any chance that they will change in the future. This includes gems and even possibly some core Ruby/Rails classes, because API-breaking changes in gems are very common (I'm looking at you, Rails 3.0, 3.1, and 3.2!).

Robert · Answer 2 · 2013-03-08T01:23:39.470

I would highly suggest doing three total queries instead. Always leverage the database vs. looping over a bunch of items in an array. I would think something like this could work.

For the purposes of writing this, I'll assume is_active checks a field active where 1 is active. I'll assume live is the same as well.

Rails 3 approach

class AddColorToGroup < ActiveRecord::Migration
  def self.up
    add_column :groups, :color, :string
    Group.where(active: 1, live: 1).update_all(type: "red")
    Group.where(active: 1, live: 0).update_all(type: "green")
    Group.where(active: 0, live: 0).update_all(type: "orange")
   end
 end

Feel free to review the documentation of update_all here.

Rails 2.x approach

class AddColorToGroup < ActiveRecord::Migration
  def self.up
    add_column :groups, :color, :string
    Group.update_all("type = red", "active = 1 AND live = 1")
    Group.update_all("type = red", "active = 1 AND live = 0")
    Group.update_all("type = red", "active = 0 AND live = 0")
   end
 end

Rails 2 documentation

:( my application is not using Rails 3. Thanks though! – user1404536 Mar 08 '13 at 01:20 — user1404536, Mar 08 '13 at 01:20
Thank you so much Robert. It's what I needed. – user1404536 Mar 08 '13 at 05:42 — user1404536, Mar 08 '13 at 05:42

AdamT · Answer 3 · 2013-03-08T01:03:08.860

1

I would do this in a

after_create
# or
after_save

in your ActiveRecord model:

class Group < ActiveRecord::Base
  attr_accessor :color

  after_create :add_color

  private

  def add_color
    self.color = #the color (wherever you get it from)
  end

end

or in the migration you'd probably have to do some SQL like this:

execute('update groups set color = <another column>')

Here is an example in the Rails guides:

http://guides.rubyonrails.org/migrations.html#using-the-up-down-methods

edited Mar 08 '13 at 01:03

answered Mar 08 '13 at 00:23

AdamT

6,405
10
49
75

2

thanks, but the column should be pre-populated for all the existing groups. This would only work for newly created groups. – user1404536 Mar 08 '13 at 00:41

score 1 · Answer 4 · answered Sep 20 '17 at 20:00

In a similar situation I ended up adding the column using add_column and then using direct SQL to update the value of the column. I used direct SQL and not the model per Jim Stewart's answer, since then it doesn't depend on the current state of the model vs. the current state of the table based on migrations being run.

class AddColorToGroup < ActiveRecord::Migration
  def up
    add_column :groups, :color, :string
    execute "update groups set color = case when is_active and is_live then 'red' when is_active then 'green' else 'orange' end"
  end

  def down
    remove_column :groups, :color
  end
end

add a database column with Rails migration and populate it based on another column

4 Answers4

Linked