Writing Tests for Multitenancy solution

Ever since I’ve started on Elixir, I’ve fell in love :heart_eyes: with writing tests for my applications.

For the most part, writing tests for a regular Phoenix + Ecto + ExMachina stack has been a breeze since it is well documented.

Recently, I’ve been working on an application that is multi-tenant in nature and it’s been a pain to write tests with existing tools.

The current design is to have a different database schema for every tenant. I’m looking for advise for these questions:

  1. Should I set up and teardown the test database everytime?
  2. Should I use ExMachina here? It doesn’t seem to support prefixes yet.

Would be open to hear other advise as well (test methodology, approaches, etc). Thanks!

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.


Thanks for the tips @alvinncx , I’m also working on the same type of application, and plus it’s an umbrella app, so I’m facing the issue which is: When running all tests as one mix test it runs without problem, I believe it’s because it only opens one connection for everything. But when I run tests for s specific app, for one it runs succssefully, in this case the app that is responsible for the database. And when I run the tests for the web application it says that could not checkout the connection owned by #PID<xxxx>, it’s stressfull since I wasn’t able to find a solution which supports testing multi tenancy with umbrella structures. I wish I could get some reply from someone that knows about the problem. I don’t understand how can I manage the connections in this case so I’ll be able to run them as one and separately like when I’m developing for example. Any help? @josevalim

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?

Thanks a lot for your reply @dimitarvp !
Yes, I have tried managing the checkout connections, and I feel like that’s the way to solve the problem, but I don’t know how, I have tried multiple different ways to checkout the connections with manual, auto and shared mode, but none have worked, as I said, they only work when all tests run together, but not for a specific test or a specfic app, in this case my web app.

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.

Yes, I’m thinking to get rid of the umbrella structure, this will make the tests run a lot easier with multi tenancy.

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)

defmodule MyProj.Factory do
  use ExMachina
  use MyProj.EctoStrategy

  # Factory definitions


1 Like

Seems like a nice solution, do you have any examples on how to use it with tests?

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.

1 Like