Null and undefined

Prisma Client differentiates between null and undefined:

  • null is a value
  • undefined means do nothing

Note: This is particularly important to account for in a Prisma with GraphQL context, where null and undefined are interchangeable.

In the following example, if emailInput is null, the query sets email (a mandatory field) to undefined - which means ✔ do not include this in the update:

const update = await prisma.user.update({
where: {
id: 1,
},
data: {
name: "Petunia",
email: emailInput != null ? emailInput : undefined, // If null, don't include in update!
},
});
function getEmail() {
const random = Math.floor(Math.random() * 10);
if (random > 5) {
return "ariadne@prisma.io"; // Could be null!
}
return null;
}

Setting a field value to undefined is the same as not including the email field in the update query at all:

const update = await prisma.user.update({
where: {
id: 1,
},
data: {
name: "Petunia",
// No email update here...
},
});
function getEmail() {
const random = Math.floor(Math.random() * 10);
if (random > 5) {
return "ariadne@prisma.io"; // Could be null!
}
return null;
}

By contrast, the following would ✘ not work as the mandatory email field cannot be null:

email: isValid(emailInput) ? emailInput : null, // email is a mandatory field!

Note: TypeScript will give you an error in this scenario: Type 'null' is not assignable to type 'string'. ts(2322)

model User {
email String @unique
id Int @id @default(autoincrement())
name String?
posts Post[]
}
model Post {
id String @id @default(cuid())
title String
authorId Int?
views Int?
author User? @relation(fields: [authorId], references: [id])
}

Use case: null and undefined in a GraphQL resolver

In the following example mutation that updates a user, both authorEmail and name accept null - from a GraphQL perspective, this means that fields are optional:

type Mutation {
// Update author's email or name, or both - or neither!
updateUser(id: Int!, authorEmail: String, authorName: String): User!
}

However, if you pass null values for authorEmail or authorName on to Prisma, the following will happen:

  • If args.authorEmail is null, the query will fail - email does not accept null
  • If args.authorName is null, Prisma changes the value of name to null - this is probably not how you want an update to work ✘
updateUser: (parent, args, ctx: Context) => {
return ctx.prisma.user.update({
where: { id: Number(args.id) },
data: {
email: args.authorEmail, // email cannot be null
name: args.authorName // name set to null - potentially unwanted behavior
},
})
},

Instead, set the value of email and name to undefined if the input value is null. Doing this is the same as not updating the field at all:

updateUser: (parent, args, ctx: Context) => {
return ctx.prisma.user.update({
where: { id: Number(args.id) },
data: {
email: args.authorEmail != null ? args.authorEmail : undefined, // If null, do nothing
name: args.authorName != null ? args.authorName : undefined // If null, do nothing
},
})
},

The effect of null and undefined on conditionals

There are some caveats to filtering with conditionals which might produce unexpected results. When filtering with conditionals you might expect one result but receive another given how Prisma treats nullable values.

The following table provides a high-level overview of how the different operators handle 0, 1 and n filters.

Operator0 filters1 filtern filters
ORreturn empty listvalidate single filtervalidate all filters
ANDreturn all itemsvalidate single filtervalidate all filters
NOTreturn all itemsvalidate single filtervalidate all filters

This example shows how an undefined parameter impacts the results returned by a query that uses the OR operator.

interface FormData {
name: string
email?: string
}
const formData: FormData = {
name: 'Emelie',
}
const users = await prisma.user.findMany({
where: {
OR: [
{
email: {
contains: formData.email,
},
},
],
},
})
// returns: []

The query receives filters from a formData object, which includes an optional email property. In this instance, the value of the email property is undefined. When this query is run no data is returned.

This is in contrast to the AND and NOT operators, which will both return all the users if you pass in an undefined value.

This is because passing an undefined value to an AND or NOT operator is the same as passing nothing at all, meaning the findMany query in the example will run without any filters and return all the users.

interface FormData {
name: string
email?: string
}
const formData: FormData = {
name: 'Emelie',
}
const users = await prisma.user.findMany({
where: {
AND: [
{
email: {
contains: formData.email,
},
},
],
},
})
// returns: { id: 1, email: 'ems@boop.com', name: 'Emelie' }
const users = await prisma.user.findMany({
where: {
NOT: [
{
email: {
contains: formData.email,
},
},
],
},
})
// returns: { id: 1, email: 'ems@boop.com', name: 'Emelie' }
Edit this page on GitHub