Read more

Rails migration: Changing a column type without losing the content

Jakob Scholz
March 21, 2023Software engineer at makandra GmbH

The change_column method for rails migrations support casting with a custom SQL statement. This allows us to change a column type and keep the former content as the new type. This way, we can for example prepare an address number column to hold German address numbers, which can contain letters:

Example (in most cases not a good idea!)

class ChangeAnIntegerColumnToString < ActiveRecord::Migration[6.1]
  def up
    change_column :users, :address_number, 'varchar USING CAST(rating AS varchar)'
  end

  def down
    change_column :users, :address_number, 'int USING CAST(rating AS int)'
  end
end

Warning

This migration's rollback strategy will fail, if any existing entry can't be casted. This is likely to happen, if you migrate the previous example, insert an entry with the value 12a and later try to rollback.

The safe way

Illustration online protection

Rails Long Term Support

Rails LTS provides security patches for old versions of Ruby on Rails (2.3, 3.2, 4.2 and 5.2)

  • Prevents you from data breaches and liability risks
  • Upgrade at your own pace
  • Works with modern Rubies
Read more Show archive.org snapshot

BE CAREFUL with this method, you should really be sure that it's okay to use an irreversible migration! if you know that no one will ever rollback your migration, you can define the down method as follows:

  def down
    raise ActiveRecord::IrreversibleMigration
  end

Info

The ActiveRecord::IrreversibleMigration error states that a migration can't be rolled back due to data loss.

The safest way

The safest (but also most expensive) way would be to handle the cast yourself:

class ChangeAnIntegerColumnToString < ActiveRecord::Migration[6.1]
  class User < ActiveRecord::Base; end
  
  def up
    change_column :users, :address_number, 'varchar USING CAST(rating AS varchar)'
  end

  def down
    rename_column :users, :address_number, :address_number_string
    add_column :users, :address_number, :integer
    User.reset_column_information
    
    User.find_each do |user|
      # your cast here, e. g.:
      sanitized_address_number = user.address_number_string.remove(/\D/)
      user.update!(address_number: sanitized_address_number)
    end
    
    remove_column :users, :address_number_string
  end
end
Jakob Scholz
March 21, 2023Software engineer at makandra GmbH
Posted by Jakob Scholz to makandra dev (2023-03-21 16:03)