SQL query to include only requested graphql query columns

[eastuto]

Motivation

The SELECT SQL statement includes all of the database columns regardless of whether the graphql query has actually requested this data. Is this what is actually happening under the bonnet? Seems quite inefficient and defeats one of the benefits of graphql over REST, namely only querying from the database the fields that you actually need to consume.

Potential performance improvements, particularly in enterprise systems operating at scale and under load.

Proposed Solutions

Construct the SQL query to only include the data requested in the graphql query.

[billy1624]

Hey @eastuto, thanks for the filing!! I think this is a important performance tuning for database table with large number of columns.

The main difficulties is that we need to find a "dynamic" data type to store the returned result. I guess serde_json::Value might be a good candidate but we need to investigate.

@karatakis thoughts? Asking because you are the expert in and the author of seaography :P

[karatakis]

@eastuto Good optimization suggestion, thanks for the filling.

async-graphql provides us the API to parse the query and utilize the information the query contains to construct a custom select query with only the provided fields [1][2]. Then we can execute the query and store it into a dynamic type like JsonValue or QueryResult as @billy1624 already mentioned [3][4][5].

1. https://docs.rs/async-graphql/latest/async_graphql/dynamic/struct.ResolverContext.html#method.look_ahead
2. https://www.sea-ql.org/SeaORM/docs/advanced-query/custom-selectv https://www.sea-ql.org/SeaORM/docs/advanced-query/custom-select
3. https://www.sea-ql.org/SeaORM/docs/basic-crud/raw-sql/#query-by-raw-sql
4. https://docs.rs/sea-orm/latest/sea_orm/struct.QueryResult.html
5. https://docs.rs/sea-orm/latest/sea_orm/query/enum.JsonValue.html

TLDR: The issue can be solved, it will be planned in future versions