Rails DB Migrations [Guide + Code] — Cheatsheet

All you need to know to jump start your Rails journey

Rails DB Migrations [Guide + Code] — Cheatsheet

Ruby is an object-oriented programming (OOP) language built in 1994 with performance and simplicity at its core – and if that was not enough, a programmer named David Heinemeier Hansson created the Ruby on Rails framework in 2003 for fast web development.

Ruby has become so popular that – despite fierce competition – it still is being taught in many development schools and bootcamps alike across the world in late 2022.

Rails holds a special place in our lumberjack's furry hearts as it is the first technology we used to develop our v1 agent for our solution 🌲. So we thought it'd be good to have a little refresher on how to manipulate some basic Rails commands when starting a project.

Also, Databases being our bread and salted butter, we will give a quick conceptual explanation about Object Relational Mapping (ORMs) – so crucial to OOP databases – so that you may easily understand what Rails helps you achieve through Active Records.

Rails Migrations were introduced to be a convenient way to alter a database in a way that is both structured and organized. This effectively brings consistency over time.

While the most common migrations are no-brainers, some of them are either not covered in the manual, or not clearly enough.

At Forest Admin, it’s our job to handle every possible DB schema, in Rails, NodeJS and others.

This means that we’ve come across a few variations already.

So let’s walk through 9 tricks for generators and migrations gathered from experience (and Stack Overflow 🙊).

List of tips (Level of expertise):

  • Drop DB columns ★☆☆
  • Drop a table ★☆☆
  • Roll back a specific migration ★☆☆
  • Default values ★★☆
  • :text V/S :string ★★☆
  • Rename a column ★★☆
  • Change a column data type ★★☆+
  • Add a new model ★★☆+
  • Rename a model ★★☆
  • Reset your database ★★★
  • Replicate records safely ★★★
  • Retro-update existing records’ when adding a new column ★★★
  • Migrate safely JSON stores ★★★

We will cover the basic – that you can easily find in the doc – before getting into the more technical bits... but first ORMs

1 - Object Relational Mapping and Active Record

Using Rails, we see Active Records everywhere at the beginning of a project in order to create our Database and turn our DB schema drawings into hard-coded reality.

But, do you know where it comes from?

Before getting into it, let us look at the bigger picture: the Object-relational mapping concept without which object-oriented programming would not be able to exist.

Picture this: your program is made of 1) your application on one side and 2) your database on the other.

Manipulating "objects" when coding entails a particular relationship between these objects, all "mapped" in the Database of your program – understand its memory.

These relationships must be properly established before you start getting into the business logic of your program, or it'll be a massive mess that will inevitably break.

In order for you to build these relationships, you have two choices: be a SQL guru and do all the hard plugging yourself, OR use an Object Relational Mapper that will abstract all the hard work from you and, given some specific commands, do the heavy lifting.

Active Record is originally a pattern – referred to as the Active Record pattern when speaking of ORMs concepts – and it turns out that Rails named its Query interface after this concept

Rails Active Record will perform all the queries you need on the database – hassle free.

It is compatible with most Databases, including PostgreSQL, MySQL, MariaDB and SQLite.

Now that this is out of the way, let's code!

2 - One line migration to drop DB columns

Rails automatically generates this kind of migrations through its command line generator:

$ rails generate migration RemoveFieldNameFromTableName field_name:datatype

The migration file generated will contain the following line:

remove_column :table_name, :column_name

For instance:

$ rails generate migration RemoveNameFromMerchant name:string

Gives:

class RemoveNameFromMerchant < ActiveRecord::Migration
  def change
    remove_column :merchants, :name, :string
  end
end

Which would remove the field/column "name" from the Merchant table after running rake db:migrate.


🌲 When using Forest Admin, bear in mind to constantly restart your server and refresh the web page (🍎+R) so that the new schema can be displayed correctly.

3 - The right way to write a migration that drops a DB table

Rails magic isn’t always enough: since this brutally throws away quite a lot of data at a time, there is no one line generator to drop entire tables.

We need to generate a migration (important to get the timestamp that keeps everyone’s database synced), and manually add the deletion inside.

