Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Node.js, PHP and APIs

How to match NULL columns in TypeORM find options

ErrorA find call with null in where no longer matches NULL rows

Short answer

Write IsNull() from typeorm in the where object, not null. It produces WHERE column IS NULL. Since 0.3.0 a bare null is unsupported in find methods, and in TypeORM 1.0 null and undefined in where throw an error unless you switch that off.

  • Applies to TypeORM 0.3.x, TypeORM 1.0, Node.js 22, TypeScript 5
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Turn on query logging for one call and read the SQL. If it has no condition on your column, or compares with = NULL, the null is not being turned into IS NULL.

    const rows = await AppDataSource.getRepository(Post).find({
      where: { deletedAt: null as any },
    })
  2. 2Match what you see, then jump to its fix

    1. 1The SQL has no condition for the column, or the call throws on 1.0A bare null is used where an operator is neededFix
    2. 2On TypeORM 1.0, a TypeORMError about a null or undefined where valueA variable that can be null or undefined is passed straight into whereFix

What does the error look like?

  • A find call with null in where no longer matches NULL rows
  • A find call with a null value in where throws a TypeORMError

Cause 1 of 2

A bare null is used where an operator is needed

1Check

The 0.3.0 release notes say null as a where value in find methods is no longer supported and you must use the IsNull operator.

grep -rn ": null" src --include=*.ts | grep -i where

2Fix

Replace the null with IsNull().

import { IsNull } from "typeorm"

const open = await postRepo.find({
  where: { deletedAt: IsNull() },
})

3Verify

The logged SQL now contains the IS NULL condition.

# expected: SELECT ... FROM "post" WHERE "deletedAt" IS NULL

Confirmed by github.com

Did this fix it?

Cause 2 of 2

A variable that can be null or undefined is passed straight into where

1Check

The 1.0 upgrade guide says null and undefined in where used to be skipped silently and now throw by default, controlled by invalidWhereValuesBehavior.

grep -rn "invalidWhereValuesBehavior" src

2Fix

Decide the meaning before the query: map null to IsNull(), and reject undefined. Only restore the old behavior when you must, with the DataSource option.

const where = value === null ? { deletedAt: IsNull() } : { deletedAt: value }
// old behavior, not recommended:
// invalidWhereValuesBehavior: { null: "ignore", undefined: "ignore" }

3Verify

A call with null returns only NULL rows, and a call with undefined fails loudly.

npm test

Confirmed by typeorm.io

Did this fix it?

How do I stop it coming back?

  • Import IsNull and Not wherever you write find options.
  • Validate optional filter values before they reach the repository.
  • Log SQL in tests for queries that filter on nullable columns.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.