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:
session.connection(execution_options={
"schema_translate_map": {"per_user": "account_one"}})
# will query from the ``account_one.user`` table
session.query(User).get(5)
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 advice on the sanity of the proposed options? Does the 1.4 construct change the game?