$ rails generate migration DropMerchantsTable

This will generate the empty .rb file in /db/migrate/ that still needs to be filled to drop the “Merchant” table in this case.

A Quick-and-Dirty™ implementation would look like this:

class DropMerchantsTable < ActiveRecord::Migration
  def up
    drop_table :merchants
  end
  def down
    raise ActiveRecord::IrreversibleMigration
  end
end

It’s “correct” as it shows that the migration is one-way only and should not/cannot be reversed.

That being said, for our job to be proper, we must anticipate the possibility of needing to reverse these changes. This is why we need to have a symmetrical migration (assuming we could recover the lost data), which we can do by declaring all the fields of our table in the migration file:

class DropMerchants < ActiveRecord::Migration
  def change
    drop_table :merchants do |t|
      t.string :name, null: false
      t.timestamps null: false
    end
  end
end

This can be long if the model is complex, but it ensures full reversibility.

Here again, changes will enter into effect only after running rake db:migrate.

4 - Rollback a specific migration

It’s generally not a good idea to do that as migrations are made to ensure consistency over projects, environments, and time.

So reverting even one migration breaks the chain.

However, in some cases and for debugging purposes, we can rollback the migration. For that, we need the corresponding timestamp, and we need to punch in this command:

$ rake db:migrate:down VERSION=20170815201547

This will revert the corresponding migration file: db\migrate\20170815201547_create_merchants.rb (where “create_merchants” does not play a role, as the only identifier is the timestamp).

Once again: ⚠️ This migration targets only this specific file, and not up to this specific file.

A better idea is to revert all migrations up to a certain point in time. To achieve that, we may use the same command, only with the “STEP” argument defining the number of migration files we want to chronologically roll back:

$ rake db:rollback STEP=3

As you probably know, to rollback only the last migration we can simply ommit the STEP argument:

$ rake db:rollback

5 - Set default values for new records

Edit: as reported by MCAU, as of Rails 5.2 (and maybe some earlier versions), this is not actual anymore. Default values set in the active record migrations will apply not only to the database columns, but also when instantiating with Model.new(). The hereunder recommendation can still apply in the case you would want to have some computed “virtual” attributes being setup upon instantiation.

Defining default values in ActiveRecord only works part of the time, in particular it won’t work when calling Model.new(), as it won’t call anything from ActiveRecord.

Instead of “enforcing” the default values with a migration, writing an after_initialize callback in our model will let us set the default values we need, and even associations:

class Merchant < ActiveRecord::Base
  has_one :client
  after_initialize :init
  def init
    self.name  ||= "SuperStore"  # will set the default value only if it's nil
    self.client ||= Client.last  # also setting a default association
    self.in_business = true if self.in_business.nil? # ⚠️ only default booleans to 'true' if they're 'nil' to avoid overwriting a 'false'
    end
  end

This allows to have one flexible and easily editable place to initialize our models.

Overriding initialize can also work, but let’s not forget to call super to avoid breaking the init chain.

6 - When should we use ‘text’ or ‘string’ in Rails?

What’s the difference between stringand text? And when should each one be used?

Quite simply, the difference relies in how the symbol is converted into its respective column type in query language: for instance with MySQL :string is mapped to VARCHAR(255).

When should each be used?

As a rule of thumb, use :string for short text input (username, email, password, titles, etc.) and use :text for longer expected input such as descriptions, comment content, etc.

Performance requirements can come into play from time to time, but you should not worry too much about it for now.

With MySQL, we can have indexes on varchars, but we can’t have any on text. ➡️ Use :string when indexes are needed.

With POSTGRES, we should use :text wherever we can, unless there is a size constraint, since there is no performance penalty for text Vs. varchar.

7 - Rename a column with one line

While creating a Migration as for renaming a column, Rails 7 generates a change method instead of up and down as mentioned in the above answer. The generated change method is as below :

$ rails g migration ChangeColumnName

which will create a migration file similar to this :

class ChangeColumnName < ActiveRecord::Migration
  def change
    rename_column :table_name, :old_column, :new_column
  end
end

8 - Add a column to an existing table

