Marhsmallow-SQLAlchemy + Sharded read/write databases

[shirblc]

Hi!

In our project we're using SQLAlchemy's ShardedSession to handle read/write replicas handling. It works fine with SQLAlchemy and Flask-SQLAlchemy (all CRUD operations run on the write replica unless set_shard() is called on the query), but Marshmallow-SQLAlchemy seems to be having issues dumping the results. In particular, it seems to have issues around dumping nested fields.

Since set_shard is a query method, rather than a session method, it's not passed down to the queries Marshmallow-SQLAlchemy makes to get the nested objects. So when Marshmallow-SQLAlchemy dumps the results it either:

• errors because it's trying to grab one_or_none() and there are two shards to read from
• correctly grabs it from the default database (which makes the read replica partially useless)

So it seems The question is, is there a way to pass the query's execution_options (where the shard ID is) to the queries Marshmallow-SQLAlchemy makes for the nested objects?

Basic reproduction - https://github.com/shirblc/marshmallow-sqla-sharded

Thank you!

EDIT:
Also, when we've replaced ShardedQuery with our own custom class that overrode one_or_none() to add a shard_id, SQLAlchemy worked fine, but Marshmallow-SQLAlchemy still failed. Is Marshmallow-SQLAlchemy using another query class, despite the Session's configuration?