Benefits of DataLoaders | Sage Active Developer

NEWNew Feature Recently Added. (Introduced in the last version as well as in the previous version)

The new version of the API leverages dataloaders, which is a significant advancement.
We recommend that you familiarize yourself with the benefits described on this page.

Context

GraphQL Hot Chocolate DataLoaders offer a powerful solution for efficiently fetching data in GraphQL applications.
By leveraging DataLoaders, developers can significantly reduce the number of database calls, thus minimizing the response time and improving the overall performance of the API.

A key advantage of using DataLoaders is their ability to address the N+1 query problem, a common challenge in GraphQL APIs where executing a query for a list of items results in a separate database call for each item.
Through batching and caching strategies, DataLoaders efficiently mitigate this issue, enhancing data retrieval processes and optimizing server performance.

Advantages

Batching: DataLoaders allow for batching multiple data requests into a single call, effectively solving the N+1 problem by reducing database overhead and network congestion. This leads to faster data retrieval and lower resource consumption.
Caching: DataLoaders cache loaded data for the duration of the request, directly addressing the N+1 problem by eliminating redundant database queries. If the same data is requested again, it can be served from the cache, further improving performance.
Reduced Overfetching and Underfetching: By enabling precise data fetching strategies, DataLoaders help in avoiding overfetching and underfetching scenarios. This means that clients fetch exactly what they need, no more, no less.
Concurrency Handling: DataLoaders handle concurrent data fetches efficiently, optimizing the data loading process and ensuring that the data is consistent and reliable.
Simplified Codebase: Implementing DataLoaders can lead to a more simplified and maintainable codebase. By abstracting the data fetching logic, developers can focus on business logic rather than data fetching optimizations.

Notes:

DataLoaders are particularly beneficial in scenarios with complex data models and relationships, where multiple round-trips to the database would otherwise be required.
Integrating DataLoaders into your GraphQL server setup with Hot Chocolate is straightforward and can significantly enhance the data fetching capabilities of your API.
Embracing DataLoaders aligns with GraphQL’s philosophy of efficient data fetching, making it an essential tool in the GraphQL ecosystem.

Example of query

Examining the provided GraphQL query and the benefits of DataLoaders, we can extract specific advantages of DataLoaders as demonstrated by this query:

query {
    accountingEntries(
        first:100
        where: {
            and: [
                {
                    or: [
                        {journalType: {code:{eq: "VTE"}}},
                        {journalType: {code:{eq: "ACH"}}}
                    ]
                },
                {date: {gte: "2023-01-01", lte: "2023-01-31"}}
            ]
        }
          order: [{ journalType:{code: ASC }}, { date: ASC }]
    ) {
        edges {
            node {
                date
                number 
                journalType{
                    code
                    name
                }                       
                accountingEntryLines {
                    subAccount{
                        code
                        name
                    }
                    description
                    debitAmount
                    creditAmount
                    order
                    accountingEntryThirdParty {
                        code
                        socialName
                    }
                    accountingEntryInvoice {                 
                        externalInvoiceNumber                  
                    }
                }
            }
        }
        totalCount
        pageInfo {
            hasNextPage
        }
    }     
}

Addressing the N+1 Problem with DataLoaders

One of the critical challenges in data fetching technologies, including GraphQL, is the N+1 problem. This issue arises when a query leads to multiple subsequent data fetches for each item in a collection, significantly increasing the number of database queries. Unlike REST, where the N+1 problem can proliferate across multiple clients, GraphQL centralizes this issue on the server. The advantage here is that we only need to tackle this problem once, on the server side, rather than in every client implementation.

DataLoaders play a crucial role in mitigating the N+1 problem by batching multiple data requests into a single database call and caching the results for the duration of the request. This approach not only reduces the number of direct database hits but also ensures efficient data retrieval without over-fetching or under-fetching. By aggregating data requests and serving repeated requests from cache, DataLoaders significantly cut down the overall number of database queries, directly addressing the N+1 problem and enhancing API performance.

This sophisticated handling of data fetching requests demonstrates the strength of GraphQL with Hot Chocolate’s implementation, providing a scalable solution to a common issue that affects data-driven applications. By efficiently managing the N+1 problem, DataLoaders ensure that GraphQL APIs can serve complex data models without compromising on performance or data integrity.

