Database migration tips in Rails - Part 1

Database migrations are an everyday task and as such are not generally considered as exciting as software architecture or any other hot topic in the industry. Yet, they are essential in any applications that use databases!

Ruby on Rails handles a lot of problems with migrations for us. However, there are still some details everyone must remember to avoid troubles. That’s why I would like to share with you some simple tips on how to improve your database migrations, which hopefully, you will find very useful and powerful!

Here are the tips you should follow:

1. Redefine ActiveRecord classes

Often, we need to refer to ActiveRecord Models inside migrations, for example, to update some fields. So, let's imagine we want to introduce a new :status column to the Users table and update Users with the "active" :status.

It might look like this...

But there’s a better way to do this by stubbing out the model in the migrations. It gives us two main advantages. First of all, it guards against the case where a model is removed from the codebase but is still being called in a migration. Secondly, it prevents validations from being run, as well as eliminates the associations overhead.

The improved version would look like this:

2. Irreversible migrations

Always make sure your migration is reversible (you should be able to run rails db:rollback on it). Rails has a #change method that is primarily used in writing migrations. It works for most of the cases in which Active Record knows how to reverse a migration automatically.

However, if you modify the data, for example, update the :status column of User, Rails won’t know how to rollback that migration. Hence, never use the #change method in migration in such a scenario and always use #up/#down methods, where #down raises ActiveRecord::IrreversibleMigration or implements logic to reverse change introduced in #up.

Alternatively, you can use the #reversible block/helper in the #change method as shown in the example below: 

Active Record has a class (ActiveRecord::Migration::CommandRecorder) that records commands done during migration and knows how to reverse them. Currently, there are around 27 commands and most of them are reversible like #create_column or #add_column. Examples of a non-reversible may be #execute, #execute_block, or #transaction. If you would like to find out more, please visit https://api.rubyonrails.org/classes/ActiveRecord/Migration/CommandRecorder.html.

3. Clean up old migrations

It might be a good idea to clean up old migrations in case of the circumstance when you can’t run all migrations (rails db:migrate) on the fresh database. Otherwise, it might not be necessary to do so as there aren’t many benefits besides fewer files in db/migrations.

In GAT, we only did it once because of old, poorly written migrations. Cleaning up legacy migrations is fairly simple; you just need to create one migration file that has the current state of the database. In other words, a copy of your schema.rb. Now you can forget about all the troubles when running migrations on a fresh database:

4. Bulk migrations

Let's say that we need to add :first_name, :last_name, :address, :phone_number to the Users table.

Instead of adding each field separately we can do it in bulk mode and add them at once. This way the query will be executed once instead of four times, and in the database world, executing a single query is much faster than executing four individual queries one by one even though their results are the same.

Compare these two examples:

In general, it’s 2-3 times faster, which is quite a time saving, especially with a large database, given how simple the improvement is.

5. Handle complex data migrations in temporary rake tasks

By definition, Rails migrations should only be used for schema changes and not for actual data changes in the database. It might be a good idea to handle complex data migrations in temporary rake tasks. Given that, we decouple our data changes from the deployment. Furthermore, data migrations don’t follow business logic so shouldn’t stay forever in the codebase.

That said, it’s not the worst idea to add a little data change after adding a new column, especially if you stick to tip #1 from this article or use the Database migration tips in Rails. Part 2 - Strong migrations. It shouldn’t cause you any trouble and there are also cool gems like after_party and maitenence_task that you might find helpful.

I consider the code below fine as well, as long as we keep in mind tip #1 from this article, and we know that migration won’t take too much time:

6. Get familiar with Active Record

When dealing with large datasets, be aware of what SQL query Active Record generates under the hood. You can run #to_sql on the Active Record query to do so. It may turn out that your query is not performant enough, and even though it was fine on the dev environment, it takes too much time to be executed on production.

Here’s a short list of Active Record tips that might help you with trying to speed up your query:

1. Use #find_each over #each - > It reduces memory consumption as it’s more efficient than looping through a collection because it doesn’t instantiate all the objects at once. Instead, it allows us to work with individual records.
2. Use #update_all/#delete_all instead of #update/#delete -> It will update/delete all records with one SQL query which is far more performant than doing it one by one.
3. Use #insert_all/#upsert_all -> It inserts multiple records in a single SQL INSERT statement without instantiating models nor triggering ActiveRecord’s callbacks.
4. Watch out for #destroy/#destroy_all -> Remember that destroying might be an expensive operation as it instantiates, executes callbacks, and deletes each record.

Summary

Migrations are essential in database applications. Some frameworks, like Ruby on Rails, will do a lot for us under the hood, but we shouldn’t forget how important migrations are. As such, it’s worth still giving them plenty of attention.

Hopefully, these tips will prove helpful and easy to apply to your project, but we’re not done yet! In part two we’ll take a look at strong migrations and see how we can make our migrations even stronger!