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 <https://docs.sqlalchemy.org/en/latest/core/connections.html?highlight=schema_translate_map#schema-translating> 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! 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.
