Ordering migrations across Ecto repos.

[Ruslan Doga]

👋

I wonder how people here handle implicit dependencies between migrations across Ecto repos. Say, we have two repos, RepoA and RepoB, and we:

- add a table to RepoA db

- add a column to RepoB table that references the new RepoA table

- add a migration to RepoA that reads from that new column in RepoB

When we run them one by one, e.g. during development, it all works fine. But when they run all at once, using either ecto.migrate Mix task or the App.Release.migrate examples from Ecto.Migrator docs, it fails since the migrations run per repo in the following order:

- add a table to RepoA db

- add a migration to RepoA that reads from that new column in RepoB <- fails

- add a column to RepoB table that references the new RepoA table

Is there an idiomatic way to "interweave" and "sort" all migrations across repos, and then migrate them in a single queue? I started working on a PR in a project that has that problem but it feels like a hack so far: https://github.com/plausible/analytics/pull/4466

[Ruslan Doga]

I think I'll end up going with this:

  def interweave_migrate do
    # interweave
    all_pending =
      Enum.flat_map(repos(), fn repo ->
        Ecto.Migrator.migrations(repo)
        |> Enum.filter(fn {status, _version, _name} -> status == :down end)
        |> Enum.map(fn {_status, version, _name} -> {repo, version} end)
      end)

    # sort
    all_sorted = Enum.sort_by(all_pending, fn {_repo, version} -> version end, :asc)

    # group into streaks
    streaks = migration_streaks(all_sorted)

    # migrate the streaks
    Enum.each(streaks, fn {repo, version} ->
      {:ok, _, _} = Ecto.Migrator.with_repo(repo, &Ecto.Migrator.run(&1, :up, to: version))
    end)
  end

Unlike normal migrate/0 this function:
   - lists all pending migrations across repos,
   - sorts them into a single list,
   - groups consequent migration into "streaks" by repo,
   - migrates each streak consequently

  For examples, assuming we have the following migrations across two repos:

      priv/repo/migrations/
      - 20230530161856_add_enable_feature_fields_for_site.exs
      - 20230724131709_change_allowed_event_props_type.exs
      - 20230802081520_cascade_delete_user.exs
      - 20230914071244_fix_broken_goals.exs
      - 20230914071245_goals_unique.exs
      - 20230925072840_plugins_api_tokens.exs
      - 20231003081927_add_user_previous_email.exs
      - 20231010074900_add_unique_index_on_site_memberships_site_id_when_owner.exs
      - 20231011101825_add_email_activation_codes.exs
      - 20231018081657_add_last_used_at_to_plugins_api_tokens.exs
      - 20231109090334_add_site_user_preferences.exs
      - 20231115131025_add_limits_to_enterprise_plans.exs
      - 20231115140646_add_totp_user_fields_and_recovery_codes.exs
      - 20231121131602_create_plans_table.exs
      - 20231127132321_remove_custom_domains.exs
      - 20231129103158_add_allow_next_upgrade_override_to_users.exs
      - 20231129161022_add_totp_token_to_users.exs
      - 20231204151831_backfill_last_bill_date_to_subscriptions.exs
      - 20231208125624_add_data_retention_in_years_to_plans.exs
      - 20231211092344_add_accept_traffic_until_to_sites.exs
      - 20231219083050_track_accept_traffic_until_notifcations.exs
      - 20231220072829_add_accept_traffic_until_to_user.exs
      - 20231220101920_backfill_accept_traffic_until.exs
      - 20240103090304_upgrade_oban_jobs_to_v12.exs
      - 20240123085318_add_ip_block_list_table.exs
      - 20240123095646_remove_google_analytics_imports_jobs.exs
      - 20240123144308_add_site_imports.exs
      - 20240129102900_migrate_accepted_traffic_until.exs
      - 20240129113531_backfill_accept_traffic_until_for_users_missing_notifications.exs
      - 20240214114158_add_legacy_flag_to_site_imports.exs
      - 20240220144655_cascade_delete_ip_rules.exs
      - 20240221122626_shield_country_rules.exs
      - 20240307083402_shield_page_rules.exs
      - 20240319094940_add_label_to_site_imports.exs
      - 20240407104659_shield_hostname_rules.exs
      - 20240528115149_migrate_site_imports.exs
      - 20240702055817_traffic_drop_notifications.exs
      - 20240708120453_create_help_scout_credentials.exs
      - 20240722143005_create_helpscout_mappings.exs
      - 20240801052902_add_goal_display_name.exs
      - 20240801052903_make_goal_display_names_unique.exs
      - 20240809100853_turn_google_auth_tokens_into_text.exs

      priv/ingest_repo/migrations/
      - 20231017073642_disable_deduplication_window_for_imports.exs
      - 20240123142959_add_import_id_to_imported_tables.exs
      - 20240209085338_minmax_index_session_timestamp.exs
      - 20240220123656_create_sessions_events_compression_options.exs
      - 20240222082911_sessions_v2_versioned_collapsing_merge_tree.exs
      - 20240305085310_events_sessions_columns_improved.exs
      - 20240326134840_add_metrics_to_imported_tables.exs
      - 20240327085855_hostnames_in_sessions.exs
      - 20240419133926_add_active_visitors_to_imported_pages.exs
      - 20240423094014_add_imported_custom_events.exs
      - 20240502115822_alias_api_prop_names.exs
      - 20240709181437_populate_location_data.exs

  The migrations would happen in the following order:

      priv/repo/migrations/
      - 20230530161856_add_enable_feature_fields_for_site.exs
      - 20230724131709_change_allowed_event_props_type.exs
      - 20230802081520_cascade_delete_user.exs
      - 20230914071244_fix_broken_goals.exs
      - 20230914071245_goals_unique.exs
      - 20230925072840_plugins_api_tokens.exs
      - 20231003081927_add_user_previous_email.exs
      - 20231010074900_add_unique_index_on_site_memberships_site_id_when_owner.exs
      - 20231011101825_add_email_activation_codes.exs

      priv/ingest_repo/migrations/
      - 20231017073642_disable_deduplication_window_for_imports.exs

      priv/repo/migrations/
      - 20231018081657_add_last_used_at_to_plugins_api_tokens.exs
      - 20231109090334_add_site_user_preferences.exs
      - 20231115131025_add_limits_to_enterprise_plans.exs
      - 20231115140646_add_totp_user_fields_and_recovery_codes.exs
      - 20231121131602_create_plans_table.exs
      - 20231127132321_remove_custom_domains.exs
      - 20231129103158_add_allow_next_upgrade_override_to_users.exs
      - 20231129161022_add_totp_token_to_users.exs
      - 20231204151831_backfill_last_bill_date_to_subscriptions.exs
      - 20231208125624_add_data_retention_in_years_to_plans.exs
      - 20231211092344_add_accept_traffic_until_to_sites.exs
      - 20231219083050_track_accept_traffic_until_notifcations.exs
      - 20231220072829_add_accept_traffic_until_to_user.exs
      - 20231220101920_backfill_accept_traffic_until.exs
      - 20240103090304_upgrade_oban_jobs_to_v12.exs
      - 20240123085318_add_ip_block_list_table.exs
      - 20240123095646_remove_google_analytics_imports_jobs.exs

      priv/ingest_repo/migrations/
      - 20240123142959_add_import_id_to_imported_tables.exs

      priv/repo/migrations/
      - 20240123144308_add_site_imports.exs
      - 20240129102900_migrate_accepted_traffic_until.exs
      - 20240129113531_backfill_accept_traffic_until_for_users_missing_notifications.exs

      priv/ingest_repo/migrations/
      - 20240209085338_minmax_index_session_timestamp.exs

      priv/repo/migrations/
      - 20240214114158_add_legacy_flag_to_site_imports.exs

      priv/ingest_repo/migrations/
      - 20240220123656_create_sessions_events_compression_options.exs

      priv/repo/migrations/
      - 20240220144655_cascade_delete_ip_rules.exs
      - 20240221122626_shield_country_rules.exs

      priv/ingest_repo/migrations/
      - 20240222082911_sessions_v2_versioned_collapsing_merge_tree.exs
      - 20240305085310_events_sessions_columns_improved.exs

      priv/repo/migrations/
      - 20240307083402_shield_page_rules.exs
      - 20240319094940_add_label_to_site_imports.exs

      priv/ingest_repo/migrations/
      - 20240326134840_add_metrics_to_imported_tables.exs
      - 20240327085855_hostnames_in_sessions.exs

      priv/repo/migrations/
      - 20240407104659_shield_hostname_rules.exs

      priv/ingest_repo/migrations/
      - 20240419133926_add_active_visitors_to_imported_pages.exs
      - 20240423094014_add_imported_custom_events.exs
      - 20240502115822_alias_api_prop_names.exs

      priv/repo/migrations/
      - 20240528115149_migrate_site_imports.exs
      - 20240702055817_traffic_drop_notifications.exs
      - 20240708120453_create_help_scout_credentials.exs

      priv/ingest_repo/migrations/
      - 20240709181437_populate_location_data.exs

      priv/repo/migrations/
      - 20240722143005_create_helpscout_mappings.exs
      - 20240801052902_add_goal_display_name.exs
      - 20240801052903_make_goal_display_names_unique.exs
      - 20240809100853_turn_google_auth_tokens_into_text.exs

