Disintegrated Parts

#software-development #dotnet #sql #graphql #data-storage

Trigger warning:
In this article I am bashing quite a bit on traditional REST api’s. Not that you can’t have a great architecture with REST api’s, but from my experience these types of API’s are badly implemented more often than not. Same goes for the tech described in this article. If used incorrectly you’ll end up voluntary amputating one of your limbs.

As a friend noted: “Talk to REST and get a certain object, or ask GraphQL and retrieve some information”.

0. Contents

  1. Introduction
  2. Background
    2.1 Cursor Based Pagination
    2.2 Connection Specification
    2.3 Specification, but simplified
    2.4 The Connection’s Structure
    2.5 Generating Cursors
  3. Overview
  4. Architecture
  5. The GraphQL Connection Endpoint
  6. Data Retrieval
    6.1 [Contextual] Model and Graph Types
    6.2 Resolving Arguments
    6.3 Data Filters
    6.4 Slicing for Pagination
    6.5 Creating the Connection object
  7. Overview of a real-world implementation
    7.1 The Connection
    7.2 Data access function
    7.3 Defining order on a connection
  8. Other Resources

1. Introduction

Pagination is always a topic that’s a bit tricky to tackle. The goal is to give as much freedom as possible for (client side) queries, while staying in control of the data that goes out.

GraphQL is no difference, but thankfully the Relay extension has been developed on top of GraphQL. Relay consists of a set of conventions which solidifies the GraphQL specification a bit more so that client side tools can quickly scaffold application logic for said imaginative api.

For the assumptions Relay makes about a GraphQL server, see this document.

2. Background

While quite poorly documented in the GraphQL.NET docs (at the time of writing), functionality for Relay compatible endpoints has been implemented. Some of this logic resides in the main graphql-dotnet package, while other tools and helpers reside in the relay package.

Because Relay is only a specification of assumptions about interfaces and available methods, it is fairly easy to implement yourself. Consider most of the helper methods provided by the graphql-dotnet/relay package as suggestions, and think about writing specialized variants which suit your use-cases. More on that later.

If you are already familiar with the design philosophy that goes with GraphQL, and indirectly with the graphql-dotnet package, feel free to skip to the overview.

2.1 Cursor Based Pagination

The biggest part of the Relay specification consists of details on the Connection types. A connection is a type which which describes a list of objects, and arguments to filter and slice this list. The arguments available in a connection by default:

These 4 arguments make up for a powerful concept which is called cursor based pagination. With this model every object has an unique identifier (you most certainly already have one already anyway), which you can use in order to create slices of data.

These slices of data are relative to one another. For the first call to the api you could provide the first argument to a connection. For all subsequent calls you would use the cursor of the last element in this list to retrieve a new slice of data.

This type of pagination, although it takes a bit more work implementing than traditional offset based pagination, is a delight to work with. On the client side it allows you to implement both traditional page based pagination methods, as well as more progressive and user-friendly paradigms. Another improvement over offset based pagination is that this method works fine with realtime data. A lot of complexities that are inherent to combining realtime data and offset based pagination just simply disappear.

If you look on Google you’ll find plenty of reasons why we should stop doing offset based pagination. Honestly I do not care about the technical implications at all, but the thing is that it’s just not user-friendly. The beginning of this article digs into that a bit deeper.

2.2 Connection Specification

For a detailed specification about connections and cursors, see this document: https://facebook.github.io/relay/graphql/connections.htm. Item 4.3 of the Relay specification covers expected behaviour of the pagination algorithm.

2.3 Specification, but simplified

The specification describes the way data should be retrieved. The following steps could be followed:

  1. Apply your data filtering operations
  2. If after is specified:
    • Take all data starting from this cursor
  3. If before is specified:
    • Take all data before this cursor
  4. If first is specified:
    • Take the specified amount of entities from the beginning.
  5. If last is specified:
    • Take the specified amount of entities from the end.
  6. Return data

The specification defines the cases where all parameters (after, before, first and last) are specified. There are few use-cases where using all these parameters at the same time will make any sense at all. It does provide some consistency and predictability to the use of cursors, though.

