Multiple Ways to Change Database Column in Rails
Tags: rails, rails5, database
Published: 2018-04-12 08:38:47

---
title: Multiple Ways to Change Database Column in Rails
published: true
description: This article covers different approaches on how to change the database column. It also covers changing the column name and the column type, as well as changing the column with the type conversion.
tags: Ruby on Rails, Rails 5, Database.
---
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](https://kolosek.com/rails-join-table/) 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](https://kolosek.com/rake-db-commands/) the profiles data will be migrated to the database. Then, let's 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.

**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](https://kolosek.com/rake-db-commands/) 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](https://kolosek.com/rails-rspec-setup/) 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](https://reservedwords.herokuapp.com/) to help you.

## Changing the Column Type
This is a common change that [every programmer](https://kolosek.com/differences-between-good-and-bad-programmers/) 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](https://kolosek.com/factory-girl-associations/) 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.

This article is originally published on [Kolosek Blog](https://kolosek.com/rails-change-database-column/).