Solving GraphQL N+1 problem in fastify with loaders and conditional queries

What is 1+N, err, N+1 ?

To understand this, let’s give an example.

Lets start by defining our graphql schema in a schema.graphql file

1
2
3
4
5
6
7
8
type Song {
 songid: ID!
 songname: String!
 genre: String!
}
type Query {
 songs: [Song]
}

Normally we would have a songs table and a genres table in a relational database, say PostgreSQL:
Songs table:

1
2
3
4
5
CREATE TABLE Songs (
    SongID UUID NOT NULL PRIMARY KEY,
    SongName VARCHAR(75) NOT NULL,
    GenreID SMALLINT NOT NULL,
);

Genres table

1
2
3
4
CREATE TABLE Genres (
    GenreID SMALLINT NOT NULL PRIMARY KEY,
    GenreDescription VARCHAR(75) NOT NULL
);

Here, a genre can be in many songs, we’re linking the two tables by having a foreign key (genreid ) in our songs table that references the genreid column in the genres table.

1
2
3
4
ALTER TABLE songs
   ADD CONSTRAINT FK_Songs 
   FOREIGN KEY (GenreID)
   REFERENCES Genres(GenreID);

Now let’s define a query to fetch our songs and their genres from our server.

1
2
3
4
5
6
7
query {
 songs{
  songid
	songname
	genre
 }
}

We define our resolvers in resolvers.js file:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
const resolvers = {
    Query: {
        songs: async (_, args, {client, reply}) => {
            reply.log.info("getting all songs")
            let {rows} = await client.query(`
            SELECT songs.songid, songs.songname, songs.genreid FROM songs LIMIT 5;
            `)
            return rows
            }
    },
    Song: {
        genre: async (parent, args, {client, reply}) => {
            reply.log.info("getting genre") 
            let {rows} = await client.query(`
            SELECT genredescription AS genre FROM genres WHERE genreid = $1
            `, [parent.genreid])
            return rows[0].genre
        }
    }
}
module.exports = {resolvers}

If we ran our query and inspect the logs we see:

1
2
3
4
5
6
{"msg":"getting all songs"}
{"msg":"getting genre"}
{"msg":"getting genre"}
{"msg":"getting genre"}
{"msg":"getting genre"}
{"msg":"getting genre"}

What’s happening?

We are fetching all songs from our database and for each song we are also making
a database request to get the genre, hence the “N + 1” problem.

That’s really not efficient, especially with nested queries in many to many relations. A GraphQL API should fetch data efficiently as possible.

Solutions

Solution 1: Using a join

One of the solution would be to perform a SQL INNER JOIN.

Now our reslovers will look like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
const resolvers = {
    Query: {
        songs: async (_, args, {client, reply}) => {
            reply.log.info("getting all songs and genres")
            let {rows} = await client.query(`
            SELECT songs.songid, songs.songname, genres.genredescription AS genre
            FROM genres
            INNER JOIN songs
            ON genres.genreid = songs.genreid
            LIMIT 5
            `)
            return rows
            }
    },
}

In our logs we’ll see this:

1
{"msg":"getting all songs and genres"}

Great! we have eliminated the extra SQL queries from our previous resolvers.

Have we really?

The problem with this:

Let’s say our API user now creates a query to fetch the songid and songname only, no genre.

1
2
3
4
5
6
query {
 songs{
  songid
  songname
 }
}

If you could check your database logs, you’ll notice that we still have the join which is really unnecessary in this case.

The same SQL query will be executed even though we don’t need the genre. That’s not very efficient right?

Solution 2: Conditional queries

What if we could be able to look into the query fields, see which fields our users have requested and create conditional SQL queries that returns only the data they requested.

That sounds great, but how do we do that?

The GraphQL info object

Our resolver function takes four arguments: parent, args, context and info. The info object contains, well, information on the incoming GraphQl query. What we are interested in are the fields requested in the info.

We could parse the info ourselves but there are some libraries we could use for that.

graphql-parse-resolve-info

It parses the info object and returns  the fields that are being requested by our API user enabling us to optimise our resolvers.

1
npm i graphql-parse-resolve-info

