Working with raw SQL in the Elixir/Ecto world is not easy. I thought executing a multi-line SQL file in a database migration would be easy; it’s not. Here’s what I learned:

The execute function does not support executing multiple statements at once. It’s perplexing that there’s no direct solution for this in Elixir.

While AyeSQL claims raw SQL support, it mandates a one-statement-at-a-time approach. This limitation is particularly problematic when importing SQL designed to create triggers spanning multiple lines.

For simple SQL tasks, you might consider splitting the raw SQL into multiple lines:

File.read!("priv/repo/audit_trigger.sql")
    |> String.split("\n")
    |> Enum.map(&String.trim_trailing/1)
    |> Enum.join("\n")
    |> String.split(~r/;\s*$/)
    |> Enum.each(&execute/1)

But only if your SQL is well-formed and relatively simple. If you are attempting to setup a complex Postgres Trigger like the open source audit trigger this will fail.

The only workaround I could come up with is to pass the SQL file to the psql CLI. I published a package to do just this:

defmodule App.Repo.Migrations.AddAuditTrigger do
  use Ecto.Migration

  def up do
    abs_path = Path.expand("priv/repo/audit_trigger.sql")
    database_url = Application.fetch_env!(:app, App.Repo)[:url]

    {:ok, result} =
      PostgresExecutor.execute_sql_file(database_url, abs_path)

    IO.puts(result)
  end
end