Rails migrate down to reduce String value size-CodePudding

I'm working on a task where I need to increase the size limit of a String field. I've written the migration that I currently have below.

class IncreaseCompanyNumberLimit < ActiveRecord::Migration[6.1]
  def up
    if Company.columns.find { |c| c.name == 'company_number' }.limit < 25
      change_column :company, :company_number, :string, :limit => 25
    end  
  end

  def down
    change_column :company, :company_number, :string, :limit => 20
  end
end

The problem I'm running into is if someone has entered a company_number after the up method is run during the migration. If the new company_number is larger than the 20 limit I'm attempting to set with the down method, then it crashes when I run the down method because the existing data is too long.

Is there an effective way to trim anything down that is larger than the limit being set by the down method? This is a large table so I'm trying to find an efficient way to achieve this, and google hasn't been much help.

Thank you for your help in advance!

CodePudding user response：

You need to run trim the values as part of the migration. You can use the database's own string handling functions to do this much more efficiently than retrieving the values and trimming in Ruby. These functions are different per-database. I'll use PostgreSQL's string functions for this example. You should be able to adapt it to any database.

Do it in batches to avoid locking the whole table while it runs.

class IncreaseCompanyNumberLimit < ActiveRecord::Migration[6.1]
  NEW_LIMIT = 25
  OLD_LIMIT = 20

  def up
    change_column :company, :company_number, :string, limit: NEW_LIMIT
  end

  def down
    Company.in_batches.update_all("company_number = left(company_number, OLD_LIMIT)")
    change_column :company, :company_number, :string, :limit => OLD_LIMIT
  end
end

Traditional SQL schema management says to use constraints and limits in the schema to ensure the data conforms. Rails is different. The models and database are inseparable. With an ORM managed schema like this I recommend against putting business logic in your schema. Arbitrary field limits are the biggest offenders. As you're learning, they can arbitrarily change. Having to do a schema update because this week the limit is 25 and next week the limit is 20 is an inefficient use of programmer time.

class RemoveCompanyNumberLimit < ActiveRecord::Migration[6.1]
  NEW_LIMIT = nil
  OLD_LIMIT = 20

  def up
    change_column :company, :company_number, :string, limit: NEW_LIMIT
  end

  def down
    Company.in_batches.update_all("company_number = left(company_number, OLD_LIMIT)")
    change_column :company, :company_number, :string, :limit => OLD_LIMIT
  end
end

Instead, enforce this in the Company model with a validation. This is more flexible, and it will provide an informative error message.

class Company
  validates :company_number, length: { maximum: 25 }
end

Then your controller can use the validation errors to inform the user what went wrong.