How to perform Data Migrations aligned with regular Schema Migrations

Can you explain the benefit from that? To me this sounds like it’s just waiting for some inconsistency between those two to happen.

In two years you can get many migrations with outdated code and you need to fix them from time to time(module removed or renamed). It’s hard to do If you mix schema changes and data changes

with separate file for data migration you can get some control
e.g. run only schema migrations on local machine (dev, test env)
disable specific data migration and skip evaluation.

it’s all about control, testability and managebility.

Usually data migration have short life time

1. create it
2. write tests
3. release to all envs
4. comment or remove it somehow

Ok, I don’t have such problems because I migrate data using plain sql, therefore I can keep them around without problems.

I’d highly encourage writing these kinds of transformations as pure modules and functions that wrap straightforward Ecto calls, i.e. Repo.update_all. This approach lends itself well to testing, repeatable execution, and will look familiar to developers as they’re essentially specialized context modules. Tying them to the mindset of migrations that can be “checked off” as completed and don’t need to happen again doesn’t often mesh with my experience in reality. More often I see situations where it takes several passes through the data with the same intent to fully clean it in-place before a stricter DDL design can be applied. You can still drop the modules and their tests from your codebase once the need is conclusively finished, but you won’t need any acrobatics to repeat the effort again.

That’s also why I generally recommend that traditional Ecto migrations only contain DDL statements and no updates or insertions if at all avoidable.

I even skip anything MyApp.Repo, because it means :my_app would need to be started to be able to run migrations. If I really don’t know the sql I do MyApp.Repo.to_sql/2 and copy it in the migration, where it’s run using execute(sql) or execute(up, down).

True, but for one-off, deterministic and simple conversions from an old column to a new column Ecto migratory updating code works okay even months in the future.

Agreed with everything else.

I understand your point.
It’s mostly linked to migrations structure and testability

On previous rails project we use similar approach class which contain everything and it’s easy to test it.
Data migration was kind of humble object to run Migrator class with all logic

let me show rails example with data migration file

# cat db/data/20190814115720_remove_redundant_records.rb
class RemoveRedundantRecords < ActiveRecord::Migration
  def self.up
    DataMigrationJob.perform_sync Migrators::OldNotificationsDestroyer.name, 0
  end

  def self.down
  end
end

You can test Migrator like regular class and run it during db:migrate

Also to turn off or remove migration it should be executed everywhere and after certain period e.g. 1 year it can be disabled/removed.

One thing that saved the sanity of me and my colleagues both in Ruby on Rails and Elixir/Phoenix projects was to periodically squish all migrations into a singular .sql file with the contemporary schema and then just “restart” migrations (i.e. have zero of them after the squish).

At one point you can absolutely feel how maintaining the old migration scripts and making sure every new onboarded dev goes through them without errors is just not worth it.

But I still don’t recommend the squish / pruning to take place before 6 months (or 50-100 migrations).

A really nice compromise on this idea is Ecto.Migrator.with_repo/2 which was introduced in the 3.x series. You won’t have your full OTP app running, but you can start a skeleton Ecto Repo with minimal connections for the duration of your DB interactions, which don’t have to be traditional migrations. You can readily use Repo calls or context modules, as long as the connection count is set appropriately for your needs. We use this in Distillery custom commands, seeds, etc. to avoid having the whole supervision tree start up just to do some INSERTs.

Something fairly similar is built-in as mix ecto.dump and mix ecto.load, and doesn’t require you to fully discard the previous migration code content. Instead it just loads a structure snapshot and then marks previous migrations up to that point as applied, and they can still be replayed from-scratch using the normal ecto.migrate to ensure continuity for environments that aren’t able to use this approach, like your Production database. I don’t personally endorse fully squashing or discarding the historical content ourselves when you can get all of the same speed improvements using built-in functionality and still have a great escape hatch when needed.

This is part of the disconnect, perhaps, because I’m advocating for treating data-munging tasks as operationally and conceptually separate from schema evolution, and considering that a virtue rather than an inconvenience.