Null and undefined
Prisma Client differentiates between null
and undefined
:
null
is a valueundefined
means do nothing
Note: This is particularly important to account for in a Prisma with GraphQL context, where
null
andundefined
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 @uniqueid Int @id @default(autoincrement())name String?posts Post[]}model Post {id String @id @default(cuid())title StringauthorId 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
isnull
, the query will fail -email
does not acceptnull
✘ - If
args.authorName
isnull
, Prisma changes the value ofname
tonull
- 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 nullname: 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 nothingname: 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.
Operator | 0 filters | 1 filter | n filters |
---|---|---|---|
OR | return empty list | validate single filter | validate all filters |
AND | return all items | validate single filter | validate all filters |
NOT | return all items | validate single filter | validate all filters |
This example shows how an undefined
parameter impacts the results returned by a query that uses the OR
operator.
interface FormData {name: stringemail?: 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 anAND
orNOT
operator is the same as passing nothing at all, meaning thefindMany
query in the example will run without any filters and return all the users.
interface FormData {name: stringemail?: 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' }