Using "flush" in Ecto Migration

I’m having an issue running Ecto.migrate. An earlier migration file is querying for Team.all and failing because it’s trying to select a field (“type”) on team that does not exist yet - it’s added in a later migration.

From what I’ve read, “Ecto migrator is queueing up everything in our migration to be executed against the database,” and flush is the solution to this issue.

However, flush is not working for me, despite having tried it in several places. Any other recommendations would be greatly appreciated. Thanks!

Error Output

[info] == Running MyApp.Repo.Migrations.AddMissingSecondaryContacts.change/0 forward
[debug] QUERY ERROR source="team" db=0.2ms
SELECT t0.`id`, t0.`created_at`, t0.`updated_at`, t0.`deleted`, t0.`name`, t0.`class_name`, t0.`icon`, t0.`program_type`, t0.`gender`, t0.`primary_contact_is_coach`, t0.`parent_team_id`, t0.`type`, t0.`admin_selected_parent`, t0.`extra_data`, t0.`address_id`, t0.`primary_contact_id`, t0.`secondary_contact_id` FROM `team` AS t0 WHERE (t0.`deleted` = ?) [false]
** (Mariaex.Error) (1054): Unknown column 't0.type' in 'field list'
    (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/20170308201611_add_missing_secondary_contacts.exs:12: MyApp.Repo.Migrations.AddMissingSecondaryContacts.change/0
    (stdlib) timer.erl:197:
    (ecto) lib/ecto/migration/runner.ex:27:

Earlier migration selecting all teams and failing (see error above)

defmodule MyApp.Repo.Migrations.AddMissingSecondaryContacts do
  use Ecto.Migration

  alias MyApp.Team
  alias MyApp.Person
  alias MyApp.Person.Address

  def change do
    teams = Team.all # This line is failing

    |> Enum.filter(&(&1.primary_contact_id == nil || &1.address_id == nil || &1.secondary_contact_id == nil))
    |> -> 
        Team.changeset(team, %{
          primary_contact_id: team.primary_contact_id || empty_person().id,
          address_id: team.address_id || empty_address().id,
          secondary_contact_id: team.secondary_contact_id || empty_person().id
        |> Team.update!

  defp empty_person() do 
    %Person{address_id: empty_address().id}
      |> Person.changeset(%{})
      |> Person.insert!

  defp empty_address() do
    |> Address.changeset(%{})
    |> Address.insert!

Later migration adding field “type” to Team

defmodule MyApp.Repo.Migrations.AddTypeToTeam do
  use MyApp.Data, type: :migration

  def change do
    alter table(:team) do
      add :type, :string
   # Tried adding "flush()" here

That’s the problem then. Migrations are run in order and depend only on what has come before, not after. The type needs to be created in the database before it is used, either earlier in the same migration file, or in a prior migration.

And it’s not so much that ecto is queueing them up as they are run in transactions. :slight_smile:

Thus the question, what are the exact filenames for the given pieces of sourcecode?
As well as you should not use schema’s in migrations, at least not ‘future’ ones (you could version them of course). You should probably use schema-less queries instead in migrations. :slight_smile:

1 Like

Avoid using schema in the migration. Avoid even any functions outside of migration.

As your code (schema or any functions called from migration) may change later to be incompatible.

For example, the page you linked has this code:

  from(p in "posts",
    update: [set: [published_at: p.updated_at]],
    where: p.published)
  |> MyApp.Repo.update_all([])

And what is the benefit of using Ecto.Migration.flush/0? Why don’t you just create two separate migrations?

1 Like

Thank you @OvermindDL1!! The schema-less queries do the trick. If I’m understanding correctly, Team.all is reading from the schema block, which defines Team, but the column hasn’t been added to the database yet. Still new to Elixir & Ecto. I really appreciate your help and quick response!

schema "team" do
    field :name, :string
    field :gender, :string
    field :parent_team_id, :id
    field :type, :string
    belongs_to :address, Address
1 Like

To be honest, flush() wasn’t clear to me. I think I was headed in the wrong direction with that. Thanks for your response!

That is it precisely. :slight_smile:

Think of an Ecto Schema as a ‘View’ into the database. You can even defined different schemas for the same tables (or real sql views, or other things) for specific tasks, like if a certain module doesn’t need access to certain fields, make them up a new schema without those fields, even though the database actually does have them. In this case the schema was saying something existed when it did not yet actually exist. :slight_smile:

It’s not even a bad idea to keep versioned schema’s either in my opinion.

1 Like