Rails database migrations best practices

Have you ever found yourself wondering how to best manage your database migrations in Rails? Migrations are a powerful tool for evolving your database schema, but without proper practices, they can become difficult to manage and even lead to inconsistencies between environments. In this post, we’ll cover essential strategies for keeping your migrations organized, efficient, and in sync across development, staging, and production, helping you avoid common pitfalls and maintain a clean, up-to-date database.

Meant to be Deleted

Database migrations are designed to be temporary. They serve as a bridge to implement incremental changes but aren’t meant to stick around indefinitely. Once they’re run, particularly in a production environment, they’ve done their job. The schema file then becomes the definitive source of truth for the database structure, as it reflects the database’s current state after all migrations have been applied.

Migrations, especially in production or staging, don’t need to be executed repeatedly. After being run, their purpose is fulfilled (unless you need a rollback). In development or test environments, we can reload the database schema with bin/rails db:schema:load to stay in sync, rather than relying on a long chain of migrations.

There’s an argument for keeping migration files for historical context, as they can show how the database evolved over time. In these cases, archiving older migrations might be a cleaner approach than just deleting them.

(Simon Chiu opens a new window ) provided this code snippit:

# lib/tasks/migration_archive.rake
namespace :db do
  namespace :migrate do
    desc 'Archives old DB migration files'
    task :archive do
      sh 'mkdir -p db/migrate/archive'
      sh 'mv db/migrate/*.rb db/migrate/archive'
    end
  end
end

Which allows you archive migrations by running:

bin/rails db:migrate:archive

Data Migrations

The best approach to handling data migrations depends on the team and the scale of the application. Some teams find it more efficient to include data migrations in migration files, as this can streamline the process. However, others prefer to use rake tasks or standalone scripts for data migrations. This keeps schema migrations focused solely on changes to the database structure. Ultimately, the choice should align with the team’s workflow and the needs of the application.

Reversible migrations

Occasionally, you may need to roll back migrations in production. To ensure this process goes smoothly, it’s essential to write migrations with reversibility in mind. This typically means using the change method instead of separate up and down methods, as change can often handle reversals automatically.

A helpful practice is to run bin/rails db:migrate:redo when adding new migrations, allowing you to confirm that they can be rolled back without issues. Writing reversible migrations not only prepares you for rollbacks but also encourages better migration practices overall.

Keep Your Local Database in Sync with Production

Your local database structure should always match the production database as closely as possible. This alignment is crucial because it ensures that when we create or reset a database (especially locally), the structure matches production exactly. By relying on the schema.rb file as the source of truth, we can confidently make changes to the application, knowing how they will impact production.

To maintain this consistency, everyone on the team should commit the updated schema file whenever they add new migrations. This discipline keeps the schema file an accurate reflection of the production database, enabling smooth collaboration and preventing drift between environments.

Whenever you pull changes from the upstream repository, it’s good practice to run bin/rails db:migrate. This command applies any new migrations and automatically updates the schema.rb file. Because our team ensures the schema file is always up-to-date and committed in source control, you shouldn’t encounter structural changes after running db:migrate. This approach keeps the database structure consistent across all environments and ensures a smooth development workflow.

…

db:prepare

Introduced in Rails 6.1, the bin/rails db:prepare command is the ideal choice for setting up your local database environment. This command creates the database, loads the schema, and runs seeds if the database does not exist. If the database already exists, it only runs the migrations and seeds; setting up your local environment in one go. You can see exactly what this task does by reviewing the database.rake source code opens a new window . Interestingly, the default bin/setup script provided by Rails also uses bin/rails db:prepare to set up the database (source opens a new window ).

It’s a good practice to occasionally drop your local database and run bin/rails db:prepare again. This serves as a smoke test to ensure the schema file is accurate and confirms that your database can be rebuilt smoothly from scratch.

A similar command is bin/rails db:setup, which also creates the database, loads the schema, and seeds it. However, the key difference is that db:setup doesn’t run migrations, making it particularly useful when setting up a database for the first time. You can review the source code opens a new window to see how db:setup and db:prepare differ.

db:schema:load

In CI, development, or test environments, running bin/rails db:schema:load is generally much faster than bin/rails db:migrate, especially in projects with a large number of migrations. Since db:schema:load directly loads the current schema, it bypasses the need to process each migration file individually, saving time.

