Multitenancy with Ecto

Hi,

I’m implementing a system that implements multitenancy via multiple databases. For business reasons I cannot do away with prefixes, which I’d prefer. In any case, I was able to make it work by using put_dynamic_repo, but am concerned it might be a bit too “magic”.

I’m doing this with a Phoenix app, so I set the repo on the request process, any sub process will have to set the repo again, which is easy to forget.

I’ve been pointed out to an alternative that is to wrap the Ecto API and require a pid for every call, which seems more obvious but both more labor intensive as well as brittle, since any change in the API breaks the code.

I’m very torn in terms of what’s the best option (and maybe there’s even another that I’m missing). Any advice would be great.

Here’s what I’m using to manage the repos, note that the tenants are agencies and I’m using a GenServer to cache the dynamic repos that were created. Also have a function to create a new database and run the migrations if needed.

defmodule MyApp.RepoManager do
  use GenServer

  alias MyApp.{
    Application,
    Repo
  }

  def start_link(settings) when is_list(settings) do
    GenServer.start_link(__MODULE__, settings, name: __MODULE__)
  end

  def set_agency_repo(agency, ensure_exists \\ false) do
    if ensure_exists do
      ensure_repo_exists(agency)
    end

    repo_pid = GenServer.call(__MODULE__, {:get_dynamic_repo, agency})
    Repo.put_dynamic_repo(repo_pid)

    {:ok, repo_pid}
  end

  def unset_agency_repo do
    repo_pid = Repo.put_dynamic_repo(Repo)

    {:ok, repo_pid}
  end

  def init(_opts) do
    {:ok, %{repos: %{}}}
  end

  def handle_call({:get_dynamic_repo, agency}, _from, state) do
    case state.repos[get_database_name(agency)] do
      nil ->
        {:ok, repo_pid} = Repo.start_link(get_connection_options(agency))

        {:reply, repo_pid,
         %{state | repos: Map.put_new(state.repos, get_database_name(agency), repo_pid)}}

      repo_pid ->
        {:reply, repo_pid, state}
    end
  end

  defp get_database_name(agency) do
    "my_app_#{agency.slug}"
  end

  defp get_connection_options(agency) do
    [
      name: nil,
      pool_size: 2,
      database: get_database_name(agency)
    ] ++ Application.db_config()
  end

  defp ensure_repo_exists(agency) do
    options = get_connection_options(agency)
    Repo.__adapter__().storage_up(options)
    {:ok, repo_pid} = Repo.start_link(options)
    Repo.put_dynamic_repo(repo_pid)
    Ecto.Migrator.run(Repo, :up, all: true, dynamic_repo: repo_pid)

    Repo.stop(1000)
    Repo.put_dynamic_repo(Repo)
  end

  # TODO: Manage pool size better
  # TODO: Support removing repos
end