You need to change production data. You know that doing it manually is unsafe and will definetely cause lots of trouble, like corrupting all you customer’s data.

How else should you do it?

You might be spending a lot of time asking yourself these questions:

  • Is there a proper rails way to change data in production?
  • Should I run data and schema modifications in one database migration?
  • Should I run rake scripts in production to modify data?
  • Should I use a specialized data migration gem?

You will get different answers depending on whom you ask. Some people will tell you that changing data with a database migration is an anti-pattern and migrations should only contain schema changes.

But isn’t the whole point of a migration to be a good way to define a point-in-time transition of database structure or data, using code?

If you have a larger application or a mission-critical system, be careful with production data updates. This can lead to downtime and big problems. This post won’t cover this scenario.

However, if your app is still relatively new and small, this post is for you. Here’s how you can use data migrations to change production data without shooting yourself in the foot.

1. Schema changes and data changes are like water and oil: they do not hangout together

Write data manipulation code in separate Rails migrations and give them proper names. NEVER change schema and data in the same migration.

Another tip: consider separating complicated migrations and code changes in multiple pull requests you can merge and deploy separately. That way you can incrementally change the database and catch errors or rollback more easily.

2. Stick to plain old SQL, if you can

Careful when using models in migrations. It’s better to just write data manipulation as Arel or plain SQL queries and not use models at all, because if you change the model, your old migrations might break.

SQL is independent from ActiveRecord model’s definitions, queries won’t trigger callbacks, and most importantly: any future code changes to a model won’t cause you a world of pain.

Well-written SQL queries can be really fast. Just be careful and make sure you’re updating the right things.

Always ask yourself: Am I missing a where statement here somewhere?

See this nice example from Discourse of a simple data migration:

class MigrateAtDesktopBookmarkReminders < ActiveRecord::Migration[6.0]
  def up
    DB.exec(
      <<~SQL, now: Time.zone.now
        UPDATE bookmarks SET reminder_type = NULL, reminder_at = NULL, updated_at = :now
        WHERE reminder_type = 0
      SQL
    )
  end

  def down
    # add some code to revert this migration, if possible
  end
end

3. Strong Migrations (optional)

Consider adding the strong_migrations gem to your project and following its suggestions as much as possible. These guidelines will help you catch unsafe migration code during development and teach you some good practices.

Pay special attention to the warnings related to adding indexes and default values to existing columns, as this can cause downtime.

Doing these things will put you in a good spot and make your life easier in the future.