Batching Requests: The query likely benefits from DataLoaders’ ability to batch requests, especially when fetching related data like journalType, accountingEntryLines, subAccount, accountingEntryThirdParty and accountingEntryInvoice.
Without DataLoaders, each of these fetches could potentially result in a separate database query, significantly increasing the load on the database and the response time.
Benefit: DataLoaders combine these into fewer database calls.
Efficient Data Loading with Complex Filtering: The query applies complex filtering (using and and or conditions on journalType and date) and sorting (ordering by journalType.code and date in ascending order).
DataLoaders can efficiently manage such complex operations by optimizing the data fetching strategy.
Benefit: Reduce the computational overhead on the server.
Caching for Repeated Access: In scenarios where the same journalType, subAccount, or other related entities are accessed multiple times within the same query or across different queries in the same request, DataLoaders’ caching mechanism can serve this data from cache.
This avoids redundant database hits for data that has already been fetched during the request lifecycle.
Benefit: Reduces the number of database queries, leading to faster response times and lower server load by utilizing cached data for repeated access.
Reducing Overfetching and Underfetching: The query specifies exactly what fields are needed for each object, aligning with the GraphQL philosophy of fetching exactly what is needed.
DataLoaders facilitate this by allowing efficient retrieval of nested entities like accountingEntryLines and their related subAccount and accountingEntryInvoice information, ensuring that the data fetched is precisely what the client requires, without unnecessary data transfer.
Benefit: Enhances API efficiency and minimizes unnecessary data transfer by ensuring that only the required data is fetched, aligning with client needs.
Handling Concurrency and Data Consistency: The query might involve concurrent data fetches, especially for related entities within accountingEntries.
DataLoaders ensure that these concurrent fetches are handled efficiently, maintaining data consistency and reliability across the fetched data, even in complex queries with multiple nested entities and relationships.
Benefit: Improves performance and ensures data integrity by efficiently managing concurrent data requests and maintaining consistency across related entities.
Streamlined Data Retrieval Process: The query demonstrates a streamlined data retrieval process enabled by DataLoaders, where data is fetched in an optimized manner according to specified filters and orders.
This exemplifies how DataLoaders contribute to a simplified and more maintainable codebase by abstracting away the complexities of data fetching and optimization.
Benefit: Simplifies the development process and boosts API performance through optimized data fetching, reducing the complexity and enhancing maintainability of the codebase.

Sample of possible business examples enabled by dataloaders

Below are three examples of queries demonstrating the new capabilities of dataloaders.
A single query is now sufficient to return all the desired information, eliminating the need to search for elements based on the id field values of the main query, streamlining the data retrieval process significantly.

Journal entries for a journal and a month

## Thanks to the new logic of data loaders, it's possible to expose, filter, or sort fields in child subresources or parent subresources.
## Here's a query to retrieve the journal entries for two journals for one month ordered by journal code and then by date.

query {
    accountingEntries(
        first:100
        where: {
            and: [
                {
                    or: [
                        {journalType: {code:{eq: "VTE"}}},
                        {journalType: {code:{eq: "ACH"}}}
                    ]
                },
                {date: {gte: "2021-01-01", lte: "2023-01-31"}}
            ]
        }
          order: [{ journalType:{code: ASC }}, { date: ASC }]
    ) {
        edges {
            node {
                date
                number 
                journalType{
                    code
                    name
                }                       
                accountingEntryLines {
                    subAccount{
                        code
                        name
                    }
                    description
                    debitAmount
                    creditAmount
                    order
                    accountingEntryThirdParty {
                        code
                        socialName
                    }
                    accountingEntryInvoice {                 
                        externalInvoiceNumber                  
                    }
                }
            }
        }
        totalCount
        pageInfo {
            hasNextPage
        }
    }     
}

Third Party Ledger

## Thanks to the new logic of data loaders, it's possible to expose, filter, or sort fields in child subresources or parent subresources.
## Here is a query to retrieve the Third-Party Ledger lines not lettered for a customer for a month.

query {
    accountingEntryLines(
        first:100
        where: {
        and: [
           {accountingEntryPayment:{matchingLetter:{eq: null}}}
           {accountingEntryThirdParty:  { code:{eq: "CARAT" }}}
           {accountingEntry:{date: {gte: "2021-01-01", lte: "2023-01-31"}}}
        ]
      }
       order: [{ accountingEntry:{date: ASC }}]
    )

     {
        edges {
            node {
                accountingEntry{
                    date
                    number
                    journalType{
                        code
                        name
                    }
                }
                accountingEntryPayment{
                    matching
                }

                description
                debitAmount
                creditAmount
            }
        }
        totalCount
        pageInfo {
            hasNextPage
        }
    }     
}

General Ledger

## Thanks to the new logic of data loaders, it's possible to expose, filter, or sort fields in child subresources or parent subresources.
## Here a query to retrieve the General ledger lines for a month

query {
    accountingEntryLines(
        first:100
        where: {
        and: [       
           {accountingEntry:{date: {gte: "2021-01-01", lte: "2023-01-31"}}}
        ]
      }
        order: [
            {subAccount: {code: ASC}},
            {accountingEntry: {date: ASC}}
            {accountingEntry:{journalType:{code: ASC }}}
        ]
    )

     {
        edges {
            node {
                subAccount{
                    code
                    name
                }
                accountingEntry{
                    date
                    number
                    journalType{
                        code
                        name
                    }
                }
                accountingEntryThirdParty{
                    code
                }
                description
                debitAmount
                creditAmount
            }
        }
        totalCount
        pageInfo {
            hasNextPage
        }
    }     
}