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.