Datatabase Multitenancy for Elixir applications

June 22, 2019

If you have never needed splitting data between different schemas (in Postgres), you work on just one (the default) schema - “Public”. Why would you want to use more schemas? For instnace financial institutions may require to keep their data isolated, and multi-tenant schemas is one way doing it. Different schemas may require different ACL, different backup procedures, etc. It’s still possible to do reference between tables in different schemas. I’m using this approach in my current approach but as it’s not released yet, I cannot speak about performance. Multitenancy is a much broader term and I’m describing here only specific case here.

Ecto Queries

You don’t have to use any package except Ecto to handle multitenancy as Ecto provides Query Prefixes but that still requires a lot of manual work to do on top of query prefixes, for instance you need to make sure that each tenant has its namespace in the database or migrations run for each of these tenants and that’s where Triplex comes in.

Meet triplex

I’ve been using Triplex which “leverages database data segregation techniques (such as Postgres schemas) to keep tenant-specific data separated, while allowing you to continue using the Ecto functions you are familiar with." It also suppors MySQL where each tenant will have its own MySQL database.

Using Triplex is failry simple but the documentation sucks. There’s no guideline aka ‘step by step’ page, so I hope this tutorial will help you to start.

Let’s build a sample project.

mix new multi_tenant_demo --sup
def deps do
  [
    {:triplex, "~> 1.3.0"},
  ]
end
mix deps.get

Next step is to configure the Repo you will use to execute the database commands with.

Create config folder in the root of the project and add config.exs with the following content:

# config/config.exs
use Mix.Config

# multi-tenancy
config :triplex,
  # the ecto repo that will be used to execute the schema operations
  repo: MultiTenantDemo.Repo,
  # a prefix for all tenants
  tenant_prefix: "demo_tenant_",
  # an atom with the name of the field to get the tenant name
  # if the given tenant is a struct. By default it’s :id.
  tenant_field: :tenant_key

If the prefix is omitted, the tenant_prefix configuration from Triplex.Config will be used.

The tenant can be a string, a map or a struct. For a string it will be used as the tenant name to concat the prefix. For a map or a struct, it will get the tenant_field/0 from it to concat the prefix.

Add Ecto

You can run mix ecto.gen.repo -r MultiTenantDemo.Repo to create Repo for you, but I’ll add each part of the code that would be generated by mix myself, so we know what actually happens behind the scenes.

If you generate a new Phoenix project (“mix phx.new project_name), Ecto will be there by default but for the purpose of better ilustration of dependendencies I started without Phoenix.

  defp deps do
    [
      {:ecto_sql, "~> 3.3"},
      # we wouldn't be able to run anything without :postgrex
      {:postgrex, "~> 0.15.3"},
      {:ecto, "~> 3.3.2"},
      {:triplex, "~> 1.3.0"}
    ]
  end
mix deps.get

Time to create Repo. Repo is the heart of Ecto and acts as a kind of proxy for your database. All communication to and from the database goes through Repo.

defmodule MultiTenantDemo.Repo do
  # lib/multi_tenant_demo/repo.ex
  use Ecto.Repo,
    otp_app: :multi_tenant_demo,
    adapter: Ecto.Adapters.Postgres
end
# config/dev.exs
use Mix.Config

# Configure your database
config :multi_tenant_demo, MultiTenantDemo.Repo,
  username: System.get_env("POSTGRES_USER") || "postgres",
  password: System.get_env("POSTGRES_PASSWORD") || "postgres",
  database: System.get_env("POSTGRES_DB") || "multitenantdemo_dev",
  hostname: System.get_env("POSTGRES_HOST") || "localhost",
  port: System.get_env("POSTGRES_PORT") || "5432",
  # how connection data and configuration whenever there is an error attempting
  show_sensitive_data_on_connection_error: true,
  pool_size: 10
# config/test.exs
use Mix.Config

# Configure your database
config :multi_tenant_demo, MultiTenantDemo.Repo,
  username: System.get_env("POSTGRES_USER") || "postgres",
  password: System.get_env("POSTGRES_PASSWORD") || "postgres",
  database: System.get_env("POSTGRES_DB") || "multitenantdemo_test",
  hostname: System.get_env("POSTGRES_HOST") || "localhost",
  port: System.get_env("POSTGRES_PORT") || "5432",
  # how connection data and configuration whenever there is an error attempting
  show_sensitive_data_on_connection_error: true,
  pool_size: 10
# config/config.exs
use Mix.Config

