N + 1 round trip problem

[itaied246]

Does this library handle nested models (joins) in a single query from the server to the DB?
For example

user {
  id
  posts {
    id
  }
}

[yfilali]

No, it is something you'll need to handle on your own. See a few code samples to deal with this here: graphql-python/graphene#348

Use the code from that issue to know what is being asked at the very first resolve level, then combine all of it into the query you return in the resolve (using subqueryload or joinedload)

[yfilali]

In case you are still looking for a complete solution:
https://yacine.org/2017/02/27/graphqlgraphene-sqlalchemy-and-the-n1-problem/

[Cito]

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.

[chanind]

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

[flewellyn]

@yfilali Is that code you wrote for optimize_resolve under the same MIT license as the rest of the library? I would love to make use of it.

[yfilali]

@flewellyn absolutely!

[mekhami]

@yfilali I can't seem to access your website, is it still up? Would you mind posting the contents here if not?

[yfilali]

@mekhami it seems up at the moment so it could have been temporary or regional (I didn't get any alerts either)

[mekhami]

@yfilali sorry to bother again but it's still down and I'm not able to access it. Could you post the text of it here or somewhere? I remember it being extremely valuable but didn't have the time then to implement it.

[ocavue]

@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.

[yfilali]

@mekhami it's completely fine. Sorry about that. I'll have to find time to work on my personal site's uptime at some point :)

[iksteen]

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

[flewellyn]

@iksteen Your eager loader looks fascinating. Question: will it be able to handle custom-defined object types that are not SQLAlchemy models? I have a couple defined to handle special cases, like geoalchemy2 geometries.

[jnak]

Just a heads that there is a PR currently opened that addresses the N+1 problem. Would love to get your input. Cheers.

See #253 (review)