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
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:
CREATE TABLE Songs (
SongID UUID NOT NULL PRIMARY KEY,
SongName VARCHAR(75) NOT NULL,
GenreID SMALLINT NOT NULL,
);
Genres table
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.
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.
query {
songs{
songid
songname
genre
}
}
We define our resolvers in resolvers.js file:
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:
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.
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.
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
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:
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:
fastify.register(mercurius,{
schema: makeExecutableSchema({
typeDefs,
resolvers
}),
context: () => {
return {
client: fastify.db.client
}
},
loaders,
graphiql: 'playground'
})
Refactor our resolver and add our loader
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
}
},
}
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, queriesand context. queries is an array of objects in our case this:
[
{
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.
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.
return genreids.map(genreid => {
return rows.filter(genreitem => genreitem.genreid === genreid)[0].genre
})
Our resolvers can now query our database efficiently.
Top comments (0)