How to move an existing database into a multi-tenant configuration?

Questions / Help
, ,
1

Hey all,

I’ve been working on a project where I need to move an existing database into a multi-tenant configuration.

We have some migrations that we want to run outside of individual tenants (creating enums, etc) to be run from priv/repo/migration, and also a set of migrations we want to run to generate tables for each individual tenant from priv/repo/tenant_migrations.

Ideally I would want the migrations to run for all of these sequentially whenever I run mix ecto.migrate on the single database, in the following order:

  1. public
  2. tenant_1
  3. tenant_2
  4. tenant_3
  5. etc…

Additionally I do not wish maintain multiple pools of database connections to the same database, if possible, though it would be nice to be able to have separate Ecto Repo’s for each tenant, to make queries such as:

MyApp.Repo.Tenant1.all(query)

# -- or --

MyApp.Repo.all(query, prefix: "tenant_1")

I’ve been able to run these migrations properly from the command line through multiple commands, by doing the following:

mix ecto.migrate

mix ecto.migrate --prefix=tenant_1 --migrations-path=priv/repo/tenant_migrations/

mix ecto.migrate --prefix=tenant_2 --migrations-path=priv/repo/tenant_migrations/

mix ecto.migrate --prefix=tenant_3 --migrations-path=priv/repo/tenant_migrations/

If possible, it would be nice to configure each Repo with a shared connection pool and then have their own set of migrations. One of the issues I encountered while trying to define a separate Repo for each tenant was that running mix ecto.create or mix ecto.drop, it tries to do create or drop the database multiple times; working the first and failing the rest of the times.

I’ve scoured the Ecto and EctoSQL libraries for documentation on this and tried multiple solutions I’ve come across, but it’s not yet produced a solution that works yet aside from running multiple ecto.migrate mix tasks as demonstrated above.

Has anyone come across this use-case and able to point me in the right direction? Any assistance would be greatly appreciated.

2

Have you looked at Triplex?

2 Likes
3

I know this is a bit of a stretch, but… I just started playing with Ash Framework and I noticed that it has multi-tenancy built in.

I say a bit of a stretch because it might be too much all at once. I decided to try Ash with a section of an app I’m working on and so far so good, but it is quite a lot to ingest.

Just another option to consider.

4

Like @l00ker said, take a look at triplex; the process you described that you want is so similar to how that package works I thought you might be using it already.

1 Like
5

Hey guys, thanks for the responses!

I’ve taken a look at Triplex and it looks promising but one of the internal concerns we have is that it hasn’t had a new release in nearly 4 years so I’m not sure if we want to use such a dependency on something that’s important to our project.

I’m hoping to be able to complete this with just Ecto/EctoSQL alone if possible to migrate an existing project with several hundred migrations into a multi-tenant DB

6

I can understand this concern for some libraries, but take the time to read through the Triplex code: there’s not much of it, and what’s there is mostly gluing together things that already exist. All the actual “tenent enforcement” is delegated to Ecto’s prefix machinery.

1 Like
7

I start a separate Ecto.Repo per Postgres server. Each server holds many tenants (as Postgres schemas).

Then you use these callbacks…

put_dynamic_repo/1
get_dynamic_repo/0
default_options/1

to determine which Ecto.Repo to use and also set the prefix (schema).

Then I wrote my own mix ecto.tenants.migrate which uses Task.async_stream to migrate all tenants, delegating out to the existing migration functions, kinda looking at how mix ecto.migrate does it.

Ecto is close to having multi-tenancy out of the box… it’s just missing like one callback (something to list all the tenants) and a migration task that basically iterates over all tenants, calling the normal migration code.

1 Like
8

That makes a lot of sense when the use-cases is separate databases.

I was curious if anyone could point me in the right direction for different schemas within the same database as well.

9

You can specify prefixes inside the migration code:
Multi tenancy with query prefixes — Ecto v3.9.4 (hexdocs.pm)

The other approach is by changing each desired migration to run across multiple prefixes.

10

It’s even easier because you just have one repo. Then you just use the default_options/1 callback to automatically set the prefix.

defmodule MyApp.Repo do
  use Ecto.Repo, otp_app: :my_app
  
  def default_options(_operation) do
    prefix = Tenant.get()
    |> Tenant.to_schema()
    
    [prefix: prefix]
  end
  
end

Then your migration code is something like…

def run(_args) do
  Tenant.all()
  |> Enum.each(fn tenant ->
    opts = [
      prefix: Tenant.to_schema(tenant),
      migration_lock: false
    ]
    
    Ecto.Migrator.run(MyApp.Repo, migrations, dir, opts)
  end)
end

Note the migration_lock: false. Last I checked, the code that does migration locking does not honor the :prefix option. Should probably open a GitHub issue about that.