How to properly use IsNotNull/ IsNull in Typeorm?

In TypeORM, IsNotNull and IsNull are functions used to build SQL queries, particularly useful for handling NULL values in queries. These functions help filter records in the database where column values are NULL or NOT NULL.

Using IsNull

When you need to find all records where a specific column value is NULL, you can use the IsNull function. For example, suppose we have an entity named User that includes a lastLoginDate field which may be NULL. If we want to find all users who have never logged in (i.e., users where the lastLoginDate field is NULL), you can write it as:

typescript
import { getRepository, IsNull } from "typeorm";
import { User } from "./entity/User";

async function findUsersNeverLoggedIn() {
  const userRepository = getRepository(User);
  const users = await userRepository.find({
    where: {
      lastLoginDate: IsNull()
    }
  });
  return users;
}

Using IsNotNull

Conversely, when you need to find all records where a specific column value is NOT NULL, you can use the IsNotNull function. Using the User entity as an example, if we want to find all users who have logged in at least once (i.e., users where the lastLoginDate field is NOT NULL), you can write it as:

typescript
import { getRepository, IsNotNull } from "typeorm";
import { User } from "./entity/User";

async function findUsersLoggedIn() {
  const userRepository = getRepository(User);
  const users = await userRepository.find({
    where: {
      lastLoginDate: IsNotNull()
    }
  });
  return users;
}

Summary

Using IsNull and IsNotNull can conveniently handle NULL value issues in the database; they are concise methods provided by TypeORM to build queries involving NULL value logic. As shown in the examples, these functions help developers simplify code and intuitively handle data filtering requirements in practical applications. These functions are particularly useful when dealing with data integrity and optional fields, helping developers write clearer and more maintainable code.