Usage

In our reslovers.js file require the module

1
const {parseResolveInfo,simplifyParsedResolveInfoFragmentWithType} = require('graphql-parse-resolve-info');

We use the first function to parse the info object and the second function as a helper to obtain the fields  in  our  returnType, in our case Song. From these, we can create conditional SQL queries.

By using the hasOwnproperty() method, we can check whether our Song object has the field we need and execute the SQL queries conditionally.

Now our resolvers will look like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
const resolvers = {
    Query: {
        songs: async (_, args, {client, reply}, info) => {
            reply.log.info("getting all songs")
            const parsedInfoObject = parseResolveInfo(info);
            const {fields} = simplifyParsedResolveInfoFragmentWithType(parsedInfoObject, info.returnType);
            if (!fields.hasOwnProperty('genre')) {
                let {rows} = await client.query(`
                SELECT songs.songid, songs.songname FROM songs LIMIT 5
                `)
                return rows
            }
            let {rows} = await client.query(`
                SELECT songs.songid, songs.songname, genres.genredescription AS genre
                FROM genres
                INNER JOIN songs
                ON genres.genreid = songs.genreid
                LIMIT 5
                `)
                return rows
        }
            
           
    },
}

The problem with this:

This is a good solution for simple query types, in our case, we only check whether the API user includes the genre in the query fields and perform the conditional SQL queries.

However, with complex query types, our resolvers could get really messy and verbose.

Solution 3: Loaders

From mercurius documentation:

Each defined loader will register a resolver that coalesces each of the request and combines them into a single, bulk query.

Loaders enable us to write resolvers that batch requests.

Mercurius - the graphql adapter for fastify - comes with great set of features including automatic loaders integration to solve 1 + N queries.

We just need to define our loaders and add them in the options object where we register the mercurius plugin.

In theserver.js we have:

1
2
3
4
5
6
7
8
9
10
11
12
13
fastify.register(mercurius,{
    schema: makeExecutableSchema({
        typeDefs,
        resolvers
    }),
    context: () => {
        return {
          client: fastify.db.client
        } 
    },
    loaders,
    graphiql: 'playground'
})

Refactor our resolver and add our loader

1
2
3
4
5
6
7
8
9
10
11
const resolvers = {
    Query: {
        songs: async (_, args, {client, reply}) => {
            reply.log.info("getting all songs")
            let {rows} = await client.query(`
            SELECT songs.songid, songs.songname, songs.genreid FROM songs LIMIT 5;
            `)
            return rows
            }           
    },
}
1
2
3
4
5
6
7
8
9
10
11
12
13
const loaders = {
    Song: {
            genre: async (queries, {client}) => {     
            let genreids = queries.map(({ obj }) => obj.genreid)  
            let {rows} = await client.query(`
            SELECT genreid, genredescription genre FROM genres WHERE  genres.genreid = ANY ($1)
            `,[genreids])     
            return genreids.map(genreid => {
               return rows.filter(genreitem => genreitem.genreid === genreid)[0].genre
            })
        },
    }
}

We have created a resolver for the genre in our Song object type. The loader receives two arguments, queries and context. queries is an array of objects in our case this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
[
    {
      obj: {
        songid: 'f4b800b9-5093-49a7-9bc8-37561b2d7041',       
        songname: 'Let Me Down Slowly (feat. Alessia Cara)',  
        genreid: 2
      },
      params: {}
    },
    {
      obj: {
        songid: '8a3416e9-a4ab-468c-b81d-b58c214ed3fd',       
        songname: 'stupid',
        genreid: 2
      },
      params: {}
    },
    // more objects
  ]

We map this object to obtain all the genreids, and perform a SQL batch query.

1
2
3
4
let genreids = queries.map(({ obj }) => obj.genreid)  
let {rows} = await client.query(`
            SELECT genreid, genredescription genre FROM genres WHERE  genres.genreid = ANY ($1)
            `,[genreids])

We then return the result ordered by the genreids.

1
2
3
return genreids.map(genreid => {
              return rows.filter(genreitem => genreitem.genreid === genreid)[0].genre
           })

Our resolvers can now query our database efficiently.

References