travisf
Multitenancy shared vs seperate schema
I’m likely going to be converting a rather large sales application to a multi-tenant architecture in the next few weeks and I’m curious about everyone’s thoughts between shared vs separate schemas?
A few qualifiers:
- The app is fairly low traffic (it’s a B2B sales portal), I don’t think it would ever have more than a few hundred users at one time.
- Right away there will be two tenants however the owner is hoping to scale up quickly, it’s unlikely we’d ever have more than 100 tenants though but I’d expect double digits to be coming within a year or two.
A few years ago I set up a multi-location function which used shared schemas and every record was associated with a location ID, I like that approach but it was a bit cumbersome to set it up originally. On the flip side Triplex seems like an absolute joy to use and I’ve already run some migrations on a test branch and for the most part it’s been a breeze.
Any thoughts on the pros/cons of either approach?
Most Liked Responses
travisf
I didn’t realize Ecto had guides for this! That’s awesome, I read through the Triplex code this morning and frankly I don’t think there is a lot going on there that you can’t just do with Ecto.
Initially, I leaned heavily on the query prefixes approach because this is a large codebase and scoping and adding an organization ID to every record is going to be a headache. However, that “magic” you mention with prepare_query may be a good way to find/prevent unscoped queries.
timothy
I never used Triplex, so I can’t say anything on that, but Ecto has a few guides on handling Multi tenancy:
The company I work at uses FK on all schemas similar to the second guide of Ecto (shared schema). However we never implemented the prepare_query/3 to add the tenant id to all queries of a repo, since we didn’t like the “magic” of it. Instead we just pass the tenant struct to each function that controls anything related to a tenant (e.g. list_users(%Tenant{id: tenant_id}), do: %User{} |> where([u], u.tenant_id == ^tenant_id) |> Repo.all()). It is a bit cumbersome but does provide flexibility and is a bit more explicit on what’s happening. It just comes down to personal taste I guess.
fmn
hi there, not sure if:
- you went through it already.
- you found/will find it relevant.
but here’s, kind of related - and interesting IMO - exchange: Dynamic database creation for SaaS product platform : Need help
Popular in Discussions
Other popular topics
Latest Phoenix Threads
Latest on Elixir Forum
Sponsor Spotlight
Supporting innovation across the BEAM ecosystem.
Categories:
Sub Categories:
Forums
Popular Tags
- #ecto
- #liveview
- #troubleshooting
- #learning-elixir
- #deployment
- #library
- #erlang
- #testing
- #genserver
- #mix
- #absinthe
- #remote-other
- #otp
- #plug
- #how-to-question
- #macros
- #postgres
- #channels
- #elixirconf
- #exunit
- #discussion
- #javascript
- #podcasts
- #code-sync
- #onsite
- #dialyzer
- #docker
- #authentication
- #umbrella
- #full-time-contract
- #podcasts-by-brainlid
- #ecto-query
- #elixir-ls
- #phoenix_html
- #iex
- #blog-post
- #graphql
- #genstage
- #ai
- #websockets
- #supervisor
- #advent-of-code
- #elixirconf-us
- #distillery
- #processes
- #forms
- #api
- #metaprogramming
- #security
- #performance
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.
Real-time error tracking, performance insights, and observability for devs.
Producing high quality Elixir screencasts since 2017.
Enabling companies to succeed by building software people love.
The deployment platform built for Elixir: PaaS ease, VPS control.
Courses that'll move you from confusion to "Aha, now I get it!"