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.
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
   Ecto 3 Support · Issue #59 · ateliware/triplex · GitHub. 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

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.14.0 – have you read through it? Have you tried its different modes of operation?