While it is possible to run all filter operations on the .NET runtime, I would totally NOT recommend doing so. It would have the implication that all data has to be loaded locally, which does seem to result in the infamous OutOfMemoryException in my case. If it works for you now but your data starts scaling later, it would be incredibly difficult to keep it all running smoothly after a while. I recommend to invest some time researching the different options for cursor based pagination within your data provider. E.g. MsSql, MySql, ElasticSearch or whatever your tool of choice is. If you still decide you want to shoot your own foot off and do pagination in-memory, go ahead. There are some methods available in the graphql-dotnet/relay project for which I have created the following extension method:

public static Connection<TSource> ToConnection<TSource, TParent>(
    this IEnumerable<TSource> items,
    ResolveConnectionContext<TParent> context,
    int sliceStartIndex,
    int totalCount,
    bool strictCheck = true)
{
    return ToConnection(items, context, sliceStartIndex, totalCount, strictCheck);
}

But again, this is definitely the unfun way to do it! If this is all you’ve been looking for you can close this article now. This article will further elaborate on offloading these filter and slice operations to an external data-store.

2.4 The Connection’s Structure

The connection is not only these four arguments you can slice your data with. Another part of the connection types are some subtypes to help query through the data. The main structure looks as follows:

friends(first: 10, after: "opaqueCursor") {
  edges {
    cursor
    node {
      id
      name
    }
  }
  pageInfo {
    hasNextPage
  }
}

As taken from this example.

Connection endpoints created with the graphql-dotnet project have the following default structure (see the ConnectionType class):

2.5 Generating Cursors

It’s important to note that the cursors used in a GraphQL connection are defined as a string type. This means that you can virtually put anything in there. For consistencies sake, and in order to keep the back-end and the front-end totally separated, it’s recommend to encode your ‘primary keys’ in a base64 representation. This makes the front-end less reliant on your primary key type, and allows you to switch or combine many different data sources in a consistent way.

After all designing api’s involves a lot of human interaction design.

As most of my infrastructure is build around Guid’s as unique identifier I have two simple extension methods which help me a lot when dealing with GraphQL endpoints:

public static string ToCursor(this Guid guid)
    => Convert.ToBase64String(guid.ToByteArray());

public static Guid FromCursor(this string base64Guid)
    => new Guid(Convert.FromBase64String(base64Guid));

3. Overview

It’s quite difficult to think about pagination in combination with GraphQL. Like always we have to choose a balance between performance, quality and cost. There is a combination of issues which makes the problems look fuzzy:

By separating these issues you can write more powerful, and reusable code which easily plugs into GraphQL. The most interesting side effect is that the resulting code will be incredibly easy to maintain (again, when compared to the traditional REST API paradigms).

The structure we build will contain:

In this guide we will walk through implementing connections in a GraphQL api using the graphql-dotnet project.

We assume you have the following NuGet packages installed (version numbers at the time of writing):

Besides that we assume you have your graph types and models set up properly.

The convention I use is ModelName and ModelNameType to make the distinction between models and their respective graph types. As I have the habit of reusing my (Entity Framework) data models throughout my graph types, I usually ignore complex properties on the object. Resolving these other types is a task for which the data loader is perfectly suited.

4. Architecture

The software architecture is one of the most important aspects in this article. The way you retrieve your data can either make or break your api. Throughout the implementation we will follow these core guidelines:

By applying these core rules we end up with a few components from technical perspective:

5. The GraphQL Connection Endpoint

The GraphQL connection endpoints can be compared with traditional Field based ones. They show that the api offers access to a certain type of data. The major difference is that a Connection graph types comes with a bit more complexity to handle the added complexity of pagination. Implementing a Connection in a query is not difficult at all:

Connection<FriendType>()
  .Name("friends")
  .Bidirectional()
  .Argument<StringGraphType>("query", "The search query you want to use to filter friends")
  .ResolveAsync(async context => {
    // ToDo: Retrieve our data
  });

You can specify whether your connection is either bidirectional or unidirectional by using the .Bidirectional() or .Unidirectional() extension methods. Unidirectional pagination will lead to only the first and after arguments being exposed, while bidirectional pagination will expose the first, after, last, and before arguments for use on the connection.

