TypeORM Tips (Part 2: Use where() with care)

Hey everyone! This is the second post in my series on tips while developing with TypeORM, a popular typescript ORM library. In this post I will remark on a common pitfall when using the where() method in the library.

Use where() with care

TypeORM's QueryBuilder provides a where() method to add SQL WHERE clauses in a your queries which allows you to specify a condition to control the records which your query processes. Here's a code snippet illustrating how you can use this method:

const query = await this.userRepository.createQueryBuilder()
             .select()
             .where("user.id=:userId", {userId: "123"})
             .getOne();

This method follows a similar pattern to several other methods of QueryBuilder which let you successively chain methods. Each method call returns back another QueryBuilder instance. Here is an example of a chain which performs multiple LEFT JOINS, filters by a where condition and finally limits the query result to just 10 rows:

const query = await this.userRepository.createQueryBuilder('user')
            .leftJoinAndSelect('user.posts', 'posts')
            .leftJoinAndSelect('user.comments', 'comments')
            .where('user.age > :minAge', { minAge: 18 })
            .limit(10)
            .getMany();

Neat! Now lets say I want to add another filter on this query to ensure that the user age is also under 65 years old. Naturally, if I were to follow the chain pattern offered by the library I might do the following:

const query = await this.userRepository.createQueryBuilder('user')
            .leftJoinAndSelect('user.posts', 'posts')
            .leftJoinAndSelect('user.comments', 'comments')
            .where('user.age > :minAge', { minAge: 18 })
            .where('user.age < :maxAge', { maxAge: 65 })
            .limit(10)
            .getMany();

TypeORM successfully executes the above and doesn't give any compile-time/runtime warnings at all. But this piece of code will not filter out records correctly!

What's the problem?

Adding multiple where() clauses to a query doesn't make sure all of them are satisfied. Rather, TypeORM only picks the last where() in the chain and uses that as the sole filter for the query. In other words, successive where() clauses just override previous clauses instead of adding new conditions. Thus the above code snippet will just return users whose age is less than 65 (i.e, The condition user > 18 won't be enforced!).

This is vague as the library doesn't complain with this usage and can sometimes blindside developers. If a developer didn't test the above code on corner-cases, he/she might unknowingly deploy this on production and may discover the edge case only much later when the bug is reported.

How do you fix this?

The correct usage is to use andWhere or orWhere depending on if you want to concatenate multiple conditions using AND or OR. For example, the above code snippet can be correct to:

const query = await this.userRepository.createQueryBuilder('user')
            .leftJoinAndSelect('user.posts', 'posts')
            .leftJoinAndSelect('user.comments', 'comments')
            .where('user.age > :minAge', { minAge: 18 })
            .andWhere('user.age < :maxAge', { maxAge: 65 })
            .limit(10)
            .getMany();

You can also use the Brackets function to create more complicated queries. Say I wanted to check if the user falls in either of two age ranges (18<age<35 OR 50<age<65), I could do the following:

const query = await this.userRepository.createQueryBuilder('user')
            .leftJoinAndSelect('user.posts', 'posts')
            .leftJoinAndSelect('user.comments', 'comments')
            .where(new Brackets(qb => 
                qb.where('user.age > :minAge', { minAge: 18 })
                  .andWhere('user.age < :maxAge', { maxAge: 35 }))
            .orWhere(new Brackets(qb => 
                qb.where('user.age > :minAge', { minAge: 50 })
                  .andWhere('user.age < :maxAge', { maxAge: 65 }))
            .limit(10)
            .getMany();

Note that here it was completely safe to use multiple where() clauses as the other usages actually operate on a seperate QueryBuilder instance and not the parent one. The basic rule to follow is to avoid multiple where() method calls on the same QueryBuilder instance.