Hi all, I'm working on a multi tenant web app with SQLAlchemy and I would like to use a tenant_id column on some tables to isolate data per tenant. Ideally, filtering on and adding this tenant_id would be automatic. I found this <https://www.infoq.com/presentations/saas-python?utm_source=infoq&utm_medium=slideshare&utm_campaign=slidesharelondon> talk by the creator of Flask that briefly mentions a way to do this with sqlalchemy. This <https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/FilteredQuery> recipe goes into more detail on this approach but also points out some flaws. Primarily that it will not handle update or delete queries. To me this feels like a major flaw, a developer, used to having tenant_id handled automatically, may easily forget when performing an update or delete, thus creating a vulnerability. It proposes to solve the update and delete issue by using before_cursor_execute() event handler. However, as far as I can tell, this approach would involve editing the SQL text directly, defeating much of the point of using SQLAlchemy. Not as much of an issue but inserts are also not handled.
I also found this <https://github.com/mwhite/MultiAlchemy> library that tries to solve the problem, however it is described as experimental and hasn't been updated in 5 years. It seems to have the problems of the above solution as well. It seems to me that this is a common pattern and I'm surprised there aren't good, solid solutions out there for SQLAlchemy. This <https://github.com/citusdata/django-multitenant> library supports this approach for Django. Ideally I would love to have a session that is scoped to a tenant and automatically applies a tenant_id on every select, update, delete, and insert so that queries can be written without the need to consider tenant at all. Is this feasible? I would greatly appropriate if anyone could help or point me in the right direction. Thanks! -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