db:schema:dump

Every time we migrate the database, Rails automatically performs a schema dump to ensure that the schema file stays up-to-date with the current state of the database. This happens because bin/rails db:schema:dump is run right after bin/rails db:migrate (see the source code here opens a new window ).

db:migrate:status

To check if your database is up-to-date, you can use the command bin/rails db:migrate:status. This command provides the status of each migration, making it easy to identify any pending migrations.

For example, here’s an output from one of our open-source projects opens a new window . This shows that all migrations have been successfully run:

❯ bin/rails db:migrate:status
database: points-development

Status   Migration ID    Migration Name
--------------------------------------------------
  up     20180530174255  Devise create users
  up     20180605185712  Create projects
  up     20180622182249  Create stories
  up     20180622183315  Create estimates
  up     20180810181844  Add admin to users
  up     20180829192236  Add name to users
  up     20190327210527  Add columns to users
  up     20190730150431  Add position to stories
  up     20190926190608  Add real score to stories
  up     20210201161014  Add parent id to project
  up     20211209170501  Add position to project
  up     20220312201424  Add extra info to stories
  up     20220621141342  Add locked to projects
  up     20230829001347  Add status to stories
  up     20230830143908  Create version jumps
  up     20230831175732  Add version jump to projects
  up     20230908142819  Create comments

In contrast, here’s what the output looks like when one of the migration files is deleted:

Status   Migration ID    Migration Name
--------------------------------------------------
  up     20180530174255  Devise create users
  up     20180605185712  Create projects
  up     20180622182249  Create stories
  up     20180622183315  Create estimates
  up     20180810181844  Add admin to users
  up     20180829192236  Add name to users
  up     20190327210527  Add columns to users
  up     20190730150431  Add position to stories
  up     20190926190608  Add real score to stories
  up     20210201161014  Add parent id to project
  up     20211209170501  Add position to project
  up     20220312201424  Add extra info to stories
  up     20220621141342  Add locked to projects
  up     20230829001347  Add status to stories
  up     20230830143908  Create version jumps
  up     20230831175732  Add version jump to projects
  up     20230908142819  ********** NO FILE **********

Another way to determine which migrations have been executed is by querying the schema_migrations table using dbconsole. Rails uses this table internally to track completed migrations. Any migration not listed in the schema_migrations table is considered pending and will be executed when running migrations.

Below is an example of the output from the points-development database when running the query:

SELECT * FROM schema_migrations;

❯ bin/rails dbconsole

points-development=# select * from schema_migrations;
    version
----------------
 20230908142819
 20230831175732
 20230830143908
 20230829001347
 20220621141342
 20220312201424
 20211209170501
 20210201161014
 20190926190608
 20190730150431
 20190327210527
 20180829192236
 20180810181844
 20180622183315
 20180622182249
 20180605185712
 20180530174255
(17 rows)

structure.sql

The only reason to switch to structure.sql is if you’re using database features that can’t be represented by the schema.rb DSL. For most use cases, schema.rb should be sufficient, but structure.sql may be necessary if you need to capture more complex database structures or features.

NOTE: Example is PostGIS, but there are many other examples but these are an extra dependency that you need to manage

Team Agreements

1. Commit the Schema File: Agree to always commit the schema file whenever new migrations are added.
2. Archive or Delete Old Migrations: Agree to delete or archive migrations that have been in production for more than a month.
3. Keep Local Databases in Sync: Agree to periodically drop local databases and run bin/rails db:prepare` to ensure the local database remains as close to the production database as possible.
4. Write Reversible Migrations: Agree to write reversible migrations whenever possible. (NOTE: Or decide on a migration stategy when things don’t go as planned in production)

Conclusion

In summary, maintaining a clean and consistent database migration process is key to ensuring smooth development and production environments. By following best practices such as keeping migrations reversible, committing the schema file regularly, and keeping local and production databases in sync, teams can avoid common pitfalls and minimize technical debt. Periodically cleaning up old migrations and using commands like bin/rails db:schema:load and rails db:migrate:status help ensure the database schema is always in top shape. Adhering to these agreements not only streamlines the development process but also sets the stage for a more manageable and efficient codebase in the long run.

Need help with your Rails application? Talk to us today! opens a new window