Use TypeORM Query Builder with Where, Where In and andWhere

TypeORM uses WHERE to construct complex SQL query conditions dynamically. You have different ways to use these conditions within NestJS and TypeORM. This guide will teach you how to use TypeORM with WHERE, WHERE IN, and WHERE ADD conditions.

RelatedHow to Create QueryBuilder with TypeORM and NestJS

To make this fun, you will also extend these where conditions and learn how to use TypeORM QueryBuilder with where, andWhere, and andWhereInIds to filter records with TypeORM based on conditions.

Dive into this step-by-step guide to using TypeORM QueryBuilder with WHERE, WHERE IN, andWhere, and andWhereInIds and WHERE ADD in Nest.js.

Step 1: Creating a TypeORM WHERE, WHERE IN and WHERE ADD Nest.js App

You need to ensure you have a NestJS TypeORM application. Go ahead and create a simple NestJS app:

npm i -g @nestjs/cli
nest new nestjs-where

Change the directory and point to the newly created app:

cd nestjs-where

To create a module, use the following command as a resource:

nest g resource task --no-spec

You will need to install your packages as follows:

npm install --save @nestjs/typeorm typeorm pg

Now, create your entity:

import { Column, Entity, PrimaryGeneratedColumn, CreateDateColumn} from 'typeorm';

@Entity('tasks')

export class Task {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  title: string;

  @Column()
  rating: number;

  @Column({ default: false })
  completed: boolean;

  @CreateDateColumn()
  createdAt: string;

Update the TaskModule with your entity:

import { Module } from '@nestjs/common';
import { TaskService } from './task.service';
import { TaskController } from './task.controller';
import { Task } from './entities/task.entity';
import { TypeOrmModule } from '@nestjs/typeorm';

@Module({
  imports: [TypeOrmModule.forFeature([Task])],
  controllers: [TaskController],
  providers: [TaskService],
})
export class TaskModule {}

Now, use TypeOrmModule in your AppModule as follows:

import { Module } from '@nestjs/common';
import { TypeOrmModule } from '@nestjs/typeorm';
import { Task } from './task/entities/task.entity';
import { TaskModule } from './task/task.module';

@Module({
  imports: [
    TypeOrmModule.forRoot({
      type: 'postgres',
      host: 'localhost',
      port: 5432,
      username: 'postgres',
      password: 'pass',
      database: 'task',
      synchronize: true,
      entities: [Task],
    }),
    TaskModule
  ],
})
export class AppModule {}

At this point, run your app:

npm run start:dev

This should create your database table so you can now seed the tasks table with sample records:

INSERT INTO "tasks" ("title", "rating", "completed", "createdAt") VALUES
  ('Task 1', 4, true, '2023-01-01 08:00:00'),
  ('Task 2', 5, false, '2023-01-02 10:30:00'),
  ('Task 3', 3, true, '2023-01-03 12:45:00'),
  ('Task 4', 2, false, '2023-01-04 15:15:00'),
  ('Task 5', 4, true, '2023-01-05 18:00:00'),
  ('Task 6', 3, false, '2023-01-06 20:30:00'),
  ('Task 7', 5, true, '2023-01-07 22:45:00'),

OR if you just want to use TypeORM, check this How to Seed Database with TypeORM, Nest.js and PostgreSQL guide.

Step 2: Adding TypeORM WHERE to NestJS

You need to go ahead to your task.service.ts file. This is where you will construct your queries.

But first, ensure your Provider is @Injectable() as follows:

import { Injectable } from '@nestjs/common';
import { Task } from './entities/task.entity';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository } from 'typeorm';

@Injectable()
export class TaskService {
  constructor(
    @InjectRepository(Task)
    private tasksRepository: Repository<Task>,
  ) { }
}

Step 3: Using a Simple Where Clause with TypeORM

Now, if you want to fetch data based on a condition, where runs perfectly with SQL as follows.

SELECT * FROM "tasks" WHERE "id" = 1;

But on code, this will completely change. Suppose you want to dynamically create a single task based on its ID. WHERE will need to execute a condition to determine which rows to include in the result set.

    // Fetch a single task by ID from db
    async fetchById(id: number): Promise<Task> {
      const task = await this.tasksRepository.findOne({ 
        where: { 
          id: id 
        } 
      });
      return task;
    }

