Null and undefined

warning

Prior to Prisma ORM 5.20.0, undefined was treated as a special value that would not be included in the generated query. This behavior could lead to unexpected results and data loss. If you are using an older version of Prisma ORM, we strongly recommend updating to 5.20.0 or later to take advantage of the new strictUndefinedChecks feature.

For documentation on the legacy behavior, see Legacy behavior.

Strict undefined checks (Preview feature)

Prisma ORM 5.20.0 introduces a new Preview feature called strictUndefinedChecks. This feature changes how Prisma Client handles undefined values, offering better protection against accidental data loss or unintended query behavior.

Enabling strict undefined checks

To enable this feature, add the following to your Prisma schema:

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["strictUndefinedChecks"]
}

Using strict undefined checks

When this feature is enabled:

Explicitly setting a field to undefined in a query will cause a runtime error.
To skip a field in a query, use the new Prisma.skip symbol instead of undefined.

Example usage:

// This will throw an error
prisma.user.create({
  data: {
    name: 'Alice',
    email: undefined // Error: Cannot explicitly use undefined here
  }
})

// Use `Prisma.skip` (a symbol provided by Prisma) to omit a field
prisma.user.create({
  data: {
    name: 'Alice',
    email: Prisma.skip // This field will be omitted from the query
  }
})

This change helps prevent accidental deletions or updates, such as:

// Before: This would delete all users
prisma.user.deleteMany({
  where: {
    id: undefined
  }
})

// After: This will throw an error
Invalid \`prisma.user.deleteMany()\` invocation in
/client/tests/functional/strictUndefinedChecks/test.ts:0:0
  XX })
  XX 
  XX test('throws on undefined input field', async () => {
→ XX   const result = prisma.user.deleteMany({
         where: {
           id: undefined
               ~~~~~~~~~
         }
       })
Invalid value for argument \`where\`: explicitly \`undefined\` values are not allowed."

Migration path

To migrate existing code:

// Before
let optionalEmail: string | undefined

prisma.user.create({
  data: {
    name: 'Alice',
    email: optionalEmail
  }
})

// After
prisma.user.create({
  data: {
    name: 'Alice',
    email: optionalEmail ?? Prisma.skip
  }
})

This new behavior is intended to become the default in Prisma ORM 6.

`exactOptionalPropertyTypes`

In addition to strictUndefinedChecks, we also recommend enabling the TypeScript compiler option exactOptionalPropertyTypes. This option enforces that optional properties must match exactly, which can help catch potential issues with undefined values in your code. While strictUndefinedChecks will raise runtime errors for invalid undefined usage, exactOptionalPropertyTypes will catch these issues during the build process.

Learn more about exactOptionalPropertyTypes in the TypeScript documentation.

Feedback

As always, we welcome your feedback on this feature. Please share your thoughts and suggestions in the GitHub discussion for this Preview feature.

Legacy behavior

warning

This section is relevant if you are using Prisma ORM 5.19.1 or earlier.

Prisma Client differentiates between null and undefined:

null is a value
undefined means do nothing

info

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

The data below represents a User table. This set of data will be used in all of the examples below:

id	name	email
1	Nikolas	nikolas@gmail.com
2	Martin	martin@gmail.com
3	empty	sabin@gmail.com
4	Tyler	tyler@gmail.com

`null` and `undefined` in queries that affect many records

This section will cover how undefined and null values affect the behavior of queries that interact with or create multiple records in a database.

Null

Consider the following Prisma Client query which searches for all users whose name value matches the provided null value:

const users = await prisma.user.findMany({
  where: {
    name: null,
  },
})

Show query results

[
  {
    "id": 3,
    "name": null,
    "email": "sabin@gmail.com"
  }
]

Because null was provided as the filter for the name column, Prisma Client will generate a query that searches for all records in the User table whose name column is empty.

Undefined

Now consider the scenario where you run the same query with undefined as the filter value on the name column:

const users = await prisma.user.findMany({
  where: {
    name: undefined,
  },
})

Show query results

[
  {
    "id": 1,
    "name": "Nikolas",
    "email": "nikolas@gmail.com"
  },
  {
    "id": 2,
    "name": "Martin",
    "email": "martin@gmail.com"
  },
  {
    "id": 3,
    "name": null,
    "email": "sabin@gmail.com"
  },
  {
    "id": 4,
    "name": "Tyler",
    "email": "tyler@gmail.com"
  }
]

Using undefined as a value in a filter essentially tells Prisma Client you have decided not to define a filter for that column.

An equivalent way to write the above query would be:

const users = await prisma.user.findMany()

This query will select every row from the User table.

info

Note: Using undefined as the value of any key in a Prisma Client query's parameter object will cause Prisma ORM to act as if that key was not provided at all.

Although this section's examples focused on the findMany function, the same concepts apply to any function that can affect multiple records, such as updateMany and deleteMany.

`null` and `undefined` in queries that affect one record

This section will cover how undefined and null values affect the behavior of queries that interact with or create a single record in a database.

warning

Note: null is not a valid filter value in a findUnique() query.

The query behavior when using null and undefined in the filter criteria of a query that affects a single record is very similar to the behaviors described in the previous section.

Null

Consider the following query where null is used to filter the name column:

const user = await prisma.user.findFirst({
  where: {
    name: null,
  },
})

Show query results

[
  {
    "id": 3,
    "name": null,
    "email": "sabin@gmail.com"
  }
]

Because null was used as the filter on the name column, Prisma Client will generate a query that searches for the first record in the User table whose name value is empty.

Undefined

If undefined is used as the filter value on the name column instead, the query will act as if no filter criteria was passed to that column at all.

Consider the query below:

const user = await prisma.user.findFirst({
  where: {
    name: undefined,
  },
})

Show query results

[
  {
    "id": 1,
    "name": "Nikolas",
    "email": "nikolas@gmail.com"
  }
]

In this scenario, the query will return the very first record in the database.

Another way to represent the above query is:

const user = await prisma.user.findFirst()

Although this section's examples focused on the findFirst function, the same concepts apply to any function that affects a single record.

`null` and `undefined` in a GraphQL resolver

For this example, consider a database based on the following Prisma schema:

model User {
  id    Int     @id @default(autoincrement())
  email String  @unique
  name  String?
}

In the following GraphQL 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 Client, the following will happen:

If args.authorEmail is null, the query will fail. email does not accept null.
If args.authorName is null, Prisma Client 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 Client 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: 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' }

Strict undefined checks (Preview feature)​

Enabling strict undefined checks​

Using strict undefined checks​

Migration path​

exactOptionalPropertyTypes​

Feedback​

Legacy behavior​

null and undefined in queries that affect many records​

Null​

Undefined​

null and undefined in queries that affect one record​

Null​

Undefined​

null and undefined in a GraphQL resolver​

The effect of null and undefined on conditionals​

Strict undefined checks (Preview feature)

Enabling strict undefined checks

Using strict undefined checks

Migration path

`exactOptionalPropertyTypes`

Feedback

Legacy behavior

`null` and `undefined` in queries that affect many records

Null

Undefined

`null` and `undefined` in queries that affect one record

Null

Undefined

`null` and `undefined` in a GraphQL resolver

The effect of `null` and `undefined` on conditionals