Solving GraphQL N+1 problem  in fastify with loaders and conditional queries
Tags: graphql, node, webdev, javascript
Published: 2021-04-30 19:36:22

## 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
```qraphql
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:
```sql
CREATE TABLE Songs (
    SongID UUID NOT NULL PRIMARY KEY,
    SongName VARCHAR(75) NOT NULL,
    GenreID SMALLINT NOT NULL,
);
```
Genres table
```sql
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.
```sql
 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.
```graphql
query {
 songs{
  songid
	songname
	genre
 }
}
```
We define our resolvers in resolvers.js file:
```javascript
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:
```
{"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:
```javascript
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:
```
{"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.
```graphql
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](https://www.npmjs.com/package/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.
```
npm i graphql-parse-resolve-info
```
**Usage**

In our `reslovers.js` file require the module
```javascript
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:
```javascript
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](https://mercurius.dev/#/docs/loaders):

> 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 the`server.js` we have:
```javascript
fastify.register(mercurius,{
    schema: makeExecutableSchema({
        typeDefs,
        resolvers
    }),
    context: () => {
        return {
          client: fastify.db.client
        } 
    },
    loaders,
    graphiql: 'playground'
})
```

Refactor our resolver and add our loader
```javascript
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
            }           
    },
}
```

```javascript
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:
```javascript
[
    {
      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.
```javascript
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.

```javascript
 return genreids.map(genreid => {
               return rows.filter(genreitem => genreitem.genreid === genreid)[0].genre
            })
```

Our resolvers can now query our database efficiently.

## References
- [Mercurius documentation](https://mercurius.dev/#/)