I’m missing something and I hope someone can point it out to me. Long story short, the first migration in my app needs to slurp up a dump file, e.g. init.sql. It sets does stuff like SET statement_timeout = 0 and CREATE SCHEMA core etc. (There are some historical reasons why we are using this).

Two things made this tricky:

1. The Ecto repo didn’t use the normal name.
2. The app that controls the repo is sometimes used as a dependency.

But we figured this out. The trick was a migration file that could point to the init.sql file, both when the app was used as the primary app OR when it gets used as a dependency. It boiled down to this (comments left in for transparency):

defmodule MyApp.PGRepo.Migrations.InitDb do
  use Ecto.Migration

  require Logger

  @doc """
  This is a "Mixless" way of running a custom migration.

  This migration must be flexible enough so we can run it from inside `my_app`
  or inside some other app that uses `my_app` as a dependency. We rely on
  `Ecto.Migration.repo/0` to tell us which repo is triggering the migration, and
  we use `:code.priv_dir/1` to infer the environment.
  """
  def change do
    # This magically knows which Repo is running the migrations
    # (it is important that we do not hard-code `MyApp.Repo`)
    repo = Ecto.Migration.repo()

    # [`c:Ecto.Adapter.Structure.structure_load/2`](https://hexdocs.pm/ecto_sql/Ecto.Adapter.Structure.html#c:structure_load/2)
    # REQUIRES the configuration to be broken out with individual keys
    # for username, password, etc.
    x = repo.__adapter__().structure_load(repo_path(), config(repo))
    Logger.debug(inspect(x))
  end

  # This migration must be flexible enough so we can run it from inside this app
  # or inside some other app that uses this as a dependency.
  defp repo_path do
    "#{:code.priv_dir(:my_app)}/pg_repo"
  end

  # This fetches the repo config and overrides the `:dump_path` to point to
  # the sql dump used to define the initial structure of the database.
  #
  # Do NOT use `Application.get_env(:my_app, Ecto.Migration.repo())`
  # because it will NOT parse out individual settings from a db URL!
  # Instead, use the [`c:Ecto.Repo.config/0`](https://hexdocs.pm/ecto/3.5.2/Ecto.Repo.html#c:config/0)
  # because this DOES parse out a db URL into its component parts (`:username`, `:password`, etc.).
  defp config(repo) do
    repo.config()
    |> Keyword.put(:dump_path, "#{repo_path()}/migrations/init.sql")
  end
end

The migrations get executed when we run tests on a PR on Github via Github actions. The github workflow is pretty straightforward, it contains (in part):

      - name: Compile
        run: mix compile

      - name: Create PostGres database
        run: mix ecto.create

      - name: Migrate PostGres database
        run: mix ecto.migrate

This has all been working fine.

Until… I copied this structure over to a new repo. And suddenly the init.sql file is not found when the Github workflow executes on the new repo. I added an inspection to the problematic line:

    x = repo.__adapter__().structure_load(repo_path(), config(repo))
    Logger.debug(inspect(x))

When I run tests etc. locally, it works fine. On Github, however, I get an error because the external file is not found:

_build/test/lib/my_app/priv/pg_repo/migrations/init.sql NOT FOUND

I’m not seeing anything between the 2 repos that would explain why the old app finds its init.sql file but the new app does not. Does anyone have any ideas of why the .sql file isn’t being found when the tests run externally in Github?

And, just to be thorough, I tried rewriting this first migration WITHOUT this weird .sql file, with something like this:

def change do
  # ...
  execute("CREATE SCHEMA core")
end

But this fails with a different error:

** (Postgrex.Error) ERROR 42P01 (undefined_table) relation "schema_migrations" does not exist

These are 2 different errors… I need to solve one of them to make migrations work (I don’t really care which one shakes out).

Thanks for any ideas!