We use the Association Object pattern in SQLAlchemy to deal with Many-To-Many relationship.

Implementation

Check out SQLAlchemy - Association Object for more info.

Association Proxy

Association Proxy is used to map values across Many-To-Many tables.

Check out SQLAlchemy - Association Proxy for more info.

Lazy Load Of Association Proxy

Like normal relationship ORM attributes, the AssociationProxy columns are also designed as lazy-load attributes. The loading could happened in several points:

# consider we have a `User` class with `roles` as an AssociationProxy attr.

roles_of_this_user: ORMRole = user.roles    # Trigger lazy load

for r in roles_of_this_user:                # Trigger lazy load again!
    print(r)

From example above, we know:

• Access AssociationProxy attribute itself will emit lazy querying.
• If AssociationProxy could be iterate or accessed, then the access "inside" this attribute will also emit querying.

One way to make these attributes works in SQLAlchemy Asyncio pattern is to put all possible access to an AssociationProxy attributes inside AsyncSession.run_sync(). Bellowing is an example in production:

# member function of `User` class
def check_role(self, ss: AsyncSession, roles: List[str])
	roles_of_this_user: list[Role] = await ss.run_sync(lambda ss: self.roles)

	def _check_user_has_allowed_roles(ss):
	    # iterate through user's roles.
	    # once one of the user role is in the allowed role list, pass the test
	    for user_role in roles_of_this_user:
	        if user_role.role_name in roles:
	            return True

	    return False

	return await ss.run_sync(lambda ss: _check_user_has_allowed_roles(ss))

Soft Delete Of Relationship

All soft delete code implementation should follow with the Soft Deletion Guide of this project when deal with soft deletion of the Association ORM Table.

We may need to call session.refresh() after updating relationship and association to reflect the changes into ORM class instances.