How To Handle Prisma Unique Constraints with a Friendly Error

Often, your data model will have a unique constraint to prevent duplicate records.

For example:

model Character {
  id        Int       @id @default(autoincrement())
  name      String    @unique
  appearsIn Episode[]
}

A Character has to have a unique name. No two characters can be named the same.

If you try to add a new Character with the same name – or update an existing Character with a name that already exists – then your database and Prisma will prevent the data change from happen and raise an error.

Unless you handle this error, the RedwoodJS GraphQL API will mask the error with the Something went wrong message and a stack trace will be shown in the logs.

Alternatively, you could check the existence of the record or use the [validateUniqueness Service Validation[(Services | RedwoodJS Docs), but that requires two database transactions per request.

What if you could simple more gracefully handle the error?

Prisma defines many error codes and one of them P2002 is for an error when the Unique constraint failed.

Therefore, we can try/catch the error, check to see if it due to a unique constraint, and throw a RedwoodError that will send a more friendly message back through the GraphQL response.

The following code for the characters service demonstrates how to catch and handle the P2002 unique constraint failed error.

import type { QueryResolvers, MutationResolvers } from 'types/graphql'
import { Prisma } from '@prisma/client'
import { RedwoodError } from '@redwoodjs/api'

import { db } from 'src/lib/db'
import { logger } from 'src/lib/logger'

export const createCharacter: MutationResolvers['createCharacter'] = async ({
  input,
}) => {
  try {
    return await db.character.create({
      data: input,
    })
  } catch (e) {
    logger.error(e, 'Error creating character')

    if (e instanceof Prisma.PrismaClientKnownRequestError) {
      // P2022: Unique constraint failed
      // Prisma error codes: https://www.prisma.io/docs/reference/api-reference/error-reference#error-codes
      if (e.code === 'P2002') {
        logger.error('The character already exists', e)
        throw new RedwoodError('The character already exists')
      }
    }
    throw e
  }
}

export const updateCharacter: MutationResolvers['updateCharacter'] = async ({
  id,
  input,
}) => {
  try {
    return await db.character.update({
      data: input,
      where: { id },
    })
  } catch (e) {
    logger.error(e, 'Error updating character')

    if (e instanceof Prisma.PrismaClientKnownRequestError) {
      // P2022: Unique constraint failed
      // Prisma error codes: https://www.prisma.io/docs/reference/api-reference/error-reference#error-codes
      if (e.code === 'P2002') {
        logger.error('The character already exists', e)
        throw new RedwoodError('The character already exists')
      }
    }
    throw e
  }
}

Note: It is important here that your service is async/await. Redwood services generate without this, but is needed to return the error properly.

Now you have given the user a better experience and can inform them why the create or update failed.

To explore more Prisma error code, see: error codes in the Prisma reference documentation.

Next

Maybe one can parse the error info

api | 11:47:35 🐛 graphql-server GraphQL execution started: CreateCharacterMutation
api | 11:47:35 🚨 Error creating character 
api | 
api | 🚨 PrismaClientKnownRequestError Info
api | 
api | {
api |   "code": "P2002",
api |   "clientVersion": "4.3.1",
api |   "meta": {
api |     "target": [
api |       "name"
api |     ]
api |   }
api | }

To enrich the message with the field names and/or values that violated the constraint?

2 Likes

Really cool stuff, does this still work? I am using Zenstack so it might be interfering but I just wanted to check, because my log looks like this (Notice “GraphQLError”):

api | Invalid `prisma.user.create()` invocation:
api |
api |
api | Unique constraint failed on the fields: (`name`)
api |
api | 🚨 GraphQLError Info
api |
api | {
api |   "path": [
api |     "createUser"
api |   ],
api |   "locations": [
api |     {
api |       "line": 1,
api |       "column": 76
api |     }
api |   ],
api |   "extensions": {}
api | }
api |
api | 🥞 Error Stack
api |
api | Error calling enhanced Prisma method `user.create`:      
api | Invalid `prisma.user.create()` invocation:

I used the same code you suggested with the try, catch. Clearly I don’t see a code and I don’t have extensions. Is there another way to grab maybe the stack trace? Actually it looks like Zenstack lets prisma error pass through according to their docs.

I’ll try this again soon @jamesj but my suspicion is that ZenStack might wrap the Prisma client and thus perhaps doesn’t return the same error codes.

See Enhanced Prisma Client | ZenStack

1 Like

@ymc9 Hello sir, would you be able to clear this up? According to the docs these errors should pass through with codes, no?

Yup. Still works for me in RW 7.7.3

api | PrismaClientKnownRequestError: 
api | Invalid `db.character.create()` invocation in
api | /Users/dthyresson/temp/unique-constraint/api/src/services/characters/characters.ts:23:31
api | 
api |   20   input,
api |   21 }) => {
api |   22   try {
api | → 23     return await db.character.create(
api | Unique constraint failed on the fields: (`name`)
api |     at In.handleRequestError (/Users/dthyresson/temp/unique-constraint/node_modules/@prisma/client/runtime/library.js:122:6877)
api |     at In.handleAndLogRequestError (/Users/dthyresson/temp/unique-constraint/node_modules/@prisma/client/runtime/library.js:122:6211)
api |     at In.request (/Users/dthyresson/temp/unique-constraint/node_modules/@prisma/client/runtime/library.js:122:5919)
api |     at async l (/Users/dthyresson/temp/unique-constraint/node_modules/@prisma/client/runtime/library.js:127:11167)
api |     at Object.createCharacter (/Users/dthyresson/temp/unique-constraint/api/src/services/characters/characters.ts:23:12)
api |     at async /Users/dthyresson/temp/unique-constraint/node_modules/@envelop/core/cjs/orchestrator.js:386:27
api |     at async YogaServer.getResultForParams (/Users/dthyresson/temp/unique-constraint/node_modules/graphql-yoga/cjs/server.js:282:26)
api |     at async handle (/Users/dthyresson/temp/unique-constraint/node_modules/graphql-yoga/cjs/server.js:352:25)
api |     at async handlerFn (/Users/dthyresson/temp/unique-constraint/node_modules/@redwoodjs/graphql-server/dist/functions/graphql.js:86:24)
api |     at async execFn (/Users/dthyresson/temp/unique-constraint/node_modules/@redwoodjs/graphql-server/dist/functions/graphql.js:125:16)
api | 
api | 15:53:39 🚨 The character already exists
api | 15:53:39 🐛 Converting RedwoodError to GraphQLError 

One question @jamesj … is your db client prisma or db after enhancing with ZenStack.

Also, did you migrate and generate the client so the user model is known?

In my services I use context.db, not db anymore. Declared in a zenstack.d.ts file:

import type { PrismaClient } from '@prisma/client'

import {
  InferredCurrentUser,
  Overwrite,
  UndefinedRoles,
} from '../../.redwood/types/includes/all-currentUser'

declare module '@redwoodjs/context' {
  interface GlobalContext {
    db: PrismaClient
    currentUser?: Overwrite<UndefinedRoles, InferredCurrentUser>
  }
}

Added in the graphQLHandler:

  extraPlugins: [useZenStack(db)],

And in my services I have:

context.db.user.create({
        data: input,
      })

Yes, the user model exists as I can successfully create users, but when I hit a constraint error I get the output as shown above.

Hey @jamesj , sorry I missed the message here. Is this something that I still need to look into?

1 Like

I solved it by doing some ninjutsu on the error return, it’s not pretty but it gets the job done because I don’t seem to be getting the error codes on the backend, so the check lives on the frontend.

onError: (error) => {
        if (error.graphQLErrors && error.graphQLErrors.length > 0) {
          const originalError = error.graphQLErrors[0].extensions
            ?.originalError as OriginalError | undefined
          if (originalError && typeof originalError.message === 'string') {
            if (originalError.message.includes('Unique constraint failed')) {
              showNotification({
                type: 'error',
                title: 'Duplicate Error',
                message:
                  'A user with this name already exists. Please choose a different name.',
              })
            }
          }
        }
      },

ZenStrack doesn’t interfere with regular Prisma errors, and they should be thrown as is. For ZenStack-specific errors, in a redwood setup, they are supposed to be transformed to standard RW errors like ForbiddenError and ValidationError.

I’ll double check that part. It would be great if you could help file a bug with a bit more details.

Error codes come from Prisma when the error is of type Errors | Prisma Documentation

Those errors have code that can be used to determine the P2002 unique constraint failed assigning the reason for the creat failure is that.

Have you looked at the error type thrown by the db operation?

Yeah I mean I tried this again and did some debugging in the resolver for the error to dig a bit deeper.

try {
    return context.db.user.create({
      data: input,
    })
  } catch (error) {
    logger.error(error)
    logger.error(typeof error)
    logger.error(error.code)
    logger.error(error instanceof Prisma.PrismaClientKnownRequestError)
  }

This doesn’t even show me the logs, so I’m thinking context.db doesn’t return a proper error. So I tried it without the enhanced prisma client:

try {
    return db.user.create({
      data: input,
    })
  } catch (error) {
    logger.error(error)
    logger.error(typeof error)
    logger.error(error.code)
    logger.error(error instanceof Prisma.PrismaClientKnownRequestError)
  }

However I don’t see the logs either, it’s like the error object is not available to me?

api |   76 
api |   77 export const createUser: MutationResolvers['createUser'] = ({ input }) => {
api |   78   try {
api | → 79     return db.user.create(
api | Unique constraint failed on the fields: (`name`)

I see this in my console when it fails, but the front end just gets “Something went wrong” anyway. So I can’t say it’s the enhanced prisma client because this fails even with db. For the record I’m just implementing the same code in the resolver as you suggested:

  try {
    return db.user.create({
      data: input,
    })
  } catch (error) {
    if (error instanceof Prisma.PrismaClientKnownRequestError) {
      if (error.code === 'P2002') {
        logger.warn('The user already exists')
        throw new RedwoodError('The user already exists')
      }
    }
    throw error
  }

Though like I mentioned, the GraphQLError object doesn’t have the code as printed in the console:

api | 🚨 GraphQLError Info
api |
api | {
api |   "path": [
api |     "createUser"
api |   ],
api |   "locations": [
api |     {
api |       "line": 1,
api |       "column": 68
api |     }
api |   ],
api |   "extensions": {}
api | }

Then I noticed you used async/await. So I tried that… and now it works! So the big takeaway here is that you can’t do this if the resolver isn’t async. :thinking:

Have you tried with an explicit async and await?

Redwood wraps services in an async await but here to catch the error you probably need to await the db.user.create and have the function be async

2 Likes

@jamesj I just tried with redwood 7.7.4 and works as expected:


import { Prisma } from '@prisma/client'
import type { QueryResolvers, MutationResolvers } from 'types/graphql'

import { ValidationError } from '@redwoodjs/graphql-server'

import { db } from 'src/lib/db'

// ...

export const createCar: MutationResolvers['createCar'] = async ({ input }) => {
  try {
    const newCar = await db.car.create({
      data: input,
    })
    return newCar
  } catch (error) {
    if (error instanceof Prisma.PrismaClientKnownRequestError) {
      if (error.code === 'P2002') {
        throw new ValidationError('Car already exists')
      }
    }
    throw error
  }
}```

```sql

model Car {
  id    Int    @id @default(autoincrement())
  name  String @unique
  model String
  color String
}

image

Note: in this example the unique constraint should be on both car and model, but just illustrative

IMPORTANT: You must await the db.create…

/// THIS WILL NOT UNDERSTAND THE ERROR
export const createCar: MutationResolvers['createCar'] = ({ input }) => {
  try {
    const newCar = db.car.create({. // NO AWAIT
      data: input,
    })
    return newCar
  } catch (error) {
    if (error instanceof Prisma.PrismaClientKnownRequestError) {
      if (error.code === 'P2002') {
        throw new ValidationError('Car already exists')
      }
    }
    throw error
  }
}

I asked Cursor to make this more reusable and it recommended:

const handleUniquenessError = async <T>(
  operation: () => Promise<T>,
  errorMessage: string
): Promise<T> => {
  try {
    return await operation()
  } catch (error) {
    if (
      error instanceof Prisma.PrismaClientKnownRequestError &&
      error.code === 'P2002'
    ) {
      throw new ValidationError(errorMessage)
    }
    throw error
  }
}

export const createCar: MutationResolvers['createCar'] = ({ input }) => {
  return handleUniquenessError(
    () => db.car.create({ data: input }),
    'Car already exists'
  )
}

but there are probably better patterns…

With some Cursor AI refactoring could do:

type ErrorType = 'UNIQUENESS' | 'OUT_OF_RANGE'

const errorCodeMap: Record<ErrorType, string> = {
  UNIQUENESS: 'P2002',
  OUT_OF_RANGE: 'P2020',
}

const handlePrismaError = async <T>(
  operation: () => Promise<T>,
  errorMap: Partial<Record<ErrorType, string>>
): Promise<T> => {
  try {
    return await operation()
  } catch (error) {
    if (error instanceof Prisma.PrismaClientKnownRequestError) {
      const errorType = Object.entries(errorCodeMap).find(
        ([, code]) => code === error.code
      )?.[0] as ErrorType | undefined

      if (errorType && errorMap[errorType]) {
        throw new ValidationError(errorMap[errorType] ?? 'Something went wrong')
      }
    }
    throw error
  }
}

export const createCar: MutationResolvers['createCar'] = ({ input }) => {
  return handlePrismaError(() => db.car.create({ data: input }), {
    UNIQUENESS: 'Car already exists',
    OUT_OF_RANGE: 'Value out of range for the type',
  })
}

and handle and error code here. Errors | Prisma Documentation

Note that last example keeps the service plain (no async) … beause the handlePrismaError will be async and await.

1 Like

Oh yeah this is pretty cool and works without explicitly setting async on the resolver. Thank you! I’m glad it works now. Could this be something that one could handle with prisma extensions potentially so you never even have to touch the create resolvers?

Something like this maybe in db.ts

import { Prisma, PrismaClient } from '@prisma/client'

import { emitLogLevels, handlePrismaLogging } from '@redwoodjs/api/logger'
import { ValidationError } from '@redwoodjs/graphql-server'

import { logger } from './logger'

type ErrorType = 'UNIQUENESS' | 'OUT_OF_RANGE' | 'FOREIGN_KEY_CONSTRAINT'

const errorCodeMap: Record<ErrorType, string> = {
  UNIQUENESS: 'P2002',
  OUT_OF_RANGE: 'P2020',
  FOREIGN_KEY_CONSTRAINT: 'P2003',
}

const handlePrismaError = (
  error: unknown,
  defaultErrorMessage = 'An unexpected error occurred'
): never => {
  if (error instanceof Prisma.PrismaClientKnownRequestError) {
    const errorType = Object.entries(errorCodeMap).find(
      ([, code]) => code === error.code
    )?.[0] as ErrorType | undefined

    if (errorType) {
      const errorMessage = {
        UNIQUENESS: 'A record with this data already exists',
        FOREIGN_KEY_CONSTRAINT: 'Invalid reference to related entity',
        OUT_OF_RANGE: 'Value is out of range',
      }[errorType]
      throw new ValidationError(errorMessage ?? defaultErrorMessage)
    }
  }
  throw new Error(defaultErrorMessage)
}

const prisma = new PrismaClient({
  log: emitLogLevels(['info', 'warn', 'error']),
})

handlePrismaLogging({
  db: prisma,
  logger,
  logLevels: ['info', 'warn', 'error'],
})

export const db = prisma.$extends({
  query: {
    $allModels: {
      async create({ args, query }) {
        try {
          return await query(args)
        } catch (error) {
          handlePrismaError(error)
        }
      },
    },
  },
})

I tested the above and it seems to work? That’s much more central and keeps you from having to update every single resolver. It properly sends it to the client and I display the error.message, meanwhile the console ouputs:

api | 15:52:32 🚨 graphql-server A record with this data already exists 
api |
api | 🚨 GraphQLError Info
api |
api | {
api |   "path": [
api |     "createUser"
api |   ],
api |   "locations": [
api |     {
api |       "line": 1,
api |       "column": 68
api |     }
api |   ],
api |   "extensions": {
api |     "code": "GRAPHQL_VALIDATION_FAILED"
api |   }
api | }
api |
api | 🥞 Error Stack
api |
api | Error calling enhanced Prisma method `user.create`: A record with this data already exists

Bonus
If you want you can further customize based on your model:

          if ((this as any).$name === 'Team') {
            handlePrismaError(error, 'Failed to create team')
          } else {
            handlePrismaError(error)
          }

or create something more elaborate and maintainable:

import { Prisma, PrismaClient } from '@prisma/client'
import { emitLogLevels, handlePrismaLogging } from '@redwoodjs/api/logger'
import { ValidationError } from '@redwoodjs/graphql-server'
import { logger } from './logger'

type ErrorType = 'UNIQUENESS' | 'OUT_OF_RANGE' | 'FOREIGN_KEY_CONSTRAINT'

const errorCodeMap: Record<ErrorType, string> = {
  UNIQUENESS: 'P2002',
  OUT_OF_RANGE: 'P2020',
  FOREIGN_KEY_CONSTRAINT: 'P2003',
}

type ModelErrorMessages = {
  [key: string]: Partial<Record<ErrorType, string>>
}

const modelErrorMessages: ModelErrorMessages = {
  team: {
    UNIQUENESS: 'A team with this name already exists',
    FOREIGN_KEY_CONSTRAINT: 'Invalid reference to a related entity in team creation',
  },
  // Add other models here as needed
}

const handlePrismaError = (
  error: unknown,
  model: string,
  defaultErrorMessage = 'An unexpected error occurred'
): never => {
  if (error instanceof Prisma.PrismaClientKnownRequestError) {
    const errorType = Object.entries(errorCodeMap).find(
      ([, code]) => code === error.code
    )?.[0] as ErrorType | undefined

    if (errorType) {
      const modelSpecificMessage = modelErrorMessages[model]?.[errorType]
      const genericMessage = {
        UNIQUENESS: 'A record with this data already exists',
        FOREIGN_KEY_CONSTRAINT: 'Invalid reference to related entity',
        OUT_OF_RANGE: 'Value is out of range',
      }[errorType]

      throw new ValidationError(modelSpecificMessage ?? genericMessage ?? defaultErrorMessage)
    }
  }
  console.error('Unexpected error:', error)
  throw new Error(defaultErrorMessage)
}

const prisma = new PrismaClient({
  log: emitLogLevels(['info', 'warn', 'error']),
})

handlePrismaLogging({
  db: prisma,
  logger,
  logLevels: ['info', 'warn', 'error'],
})

export const db = prisma.$extends({
  query: {
    $allModels: {
      async create({ model, args, query }) {
        try {
          return await query(args)
        } catch (error) {
          handlePrismaError(error, model)
        }
      },
    },
  },
})

Quick addendum, it seems the above code breaks dbAuth in api/src/functions/auth.ts

  const authHandler = new DbAuthHandler(event, context, {
    // Provide prisma db client
    db: db, <--

So I modified the db.ts file like this

import { PrismaClient } from '@prisma/client'

import { emitLogLevels, handlePrismaLogging } from '@redwoodjs/api/logger'

import { handlePrismaError } from 'src/utils/helpers'

import { logger } from './logger'

export const prisma = new PrismaClient({
  log: emitLogLevels(['info', 'warn', 'error']),
})

handlePrismaLogging({
  db: prisma,
  logger,
  logLevels: ['info', 'warn', 'error'],
})

export const db = prisma.$extends({
  query: {
    $allModels: {
      async create({ args, query }) {
        try {
          return await query(args)
        } catch (error) {
          handlePrismaError(error)
        }
      },
    },
  },
})

And then instead of importing { db } in auth.ts import and use { prisma } from the src/lib/db

  const authHandler = new DbAuthHandler(event, context, {
    // Provide prisma db client
    db: prisma,

The db auth handler doesn’t seem to like an extended prisma client :sweat_smile: