Multiple Ways to Change Database Column in Rails

While working on a Rails application, we all have had to change the database column in some way. You can change the column name and the column type, as well as changing the column with the type conversion.

Changing the Column Name

Before you can change a column, you have to create it. Let's start with creating a model for profiles.

class CreateProfiles < ActiveRecord::Migration
  def change
    create_table :profiles do |t|
      t.references :user
      t.string :avatar
      t.string :name, null: false
    end
  end
end

Once you run rake db:migrate the profiles data will be migrated to the database. Then, change the column name. How can you do this?

There are 3 different ways to achieve that:

• Making a new migration.
• Fixing the existing migration.
• Making a migration in order to change the table.

Note: Be sure to run rake db:migrate at the end of all the examples, so the changes will be saved in the database.

Making a New Migration to Change the Column Name

First, you need to create a new migration:

rails g migration rename_profile_name_to_full_name

Then, add a rename_column in the newly generated file, specifying which table and what column you want to change:

class RenameProfileNameToFullName < ActiveRecord::Migration
  def change
    rename_column :profiles, :name, :full_name
  end
end

And you are done!

Fixing an Existing Migration

Before you start, you should rollback the migration that you want to fix. Depending on when the migration was made you can do it in the following ways:

• The last migration: rake db:rollback.
• Calling the migration version: rake db:migrate:down VERSION=YOUR_MIGRATION_VERSION .

Once the rollback has been completed you are free to edit the model.

class CreateProfiles < ActiveRecord::Migration
  def change
    create_table :profiles do |t|
      t.references :user
      t.string :avatar
      t.string :full_name, null: false
    end
  end
end

Making a New Migration to Change the Table

You should only use this option when adding multiple changes to the table at once.

Start by creating a new migration:

rails g migration change_profiles

After the file has been created, specifying which column you want to change by adding a rename type on its place:

class ChangeProfiles < ActiveRecord::Migration
  def change
    change_table :profiles do |t|
       t.references :user
       t.string :avatar
       t.rename :name, :full_name   # t.string -> t.rename
    end
  end
end

Always run your RSpec tests after each change to the database to make it sure everything is working as intended. Also, please be careful with reserved names in Rails or SQL when renaming a column. Here is a list of reserved words to help you.

Changing the Column Type

This is a common change that every programmer encounters - you start with a model having an attribute of one type and then you realize it needs to be another. At first, you might think that you should store a profile name as a text when it should actually be a string.

Don't worry! You can easily fix this by generating a new migration:

rails g migration change_name_to_be_string_in_profiles

Now, open up the migration file and edit it in the following way:

class ChangeNameToBeStringInProfiles < ActiveRecord::Migration
  def change
    change_column :profiles, :name, :string
  end
end

Note that change_column is an irreversible migration. It will cause an error if you try to rollback. To prevent this, modify the usual change method in the migration to use two separate up and down methods like this:

class ChangeNameToBeStringInProfiles < ActiveRecord::Migration
  def up
    change_column :profiles, :name, :string
  end
  
  def down
    change_column :profiles, :name, :text
  end
end

Test this out! Start by creating a profile factory and change the name type to a string.

Changing the Column Type - Errors

There are times when simply changing the column type will not work. When you try to write a migration that converts a string column to an integer:

change_column :profile, :age, :integer

Your database will complain and raise an error:

PG::DatatypeMismatch: ERROR:  column "age" cannot be cast automatically to type integer
HINT:  Specify a USING expression to perform the conversion.

To solve this, we will follow the hint and specify in our migration how data should be converted. There are two ways to do this:

• change_column :profile, :age, 'integer USING CAST(age AS integer)',
• change_column :profile, :age, :integer, using: 'age::integer'.

That's it! Hope these steps helped you to change the database columns to your liking.