Ecto & Multi-tenancy - Prefixes - Part 3

Underjord is a tiny, wholesome team doing Elixir consulting and contract work. If you like the writing you should really try the code. See our services for more information.

This should be the final piece of this saga. Previous parts can be found here:

• Part 1 - Getting started
• Part 2

Now I'll cover the approach I finally ended up using. I had dynamic repos fully working but was somewhat haunted by the heavy-handed approach of running all those pools long before I knew anything about the loads I could expect for each tenant.

I think it would work fine. But it also felt like I had introduced a lot of complexity. More complexity than I wanted or should need. Elements of the solution felt somewhat fragile, such as the reliance on the Process dictionary and that repo selection would not survive through to a new process. And performance-wise I would need to tune both my pool sizes and my LRU approach to avoid potential trouble.

And that's when I noticed a guide in the Ecto docs which I must have overlooked. Like a mysterious shop in an 80's mall. Multi-tenancy with query prefixes gave me another option entirely. I expected the prefix feature to be about the same as Wordpress, Drupal and such which generally just means namespacing all tables for the product with a name prefix to avoid collisions on cheap hosting where you only have one database. But no, it is better than that.

In Postgres it uses Schema which is a great terminology collision with Elixir in that both have a thing they call a Schema and they are completely different beasts. It basically means that by default your database tables live in the public Postgres schema and you can actually use the qualified name for them when querying as select * from public.my_table .., but you don't have to, public is the default. This prefix functionality allows you to use Postgres Schemas (or schemata, but I'll go with schemas) to run separate instances of your Ecto Schema. So your end query might be select * from customer_5.my_table .. instead.

Querying a prefix

Since with prefixes all your data is in the same database you can use a single connection pool. But when you make a query you indicate clearly which prefix you want to work with by adding the prefix: "customer_5" keyword option.

Setup and migrations

I still needed my approach for runtime migrations. The migrations were unchanged I believe. I just needed to do some different setup when creating a new customer.

That ended up being a module like this:

defmodule MyApp.Customers.CustomerSelector
  @moduledoc """
  Utilities that help with managing customer's prefixed databases.
  """

  alias Ecto.Migration.SchemaMigration
  alias MyApp.Repo.Migrations

  def get_prefix_for_customer(customer) do
    "customer_#{customer.slug}"
  end

  def create_customer_database_schema(customer) do
    prefix = get_prefix_for_customer(customer)

    config =
      Application.get_env(:my_app, MyApp.Repo)
      |> Keyword.put(:name, nil)
      |> Keyword.put(:pool_size, 2)
      |> Keyword.put(:migration_default_prefix, prefix)
      |> Keyword.put(:prefix, prefix)
      |> Keyword.delete(:pool)

    {:ok, pid} = MyApp.Repo.start_link(config)
    MyApp.Repo.put_dynamic_repo(pid)

    query = """
    CREATE SCHEMA "#{prefix}"
    """

    MyApp.Repo.query(query)
    SchemaMigration.ensure_schema_migrations_table!(MyApp.Repo, config)

    migrate_repo(
      prefix: prefix,
      dynamic_repo: pid
    )

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

  def migrate_repo(options \\ []) do
    options = Keyword.put(options, :all, true)

    Ecto.Migrator.run(
      MyApp.Repo,
      Migrations.get_migrations(),
      :up,
      options
    )
  end
end

Turns out all the work with dynamic repos left me well prepared to tackle the very same need here. We even use a dynamic repo to perform the migration. If there are better ways of making a schema through Ecto for Postgres I don't know about it. I couldn't find one. There may also be some belt-and-suspenders in the options here. But we basically just use a dynamic repo to create the customer's schema in Postgres, ensure the schema migrations metadata stuff is in place and then run the migrations.

Drawbacks

There is no one clever magic state we can fiddle with as for the process dictionary and put_dynamic_repo. Unless we actually use the process dictionary. But the lack of magic state is probably for the best. I'm currently just using the prefix explicitly. I'm considering whether it would make sense to make my repo module provide some additional helpers here. I looked into using the prepare_query hook but from what I could gather it couldn't affect the prefix. So explicitly I go. It's just this: |> Repo.insert(prefix: prefix)

Testing

Prefixes definitely felt more like still being on the beaten/happy path, or very near to it, when it came to writing tests. I still have my own set of modifications. Mostly to set up migrations in the test helpers.

So my test helper looks like this:

Ecto.Migrator.with_repo(MyApp.Repo, fn repo ->
  Ecto.Migrator.run(repo, MyApp.Repo.Migrations.get_migrations(), :up, all: true)
end)

customer = %{
  name: "Test customer",
  slug: "test_customer"
}

c = MyApp.Customers.get_customer_by_slug(customer.slug)

# Clean up any existing instances of this customer from previous runs
if c do
  MyApp.Customers.delete_customer(c)
end

# Create the customer
{:ok, customer} = MyApp.Customers.create_customer(customer)
MyApp.Customers.CustomerSelector.create_customer_database_schema(customer)

ExUnit.start(exclude: [:skip])

Ecto.Adapters.SQL.Sandbox.mode(MyApp.Repo, :manual)

And my ConnCase for example. DataCase is pretty similar:

defmodule MyAppWeb.ConnCase do
    use ExUnit.CaseTemplate
  
    @customer_attrs %{
      name: "Test customer",
      slug: "test_customer",
    }
  
    using do
      quote do
        # Import conveniences for testing with connections
        use Phoenix.ConnTest
        alias MyAppWeb.Router.Helpers, as: Routes
  
        # The default endpoint for testing
        @endpoint MyAppWeb.Endpoint
      end
    end
  
    setup context do
      :ok = Ecto.Adapters.SQL.Sandbox.checkout(MyApp.Repo)
  
      unless context[:async] do
        Ecto.Adapters.SQL.Sandbox.mode(MyApp.Repo, {:shared, self()})
      end
  
      %{} = customer = MyApp.Customers.get_customer_by_slug(@customer_attrs.slug)
  
      {:ok, conn: Phoenix.ConnTest.build_conn(), customer: customer}
    end
  end

And that's pretty much what I use.

Update: I think I should add a prepare_query similar to what Stephen Bussey suggest in this blog post but I'll check if the prefix is set for queries that should have it instead.

I really enjoyed diving deeper into Ecto. Parts were frustrating, parts were delightful. It was interesting to realize I was working outside the norm in my requirements and I'm glad to see the flexibility is indeed there.

I hope y'all enjoyed the adventures in Ecto Multi-tenancy. Or that it was at least useful to you. If you have corrections, questions, feedback or topics you want me to cover you can get in touch through lars@underjord.io or find me on Twitter where I'm @lawik.

Underjord is a 4 people team doing Elixir consulting and contract work. If you like the writing you should really try the code. See our services for more information.

Note: Or try the videos on the YouTube channel.