How to Create QueryBuilder with TypeORM and NestJS

TypeORM QueryBuilder programmatically constructs complex SQL queries. It uses a design pattern and set of classes to abstract ways to build database queries. This means you generate dynamic queries based on certain conditions within your application. In this example tutorial, you will learn how to use QueryBuilder with TypeORM and Nest.js.

Dive into this step-by-step guide to using TypeORM QueryBuilder with Nest.js.

Step 1: Why Do you Need to Create QueryBuilder with TypeORM

TypeORM is TypeScript-based. This means QueryBuilder has type safety while still being able to construct queries dynamically based on certain conditions.

This way, you can abstract underlying database-specific syntax without changing your application code. This can’t be possible if you are raw SQL strings.

You will learn to create a TypeORM QueryBuilder to construct complex queries with multiple conditions and order.

Step 2: Creating a TypeORM QueryBuilder Nest.js App

Because you are using TypeORM to run QueryBuilder, ensure you have a working NestJS TypeORM application.

If not, create a simple NestJS app:

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

This should create a NestJS application that you cd to:

cd nestjs-app

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

nest g resource note --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 } from 'typeorm';

@Entity('notes')

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

  @Column()
  title: string;

  @Column()
  content: string;

  @Column()
  rating: number;
}

Update the NotesModule with your entity:

import { Module } from '@nestjs/common';
import { NotesController } from './note.controller';
import { NotesService } from './note.service';
import { TypeOrmModule } from '@nestjs/typeorm';
import { Note } from './note.entity';

@Module({
  imports: [TypeOrmModule.forFeature([Note])],
  controllers: [NotesController],
  providers: [NotesService],
})
export class NotesModule {}

And now, use TypeOrmModule in your AppModule:

import { Module } from "@nestjs/common";
import { NotesModule } from "./note/note.module";
import { TypeOrmModule } from "@nestjs/typeorm";
import { Note } from "./note/note.entity";

@Module({
  imports: [
    TypeOrmModule.forRoot({
      type: "postgres",
      host: "localhost",
      port: 5432,
      username: "postgres",
      password: "pass",
      database: "task",
      synchronize: true,
      entities: [Note],
    }),
    NotesModule,
  ],
})
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 notes table with sample records:

INSERT INTO "notes" ("title", "content", "rating") VALUES
  ('Note 1', 'This is the content of note 1.', 4),
  ('Note 2', 'Another note with different content.', 5),
  ('Note 3', 'A third note with a different rating.', 3),
  ('Note 4', 'Content for the fourth note.', 2),
  ('Note 5', 'Fifth note content here.', 4);

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

Step 3: Adding TypeORM QueryBuilder to NestJS

QueryBuilders are executed using Providers. Therefore, you need to go ahead to your note.service.ts file. This is where you will construct your queries.

But first, ensure your Provider is Injectable as follows:

import { Injectable, NotFoundException } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Note } from './note.entity';
import { Repository } from 'typeorm';

@Injectable()
export class NotesService {
  constructor(
    @InjectRepository(Note)
    private notesRepository: Repository<Note>,
  ) { }
}

Let’s now create your first QueryBuilder with TypeORM.

Step 4: Using Where QueryBuilder Clause

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

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

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

Here you need to:

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

The summary of this logic will look as follows:

In your Provider, create a new method fetchNoteById that takes id as a parameter as follows:

  // Fetch a single note by ID from the database using QueryBuilder
  async fetchNoteById(id: number): Promise<Note> {
    const queryBuilder:
    SelectQueryBuilder<Note> = this.notesRepository
    .createQueryBuilder('note');

    const found = await queryBuilder
      .where('note.id = :id', { id })
      .getOne();

    if (!found) {
      throw new NotFoundException(`Note "${id}" not found`);
    }

    return found;
  }

Note: Make sure you have the SelectQueryBuilder imported from TypeORM:

import { Repository, SelectQueryBuilder } from 'typeorm';

Now:

• this.notesRepository.createQueryBuilder('note') creates a new instance of SelectQueryBuilder for the Note entity, aliasing it as note.
• The where method creates the QueryBuilder condition to select a note with an ID (note.id = :id). Keep in mind id will be a parameter passed through your API endpoint.
• Just as you use select, TypeORM and QueryBuilder will execute your query using getOne() and retrieve the result.

