[Bug?]: Prisma built-in dataloader not working for GraphQL resolvers

[jotalanusse]

What's not working?

Recently, while optimizing our API service, we encountered the N+1 problem in our GraphQL resolvers. This issue occurs when a separate database query is executed for each related record, resulting in multiple queries being made to retrieve additional information for associated data.

Upon further investigation, we referred to the Prisma documentation, which provides guidance on addressing this issue (link). It suggests leveraging Prisma features, such as the fluent API, to enable the database to automatically batch requests and reduce the number of queries.

The issue is that, despite following the documentation and implementing these optimizations, the queries to the database are still not being batched as expected.

We are starting to suspect that the way Redwood, or its underlying GraphQL engine, executes resolvers might be interfering with the built-in data loaders provided by Prisma.

How do we reproduce the bug?

GraphQL schema definition:

type Truck {
  id: String!
  name: String!
  organizationId: String!
  organization: Organization!
  routeRuns: [RouteRun]!
  createdAt: DateTime!
}

Main resolver:

export const trucksByOrganization: QueryResolvers['trucksByOrganization'] =
  async ({ organizationId }) => {
    return db.truck.findMany({ where: { organizationId } });
  };

Field resolvers:

export const Truck: TruckRelationResolvers = {
  organization: (_args, { root }) => db.truck.findUnique({ where: { id: root.id } }).organization(),
  routeRuns: (_args, { root }) => db.truck.findUnique({ where: { id: root.id } }).routeRuns(),
};

In the code above, the same databse instance is shared for all resolvers, where:

const db = new PrismaClient();

Given the setup described above (using findUnique and the fluent API), automatic batching should function as expected, but for some reason, it does not. Due to the private nature of the project, I cannot share the code directly. However, if a reproduction is needed to test this issue, I am willing to create one and provide it.

What's your environment? (If it applies)

System:
  OS: Linux 6.8 Ubuntu 22.04.5 LTS 22.04.5 LTS (Jammy Jellyfish)
  Shell: 5.1.16 - /bin/bash
Binaries:
  Node: 18.20.4 - /tmp/yarn--1732636433916-0.04046345284337405/node
  Yarn: 1.22.19 - /tmp/yarn--1732636433916-0.04046345284337405/yarn
Browsers:
  Chrome: 131.0.6778.85
npmPackages:
  @redwoodjs/api-server: 6.6.4 => 6.6.4 
  @redwoodjs/auth-firebase-setup: 6.6.4 => 6.6.4 
  @redwoodjs/cli: 6.6.4 => 6.6.4 
  @redwoodjs/core: 6.6.4 => 6.6.4 
  @redwoodjs/internal: 6.6.4 => 6.6.4

Are you interested in working on this?

• I'm interested in working on this

[mschmidt01]

@jotalanusse,

While investigating a similar problem, we discovered something interesting: there might be a discrepancy between what logs suggest and what's actually happening at the database level.

In our case, Prisma middleware logs showed multiple separate findUnique calls, suggesting an N+1 problem:

Building.findUnique took 0.006 seconds – {"where":{"id":"00000001-1011-0003-0091-000000000000"},...}
Building.findUnique took 0.006 seconds – {"where":{"id":"00000001-1012-0003-0099-000000000000"},...}

However, when we checked the raw database logs, we found Prisma was actually batching these into a single query:

SELECT "public"."Building"."id", ...  
FROM "public"."Building"  
WHERE "public"."Building"."id" IN ($1, $2, $3, ..., $36)  

Could you check your raw database logs to see if something similar is happening in your case? It might just be a logging issue rather than a problem with the actual dataloader implementation.