Apply a global filter to all tables for every query in SQLAlchemy

Question

We are trying to setup a SaaS service that supports multi-tenancy in a shared database and schema. What we are planning is to have a tenant_id column on all our tables. what I would like to do, is without the developer having to write any extra code is for my queries to automatically filter all involved tables by this tenant id. Is there a transparent way to achieve this in SQL Alchemy?

I found how you can override the default query object:

self.session = sessionmaker(bind=engine, query_cls=TenantLimitingQuery)

But inside that TenantLimitingQuery how can apply it to all involved tables?

class TenantLimitingQuery(Query):
    def get(self, ident):
        #apply filter here

My tables have the same column to identify the tenant called tenant_id so in that get function i need to filter by tenant_id=current_tenant_id

Answer 1

This is outlined in the usage recipes wiki, reproduced here:

from sqlalchemy.orm.query import Query

class LimitingQuery(Query):

    def get(self, ident):
        # override get() so that the flag is always checked in the 
        # DB as opposed to pulling from the identity map. - this is optional.
        return Query.get(self.populate_existing(), ident)

    def __iter__(self):
        return Query.__iter__(self.private())

    def from_self(self, *ent):
        # override from_self() to automatically apply
        # the criterion too.   this works with count() and
        # others.
        return Query.from_self(self.private(), *ent)

    def private(self):
        mzero = self._mapper_zero()
        if mzero is not None:
            crit = mzero.class_.public == True

            return self.enable_assertions(False).filter(crit)
        else:
            return self

The idea is to apply the filter on demand, when the query object is iterated through.

If you want the filter to be applied to relationships as well, you'll need to use this recipe instead.