hi there - tenant ID that is done on a per-schema basis is handled by the schema name translation feature at https://docs.sqlalchemy.org/en/latest/core/connections.html?highlight=execution_options#schema-translating
for a SELECT criteria you would use the filtered query which is at https://github.com/sqlalchemy/sqlalchemy/wiki/FilteredQuery and has been modified and simplified to use events (note the bitbucket wiki is obsolete, it's not supposed to be up). The issue with query.update() and query.delete() not being included can be addressed once we add event hooks that are analogous to before_compile() event hook for Query into the persistence.BulkUpdate and persistence.BulkDelete classes. The SQLAlchemy project would welcome contributors who want to work on this as it's quite simple to add. On Tue, Jan 22, 2019 at 4:49 PM Daniel Lister <mrhu...@gmail.com> wrote: > > 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 talk > by the creator of Flask that briefly mentions a way to do this with > sqlalchemy. This 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 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 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. -- 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.
