How can I have IS NULL condition in TypeORM find options?

Question

In my queries I'm using TypeORM find option. How can I have IS NULL condition in the where clause?

Answer 1

Another way is you can use IsNull() function, for example:

import { IsNull } from "typeorm";
return await getRepository(User).findOne({
    where: { 
      username: IsNull()
    }
});

Answer 2

If someone is looking for NOT NULL, it would be like this:

import { IsNull, Not } from "typeorm";

return await getRepository(User).findOne({
    where: { 
      username: Not(IsNull())
    }
});

Answer 3

You can use QueryBuilder for this purpose:

const users = await userRepository.createQueryBuilder("user")
     .where("user.name IS NULL")
     .getMany();

Answer 4

In addition to hungneox answer you should know that you have a lot of predefined operators.

This is from the file that defines it:

export declare type FindOperatorType = "not" | 
"lessThan" | 
"lessThanOrEqual" | 
"moreThan" | 
"moreThanOrEqual" | 
"equal" | 
"between" | 
"in" | 
"any" | 
"isNull" | 
"like" | 
"raw";

Each of the above can be set in the "Operator" part here:

{ 
  where: { 
    propertyToCheck: <Operator>
  }
}

You just import it from the @typeorm package and use it like a function e.g LessThan():

import { Repository, Between, IsNull, LessThan } from 'typeorm';

{ 
  where: { 
    age: LessThan(50)
  }
}

This is really strong and important tool to know if you want to master typeorm :) Good luck!

Answer 5

I really don't like to have to use the QueryBuilder from TypeORM for this as this should, in my opinion, be treated as expected when using FindConditions.

Unfortunately, with something like the following code:

async articleRequests(
  accepted?: ArticleRequestAcceptance,
): Promise<ArticleRequest[]> {
  const where: FindConditions<ArticleRequest>[] | FindConditions<ArticleRequest> = {};

  if (accepted !== undefined) {
    switch (accepted) {
      case ArticleRequestAcceptance.Accepted:
        where.accepted = true;
        break;
      case ArticleRequestAcceptance.Rejected:
        where.accepted = false;
        break;
      case ArticleRequestAcceptance.NotReviewedYet:
        where.accepted = undefined;
        break;
    }
  }

  return await ArticleRequest.find({ where }).catch(reason => {
    throw reason.message;
  });
}

TypeORM gets you a SQL query that looks like this:

SELECT '...' WHERE "ArticleRequest"."accepted" = NULL

because, as can be seen from TypeORM log output, ... WHERE "ArticleRequest"."accepted" = @0 -- PARAMETERS: [null], properties with undefined values (accepted in this case) get converted to nulls inside the parameters array and then they are simply injected into the SQL string.

The SQL standard says that any comparison with null results in null so for comparison operators, like = or <>, in SQL this should make no sense, but the reasoning is that comparing to null means "unknown" so that why such queries don't return any results. If you ask me, SQL is broken here.

So yeah, as @hungneox said, the solution is to use IsNull() which returns a special FindOperator for that specific column you need to be queried as IS NULL and not = NULL.

Like this:

  if (accepted !== undefined) {
    switch (accepted) {
      case ArticleRequestAcceptance.Accepted:
        where.accepted = true;
        break;
      case ArticleRequestAcceptance.Rejected:
        where.accepted = false;
        break;
      case ArticleRequestAcceptance.NotReviewedYet:
        where.accepted = IsNull();
        break;
    }
  }

Answer 6

TheModel.find({ theField: null })

will do as you'd expect.