Last Updated: August 16, 2018
· irichlau

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|
      :kind => coupon.kind,
      :discount => coupon.discount,
      :coupon_id => coupon.id

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

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!

4 Responses
Add your response


Hi Richard.

Probably I have few improvements for your the first migration class. I think you should add your code under self.up method instead of using unreversible code in the change method.

Also in few months another dev can come to your team and run all migrations for some reasons instead of using schema:load and I think it would be great to define something like this before def self.up or def self.down methods:

class Coupon < ActiveRecord::Base ; end

It will skip validations for another possible things that can terminate your code.

Or instead of using create method I think it's better to use create! and wrap this code using transactions like this:

Coupon.transaction do
Coupon.findeach do |coupon|
:kind => coupon.kind,
:discount => coupon.discount,
:coupon_id => coupon.id

If something throws exception database will rollback changes.

over 1 year ago ·

thanks for the feedback! i will take note of that

over 1 year ago ·

Maybe some code illustrating the "non-slow" version might be more useful for readers? Just sayin'.

over 1 year ago ·

thank you for taking time out of your busy schedule for your insight and advice. i should update this tip with a more efficient solution.

over 1 year ago ·