Old migrations error with updated/current schema

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)

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))
    |> Repo.all()
    |> update_each_offers_regular_site()

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

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)

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

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

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.

This is your problem right here. You are using piece of your application code, i.e. schema that has added new fields in it, and expect them to be present in database. This works once, in the time you write the new migration, but will fail likely later wien Offer schema is changed in your application, and no longer matches whatever the migration is expecting to find there.

The rule of thumb I am applying is: only use migration DSL and “execute” statements in migrations. If you have to alter some data in database just write:

execute "UPDATE .... " 

with hand-written SQL instead of using your Ecto schemas.

Possibly you could also use Ecto.DSL if you really want here, with “shemaless query” but I did never attempt this (http://blog.plataformatec.com.br/2016/05/ectos-insert_all-and-schemaless-queries/)

1 Like

Great ok so just need to be more disciplined with using SQL in our migrations. Thanks!