Hi, I hope everyone's keeping well. It's been ages since I've been on the list. I do use SQLAlchemy from time to time, but now it generally works so well, that I don't have any questions to ask!
But I would appreciate some thoughts on the approach I've taken with a multi-tennant SaaS web app. It's a multichannel stock management system for online retailers. All the user data is attached to a merchant - products, variations, categories, orders, etc. It's important that one merchant cannot access data belonging to another merchant. When handling a request, the active merchant can be determined from the logged-in user, which is kept in thread local storage. So I started with lots of code like: db.Order.query.filter_by(merchant_id = twa.get_user().merchant_id) Now, this is fine, but it's repetitive, and it's risky for security - it just takes me to forget one filter_by merchant_id and we've got a security vulnerability. So, what I wanted to do is create a custom session that will do this automatically. It needs to do two things: 1) Any query object against an entity that has a merchant_id property is filtered on that 2) Any new object that has a merchant_id property has the property automatically set I don't think a session extension can do (1), so I created MySession subclassing Session, and passed this as class_ to sessionmaker. Here's my initial attempt at MySession: class MySession(sa.orm.Session): def query(self, *entities, **kwargs): query = super(MySession, self).query(*entities, **kwargs) for e in entities: if e.tables[0].name == 'user': continue if e.has_property('merchant_id') and twa.get_user(): query = query.filter(e.class_.merchant_id == twa.get_user().merchant_id) return query Now, I faced on major problem - seeing these errors: InvalidRequestError: Query.get() being called on a Query with existing criterion. As a temporary workaround, I edited query.py and disabled the check that causes this. That's got me going for now, although obviously a proper fix is needed. I haven't actually attempted (2) yet, but I will be trying that shortly. I'd really appreciate some feedback on this, particularly ideas to fix the InvalidRequestError. I think this is a very powerful technique that would be useful to many developers. Once my app is working I will see about writing a tutorial on the matter. Many thanks, Paul -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/wK5ljrQ7z4cJ. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.