I made MyApp.Repo module following Replicas and dynamic repositories — Ecto v3.8.4.

It works for functions in MyApp.Repo, but not works for custom queries.

defmodule MyApp.Repo do
  use Ecto.Repo,
    otp_app: :carrier,
    adapter: Ecto.Adapters.Postgres

  def with_dynamic_repo(credentials, callback) do
    default_dynamic_repo = get_dynamic_repo()
    start_opts = [name: nil, pool_size: 1] ++ credentials
    {:ok, repo} = __MODULE__.start_link(start_opts)

    try do
      put_dynamic_repo(repo)
      callback.()
    after
      put_dynamic_repo(default_dynamic_repo)
      Supervisor.stop(repo)
    end
  end
end

credentials = [
  hostname: "localhost",
  username: "customer",
  password: "password",
  port: 48141,
  database: "customer_db"
]

MyApp.Repo.with_dynamic_repo(credentials, fn ->
  # 'orders' table is in 'customer_db' table
  """
  SELECT * FROM orders LIMIT 10;
  """
  
  Ecto.Adapters.SQL.query!(MyApp.Repo, query, [])
end)

# relation "orders" does not exist

It works on below.

defmodule MyApp.Repo do
  use Ecto.Repo,
    otp_app: :carrier,
    adapter: Ecto.Adapters.Postgres

  def with_dynamic_repo(credentials, callback) do
    default_dynamic_repo = get_dynamic_repo()
    start_opts = [name: nil, pool_size: 1] ++ credentials
    {:ok, repo} = __MODULE__.start_link(start_opts)

    try do
      put_dynamic_repo(repo)
      callback.(repo)
    after
      put_dynamic_repo(default_dynamic_repo)
      Supervisor.stop(repo)
    end
  end
end

credentials = [
  hostname: "localhost",
  username: "customer",
  password: "password",
  port: 48141,
  database: "customer_db"
]

MyApp.Repo.with_dynamic_repo(credentials, fn repo ->
  # 'orders' table is in 'customer_db' table
  """
  SELECT * FROM orders LIMIT 10;
  """
  
  Ecto.Adapters.SQL.query!(repo, query, [])
end)

But with that, there is no meaning to using dynamic repo.
I don’t know how to use transaction with pid of repo.

Is it possible to query a custom query with dynamic repo?