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

  • What is the proper 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?

Some people will tell you that changing data with a database migration is an anti-pattern and migrations should only contain schema changes.

But the whole point of a migration is this: 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, you should be careful about how you update the data, as this can lead to downtime and big problems.

But if your app is still relatively new and small, then data migrations are a great way to do it! They are simple and straightforward. Here’s where you should start:

1. Schema changes and data changes are like water and oil, they shouldn’t 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.

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:

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

3. Strong Migrations (optional)

Consider adding the strong_migrations gem to your project and following the suggestions as much as possible. This 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.