In this example, you have to use where that points to the ID of the record you want TypeORM to filter.

Go to your task.controller.ts file and add the following change:

import {Controller, Get, Param} from '@nestjs/common';
import { TaskService } from './task.service';

import { Task } from './entities/task.entity';

@Controller('tasks')
export class TaskController {
  constructor(private readonly tasksService: TaskService) {}

  // Retrieve a task by ID
  @Get('/:id')
  getById(@Param('id') id: number): Promise<Task> {
    return this.tasksService.fetchById(id);
  }
}

Your app should be running on http://localhost:3000/. But to access tasks, you will use http://localhost:3000/tasks. And because your endpoint has a URL id parameter, you will use http://localhost:3000/tasks/id where id is the id number of the Task you want to extend QueryBuilder to. For example, http://localhost:3000/tasks/1

Send a GET request to http://localhost:3000/tasks/1, and WHERE should work its magic:

Or, if you want to fetch multiple records, you can still apply where clause as follows:

• Use find() to make sure all records that match the conditions get selected
• Add a Where clause based on the attributes you want to filter with.

For example, TypeORM will use where as follows to fetch all tasks with completed true values:

  // Fetch tasks if completed is true
  async fetchTasks(): Promise<Task[]> {
    const task = await this.tasksRepository.find({
      where: {
        completed: true,
      },
    });

    return task;
  }

All you now need to do is add an endpoint in your controller to test this condition:

  @Get()
  getTasks() {
    return this.tasksService.fetchTasks();
  }

Step 4: Using TypeORM WHERE Alongside TypeORM QueryBuilder

TypeORM QueryBuilder uses a design pattern and class set to abstract how to build database queries. This way. you dynamic queries based on certain conditions

In this example, let’s try to create a query builder with WHERE using TypeORM.

Let’s start using QueryBuilder and TypeORM with WHERE CLAUSE and fetch tasks based on the id. Here you need to:

• Create SelectQueryBuilder with a new instance of SelectQueryBuilder for your Task entity.
• Build Query with where method as a condition for selecting a task with an ID
• Execute the Query and retrieve the result.

The summary of this logic will look as follows:

In your Provider (task.service.ts), create a new method fetchById that takes id as a parameter as follows:

    async fetchById(id: number): Promise<Task> {
      const queryBuilder:
      SelectQueryBuilder<Task> = this.tasksRepository
      .createQueryBuilder('task');
  
      const task = await queryBuilder
        .where('task.id = :id', { id })
        .getOne();
  
      return task;
    }

Now:

• this.tasksRepository.createQueryBuilder('task') creates a new instance of SelectQueryBuilder for the Task entity, aliasing it as ’task’. In this case, the task is the data table you are using.
• The where method creates the QueryBuilder condition to select a task with an ID (task.id = :id). Keep in mind id will be a parameter passed through your API endpoint.
• Like you use select TypeORM, QueryBuilder will execute your query using getOne() and retrieve the result.
• getOne() is equivalent to findOne()

At the same time, you can use QueryBuilder and replace find() where clause using getMany. The good thing about QueryBuilder is that you can add extra conditions besides using where.

  // Find tasks using QueryBuilder with custom conditions
  async fetchTasks(): Promise<Task[]> {
    // Create a new instance of SelectQueryBuilder for the Task entity
    const queryBuilder: SelectQueryBuilder<Task> =
      this.tasksRepository.createQueryBuilder('task');
    // Build the query with custom conditions
    const tasks = await queryBuilder

      .where('task.completed = true')

      .orderBy('task.rating', 'DESC')

      .getMany();

    return tasks;
  }

In this case, you have:

• where will use where QueryBuilder method
• ORDER BY uses orderBy
• SELECT will use getMany

This example will use the following SQL query:

SELECT *
FROM tasks
WHERE completed = true
ORDER BY rating DESC;

Make sure the controller is ready:

  @Get()
  getTasks() {
    return this.tasksService.fetchTasks();
  }

Then send a GET request to http://localhost:3000/tasks/:

Step 5: TypeORM with WHERE AND

What if you want more than one where conditions? This case will let you have Multiple where Clauses. You need to use the WHERE AND clause if you want TypeORM to retrieve records that meet multiple conditions simultaneously.

However, when working with this clause, it is great to use QueryBuilder, as you will have the luxury of running as many conditions as you want without a challenge.