Mind the gap:
My most common error retrieving data for a GraphQL endpoint had to do with the returned type being incorrect. The ResolveAsync method requires an function which returns a Task<object> type as argument, so at compile time anything goes. Check if your function either:

  • Returns a Task<Connection<Friend>> if you use the ResolveAsync method without async modifier.
  • Returns a Connection<Friend> if you use the ResolveAsync method with async and await modifiers, or if you use the non-async Resolve method.

* Assuming the Friend type is your model of choice.
* Go here for the Connection class.

6. Data Retrieval

From here on it gets interesting. I follow the following generic structure for all the endpoints I implement. By more or less following this natural structure you create a predictability which makes it very pleasant to work with this code, and therefore makes it more maintainable.

  1. Resolve the provided arguments
  2. Application of the filter options to the query
  3. Slicing of the data set for pagination results
  4. Retrieval of the data
  5. Creation of Connection object

6.1 [Contextual] Model and Graph Types

Before digging into some details about the data retrieval process, let’s get a good understanding of the data models we’re working with throughout the example.

First we have a POCO (Plain Old CLR Objects) which contains our data. You might map this to your favourite data source. Whether you fill this object with data from a SQL database, ElasticSearch instance, some local text file or you fill it by scraping some website, it doesn’t matter. For this article we’re working with SQL, though.

public class Friend {
  public string Cursor { get; set; }
  public string Name { get; set; }
  public string City { get; set; }
}

The graph type for this model would look like this:

public class FriendType : ObjectGraphType<Friend>
{
  public FriendType() {
    Field(friend => friend.Cursor);
    Field(friend => friend.Name);
    // true at the end indicates that friend.City is allowed to be null.
    Field(friend => friend.City, true);
  }
}

In order to add a bit of sense to the next steps we’ll start using the SqlKata library to dynamically build our SQL query. SqlKata provides a QueryFactory which couples SqlKata with Dapper and provides Query objects which connection objects have already been initialized. Therefore allowing direct query execution without having to worry about connection strings and so. This QueryFactory is registerd in the DI container, and requested from the specific graph type. Feel free to forget this instantly. It might be of interest to the people wanting to go this route.

Aside:
I figured that it is nearly impossible to get cursor based pagination to work with Entity Framework, based on the available api’s. See this feature request for .SkipWhile() for some more background on this. Linked by this issue about creating connections, which is the inspiration for this article.

6.2 Resolving Arguments

Arguments are being resolved as is done with all other GraphQL queries:

var search = context.GetArgument<string>("search");
// ... and so on

If an argument is not assigned to in the query then the context.GetArgument<T>("search") method will return null.

For lists I have the approach where I do the assignment and null check together:

var list = context.GetArgument<List<string>>("list") ?? new List<string>();

The worst that will happen is that some iteration will not happen anymore. It’s quite easy to defend yourself to exeptions happening due to zero elements in the list.

6.3 Data Filters

Important to keep in the back of your head is that we are following a structure where we are only defining filters to our data, but not yet retrieving the data itself.

Over time I developed a habbit to put all filter and query logic related to a single model inside a single method. This method has an argument list describing all kinds of possible filter operations on an object. I stopped caring about what-if questions on data filtering. If the api’s users decides to apply some obscure combination of filters on a query then they kind of deserve it to face, at worst, an empty list.

Only having optional filter arguments gives total flexibility when using this method to filter through your data. An important aspect is utilizing these arguments in a way that they are always optional. Resolving an argument happens as shown below. Only an if statement with a query mutation.

// Note that unlike Entity Framework, SqlKata does not use pure functions
if (!string.IsNullOrWhiteSpace(search)) sqlQuery.Where(q => q
    .WhereContains("Name", search)
    .OrWhereContains("City", search));

For a more concrete example, take a look at section 7.

The beauty of this approach is that you have all possible operations in a single place, while all being incredibly maintainable. This is also a nice place to tuck away some filters which determine what data the user may access. This way it is possible to apply authorization rules at once, for a single type at a time.

