travisf

travisf

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?

Showing Posts 1 to 5

timothy

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

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

mlg-hub

mlg-hub

Hello .. thank you .. we did a small prototype based on the informations shared from that conversation

travisf

travisf OP

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.

travisf

travisf OP

I’ve been looking into this a bit more I think the multi-schema approach may work, however the major blocker I’m facing is how to move existing data into the new schemas? At the moment the app already has two customers that I need to somehow move into their respective schemas.
I’ve been looking at just doing this with SQL (INSERT into prefix1.customers select * from public.customer where: customer.location = "1234") or using Ecto.put_meta/2 to update the prefix and then insert it deleting the original record afterwards.
However; with both approaches you quickly run into constraint errors which wouldn’t be a problem for a few associations but this is a big legacy app it’s kind of turtles all the way down. Do I just need to bite the bullet and write all these as some kind of complex migration script or is there a better way?

— All posts loaded —

Where Next? Top

Trending in Discussions Top

AstonJ
The obligatory hello world thread! Who are you and where are you from? :stuck_out_tongue:
4663 61218 595
New
cblavier
Hey there, It’s been more than a year since we started using LiveView as our main UI library and building a whole library of UI componen...
515 12931 101
New
sntran
In my third year of college, around 2010, I found Erlang. A few years later, I found Elixir. I liked the ideas at once: many small proces...
New
gtcode
I’ve been tinkering with Lynx (by José) via hold_ledger. I used AI to write the code, a separate specification, the tests, and the Lean p...
New
AstonJ
A remark @Garrison made… ..has already inspired a thread for the technical side of reclaiming our internet, but there is of course anot...
New
ManuelGarcia
Hi everyone! I’m the developer of PaperForge, and I’ve been working on something alongside it that I’d like your opinion on. I’m conside...
New
bartblast
This is where the projects in the Hologram Showcase get posted: apps built with Hologram and libraries made for it. New ones go up usuall...
New

Other Trending Topics Top

GenericJam
Edit: 2026 May 15 - This post is archived. Mob is alive!! Main docs: mob v0.7.11 — Documentation A bit of explanation for the slightly c...
261 13183 112
New
mudasobwa
I fully migrated to my own harness from Anthropic/Gemini and I think it’s time to share it. Welcome DSH, the DeepSeek Harness, fully writ...
New
OndrejValenta
(I just needed to vent somewhere and LinkedIn is full of hope, or hype, I’m not sure which exactly) AI dream has many faces, but general...
New
kip
Localize is the next generation localisation library for Elixir. Think of it as ex_cldr version 3.0. The first version will be released ...
New
jimsynz
Beam Bots (or just BB for short) is a framework for building fault-tolerant robotics applications in Elixir using familiar OTP patterns. ...
New
mcass19
ExRatatui lets you cook up rich terminal UIs in Elixir, powered by Rust’s ratatui via Rustler NIFs. Build interactive terminal applicatio...
New

We're in Beta

About us Mission Statement

Options

Thread Display Mode




Thread Preview

Skip Thread Previews