I’ve run into this a couple of times and am wondering if I’m approaching data migrations wrong.

A week ago I had an Offer schema:

  schema "offers" do
    field(:regular_site_id, :id)
  end

I want to update offers with a regular_site_id, then add a NOT NULL constraint to the regular_site_id column:

  def up do
    offer_query = from(o in Offer, where: is_nil(o.regular_site_id))
    
    offer_query
    |> Repo.all()
    |> update_each_offers_regular_site()

    flush()
    
    alter table(:offers) do
      modify(:regular_site_id, :id, null: false)
    end
  end

This all runs fine, all environments now have run the above migration. But now I want to write a new migration:

  def up do
    alter table(:offers) do
      add(:something_new, :string, null: false)
    end
  end

Adding a field to the offer so the schema now looks like:

  schema "offers" do
    field(:regular_site_id, :id)
    field(:something_new, :string)
  end

Replicating our CI, which attempts to build out the database from scratch, if I run MIX_ENV=test mix ecto.reset I will get an error:

** (Postgrex.Error) ERROR 42703 (undefined_column): column o0.something_new does not exist
    (ecto) lib/ecto/adapters/sql.ex:431: Ecto.Adapters.SQL.execute_and_cache/7
    (ecto) lib/ecto/repo/queryable.ex:133: Ecto.Repo.Queryable.execute/5
    (ecto) lib/ecto/repo/queryable.ex:37: Ecto.Repo.Queryable.all/4
    priv/repo/migrations/20190406130008_add_regular_site_ids.exs:10: Ev2.Repo.Migrations.AddRegularSiteIds.up/0
    (stdlib) timer.erl:197: :timer.tc/3
    (ecto) lib/ecto/migration/runner.ex:25: Ecto.Migration.Runner.run/6
    (ecto) lib/ecto/migrator.ex:128: Ecto.Migrator.attempt/6
    (ecto) lib/ecto/migrator.ex:72: anonymous fn/4 in Ecto.Migrator.do_up/4

I can get around this by just commenting out the data migration part, which is ok because the migration has now run in all environments.

But it seems like this would be a fairly common issue, am I approaching the data migration wrong? The problem would also go away if I wrote the migration with pure SQL but that isn’t always possible.