A dive into database multi-tenancy in Elixir with Ecto

Some time ago I had to tackle the migration of an application with a regular database to a multi-tenancy one, and I would like to share my experience here.

Despite the existence of libraries like apartmentex, triplex or tenantex, I wanted to make the migration as dependency free as possible, so I made my own management for the multi-tenancy.

Understanding Ecto prefix option

Most of the methods provided by Ecto which directly interacts with the database have a series of options, in which the most important one for multi-tenancy is prefix. According to the documentation, for Postgres users, prefix will specify the schema where the table is located, while for MySQL users will specify the database where the table is located. When no prefix is set, Postgres queries are assumed to be executed in the public schema, while MySQL queries are assumed to be executed in the database set in the config for the repo.

For example, if we want to insert a new element in our database with Repo.insert/2, we execute something like this:

case MyAppRepo.insert(%User{email: "[e-mail]"}) do
  {:ok, struct}       -> # Inserted with success
  {:error, changeset} -> # Something went wrong
end

To do this in a multi-tenancy application, we need to specify the prefix option for the insertion, like this:

case MyAppRepo.insert(%User{email: "[e-mail]"}, prefix: "some_tenant") do
  {:ok, struct}       -> # Inserted with success
  {:error, changeset} -> # Something went wrong
end

Main changes in our application

In order to prepare our application to work with a multi-tenant database, we have to make some changes in our code. Let's see the main changes with examples.

Default Phoenix scaffold

When we use the mix phx.gen.html task, a series of files are generated, among which is the context with all the functions to interact with the repo. For example, the function responsible for creating a new element will look like:

def create_user(attrs \\ %{}) do
  %User{}
  |> User.changeset(attrs)
  |> Repo.insert()
end

To enable multi-tenancy capabilities, we have to include in all the functions which interact with the repo (create, update, delete, list...) the tenant parameter to specify the prefix option:

def create_user(attrs \\ %{}, tenant) do
  %User{}
  |> User.changeset(attrs)
  |> Repo.insert(prefix: tenant)
end

Creating a new tenant

If we want to create a new tenant, we need to execute the proper query depending on the database we use (for Postgres will be CREATE SCHEMA). So we'll have a function like:

def create_schema(repo, tenant) do
  SQL.query(repo, "CREATE SCHEMA \"#{tenant}\"", [])
end

Listing available tenants

List all the available tenants will allow us to easily switch between them. To get this information, we have to make a query against the information_schema schema and select the schema_name field of the schemata table.

To be able to identify the schemas of my application, I like to use some prefix to differentiate my schemas from other ones (like tenant_), that's why I use the schema_prefix parameter on the query.

def list_tenants(repo, schema_prefix) do
  query =
    from(
      schemata in "schemata",
      select: schemata.schema_name,
      where: like(schemata.schema_name, ^"#{schema_prefix}%")
    )

  repo.all(query, prefix: "information_schema")
end

If you want to check if this function returns the correct information, you can enter the Postgres console and execute the following query:

database_dev=# SELECT schema_name FROM information_schema.schemata WHERE schema_name LIKE 'tenant_%';

Deleting a specific tenant

Like with the creation, if we want to delete a specific tenant, we need to execute the proper query depending on the database we use (for Postgres will be DROP SCHEMA). So we'll have a function like:

def drop_schema(repo, tenant) do
  SQL.query(repo, "DROP SCHEMA \"#{tenant}\" CASCADE", [])
end

Creating and running migrations on a specific tenant

The mix ecto.gen.migration task creates a new file with the migration we want into the priv/repo/migrations folder of the application. In case we need to be able to run migrations on the public schema and on the custom generated ones, we need to create a new priv/repo/tenant_migrations folder, and store the migrations which only will be run on the custom generated schemas on this folder.

We can create a custom task in order to generate and store new migrations directly on this folder. To avoid putting too much code in this post, here you have an example of the task so we can run something like this on the console:

user@computer:~$ mix app_name.gen.tenant_migration add_users_table

Once we have the new migration in our priv/repo/tenant_migrations folder, we can create as well a custom task to run the migrations for all the custom generated schemas. Again, to avoid putting too much code in this post, here you have an example of the task so we can run something like this on the console:

user@computer:~$ mix app_name.ecto.migrate_tenants

And finally, we can create a custom task to rollback the migrations for all the custom generated schemas. Here you have an example of the task so we can run something like this on the console:

user@computer:~$ mix app_name.ecto.rollback_tenants

At the end of the post I'll leave a link to an example Phoenix application with all the examples, so don't worry if I don't specify the code here.

Storing the current tenant in our application

We'll need to have the current tenant in which we have to execute all repo functions all along the application available. For this, which worked the best for me it's using Guardian to store the current tenant as a claim with sign_in. So, for example, if we have a controller to manage sessions, the create function will look like this:

def create(conn, %{
      "session" => %{
        "tenant" => tenant,
        "email" => email,
        "password" => password
      }
    }) do
  case Guardian.authenticate_user(email, password) do
    {:ok, user} ->
      conn
      |> Plug.sign_in(user, %{current_tenant: tenant})
      |> put_flash(:success, gettext("Welcome to AppName!"))
      |> redirect(to: page_path(conn, :index))

    {:error, message} ->
      conn
      |> put_flash(:error, message)
      |> redirect(to: session_path(conn, :new))
  end
end

Once the user logs in successfully, we'll be able to access that claim on the assigns of the conn, so, using the example seen before about the creation of a user, we can do something like:

Accounts.create_user(user_attrs, conn.assigns.current_tenant)

Things to take in mind

Once you have completed the migration of the code of our application, we have to take care of the dependencies used which directly interact with the repo. Most of the packages I saw do not allow the user to propagate the opts for the repo, so we'll can't use multi-tenancy in them, so be careful. And if you're going to create a new package, please allow the user to propagate the repo opts.

Example of a multi-tenancy application

I have developed a small application in Phoenix to be able to check the real behavior of all the concepts seen here. You can check the code in this repo.

This post is originally published here.

Comments

[Daniel Olshansky]

Do you know if there's a way to use the prefix option with Postgres, but have it point to different databases?

[Iván González Sáiz]

Hi Daniel!

I think is not possible to change between different databases using the prefix option with Postgres. If you check the official documentation, you can read: "For Postgres users, this will specify the schema where the table is located, while for MySQL users this will specify the database where the table is located". Postgres uses its schemas for the multi-tenancy, not different databases.