Let say we wish to add an email to our table users below.

mysql> desc users;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| name        | varchar(255) | YES  |     | NULL    |                |
| last_name   | varchar(255) | YES  |     | NULL    |                |
| created_at  | datetime     | NO   |     | NULL    |                |
| updated_at  | datetime     | NO   |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

First things first, we need to generate a migration

$ rails g migration AddEmailToUsers email:string      
    invoke  active_record      
    create    db/migrate/56340602201089_add_email_to_users.rb

This command tells Rails to generate a migration for us. It does all the work for us.

mysql> desc users;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| name        | varchar(255) | YES  |     | NULL    |                |
| last_name   | varchar(255) | YES  |     | NULL    |                |
| email       | varchar(255) | YES  |     | NULL    |                |
| created_at  | datetime     | NO   |     | NULL    |                |
| updated_at  | datetime     | NO   |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

Voilà! Rake added the email column to the table.

9 - Rename an entire ActiveRecord model with a Rails migration

It’s not always easy to find the right name for each model in advance. When I choose a poor name for a model, I sometimes resolve to changing it for the greater good, despite the work involved. Here is how to proceed;

Rails doesn’t quite have a ready to fire command line for that, but we can write it ourselves quickly. Let’s create a migration:

$ rails generate migration RenameOldTableToNewTable

Which we can then fill in with “rename_table”:

class RenameOldTableToNewTable < ActiveRecord::Migration
  def change
    rename_table :old_table_name, :new_table_name #use the pluralized name of tables here
  end 
end

rails generate model Users first_name:string last_name:string email:string
      invoke  active_record
      create    db/migrate/20190428234334_create_users.rb
      create    app/models/user.rb
      invoke    test_unit
      create      test/models/user_test.rb
      create      test/fixtures/users.yml

We will still have to go through and manually rename the model in all our files (And not forget to check for capitalized and pluralized versions of the model).

10 - Add a new model

Rails offers that comfortable level of abstraction that helps you save time by doing all the heavy lifting for you... remember? So let's take advantage of it.

Our owner could use a dog, so let's give him one.

rails generate model Dogs name:string breed:string color:string age:integer owner_id:integer
      invoke  active_record
      create    db/migrate/20134339042824_create_dogs.rb
      create    app/models/dog.rb
      invoke    test_unit
      create      test/models/dog_test.rb
      create      test/fixtures/dogs.yml

Rails is your friend, so it prints the file name of the migration you prompted it to create. Check it out:

class CreateDogs < ActiveRecord::Migration[7.0]
  def change
    create_table :dogs do |t|
      t.string :name
      t.string :breed
      t.string :color
      t.bigint :owner_id

      t.timestamps
    end
  end
end

Let see what we actually got in the Database:

mysql> desc dogs;        
+-------------+--------------+------+-----+---------+----------------+        
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| name        | varchar(255) | YES  |     | NULL    |                |
| breed       | varchar(255) | YES  |     | NULL    |                |
| color       | varchat(255) | YES  |     | NULL    |                |
| created_at  | datetime     | NO   |     | NULL    |                |
| updated_at  | datetime     | NO   |     | NULL    |                |
| owner_id    | bigint(20)   | YES   | MUL | 999     |                |
+-------------+--------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)    

When learning about OOP and building your first Database schema, you find out that your objects ought too be somewhat linked if you are to make them interact with each other. Here, a dog must have an owner, and the connection is established with "owner_id", which will refer to a user_id if the dog created has one.

11 - Purge, recreate, drop a Ruby on Rails database

To fully delete and rebuild our database, we can either:

$ rake db:reset db:migrate

Which will reset the database and reload the current schema, or:

$ rake db:drop db:create db:migrate

Which will destroy the DB, create a new one and then migrate the current schema.

⚠️ All data will be lost in both cases, so be mindful when using these!