An added bonus this approach makes it is way easier than before to build specialized and highly usable search interfaces (both within code and client apps). Ironically, by focussing on the way we build our endpoints and therefore enabling end users to quickly filter through data, we make the whole problem of pagination a little bit less important.

6.4 Slicing for Pagination

Because we haven’t yet materialized our query we can apply some more filters which are strictly related to pagination. It’s important to do this as the last step before retrieving your data. Failing to do so would result in unpredictable and strange resultsets.

Based on your data provider, these slicing operations might be one of the most difficult things to implement efficiently. Given the implementation of these slicing operations are worth a post on itself I have described my process here. This post focusses on the generation of SQL statements from the four pagination arguments using C# and SqlKata.

Key is to write something generic which can be used for once and for all (types). While the result might be a bit slower than otherwise potentially possible, the upside is that development time is reduced significantly. Last but not least is that this will result in consistency across your platform. Expensive functions can always removed or refactored later, after they start behaving like bottlenecks.

6.5 Creating the Connection object

Last but not least is the instantiation of the connection. To make my life easier I have created an extension method that does the heavy lifting for me. It essentially combines three tasks:

  1. Application of the filter operations
  2. Data retrieval
  3. Transformation into a Connection object

Step three is the tricky part. Part of this step is determining whether there are any next or previous slices available. To get this to work we need to retrieve the total number of available records for the provided filters, and we need to know about the rownumbers of the records in our slice. Most of the logic is just wiring everything up together.

Some magic to make this code a bit easier to understand (and untangle) is to cast a Query to a XQuery object. The XQuery object is created when a query object is retrieved from the QueryFactory. It will prevent us from requiring a reference to the QueryFactory object itself.

public static async Task<Connection<T>> ToConnection<T, TSource>(
    this SqlKata.Query query,
    ResolveConnectionContext<TSource> context,
    string cursorField = "Id") 
    where T : class, IId
    where TSource : class
{
    var xQuery = query as XQuery;

    if (xQuery == null) throw new ArgumentException("Make sure the query object is instantiated from a queryFactory", nameof(query));

    var countQuery = xQuery.Clone();

    countQuery
        .Clauses
        .RemoveAll(q => q.Component == "select"
                        || q.Component == "order");

    var totalCount = (await countQuery
        .SelectRaw("COUNT(*)")
        .GetAsync<int>())
        .SingleOrDefault();

    if (totalCount == 0) return new Connection<T>();
            
    var statement = xQuery.Compiler.Compile(
        xQuery.Slice(
            context.After?.FromCursor().ToString(),
            context.First.GetValueOrDefault(0),
            context.Before?.FromCursor().ToString(),
            context.Last.GetValueOrDefault(0)));

    var dictionary = new Dictionary<long, T>(xQuery.Connection.Query(
        sql: statement.Sql,
        param: statement.NamedBindings,
        map: (T t, long i) => new KeyValuePair<long, T>(i, t),
        splitOn: "RowNumber"));

    if (!dictionary.Any()) return new Connection<T>();

    var connection = new Connection<T>
    {
        Edges = dictionary
            .Select(q => new Edge<T>
            {
                Node = q.Value,
                Cursor = q.Value?.Id.ToCursor()
            })
            .ToList(),
        TotalCount = totalCount,
        PageInfo = new PageInfo
        {
            HasPreviousPage = dictionary.First().Key > 1,
            HasNextPage = dictionary.Last().Key < totalCount
        }
    };

    connection.PageInfo.StartCursor = connection.Edges.First().Cursor;
    connection.PageInfo.EndCursor = connection.Edges.Last().Cursor;

    return connection;
}

Usage of this extension method is demonstrated in section 7.1.

7. Overview of a real-world implementation

In order to make a little bit more sense of this article and to visually check if this method is something for you to implement, I have added some interesting parts of one of my real-world implementations. The goal of this code is to act as reference if you get stuck somewhere and want to see what the bigger picture looks like.

7.1 The Connection

First of there’s the connection itself. Loads of arguments and an authorization rule (I will cover authorization with GraphQL in-depth another time).

