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

Have you looked at Triplex?

https://github.com/ateliware/triplex

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.

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.

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

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.

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.

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.

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.

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.