kelvinst
Triplex - a complete solution to multi-tenancy with PG schemas
Hey everyone!
Well, we made this lib a while ago and now we decided to finally go out and public with it! It’s a tool for creating and managing multi-tenancy applications using postgres schemas.
We’ve recently released version 1.1.2 with support for ecto 2.2.0-rc. 
I know there are some other options of libraries for the same purpose, like https://github.com/Dania02525/apartmentex and https://github.com/promptworks/tenantex. At the time we wrote this lib, the other libraries were a little bit out of date with their pull requests, didn’t have everything we wanted and, the worst for us, were a little bit intrusive (I’ll talk about it later).
So we decided to write a simple one, and now here we are, another option for you. Here is a list of features we missed on the other libraries:
- Mix tasks for migrating and rollbacking all schemas
- Plugs to load and ensure if the tenant is loaded from a param, session value or subdomain
- A way to infer the name of the tenant from a given struct which represents the tenant on your application
- Smarter configuration like:
- Set the default
Repowhere to execute the tenant management tasks - Tenant names that must be reserved, like
apiorwwwif you’re using subdomains to load your tenant
- Set the default
There are also some differences in the way the libs handle the queries and commands with the prefix. While tenantex and apartmentex are a little bit intrusive to your Repo or the use of it, on triplex we tried to stay the least intrusive we could.
For example, here is how to make a query applying a prefix from an tenant struct called org on raw ecto:
Repo.all(User, prefix: org.subdomain)
Now the same query using a properly configured triplex:
Repo.all(User, prefix: Triplex.to_prefix(org))
The same with tenantex:
# first you need to change your repo to use their repo not ecto's
defmodule Repo do
use Tenantex.Repo, otp_app: :your_app
end
Repo.all(User, prefix: org)
Finally, with apartmentex:
Apartmentex.all(Repo, User, org)
The reason behind this decision is simple: being less intrusive makes triplex a lot easier to upgrade for future versions of ecto.
And one last thing: we got our docs a little bit further! That’s a bonus for anyone who wants to use it! 
Feel free to try it and send feedbacks for us!
Most Liked
kelvinst
Heeey! Long time no see, but I’m here to announce 1.3.0 (not rc, the official one) is out! You can check the list of stuff on the release notice, but for those that have no time, the big thing on this release was the support to Ecto 3.
Hope that the lib is still useful for all of you and we are always open to contributions, but please do not be sad if we take some time to answer, there are not too many people on the core team for it and we are very busy lately.
Thanks!
kelvinst
1.2.0-rc is out!
Hey guys! Quick update: we got a new release!
Despite not having huge changes or new features, we have incremented the minor version for some reasons. You can check these reasons as well as the changelog here: https://github.com/ateliware/triplex/releases/tag/v1.2.0-rc.0
But for those who don’t have time do get there, the biggest things that are coming out with this release are:
- Plug as an optional dependency, so if you don’t use any plug on your app, triplex will not add this dependency on your project.
- The support for using triplex on OTP releases, which I really didn’t know we had a problem with, but now, thanks to @dustinfarris, we do not have it anymore! PS: if anyone does need this correction on a
1.1patch release, please let me know. - Docs and README improved.
- Refactors on plug configs and method return values.
Hope you’re liking to use it.
kelvinst
I’m here to announce the long awaited new version for Triplex!
Actually, it’s an RC for the new version. You know, we need to test it well before releasing it out in the wild.
Anyways, this version will come with the support for two new things: Ecto 3 and MySQL!
The details are here in the release notice: https://github.com/ateliware/triplex/releases/tag/v1.3.0-rc.0
Also, refer to the release notice for how to upgrade, since there are some breaking changes on Ecto 3 migrations that affected Triplex.create inside Repo.transactions.
With no further ado, we would be very grateful if you could test it out on your projects and let us know with github issues if any problem occurs!
Thank you all!
kelvinst
Hi, @marciol!
Well, we’ve bought the idea on a previous project, but didn’t come to the point of having the article’s mentioned issues. But well, I guess that there’s no perfect solution. Everything will have its pros and cons and the post you mention is focused on the cons.
While all of the cons are true (I guess), there are also some pros, and one of them (which made me decide for this technique) is the productivity and code organization. I guess that the pros I had on the start of the project totally compensate some issues running migrations or having to pay some extra money when I have more customers (I guess with more customers I’d have more money too).
The other cons he list on the article are easy to workaround, and he mentions how to do it on the end of the article.
Coming to a conclusion: “there’s no silver bullet” and you must know that before using any tool. The tools are made to solve one or two problems, not all of them. I guess that, once you start having problem with the tool, it can be an indicator you’re using it wrongly or it’s the wrong tool for the job. Using PG schemas to separate your tenants is good in some cases, but for a lot of data in lot of tables and with a lot of changes on the db, there are better alternatives for sure.
l00ker
I’m pretty sold on the separate schema multi-tenancy solution. The per table tenant_id column approach (which I’m using in a python app) works well until one of your tenants does something requiring you to restore their data from a backup. I’ll leave that potential nightmare to your imagination to sort out. With separate schemas this problem doesn’t exist. Each schema can be independently restored and/or moved to a different PostgreSQL instance if needed.
Instead of UUID I decided to use a “Snowflake-style system” that uses a PostgreSQL function to generate a big integer which is unique and increments based on time so sorting isn’t an issue. Just use a different shard_id per tenant (even per PostgreSQL instance) to be sure of uniqueness in case you have a need to relocate the schema later. The really cool thing is that given any ID you can extract the shard_id and determine the tenant that it belongs to (if tenant_id == shard_id). This can potentially save some database queries in some cases.
If you’re building a JSON API and using JavaScript on your front-end (SPA etc.) you need to convert the bigint ID’s to strings before encoding to JSON as JavaScript can’t properly handle the bigint ID’s. It will round them causing you fits until you figure that bit out. You can use an Ecto custom type to do the conversion automatically allowing you to pass ID’s as strings or integers in your queries but always get the ID’s as strings in the query result so you can just pass it directly to Poison etc.
These are the “pros” that sold me but admittedly I haven’t had any experience with this solution at the scale of the blog author, but I would think that with a little planning you could utilize several PostgreSQL instances to solve most of those issues since you can at least move the separate schemas between instances etc. I’ve also looked a table inheritance as a potential solution to some problems. Table inheritance is pretty cool on it’s own once you get to looking at it. It just depends on how much complexity you want to introduce in the long run.
@kelvinst - Thank you for Triplex! It’s simple, stays out of your way and gets the job done. 
Popular in Libraries
Other popular topics
Latest Phoenix Threads
Latest on Elixir Forum
Sponsor Spotlight
Enabling companies to succeed by building software people love.
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.
Develop your skills with books, videos, and courses.
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!"