In my humble opinion I think this GraphQL connection looks amazing because of its simplicity. All this part does is define arguments on our GraphQL endpoint, resolve these arguments, and uses these arguments in our query function (about which I have written more in section 4 (architecture)).

Connection<FlightType>()
    .Name("flights")
    .AuthorizeWith("read:flight")
    .Bidirectional()
    .Argument<ListGraphType<GuidGraphType>>("cursors",
        "The cursors of the flights to retrieve")
    .Argument<StringGraphType>("query",
        "The search query can be used to search for a relevant piece of information. Things you can search on include aircraft registrations and callsigns, but also airfield names, ICAO codes or IATA codes.")
    .Argument<ScopeGraphType>("scope",
        "The scope to search through for flights")
    .Argument<DateTimeGraphType>("timestamp", 
        "Retrieves all active flights at this given moment")
    .Argument<ListGraphType<StringGraphType>>("aircraft", "")
    .Argument<ListGraphType<StringGraphType>>("airfields", "")
    .Argument<DateTimeGraphType>("fromTime", "")
    .Argument<DateTimeGraphType>("toTime", "")
    .Argument<StringGraphType>("departureAirfield", "")
    .Argument<StringGraphType>("arrivalAirfield", "")
    .Argument<ListGraphType<FlightOrderType>>("order", "")
    .ResolveAsync(async context =>
    {
        var list = context
            .GetArgument<List<Guid>>("cursors")?
            .Where(q => q != null && q != Guid.Empty)
            ?? new List<Guid>();

        var aircraftList = context
            .GetArgument<List<string>>("aircraft")?
            .Where(q => !string.IsNullOrWhiteSpace(q))
            ?? new List<string>();

        var airfieldList = context
            .GetArgument<List<string>>("airfields")?
            .Where(q => !string.IsNullOrWhiteSpace(q))
            ?? new List<string>();

        var query = context.GetArgument<string>("query");
        var timestamp = context.GetArgument<DateTime>("timestamp");
        var fromTimestamp = context.GetArgument<DateTime>("fromTime");
        var toTimestamp = context.GetArgument<DateTime>("toTime");
        var fromAirfield = context.GetArgument<string>("departureAirfield");
        var toAirfield = context.GetArgument<string>("arrivalAirfield");
        var order = context.GetArgument<List<string>>("order");

        return await queryFactory.FlightsQuery(
            list,
            timestamp,
            fromTimestamp,
            toTimestamp,
            fromAirfield,
            toAirfield,
            order,
            aircraftList,
            airfieldList,
            query)
            .ToConnection<Flight, object>(queryFactory, context);
    });

7.2 Data access function

The FlightsQuery extension method is responsible for composing the query which describes how to retrieve our data. There’s not much exciting going on in this method.

