Bug: FindXOrThrow don't get batched on the engine

[miguelff]

I am seeing the following issue with batching:

• prisma.user.findUnique({ where { id } }); -> batching occurs
• prisma.user.findUnique({ where { id } }).posts(); -> batching occurs
• prisma.user.findUniqueOrThrow({ where { id } }); -> batching does NOT occur
• prisma.user.findUniqueOrThrow({ where { id } }).posts(); -> batching does NOT occur

example based on Query optimization page

Originally posted by @nowlena in prisma/prisma-engines#3458 (comment)

[andresribeiro]

Any plans for this to be fixed?

[cdimino]

Was told to copy/paste this same info over here, even though it's obviously available in the link to the now-closed issue above. Maybe this will cast a spell to get a Prisma response from this 6 month old bug:

Bug description

Over in MichalLytek/typegraphql-prisma#383 we've been able to repro an issue where it looks like findUniqueOrThrow doesn't get batched the same way findUnique does.

How to reproduce

Easiest way would be to follow the repro steps in the typegraphql-prisma issue, this issue also has valid repro steps: #18838

Expected behavior

Would expect findUniqueOrThrow to follow the same batching behaviors as findUnique within the Prisma dataloader.

Prisma information

Seeing this with Prisma 4.13.0, but possibly earlier as well.

Environment & setup

• OS: Amazon Linux 2
• Database: PostgreSQL
• Node.js version: v18.12.0

Prisma Version

Prisma 4.13.0.

[flesler]

@janpio this is very big issue, at least for those using GraphQL. It is very common to have a lot of entities and each of them have properties which are IDs in the DB and loading by ID in the resolver. Since the ID exists, findUniqueOrThrow() is the logical one to use. That means all those people (like me) are running into the N+1 problem for absolutely all the by-id sub-queries which can be a ton.

As an example, if there's a query that returns 20 Projects, and we include the owner (User) which is loaded by ID. This translates to 21 queries to the DB instead of 2. Huge performance hit!

[flesler]

If someone is interested, this can possibly be patched temporarily with a middleware like this, very risky of course

import { Prisma } from '@prisma/client'

const middleware: Prisma.Middleware = async (params, next) => {
  const needsPatch = (params.action as string) === 'findUniqueOrThrow'
  if (needsPatch) {
    params.action = 'findUnique'
  }
  const ret = await next(params)
  if (needsPatch && !ret) {
    const model = params.model || ''
    const table = model[0].toLowerCase() + model.slice(1)
    throw new Err(`Invalid \`prisma.${table}.findUniqueOrThrow()\` invocation: An operation failed because it depends on one or more records that were required but not found. Expected a record, found none.)`)
  }
  return ret
}

class Err extends Error {
  public code = 'P2025'
  constructor (message: string) {
    super(message)
  }
}

export default middleware

[SevInf]

This is fixed in prisma/prisma-engines#4019 and #19554 and will be available in next Prisma release.

[flesler]

@SevInf I read the release email for 4.16.0, I don't see this issue in the list. Is this included?

[SevInf]

@flesler, yes, this is a part of 4.16.0

[janpio]

Thanks for the note @flesler, I applied the milestone and added the issue to the release notes 👍