How to match NULL columns in TypeORM find options
A find call with null in where no longer matches NULL rowsShort 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.
Fix it fast
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 }, })2Match what you see, then jump to its fix
What does the error look like?
A find call with null in where no longer matches NULL rowsA 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 where2Fix
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 NULLConfirmed by github.com
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" src2Fix
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 testConfirmed by typeorm.io
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:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- WorkforceA field app, an admin console and a self-hosted Node.js server for push and reconciliation.
Related fixes
Back end
TypeORM findOne returns a row you did not ask for
findOne() with no arguments or findOne(id) no longer works after upgrading
Before TypeORM 1.0, a null or undefined value inside where was silently skipped.
3 causes, each with a fixBack end
TypeScript cannot find module '@nestjs/core' or its type declarations
error TS2307: Cannot find module '@nestjs/common' or its corresponding type declarations.
TS2307 means TypeScript searched node_modules and found neither the package nor its types.
3 causes, each with a fixBack end
NestJS app will not start: Cannot find module '/app/dist/main'
Cannot find module 'dist/main'
Node cannot find dist/main.js because the build did not put it there.
4 causes, each with a fixBack end
TypeError: something.default is not a constructor in a NestJS app
Exception has occurred: TypeError: xxx.default is not a constructor
TypeScript turns import X from 'pkg' into require('pkg').default when esModuleInterop is off, and a CommonJS package has no default, so new X() fails.
2 causes, each with a fixBack end
TypeScript cannot find req.cookies in a NestJS controller
error TS2339: Property 'cookies' does not exist on type
TypeScript says 'cookies' is missing because the Request type in your file is not Express's, or because cookie support is not installed.
3 causes, each with a fixBack end
TypeORM warns that createConnection is deprecated
'createConnection' is deprecated.
TypeORM replaced Connection with DataSource.
2 causes, each with a fix