Let’s test this example. Go ahead and create a simple endpoint. Go to your note.controller.ts file and add the following change:

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

import { Note } from './note.entity';

@Controller('notes')
export class NotesController {
  constructor(private readonly notesService: NotesService) {}

  // Retrieve a note by ID
  @Get('/:id')
  getNoteById(@Param('id') id: number): Promise<Note> {
    return this.notesService.fetchNoteById(id);
  }
}

Related:How to use TypeORM WHERE, WHERE IN and WHERE ADD in NestJS

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

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

Step 5: Extending TypeORM QueryBuilder with Multiple Condition

What if you want more conditions? Well, just SQL queries, TypeORM QueryBuilder will do exactly what you tell it to do.

In this New QueryBuilder, I want to show you how to add:

• Multiple where Clauses
• orderBy and
• select many rows instead of just getOne()

If you are using SQL, this query will be:

SELECT *
FROM notes
WHERE title LIKE 'Note%'
  AND rating >= 4
ORDER BY id DESC;

This query fetches all from the records in notes table where the title column starts with ‘Note’, the rating column is greater than or equal to 4, and the results are ordered by the rating column in descending order.

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 to select all records that meets these conditions.

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

    // Find notes using QueryBuilder with custom conditions
    async findNotes(): Promise<Note[]> {
      // Create a new instance of SelectQueryBuilder for the Note entity
      const queryBuilder: 
      SelectQueryBuilder<Note> = this.notesRepository
      // QueryBuilder for the Note entity, aliasing it as 'note'
      .createQueryBuilder('note');
      // Build the query with custom conditions
      const notes = await queryBuilder

        .where('note.title LIKE :title', { title: 'Note%' })  // Specify the condition for selecting notes with a title starting with 'Note'
        // condition for selecting notes with a rating greater than or equal 4
        .andWhere('note.rating >= :rating', { rating: 4 }) 
        // Order the results by rating in descending order
        .orderBy('note.rating', 'DESC')
        // Execute the query and retrieve the result
        .getMany();

      // Return the array of notes
      return notes;
    }

To execute it, create a controller with an endpoint as follows:

  @Get()
  getNotes() {
    return this.notesService.fetchNotes();
  }

Send a GET result to http://localhost:3000/notes. Here are my results from Postman:

Step 6: Using TypeORM QueryBuilder with LIMIT and OFFSET Clauses

Let’s say you want to limit the number of rows returned or skip a certain number of rows. LIMIT and OFFSET clauses combination is what you need.

For example, LIMIT 10 OFFSET 20 will return 10 rows, starting from the 21st row. Let’s try LIMIT 5 OFFSET 8 and return 5 rows, starting from the 5th row in our Note app.

We only need to use:

• limit(5)
• offset(8)

As follows:

    async findNotes(): Promise<Note[]> {
      const queryBuilder: SelectQueryBuilder<Note> = this.notesRepository.createQueryBuilder('note');
    
      const notes = await queryBuilder
        .orderBy('note.id', 'ASC')
        .limit(5)
        .offset(8)
        .getMany();
    
      return notes;
    }

Step 7: Using DISTINCT QueryBuilder (Postgres only)

You have used getMany() and getOne() so far. Let me show you another trick of using getRawMany() while using QueryBuilder to execute the DISTINCT clause.

DISTINCT retrieves only distinct rating values in a result set as follows:

  // Find distinct ratings from the notes table
  async findNotes(): Promise<number[]> {
    const queryBuilder: SelectQueryBuilder<Note> = this.notesRepository.createQueryBuilder('note');

    // Use the DISTINCT clause to retrieve distinct ratings
    const distinctRatings = await queryBuilder
      .distinct(true)
      .select('note.rating')
      .getRawMany();

    // Extract distinct ratings from the result
    return distinctRatings.map(result => result.note_rating);
  }

You need to:

• Add DISTINCT to the query using .distinct(true)
• Select distinct values from the rating column.
• Use getRawMany() to query and retrieve the result as raw data
• Use .map(result => result.note_rating) to extract the distinct ratings into an array of numbers.

Conclusion

This was a basic example to get you started with TypeORM QueryBuilder. I hope you can now use this programmatic, type-safe, and dynamic approach to building database queries within your code.