Just to compare the different dropping and migrating commands:

  • rake db:schema:load - Creates tables and columns within the (existing) database following schema.rb. db:schema:load is used when you setup a new instance of your app.
  • rake db:reset Clears the database (presumably does a rake db:drop + rake db:create + rake db:migrate) and runs migration on a freshly cleaned database.
  • rake db:migrate runs (single) migrations that have not run yet. Typically, you would use db:migrate after having made changes to the schema of an existing DB via new migration files.
  • rake db:create creates the database
  • rake db:drop deletes the database
  • rake db:setup does db:create, db:schema:load, db:seed
  • rake db:reset does db:drop, db:setup
  • rake db:seed runs the seeds task to populate the database with preliminary data

Extra tip: Replicate/Duplicate an ActiveRecord record

Sometimes you need to make a perfect copy of a record, changing only one or a few values, like for an association.

⛔️ Using the clone method will result in a shallow copy of the record, including copying the frozen state and the ID. No callbacks will be called either, which is probably not the effect we’ll be expecting for practical purposes.

new_record = old_record.clone # Shallow copy, without callbacks

✅ The dup method will duplicate the record calling after initialize hooks, clearing associations and IDs. A duped record will return true for new_record? and be 'saveable':

new_record = old_record.dup # For practical matters

Extra Tip 2: Updating existing records with a newly added default value

(Asked in the comments)

Example: If you have a User Model and you Add a status field

rails g migration add_status_to_user

How would existing User record be updated with a value of status after the migration?

(Response)

First, if you need all your users to have a status value, you would better specify a default value for new users to ensure it is not nil. This can be added as a parameter in the migration that adds the column, or later in another migration, as such:

def up
  change_column :users, :status, :string, default: 'active'
end
def down
  change_column :users, :status, :string, default: nil
end

Or with the one-liner:

change_column_default :users, :status, from: nil, to: 'active'

To update the existing records, it’s a best practice to do it too through a migration, so that all the versions of the database will be up to date automatically (as opposed to running some code manually on each one of them).

The right way to do that is to create a one-way new migration where you will write the update code:

$ rails g migration update_status_of_existing_users
def up
  User.update_all({ :status => 'active'})
end

def down
end

The migration should be one-way and not blindly set values back to nil in the down direction to avoid losing data if you were to roll it back.

Depending on your project, you could merge both migrations into one.

⚠️⚠️ Remember to only use raw Ruby+Rails logic in your migrations and never use anything except attributes coming from your own models.

If you need to use any kind of more advanced logic in your migration, define the methods you need in your migration and call them from there.

Using methods coming directly from your models can seem like a good idea at first to DRY your code, but with time and commits your model will change. The method you are calling in this migration might not run the same way, or exist anymore, which will prevent any new-comer to your project to run the migration set of the project smoothly.

Extra Tip 3: Migrating with Rails’ JSON Store

Working with Rails’ JSON store is remarkably easy and convenient, but requires great care to make migration reversible to prevent frustration.

Consider the following case, where the chronology is important:

  • We successfully create an hstore (as :text) in the DB, and migrated.
  • We then add in the model the rails line allowing this hstore to store “JSON attributes” i.e.: store :hstore_column_name, accessors: [attribute_one_name,...]
  • For some reason, we decide to revert the migration to change something.
  • We make our modifications in the migration, and try to re-run migrate.

Our migration will then fail with an unintuitive error message:

NoMethodError: undefined method `accessor' for #<ActiveRecord::xxx>

What is happening is that the line declaring the hstore is still present in our model at this point. When Rails tries to run the migration, the model is called, and this line throws an error, as this same DB column we’re trying to create is supposed to be already available for use by the hstore; but does not exist yet.

Quick Fix
A quick fix is to remove the hstore declaration in the model and to migrate, then re-add it.

Better solution
In general, making hstore migrations fully reversible is not obvious. A migration allowing for model evolutions (if this hstore disappears a year from now and a newcomer needs to migrate their DB to the latest), and for data population at the same time (for instance if you are renaming an hstore or some of its attributes) will require 3 successive migration files, where hstore declarations should happen inside the migration files.

What's next?


Check out our articles tackling Ruby:

  1. Forest Admin now available for Ruby in Rails
  2. Why use Ruby on Rails for your next product in 2021
  3. The most popular Ruby Gems in 2021
  4. How to build a Ruby on Rails admin panel