jmurphyweb

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)
  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.

3 comments

/phoenix #ecto #migrations

0 2211 3

2023-02-01 15:47:10 UTC

Marked As Solved

hubertlepicki

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 (Ecto’s insert_all and schemaless queries « Plataformatec Blog)

Post #1

Last Post!

kirega

Thanks so much for the link on how to perform schemaless queries, it worked like a charm.
To add more context to your answer, according to the blog post, @jmurphyweb should probably do something like this instead.

  def up do
    offer_query = from(o in "offer", where: is_nil(o.regular_site_id), select: [id: o.id])
    ...
  end

in the select be as specific as possible for the fields you need.
Hopefully, this will make it easier for anyone else down the line. Also here is a more up-to-date link to the same