A dive into database multi-tenancy in Elixir with Ecto
Tags: elixir, ecto, multitenancy
Published: 2018-07-18 09:13:38

---
title: A dive into database multi-tenancy in Elixir with Ecto
published: true
description: Some tips and experiences about the migration of an application with a regular database to a multi-tenancy one in Elixir with Ecto.
tags: elixir,ecto,multi-tenancy
---

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](https://github.com/Dania02525/apartmentex), [triplex](https://github.com/ateliware/triplex) or [tenantex](https://github.com/jeffdeville/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](https://hexdocs.pm/ecto/Ecto.html) 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](https://hexdocs.pm/ecto/Ecto.Repo.html#c:insert/2), we execute something like this:

```ruby
case MyAppRepo.insert(%User{email: "user@example.com"}) 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:

```ruby
case MyAppRepo.insert(%User{email: "user@example.com"}, 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:

```ruby
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:

```ruby
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:

```ruby
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. 

```ruby
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:

```bash
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:

```ruby
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](https://github.com/dreamingechoes/multi_tenancex/blob/master/lib/multi_tenancex/mix/tasks/multi_tenancex.gen.tenant_migration.ex) an example of the task so we can run something like this on the console:

```bash
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](https://github.com/dreamingechoes/multi_tenancex/blob/master/lib/multi_tenancex/mix/tasks/multi_tenancex.ecto.migrate_tenants.ex) an example of the task so we can run something like this on the console:

```bash
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](https://github.com/dreamingechoes/multi_tenancex/blob/master/lib/multi_tenancex/mix/tasks/multi_tenancex.ecto.rollback_tenants.ex) an example of the task so we can run something like this on the console:

```bash
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](https://github.com/ueberauth/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:

```ruby
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:

```ruby
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](https://github.com/dreamingechoes/multi_tenancex).

> This post is originally published [here](http://dreamingecho.es/blog/a-dive-into-database-multi-tenancy-in-elixir-with-ecto).