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.