zimt28
How to convert an existing app to a multi tenancy architecture?
I want to convert an existing app to a multi tenancy architecture and will be using Postgres schemas for this. One account will be a tenant = one Postgres schema.
So far I’ve had a single accounts table which held all accounts and their information:
public (schema)
└── accounts
├── contacts
├── orders
├── settings
└── users
Now the account information should be stored somewhere within the schema boundaries, but having just one account table to store one single entry feels weird. It would look like this:
tenant_1 (schema)
├── account
├── contacts
├── orders
├── settings
└── users
tenant_2 (schema)
...
I cannot think of a good alternative and couldn’t find any examples online, not even for Rails or Laravel. Is this the way to go or am I missing something?
Most Liked
thiagomajesk
I’m also working with schema-based multi-tenancy in a new project. I don’t know if it helps, but this is my checklist so far:
1- Triplex to do the “heavy lifting”
2- “Tenant specific” routes scoped with the tenant name (slug)
3 - All tenant-related data is inside the tenant’s schema; the rest is considered “global” scoped and goes inside the public schema (platform configuration, views, etc)
4- Users access only one tenant “area” (~/tenant-name-slug/resource) at a time
5- We use the :prefix option in our queries, to get tenant-related data
I don’t know exactly what you are building, but I guess you could still store tenant-specific information in the public schema - depending on what you consider part of your global scope. There’s certainly no right answers.
Also, take into consideration that if you need to implement something like search or report, there are pros and cons of both approaches. For instance: queries may be faster inside the tenant’s scope because the schema will have fewer rows in total to be queried against; but if you need to collect global information about all tenants it’ll be more expensive since you’ll have to query all schemas at once.
There are a lot of approaches around these scenarios and all depends on what you want to achieve. In the end it depends on how you are separating the scopes of your application.
yurko
We have a multitenant app that uses schemas, here are few basic points.
Triplex is a low level library that abstracts away few things that you will need and gets out of the way otherwise. It will help with migrations, prefix validation etc.
Similar to what others mentioned we have global stuff in public schema and a schema per tenant, these tenants are managed on the global level - public schema has a table with tenants and their prefixes.
We use plugs in tenant space to load and validate current prefix (schema) as well as to ensure the user only uses their own schema.
Most of the tenant context functions get an extra parameter for prefix which goes to repo calls (Ecto basically supports it out of the box).
We also have global users in public schema that with enough permissions can access any tenant.
All of that seems to work nicely, one thing you might want to know - it’s good if you have few big tenants, but not too many. After about 100 of them (somewhere between 100 and 900) you might experience problems running migrations.
mathieuprog
egze
I’ve never built multi-tenant apps, but could an accounts table be located in the public schema? You need to know somehow which accounts exist, who is paid/free/blocked/etc. You could put there the schema name associated with the account. Just an idea 
chasers
This is a very important caveat to this per table tenant design. I would probably stay away from this design unless there is a very compelling reason you need to put each tenant in their own table.
Popular in Questions
Other popular topics
Latest on Elixir Forum
Sponsor Spotlight
Producing high quality Elixir screencasts since 2017.
Categories:
Sub Categories:
Forums
Our Sponsors
Build Elixir applications with speed and confidence.
Supporting innovation across the BEAM ecosystem.
We build reliable cloud platforms for business-critical systems.
Catch errors, track performance, monitor hosts and more.
Practical resources that improve the lives of professional developers.
Producing high quality Elixir screencasts since 2017.
Enabling companies to succeed by building software people love.
Courses that'll move you from confusion to "Aha, now I get it!"