To do that with Ash the way it generally works is that you can set a context in your action to set (or override the default) repo. For example:

read :read do
  prepare fn query, _ -> 
    repo = figure_out_repo()
    Ash.Query.set_context(query, %{data_layer: %{repo: repo}})
  end
end

You can abstract this using a module-backed preparation, and attach it to a resource using the global preparations block in the resource, to trigger it on all queries:

preparations do
  prepare SetDynamicRepo
end

Not necessarily a full breakdown, but that should give you a starting point

It seems that preparations are for read actions only. Is there something more generic that would allow the correct repo to be used for actions that write to the database?

In our preparation we have tried to use the process-scoped setting Ecto.Repo.put_dynamic_repo/1 (this works from IEx such that MyApp.DynamicRepo.query!(some_sql) is sent to the correct database) however queries generated by Ash give a permission denied error. It looks like Ash is running the query in a different process to that which ran the preparation with put_dynamic_repo.

Is there some way to intercept a query in the process that runs it, in a way that applies to all action types, not just read? If I could put_dynamic_repo there I think it would work.

So, there are two relevant answers here.

Changes are the equivalent of preparations for other action types

Preparations are for read actions only, but changes can do the same thing for all other action types.

changes do
  change SetDynamicRepo
end

Keeping process context

For this, you need to add an Ash.Tracer. Ash.Tracer can “move” process context from one process to any process that Ash starts.

defmodule YourApp.DynamicRepoTracer do
  use Ash.Tracer

  def get_process_context() do
     get_dynamic_repo()
  end

  def set_process_context(repo) do
    set_dynamic_repo(repo)
  end
end

And then you can configure the tracer statically:

config :ash, tracer: [YourApp.DynamicRepoTracer]

YMMV on using put_dynamic_repo, but will be interested to hear how it goes if you go that route.

Thanks for the advice @zachdaniel, it didn’t pan out that way but we did find something that works.

Ash.Tracer has get_span_context/0 and set_span_context/1 however the problem remained of finding a nice way to execute custom code to fetch the desired repo from the span context in the querying process.

I also looked into supplying a function instead of a module name to the repo declaration in the postgres block of our resources, however that function only receives the resource and the operation type, not the queryable which is what we need to decide which repo to use.

My colleague wrote a module that implements various functions from using Ecto.Repo, and in each one checks for the data emplaced by our preparation, uses that to get the right repo and passes it on to Ecto.Repo.Queryable.

I wasn’t comfortable with that approach but unable to find another way, settled on something similar. I wrote a wrapper module that walks the module_info(:exports) for the target module in the __using__ macro, wrapping every user function in the target module sot that if the first argument has the repo identifier within it, the repo is obtained and put_dynamic_repo/1 called with it. Then the original function is called unconditionally.

This late binding of the indicated repo to the dictionary of the process calling the repo functions is the only thing we’ve found that works.

This definitely sounds unideal and I feel confident we could find a way to do this better. Would it be at all possible for you to make a small example repo that has one or two resources and has the same dynamic repo set up?

I’ll see what I can do, maybe on the weekend. For multi-tenancy in Ash there is the postgresql schema approach, but what we’re trying for is a different database per tenant (and so, each requires a different connection/process and the whole put_dynamic_repo/1 rabbit hole). I know database-per-tenant has its own trade-offs but we’re integrating with a pre-existing system so we’re stuck with it, for some resources, at least for now.

Thanks for your input and more importantly thanks for Ash!

Totally, makes sense. We ultimately want Ash to be able to support all of those models too, even though naturally database per tenant will be a bit more challenging than the alternatives. I could even see a case for building database-per-tenant multi tenancy into ash_postgres as a first class thing at some point.

Tragically the fix that I found was working in Ash v2.18.2 and Ash Postgres v1.4.0, but upgrading to v2.19.x and v1.5.x respectively broke it.

It seems that the refactoring done in Ash to improve performance and run queries async has lead to calls to in_transaction? being made when no arguments are present indicating which database to use.

I’ve created a git repository that doesn’t attempt this macrology to wrap the repo, and instead just tries calling put_dynamic_repo/1 from a preparation, which of course does not work:

Perfect, I will look at this this week, thanks.

BTW we’ve tried this in a preparation:
Ash.Query.set_context(query, %{data_layer: %{repo: dynamic_repo_pid}})

However it leads to ArgumentError 1st argument: not an atom from AshPostgres.DataLayer.run_query/2. I believe that this is because repo is expected to be an atom indicating a repo module, not a pid corresponding to a connection using a repo module.

[edit: that’s a non-starter but led on to a possible solution…]