[José Valim]

I believe Ecto SQL tasks support passing several --migrations-path and those paths are merged into an overall order, instead of individually. So if you can pass those paths by default  (by using mix.exs aliases or similar), it should work. WDYT?

--
You received this message because you are subscribed to the Google Groups "elixir-ecto" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elixir-ecto...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/eeb052d8-1ebb-4944-a05d-c96bb67b1e15n%40googlegroups.com.

[Ruslan Doga]

I looked into that but in the end Ecto.Migrator.run seems to be scoped to a single Ecto.Repo: https://github.com/elixir-ecto/ecto_sql/blob/41ed13e754fcf7449e03b348f612b6a060c2a1cd/lib/ecto/migrator.ex#L428

But I have't tried doing something like this:

```

Ecto.Migrator.run(Plausible.Repo, [Ecto.Migrator.migrations_path(Plausible.Repo), Ecto.Migrator.migrations_path(Plausible.IngestRepo)], :up, :all)

```

[Allen Madsen]

I'd say it's pretty uncommon to have migrations in separate apps that maintain the same database. If you can't pick an owner between RepoA and RepoB to fully own migrations, then maybe setup RepoC to own them for both.

Allen Madsen
http://www.allenmadsen.com

--

You received this message because you are subscribed to the Google Groups "elixir-ecto" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elixir-ecto...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/300e86ee-c05d-49f6-a9f1-5540659a67a1n%40googlegroups.com.

[Ruslan Doga]

The databases are different. One is in PostgreSQL and one in ClickHouse.

On Aug 28, 2024, at 03:04, Allen Madsen <allen.c...@gmail.com> wrote:



To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/CAK-y3Cv0GFZE6oSuOQqHiBLr8oO3mLkde5qcfuc1ZuPH9b-jag%40mail.gmail.com.

[Allen Madsen]

Ah, I misunderstood. Thought you were talking about two git repos.

Allen Madsen
http://www.allenmadsen.com

To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/EACEEFAE-FFB4-46D0-9436-392F830DC9C0%40gmail.com.