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 we can change a column, we 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 we decided to change the column name. How can we 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.
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 we 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:
- 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.
We CreateArticles every week, subscribe to stay up-to-date!