graphene-sqlalchemy: N + 1 round trip problem

Another option is to use a lazy-loader for SQLAlchemy that is more intelligent about bulk loading. We wrote a custom loader that will inspect the DB session and bulk-load all relations on similar models whenever a relation is lazy-loaded. The result should be the equivalent to using subqueryload on all relations upfront, but without needing any custom code to do so. The loader we wrote is available here in case it’s helpful to anyone else: https://github.com/operator/sqlalchemy_bulk_lazy_loader

Thank you for that blog post @yfilali, that was really helpful.

Your optimize_resolve method works pretty well for me. By using joinedload instead of subqueryload, I was able to reduce a deeply nested GraphQL query into a single database query. I had to add all paths, however, not only the leaves (simply removed the if not p.isLeaf part when assembling the joins).

Another small problem is that in resolve_related you need to translate the field names from GraphQL (camel case) to SQLAlchemy (snake case) with field = to_snake_case(field), otherwise it doesn’t work with table columns with underscores in them. You can import the to_snake_case method from graphene.utils.str_converters.

I feel something like this should be really integrated into graphene-sqlalchemy. Or at least it should provide some helper functions which make it easy to implement such optimized querying, with various loading strategies as options. After all, one of the main advantages of GraphQL over REST is that it solves the N+1 problem. This advantage would be greater if it existed also on the level of the database, not only on the network level.

A bit late to the party. Also: shameless self-promotion. But I’ve just published an article about this subject which includes a solution where you specify what and how to eagerly load related data in a node’s metadata on a per-field basis: https://blurringexistence.net/graphene-sqlalchemy-n-plus-1.html

(SQLAlchemy 1.4.40)

I’ve activated batching = True on all my graphene-sqlalchemy translated models, and whenever I try to access some relationships, the graphql resolver is failing with “future is attached to a different loop”

class GConstruct(GraphQLPrimaryKeyIsUUIDMixin, AuthorizeCreatorMixin):
    class Meta:
        model = Construct
        batching = True

class GConstructPart(GraphQLPrimaryKeyIsUUIDMixin, AuthorizeCreatorMixin):
    class Meta:
        model = ConstructPart
        batching = True

class GPart(GraphQLPrimaryKeyIsUUIDMixin, AuthorizeCreatorMixin):
    class Meta:
        model = Part
        batching = True

This is the GraphQL Query:

{
readConstruct(constructId:"ad500676-5503-aac0-ec38-d3097958c6d0")
   {
    id
    constructParts { <--- this is a relationship
      id
      part { <-- this is a relationship
        id
      }
    }
  }
}

I get an error on the part resolver (but also in several other places in the system, basically it’s not working correctly in most places)

Task <Task pending name='Task-347' coro=<ExecutionContext.resolve_field.<locals>.await_result()
 running at /home/cadu/.local/share/virtualenvs/kernel-backend-N3YlRYFF/lib/python3.10/site-
packages/graphql/execution/execute.py:625> cb=[gather.<locals>._done_callback() at
 /home/cadu/.pyenv/versions/3.10.6/lib/python3.10/asyncio/tasks.py:720]> 
got Future <Future pending> attached to a different loop

This is the traceback:

Traceback (most recent call last):

> File "/home/cadu/.local/share/virtualenvs/kernel-backend-N3YlRYFF/lib/python3.10/site-packages/graphql/execution/execute.py", line 625, in await_result
    return_type, field_nodes, info, path, await result
    │            │            │     │           └ <coroutine object get_batch_resolver.<locals>.resolve at 0x7fa8a930d7e0>
    │            │            │     └ Path(prev=Path(prev=Path(prev=Path(prev=None, key='readConstruct', typename='Query'), key='constructParts', typename='GConstr...
    │            │            └ GraphQLResolveInfo(field_name='part', field_nodes=[FieldNode at 116:141], return_type=<GrapheneObjectType 'GPart'>, parent_ty...
    │            └ [FieldNode at 116:141]
    └ <GrapheneObjectType 'GPart'>
  File "/home/cadu/.local/share/virtualenvs/kernel-backend-N3YlRYFF/lib/python3.10/site-packages/graphene_sqlalchemy/batching.py", line 87, in resolve
    return await loader.load(root)
                 │      │    └ <ConstructPart PKID=01d1cab8-2bb3-b064-1250-54c9940fd582>
                 │      └ <function DataLoader.load at 0x7fa8df12f910>
                 └ <graphene_sqlalchemy.batching.get_batch_resolver.<locals>.RelationshipLoader object at 0x7fa8b09d9960>

RuntimeError: Task <Task pending name='Task-391' coro=<ExecutionContext.resolve_field.<locals>.await_result() running at /home/cadu/.local/share/virtualenvs/kernel-backend-N3YlRYFF/lib/python3.10/site-packages/graphql/execution/execute.py:625> cb=[gather.<locals>._done_callback() at /home/cadu/.pyenv/versions/3.10.6/lib/python3.10/asyncio/tasks.py:720]> got Future <Future pending> attached to a different loop

This query is happening from the GraphQL Playground from a FastAPI app

@mekhami I think it’s valuable and I can’t access it too. I read it from google cache several months ago and I do think it’s worth to read. But the catch itself is 404 now, maybe because the original page is down for too long. I saved the cache just in case: GraphQL_Yacine.zip (github don’t allow .htm file so I compress it)

@yfilali I will delete the cache page if you are not ok with this.