5 ms·
An interesting consequence of this characteristic of SQLite is that it makes it a really good fit for GraphQL. GraphQL has the N+1 queries problem pretty much
by simonw 6y ago
An interesting consequence of this characteristic of SQLite is that it makes it a really good fit for GraphQL.
GraphQL has the N+1 queries problem pretty much baked into it. You can work around it to a certain extent with a lot of effort implementing data loader patterns, but someone will still likely be able to cow up with a query that triggers a cascade of SELECTs.
Or, because "many small queries are efficient in SQLite", you could use that instead.
I built a Datasette plugin that exposes a GraphQL API to an existing SQLite database and it works so well that I switched from being a GraphQL skeptic to a GraphQL advocate.
https://simonwillison.net/2020/Aug/7/datasette-graphql/ https://simonwillison.net/2020/Aug/7/datasette-graphql/
Here's an example nested GraphQL query that executes 32 select queries. It's pretty snappy: https://datasette-graphql-demo.datasette.io/graphql?query=%7B%0A%20%20repos(first%3A%2010%2C%20search%3A%20%22datasette%22%2C%20sort_desc%3A%20stargazers_count)%20%7B%0A%20%20%20%20totalCount%0A%20%20%20%20pageInfo%20%7B%0A%20%20%20%20%20%20endCursor%0A%20%20%20%20%20%20hasNextPage%0A%20%20%20%20%7D%0A%20%20%20%20nodes%20%7B%0A%20%20%20%20%20%20full_name%0A%20%20%20%20%20%20description%0A%20%20%20%20%20%20stargazers_count%0A%20%20%20%20%20%20created_at%0A%20%20%20%20%20%20owner%20%7B%0A%20%20%20%20%20%20%20%20name%0A%20%20%20%20%20%20%20%20html_url%0A%20%20%20%20%20%20%7D%0A%20%20%20%20%20%20issues_list(first%3A%203)%20%7B%0A%20%20%20%20%20%20%20%20totalCount%0A%20%20%20%20%20%20%20%20nodes%20%7B%0A%20%20%20%20%20%20%20%20%20%20title%0A%20%20%20%20%20%20%20%20%20%20created_at%0A%20%20%20%20%20%20%20%20%7D%0A%20%20%20%20%20%20%7D%0A%20%20%20%20%7D%0A%20%20%7D%0A%7D%0A https://datasette-graphql-demo.datasette.io/graphql?query=%7...
- tehlike 6y agohasura does fairly good query optimization using postgres json_agg, that's my preferred way - if more graphql engines did the same that'd be amazing.
- rtorr 6y agoIt does not have that problem baked into it since it relies on resolvers. It would have to be a choice to have that problem.
- ubercore 6y agoA straightforward implementation certainly would have that problem. You have to put a decent amount of effort in to reduce query count to fill a nested query graph.
- rtorr 6y agoNot really. A straightforward implementation has nothing to do with database usage. A resolver is an endpoint. You can leverage that like you can in any other http backend server.
- ubercore 6y agoNested resolvers can incur extra queries as you follow down the tree, unless you put extra effort into pre-fetching what child nodes need, or some kind of data loader as GP suggested. query { parent { child { field } } } A straightforward, naive implementation would resolve `parent`, then a resolver for `child` would execute, then for `field`. If `child` is a table linked by an FK, a second query would be executed, unless you pre-fetch the results through a join when resolving `parent`.
- wongarsu 6y agoIn a GraphQL API backed by a single database you could write resolvers that analyze the entire query and rephrase it as a single database query. But I'm not aware of anyone doing this. The common/straight forward solutions resolve each layer after each other, which you can optimize to 1 DB query per layer of the GraphQL query using dataloader patterns, with further optimizations for known common patterns.
- ex3ndr 6y agoNo, replacing sqlite with leveldb/rocksdb/lmdb will give you ~10-50x improvement.
- pdimitar 6y agoI'm interested. Any links with benchmarks or example applications demonstrating the difference?