On Thu, Nov 15, 2018 at 7:51 PM Eric Smith <e...@esmithy.net> wrote: > > Are there any good examples/patterns for implementing multitenancy with > SQLAlchemy? > > I'm in the process of converting a single-tenant web application to support > multiple tenants. Using a schema-per-tenant strategy seems appealing -- if > you can "activate" the schema for a particular tenant on a request, the > existing database access code can remain unchanged, and not leaking data > between tenants seems pretty manageable. I've read about schema translation > as a potential approach (though I think I would prefer that to happen at a > session scope rather than connection), as well as using the PostgreSQL > search_path. > > While I'm academically still interested in schema-per-tenant, my expected > scale (tens of thousands of tenants) is pushing me toward a row-per-tenant > strategy instead. Adding a tenant_id to the appropriate models is > straightforward, but the need to manually add .filter_by(tenant_id=tenant_id) > to every query feels cumbersome and error-prone. > > Maybe I should be looking at the PreFilteredQuery or GlobalFilter recipes? I > haven't quite gotten my head around those to know if they apply. > > Maybe there's a way to use a query_property on the model Base? > > I also ran across this experimental project that seems intriguing: > https://github.com/mwhite/MultiAlchemy But I'm not sure if that is a solid > strategy or if there is something egregiously wrong with it. > > Any advice, references or direction would be appreciated!
It sounds like you saw the schema-based multitenancy pattern which you can see at https://docs.sqlalchemy.org/en/latest/core/connections.html#translation-of-schema-names , which is made possible by the schema_translate_map feature. That can absolutely be per-Session like this: sess = Session() sess.connection(execution_options={"schema_translate_map": {...}}) or like this conn = engine.connect() conn = conn.execution_options(....) sess = Session(conn) > Maybe I should be looking at the PreFilteredQuery or GlobalFilter recipes? I > haven't quite gotten my head around those to know if they apply. they do but I think a more modern way to do this is to use the before_compile event: https://docs.sqlalchemy.org/en/latest/orm/events.html?highlight=before_compile#sqlalchemy.orm.events.QueryEvents.before_compile the example there shows sort of the equivlent of PreFilteredQuery. The wiki should be updated to refer to this event hook. > > Thanks, > Eric > > -- > 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.
