TypeORM: Selecting Rows with Null Values

This succinct article is for people working with TypeORM who want to select records containing Null values. We’ll examine two examples. The first one uses the find() method, and the second uses Query Builder.

Here’s the sample entity named User that will appear in both examples:

// User entity
import { Entity, PrimaryGeneratedColumn, Column } from 'typeorm'

@Entity()
export class User {
    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    name: string;

    @Column({ nullable: true })
    address: string;
}

Using find() method

This code snippet finds all users whose addresses are null:

import { IsNull } from "typeorm";
import { User } from './entities/User'
import { dataSource } from './my-data-source'

const getUsers = async () => {
    const userRepository = dataSource.getRepository(User);
    const results = await userRepository.find({
        where: {
            address: IsNull()
        }
    })
}

Using Query Builder

This code finds all users whose addresses are null with query builder:

import {dataSource } from "./my-data-source";
import { User } from './entities/User'

const getUsers = async () => {
    const userRepository = dataSource.getRepository(User);
    const users = await userRepository
        .createQueryBuilder('user')
        .where('user.address IS NULL')
        .getMany();

    // Do something with users
}

Conclusion

We’ve walked through a couple of examples about finding rows where a column is null. If you’d like to explore more new and exciting stuff about TypeORM and the backend development world, take a look at the following articles:

• TypeORM: AND & OR operators
• TypeORM: How to store BIGINT correctly
• TypeORM: Adding Fields with Nullable/Default Data
• 2 Ways to Set Default Time Zone in Node.js
• Node.js: How to Ping a Remote Server/ Website
• Using ENUM Type in TypeORM

You can also check out our database topic page for the latest tutorials and examples.