Rapid GraphQL API Development with PostGraphile

Generally, GraphQL Server implementations follow either a schema-first or a code-first design.

For those who may be relatively new to GraphQL, here is a quick recap. A GraphQL API is defined using a Schema Definition Language (SDL), pretty similar to how protocol buffer message types are defined. It is programming language agnostic and has its own type system for defining data structures that the API will work against.

A hypothetical GraphQL Schema for an API which deals with movies & actors may look as below:

In a schema-first design, such schema definition is written first and used as input by the GraphQL server implementation. The server will parse it, and finally, resolvers (written fully decoupled) would be mapped to the fields found in the schema types. The resolvers being the actual code (written in a native language) that computes a field’s value. An example of how this decoupled approach can be used to spin up a GraphQL server is demonstrated by one of the well-known server implementations, Apollo Server.

On the other side of the spectrum, when it comes to code-first design, a native language abstraction is used instead of real schema, and the resolvers are coupled together. For example, GraphQL Nexus is a well-known library that exports strong types which could be used to represent a similar SDL along with the resolvers but in a code-first design as below:

This means there will still be a GraphQL Schema, but it will be generated from a different native language abstraction.

The approach taken by PostGraphile is different, as it treats the real underlying database schema as the “source of truth.” Therefore, it generates a GraphQL Schema as well as the matching resolvers based on the database schema which we could refer to as a DataBase-First GraphQL server implementation.

We wanted to give the best reach possible to our UI/UX engineers when it comes to accessing data. Having a well-defined database schema really pays off here. PostGraphile introspects all the correct types, constraints, relations & indexes in the database schema and interprets it into an expressive GraphQL schema. This approach enables us to truly expose our database-as-an-API.

Not only generating the GraphQL schema but doing so with matching resolvers is something we now don’t want to live without. This also made perfect sense, given the majority of our use-cases of the GraphQL API are serving CRUD operations.

We find many libraries that try hard to be unopinionated (supporting multiple Object Relational Mappers, supporting multiple databases) but, in doing so, being unable to provide a fully streamlined solution. Here, PostGraphile is a prime example of doing one thing and doing that thing exceptionally well.

Let’s take a simple example of TV shows & seasons. A single TV show may have zero or more seasons. Let’s say you need to do something as simple as listing all the TV shows and their respective seasons. A simple GraphQL query as below will get the job done.

If we’ve written a naive resolver in the GraphQL server implementation using an ORM, the above query could be translated to an SQL, such as:

and then, for each TV show row, there could be another SQL, such as:

If there were N TV shows in the database, the Q2 query would run for N times, and the Q1 query would run for 1 time, giving you a total of N+1 roundtrips to the database. This is not a scalable approach. You could also imagine the exponential growth of the query performance when the number of hierarchies in the GraphQL query is increased (i.e., list all TV shows, seasons, and episodes).

To solve this performance issue when using ORMs, it’s also quite common to use some utilities called DataLoaders.

Almost all implementations of DataLoaders use some form of batching and/or caching. It’ll sit in-between the GraphQL resolver and the database to intercept the request.

Let’s take the same query as above. The 'smarter' DataLoader will first get the IDs of TV shows from Q1, and rather than running individual queries for Q2, it’ll batch everything together and issue a single query using the ORM, which results in an SQL, such as:

This immediately yields better performance by reducing the N+1 roundtrips to just 1+1. However, this is not the best approach. As you’d have noticed already, the deeper the nesting hierarchy, so increases the number of queries (at least now it’s no longer data-dependant, but rather entity-dependant).

Did you ever think: why would one need to go through all that trouble to fetch such relational data, and why can’t you simply perform a table JOIN and achieve all this in a single database query, no matter the GraphQL query nesting levels? That’s exactly what PostGraphile offers. DataLoaders offer a very generic approach to a common problem, whereas PostGraphile offers an opinionated alternative. Again, doing one thing, and doing it well, offers better performance gains compared to the DataLoader solution.

When PostGraphile auto-generates the GraphQL schema & resolvers, it makes full use of the PostgreSQL relation metadata resulting in rather smart resolvers. They are capable of converting any GraphQL query request into exactly a single SQL query with JOINs. This, combined with database design best practices of having correct constraints & indexes in place, gives you an unmatched performance advantage over the other generic alternatives.

For the same example as above, PostGraphile would generate an SQL somewhat similar to:

We’ve kept the example in the simplest form to better understand the concept here, but you could easily extrapolate how this would formulate when the request GraphQL query is complex (i.e., there will be plenty more joins). So, having a good database design is the key for almost everything we discussed so far.