Let’s say you want to find all tasks where the task has a false value and must be created on a given date range. Here, you need to use the WHERE AND clause.

Now, with TypeORM QueryBuilder:

• The first where will use the where method
• The second where has and. On TypeORM queryBuilder, this will use the andWhere method
• ORDER BY uses orderBy
• SELECT will use getMany if the filter returns many records or getOne() if you apply the where method to a single record.

Let’s now add QueryBuilder magic. In your Task Provider (task.service.ts file), create the following method:

  async fetchTasks(): Promise<Task[]> {
    // Create a new instance of SelectQueryBuilder for the Task entity
    const queryBuilder: SelectQueryBuilder<Task> =
      this.tasksRepository.createQueryBuilder('task');
    // Build the query with custom conditions
    const tasks = await queryBuilder

      .where('task.completed = false')
      .andWhere('task.rating >= :rating', { rating: 3 })
      // Date filter using BETWEEN
      .andWhere('task.createdAt BETWEEN :startDate AND :endDate', {
        startDate: new Date('2023-01-01'),
        endDate: new Date('2023-01-09'),
      })
      .orderBy('task.rating', 'DESC')
      .getMany();

    return tasks;
  }

This query Fetchs all from the tasks table where:

• createdAt is between ‘2023-01-01’ and ‘2023-01-09’
• completed column is false, and
• the rating is greater than or equal to 3,

If you were using raw SQL, your query would be:

SELECT *
FROM tasks
WHERE completed = false
  AND rating >= 3
  AND created_at BETWEEN '2023-01-01' AND '2023-01-09'
ORDER BY rating DESC;

In this case, TypeORM uses QueryBuilder to allow the use of multiple andWhere conditions within your code. If you run a GET request to execute the above fetchTasks method, the results are clear as follows:

Step 6: Using TypeORM WHERE IN Clause

WHERE IN filter query results based on a specified set of values. The column’s value matches any value in a provided list or array. For example, retrieve tasks with the following IDs:

SELECT * FROM tasks WHERE id IN (1, 3, 5);

In the context of TypeORM, WHERE IN will achieve the same result using where and In:

Consider the following example:

  async taskRating(rating: number): Promise<Task[]> {
    const tasks = await this.tasksRepository.find({
      where: {
        // Using the In operator for WHERE IN
        // Pass the rating as an array to In
        rating: In([rating]), 
      },
    });

    return tasks;
  }

Note: make sure you import for in from TypeORM:

import { In, Repository, SelectQueryBuilder } from 'typeorm';

Here:

• Each task has a rating. So, you will fetch tasks using where based on the task rating.
• However, different tasks have different ratings. You can have a rating of 4 available for multiple tasks.
• Here, you need to use In and pass the rating value and get all tasks matching the value in a provided In([rating]) array.

Because you are executing WHERE IN, you will add a parameter rating that will allow you to pass the rating number as an argument to your API endpoint as follows (task.controller.ts)

  @Get('/:rating')
  getRating(@Param('rating') rating: number): Promise<Task[]> {
    return this.tasksService.taskRating(rating);
  }

Your endpoint will run as http://localhost:3000/tasks/rating. But rating represents the rating value of the tasks you want to get.

You will then send a GET request to http://localhost:3000/tasks/3 for example. Now, here you will get all tasks WHERE IN rating is 3 as follows:

If you use QueryBuilder, TypeORM WHERE IN exists as the andWhereInIds method. This means you can only use it if you have multiple primary keys where you pass an array of the key values you want to get. Here is an example:

  async findTasks(): Promise<Task[]> {
    const queryBuilder: SelectQueryBuilder<Task> =
      this.tasksRepository.createQueryBuilder('task');

    const tasks = await queryBuilder
    // Using WHERE IN clause for task IDs
      .andWhereInIds([1, 2, 3]) 
      .orderBy('task.id', 'ASC')
      .getMany();

    return tasks;
  }

Add a controller and create an endpoint to this method:

  @Get()
  getTasks() {
    return this.tasksService.findTasks();
  }

Now, GET request to http://localhost:3000/tasks will run andWhereInIds and return the tasks with the IDs you’ve passed:

Conclusion

I’m sure you have learned something and extended your knowledge to TypeORM WHERE, WHERE IN, and WHERE ADD in NestJS.

Happy coding!