public static Query FlightsQuery(
    this QueryFactory queryFactory,
    IEnumerable<Guid> list = default,
    DateTime timestamp = default,
    DateTime fromTimestamp = default,
    DateTime toTimestamp = default,
    string fromAirfield = default,
    string toAirfield = default,
    IEnumerable<string> order = default,
    IEnumerable<string> aircraftList = default,
    IEnumerable<string> airfieldList = default,
    string query = default)
{
    var sqlQuery = queryFactory.Query("Flights")
        .Join("Aircraft", "Aircraft.Id", "Flights.AircraftId")
        .LeftJoinAs("Airfields", "DepartureAirfield", "Flights.DepartureAirfieldId", "DepartureAirfield.Id")
        .LeftJoinAs("Airfields", "ArrivalAirfield", "Flights.ArrivalAirfieldId", "ArrivalAirfield.Id")
        .WhereNotNull("Aircraft.Registration");

    if (list != default && list.Any()) sqlQuery.WhereIn("Id", list);
    if (aircraftList != default && aircraftList.Any()) sqlQuery.WhereIn("Aircraft.Registration", aircraftList);
    if (airfieldList != default && airfieldList.Any()) sqlQuery.Where(q => q.WhereIn("DepartureAirfield.Icao", airfieldList).OrWhereIn("ArrivalAirfield.Icao", airfieldList));

    if (timestamp != default) sqlQuery.Where(q => q.Where("Flights.DepartureTime", ">", timestamp.ToString("s")).Where("Flights.ArrivalTime", "<", timestamp.ToString("s")));
    if (fromTimestamp != default) sqlQuery.Where(q => q.Where("Flights.DepartureTime", ">", fromTimestamp.ToString("s")).OrWhere("Flights.ArrivalTime", ">", fromTimestamp.ToString("s")));
    if (toTimestamp != default) sqlQuery.Where(q => q.Where("Flights.DepartureTime", "<", toTimestamp.ToString("s")).OrWhere("Flights.ArrivalTime", "<", toTimestamp.ToString("s")));

    if (!string.IsNullOrWhiteSpace(fromAirfield)) sqlQuery.WhereContains("DepartureAirfield.Icao", fromAirfield);
    if (!string.IsNullOrWhiteSpace(toAirfield)) sqlQuery.WhereContains("ArrivalAirfield.Icao", toAirfield);
            
    if (!string.IsNullOrWhiteSpace(query)) sqlQuery.Where(q => q
        .WhereContains("Aircraft.Registration", query)
        .OrWhereContains("Aircraft.Callsign", query);   // ... etc

    if (order.Any())
    {
        foreach (var o in order)
        {
            if (o.Contains("^")) sqlQuery.OrderBy(o.Replace("^", ""));
            else sqlQuery.OrderByDesc(o);
        }
    }

    return sqlQuery;
}

A common argument against this type of code is that the lines are long. And I do agree on that. While maintaining this code one usually has the tendency to check for the argument names, which are located in the if-statements at the beginning of the line. For me personally this technique does wonders for readability.

In case you are wondering; the LeftJoinAs method is one I wrote myself for convenience, and is not included with SqlKata. It came from the need to join the same table multiple times. What it does:

public static Query LeftJoinAs(this Query query, string table, string alias, string first, string second, string op = "=")
{
    return query.LeftJoin(new Query(table).As(alias), q => q.On(first, second, op));
}

7.3 Defining order on a connection

While I’m at it I might explain this one as well. Like you might have seen in the previous method I also give the users the option to give order to the final result. It is one of the functionalities which is almost a requirement to do cursor based pagination. There are two things I like to accomplish:

GraphQL provides an amazing (and also underdocumented feature) to achieve this. These are the EnumerationGraphType types. You can do two things with them:

The first one is really easy. You only have to define your type as follows (assuming UserRole is your enum):

public class UserRoleType : EnumerationGraphType<UserRole> { }

The second method of defining an enum within GraphQL perfectly suits our use-case. Consider that the api and database both do not have a flat structure, but we want to query through them from the connection. In order to achieve this we would have to connect field names with (joined) column names.

The solution is to use a field name which is recognizeable to the users of the api, but under water returns another string field. The AddValue method has three arguments: string name, string description, object value.

public class FlightOrderType : EnumerationGraphType
{
    public FlightOrderType()
    {
        Name = "FlightField";
        
        // ^ indicates a field should be ordered ascending
        AddValue("DepartureTime", "", "^DepartureTime");
        AddValue("DepartureTimeDesc", "", "DepartureTime");
        AddValue("DepartureIcao", "", "^DepartureAirfield.Icao");
        AddValue("DepartureIcaoDesc", "", "DepartureAirfield.Icao");
        AddValue("ArrivalTime", "", "^ArrivalTime");
        AddValue("ArrivalTimeDesc", "", "ArrivalTime");
        AddValue("ArrivalIcao", "", "^ArrivalAirfield.Icao");
        AddValue("ArrivalIcaoDesc", "", "ArrivalAirfield.Icao");
        AddValue("Registration", "", "^Aircraft.Registration");
        AddValue("RegistrationDesc", "", "Aircraft.Registration");
        AddValue("Callsign", "", "^Aircraft.Callsign");
        AddValue("CallsignDesc", "", "Aircraft.Callsign");
    }
}

In order to have a simple way to order both ascending and descending I have used the ^ sign as indication. See the logic in 7.2 for more details on how this is resolved.

This is a method which works for me, and I did not see any reason to make it more complex than it currently is.

8. Other Resources

No webmentions were found.