config :multi_tenant_demo,
  ecto_repos: [MultiTenantDemo.Repo]

config :multi_tenant_demo, MultiTenantDemo.Repo, migration_timestamps: [type: :utc_datetime]

# multi-tenancy
config :triplex,
  repo: MultiTenantDemo.Repo,
  tenant_prefix: "demo_tenant_",
  tenant_field: :tenant_key

# Import environment specific config. This must remain at the bottom
# of this file so it overrides the configuration defined above.
import_config "#{Mix.env()}.exs"

I deliberately don’t put configuration for production here.

defmodule MultiTenantDemo.Application do
  # See https://hexdocs.pm/elixir/Application.html
  # for more information on OTP Applications
  @moduledoc false

  use Application

  def start(_type, _args) do
    children = [
      # Start the Ecto repository
      MultiTenantDemo.Repo
    ]

    # See https://hexdocs.pm/elixir/Supervisor.html
    # for other strategies and supported options
    opts = [strategy: :one_for_one, name: MultiTenantDemo.Supervisor]
    Supervisor.start_link(children, opts)
  end
end

Create the DB

Run:

mix ecto.create

If successful, you should see:

The database for MultiTenantDemo.Repo has been created

At that stage, our DB has just the default public schema:

Add a new schema

Time to play with Triplex.

Run:

iex -S mix
iex(1)> Triplex.config
%Triplex.Config{
  migrations_path: "tenant_migrations",
  repo: MultiTenantDemo.Repo,
  reserved_tenants: [],
  tenant_field: :tenant_key,
  tenant_prefix: "demo_tenant_",
  tenant_table: :tenants
}

Do we have any tentant schemas? This function also can take Repo (MultiTenantDemo.Repo) as a param.

iex(2)> Triplex.all

00:00:37.177 [debug] QUERY OK db=1.8ms decode=1.3ms queue=0.9ms idle=9155.9ms
SELECT schema_name
FROM information_schema.schemata
 []
[]

Any reserved tenants? This can extended by our own list defined in the configuration.

iex(3)> Triplex.reserved_tenants
[nil, "public", "information_schema", ~r/^pg_/]

The function we’re looking for is Triplex.create

iex(4)> h Triplex.create

def create(tenant, repo \\ config().repo)

Creates the given tenant on the given repo.

Returns {:ok, tenant} if successful or {:error, reason} otherwise.
....

Mix Tasks

Under lib create a new directory mix with a subdirectory tasks.

We’ll put there a few tasks for our tenants: creat, drop, rename

mix tenant.create

defmodule Mix.Tasks.Tenant.Create do
  # lib/mix/tasks/tenant.create.ex
  use Mix.Task

  alias MultiTenantDemo.Repo

  @shortdoc "Creates the given tenant on the given repo. "
  def run(args) do
    Mix.Task.run("app.start")
    # the sake of simplicity we don't validate the list
    for t <- args, do: create(t)
  end

  defp create(tenant) do
    if Triplex.exists?(tenant, Repo) do
      {:error, :tenant_exists}
    else
      Triplex.create(tenant, Repo)
    end
  end
end

mix tenant.drop

defmodule Mix.Tasks.Tenant.Drop do
  # lib/mix/tasks/tenant.drop.ex
  use Mix.Task

  alias MultiTenantDemo.Repo

  @shortdoc "Drops the given tenant on the given repo."
  def run(args) do
    Mix.Task.run("app.start")
    # the sake of simplicity we don't validate the list
    for t <- args, do: drop(t)
  end

  defp drop(tenant) do
    if Triplex.exists?(tenant, Repo) do
      Triplex.drop(tenant, Repo)
    else
      {:error, :cannot_find_tenant}
    end
  end
end

mix tenant.rename

defmodule Mix.Tasks.Tenant.Rename do
  # lib/mix/tasks/tenant.rename.ex
  use Mix.Task

  alias MultiTenantDemo.Repo

  @shortdoc "Renames the given tenant on the given repo."
  def run(args) do
    Mix.Task.run("app.start")
    # the sake of simplicity we don't validate the list
    rename(hd(args), tl(args))
  end

  defp rename(current_name, new_name) do
    if Triplex.exists?(current_name, Repo) do
      Triplex.rename(current_name, new_name, Repo)
    else
      {:error, :cannot_find_tenant}
    end
  end
end

You should see our tasks listed there:

...
mix tenant.create         # Creates the given tenant on the given repo.
mix tenant.drop           # Drops the given tenant on the given repo.
mix tenant.rename         # Renames the given tenant on the given repo.
...

