Row/Column Based Multi-Tenancy in SQLAlchemy using a Shared Schema

Question

I'd like to build a multi-tenancy application using Flask + SQLAlchemy. The official SQLAlchemy docs suggest that, to use multi-tenancy the tables should be distributed over 1 scheme per tenant and handle the different tenants on engine level.

Maintaining multiple schemes seems a bit bloated to me and I'd like to know if the following approach using the same tables for all tenants is feasible if designed correctly and if not, why not:

• Those tables with tenant-owned records have a non-nullable column tenant_id that indicates which tenant "owns" the row.
• Create/Update statements (INSERT and UPDATE) automatically set the value of this column to the current tenant.
• Reading/Deleting queries (SELECT or DELETE) queries automatically add a WHERE tenant_id=:current tenant clause/filter to the SQL.
• The value of tenant_id could be derived from a JSON Web Token.
• This automatically set value/where clause could be managed in the background, e.g. by a custom session like in this answer or using a pre-commit hook.

I barely find any information on this approach (excecpt for this one package called MultiAlchemy which seems to work similar to my description but has been archived and not updated for 7 years). My gut feeling says, there's a reason for it.

tl;dr: Why to not use shared schemes for multi-tenancy in SQLAlchemy?

Answer 1

As you might have already found (including the other answers to the question), there are multiple approaches to Multi-Tenancy implementation with their pros and cons without a clear winner. What is Multi-Tenant Architecture is just another, but a very good overview of several options, which I will not copy from, as reading the whole article makes sense.

With all pros and cons of all approaches, it comes down to the personal preference and experience of the team.

---

Personally I would very much like to have an extension to sqlalchemy implementing the A Single, Shared Database Schema multi-tenancy, which MultiAlchemy. It is not clear why the project was archived, but I would be interested to create a new one to support this feature based on:

• SQLAlchemy versions 1.4, 2.0 only in order not bother supporting soon to be legacy versions
• ideas from MultiAlchemy
• ideas from FilteredQuery recipe