Multi-tenant with Postgres schemas and SQLAlchemy ORM – connection, session and options sanity

  multi-tenant, postgresql, python-3.x, sqlalchemy

In a multi-tenant application, I would like to make use of PostgreSQL schemas with SQLAlchemy 1.3.

It seems convenient from a SQL perspective: table names are simply to be prefixed with the schema name to achieve tenant isolation, eg customer becomes tenant1.customer.

How does that translate in SQLAlchemy?

I do a sessionmaker once as per the documentation, during application startup. Connections can be pooled. The DB URL is the "root" of the database so that I can "see" all schemas and switch dynamically, on a per query basis:

engine = create_engine(database_url)
session_factory = sessionmaker(bind=self.engine)

To achieve scoping on a schema, I found a hint to set execution_options with schema_translate_map on the connection, like so:

    "schema_translate_map": {"per_user": "account_one"}})

# will query from the ``account_one.user`` table

It seems to work, but is there any risk of side effect, race condition or otherwise connection pollution (when the connection is returned to the pool) if I modify the connection on the session? The example I found in the official documentation sets the execution_options earlier, on engine.connect(), so I am unsure if we are talking about the same thing.

Another suggestion is to set the search path with SET search_path but I keep it as a last resort, as it taints the connection, unless you reset the search path to its original value afterwards. Also it does not look good from a performance perspective, and it seems clumsy at best – as opposed to simply prefixing table name with the schema name.

Finally, in version 1.4, there is a new Query.execution_options that looks like a good option but it is still a beta release so I cannot rely on that yet for production.

Any advise on the sanity of the proposed options? Does the 1.4 construct change the game?

Source: Python-3x Questions