TypeORM Tips (Part 2: Use where() with care)
Tags: javascript, database, node, tutorial
Published: 2022-01-22 19:15:01

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: 

```typescript
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:

```typescript
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:

```typescript
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:

```typescript
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:

```typescript
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.