So I somewhat got this sorted out. Posting my experiences so maybe it will help someone out.

This post was helpful for me to structure the application for multi-tenancy.
http://dreamingecho.es/blog/a-dive-into-database-multi-tenancy-in-elixir-with-ecto. I also used the example github repo in the post as reference to help me.

There are a few pointers in case anyone out there gets stuck with the same issues:

1. First, I stopped using factories (ExMachina). I moved all my fixtures into a single module and used Repo.insert! where ever I needed to interact. Since tests are ran in sandboxed environment I didn’t find a lot of advantages to continue using ExMachina, especially since it doesn’t support prefixes.

2. At first I thought it would be a bad idea to teardown the database and migrate for each test suite… It turns out the tenant migrations are very fast so there isn’t a big hit to test productivity. What I did is add ecto.drop to the start of the test script in mix.exs. Effectively I rebuild the database for every test run.

3. Because of the issue in 2), I can’t really use create extensions effectively. This caused me to move row defaults into the application instead. Not a major issue, but something to take note of.

4. Take note that schema migrations don’t currently work in a transaction using Ecto 3 Migrator. The problem is documented here
   https://github.com/ateliware/triplex/issues/59. This particular issue had me scratching my head for a long time, until I dug into github.

We use multi tenancy and ExMachina. It’s fairly straightforward and you don’t have to ditch your factories.

You’re going to want to define an EctoStrategy module:

defmodule MyProj.EctoStrategy do
  use ExMachina.Strategy, function_name: :insert

  def handle_insert(struct, attributes) do
    # Determine DB schema prefix to use
    ExMachina.EctoStrategy.handle_insert(struct, attributes, prefix: prefix)
  end
end

defmodule MyProj.Factory do
  use ExMachina
  use MyProj.EctoStrategy

  # Factory definitions
end

https://hexdocs.pm/ex_machina/ExMachina.Strategy.html

This might be a generic and not very helpful reply (sorry), but for posterity I feel obliged to mention Ecto.Adapters.SQL.Sandbox — Ecto SQL v3.8.3 – have you read through it? Have you tried its different modes of operation?

Hm. Reading through your OP again, it seems that you might have to get your hands dirty with Ecto dynamic repos and then combine that with the Ecto sandbox.

The only way I found to solve the issue was to remove the umbrella structure, and the tests worked like a breeze. For us it was not a problem since we detached the API from the frontend and no longer would make use of the umbrella structure.