3 Step Migration for moving columns with data to a new table

At work today I had the pleasure of working on a production level app that needed to move a couple of columns that already was populated with data from an existing table(Coupon) into a new table(CouponRules) in postgres. The safest way to do this is the 3 step migration process. It should work with most mysql databases.

First you need to create the new table and it's migration files

rails g model CouponRules kind:string discount:decimal coupon_id:integer

and then run rake db:migrate

Second you need to create a new migration file

rails g migration MoveColumnDataToCouponRulesTable

and add this:

def change
   Coupon.find_each do |coupon|
      coupon.coupon_rules.create(
      :kind => coupon.kind,
      :discount => coupon.discount,
      :coupon_id => coupon.id
      )
    end
 end

This is the easier way of doing things and it is not recommended if you have a large dataset you need to migrate over because it is slow. For large datasets, you should be using SELECT but we're going to keep it simple for now.

Basically what this does is it will find each Coupon record and "copy" the kind, discount, and coupon_id to the new CouponRules table.

then run rake db:migrate

Remember you can only run the migration file once. if you update the database and run the migration again it will not update. You should go into rails console and check to see if the data exist in your new table. If you run into errors you can run db:rollback.

The last step is to remove the columns from the Coupons Table.

rails g migration RemoveColumnsfromCouponTable

 def change
    remove_column :coupons, :kind
    remove_column :coupons, :discount
  end

and run rake db:migrate

That's it you're done!

If you have questions or want to elaborate on the SELECT, feel free to add your input on the comment section.

Hope it helps!