Ecto: run sql script before or after each migration

Question

I would like to create(if not exists) partition tables for each month for some time in the future:

execute """
CREATE TABLE IF NOT EXISTS #{table}_p#{start_date.year}_#{month}
PARTITION OF #{table} FOR VALUES
FROM ('#{start_date}')
TO ('#{stop_date}')
"""

I run it dynamically, eg: for next 12 month starting from today. I would like to make it during migration, but run it each time migration starts. I can't use Repo since in time of the migration it's not yet started. I didn't find any ability to do it with Ecto.Migration api.

Do you have any ideas how to achieve this?

Could you put this into your own mix task and then create an alias inside of mix.exs so that running mix ecto.migrate instead ran your task first and then the ecto.migrate? You may need to start the repo process yourself. — Everett

Rudziankoŭ Rudziankoŭ · Accepted Answer · 2021-05-13T13:40:17

I made it with support of feature added in ecto 3.5.0. I added separate migration folder wich is used for so called 'repeated_migrations'.

  @doc """
  repeated migrations are run each deploy(e.g: ensure partitions). Repetition achieved by down method that does nothing.
  """
  def run_repeated_migrations do
    for repo <- repos() do
      path = Ecto.Migrator.migrations_path(repo, "repeated_migrations")
      {:ok, _, _} = Ecto.Migrator.with_repo(repo, &Ecto.Migrator.run(&1, path, :up, all: true))
      {:ok, _, _} = Ecto.Migrator.with_repo(repo, &Ecto.Migrator.run(&1, path, :down, all: true))
    end
  end

method in repeated_migrations/20210427134701_partition_creation.exs

  def down do
    # to make this migration repeatable we run 'up' following by 'down'. Down do nothing.
    execute "SELECT 1"
  end

Ecto: run sql script before or after each migration

1 Answers