See more functions on Triplex functions that you can turn into your own tasks (for instance we could use reserved_tenant?(tenant) to validate given names).

Create:

$ mix tenant.create "first-tenant"

13:53:56.504 [debug] QUERY OK db=1.9ms decode=2.0ms queue=189.5ms idle=0.0ms
SELECT COUNT(*)
FROM information_schema.schemata
WHERE schema_name = $1
 ["demo_tenant_first-tenant"]

13:53:56.515 [debug] QUERY OK db=5.8ms queue=2.1ms idle=35.4ms
CREATE SCHEMA "demo_tenant_first-tenant" []

13:53:56.562 [info]  Already up

Notice that our prefix “demo_tenant” has been applied to our tenant name.

Rename:

$ mix tenant.rename "first-tenant" "first-tenant2"

13:55:59.345 [debug] QUERY OK db=1.7ms decode=2.3ms queue=172.2ms idle=0.0ms
SELECT COUNT(*)
FROM information_schema.schemata
WHERE schema_name = $1
 ["demo_tenant_first-tenant2"]

13:55:59.351 [debug] QUERY OK db=1.2ms queue=1.2ms idle=37.9ms
ALTER SCHEMA "demo_tenant_first-tenant"
RENAME TO "demo_tenant_first-tenant2"
 []

Drop:

$ mix tenant.drop "first-tenant2"

13:57:37.120 [debug] QUERY OK db=1.3ms decode=1.6ms queue=187.0ms idle=0.0ms
SELECT COUNT(*)
FROM information_schema.schemata
WHERE schema_name = $1
 ["demo_tenant_first-tenant2"]

14:37:27.731 [debug] QUERY OK db=3.2ms queue=2.1ms idle=25.7ms
DROP SCHEMA "demo_tenant_first-tenant2" CASCADE []

Mix Aliases

Aliases are shortcuts or tasks specific to the current project. Open mix.exs and add a new private function:

defp aliases do
    [
      "ecto.setup": [
        "ecto.create --quiet",
        "ecto.migrate",
        "tenant.create 'first-tenant' 'second-tenant'",
        "triplex.migrate"
      ],
      "ecto.reset": ["ecto.drop", "ecto.setup"]
    ]
  end

and extend the list in project:

def project do
    [
      aliases: aliases(),
      ...

Run:

$ mix ecto.setup
** (Mix) Could not find migrations directory "priv/repo/migrations"
for repo MultiTenantDemo.Repo.

This may be because you are in a new project and the
migration directory has not been created yet. Creating an
empty directory at the path above will fix this error.

If you expected existing migrations to be found, please
make sure your repository has been properly configured
and the configured path exists.
$ tree priv
priv
└── repo
    ├── migrations
    └── tenant_migrations

Re-run it:

$ mix ecto.setup

14:22:47.121 [info]  Already up

14:22:47.244 [debug] QUERY OK db=4.6ms queue=61.4ms idle=0.0ms
SELECT COUNT(*)
FROM information_schema.schemata
WHERE schema_name = $1
 ["demo_tenant_first-tenant"]

14:22:47.250 [debug] QUERY OK db=4.5ms queue=1.4ms idle=19.9ms
CREATE SCHEMA "demo_tenant_first-tenant" []

14:22:47.263 [info]  Already up

14:22:47.266 [debug] QUERY OK db=1.9ms queue=1.4ms idle=25.5ms
SELECT COUNT(*)
FROM information_schema.schemata
WHERE schema_name = $1
 ["demo_tenant_second-tenant"]

14:22:47.268 [debug] QUERY OK db=1.3ms queue=0.6ms idle=24.7ms
CREATE SCHEMA "demo_tenant_second-tenant" []

14:22:47.281 [info]  Already up

14:22:47.290 [debug] QUERY OK db=3.0ms queue=1.4ms idle=35.5ms
SELECT schema_name
FROM information_schema.schemata
 []

14:22:47.294 [info]  Already up

14:22:47.300 [info]  Already up

Next steps

Having the good base for setting up your schemas, you can put migrations for tenant into priv/repo/tenant_migrations.

How to use it with Ecto.Schema or ecto_sql?

Check out the link at the beginning of this post about query prefixes. Quering against a tenant schema is as simple as:

MyApp.Repo.all Sample, prefix: "public"

I put the source code to github:

https://github.com/rafalgolarz/multi_tenant_demo