Query builder in NestJS

Introduction

A query builder helps you create database queries easily without writing raw SQL. It makes building queries safer and clearer.

When you want to fetch data from a database with conditions.

When you need to update or delete records based on specific rules.

When you want to join tables and get combined data.

When you want to avoid writing raw SQL to prevent errors or injections.

When you want to build queries dynamically based on user input.

Syntax

NestJS

const query = dataSource
  .getRepository(Entity)
  .createQueryBuilder('alias')
  .where('alias.field = :value', { value: someValue })
  .getMany();

Use createQueryBuilder on a repository or data source to start building a query.

Use where with parameters to safely add conditions.

Examples

Fetches all users older than 18.

NestJS

const users = dataSource
  .getRepository(User)
  .createQueryBuilder('user')
  .where('user.age > :age', { age: 18 })
  .getMany();

Fetches a single user with ID 1.

NestJS

const user = dataSource
  .getRepository(User)
  .createQueryBuilder('user')
  .where('user.id = :id', { id: 1 })
  .getOne();

Counts how many users are active.

NestJS

const count = dataSource
  .getRepository(User)
  .createQueryBuilder('user')
  .where('user.isActive = :active', { active: true })
  .getCount();

Sample Program

This service method uses the query builder to get all active users older than a given age.

NestJS

import { Injectable } from '@nestjs/common';
import { DataSource } from 'typeorm';
import { User } from './user.entity';

@Injectable()
export class UserService {
  constructor(private dataSource: DataSource) {}

  async getActiveUsersAboveAge(age: number): Promise<User[]> {
    return this.dataSource
      .getRepository(User)
      .createQueryBuilder('user')
      .where('user.isActive = :active', { active: true })
      .andWhere('user.age > :age', { age })
      .getMany();
  }
}

Important Notes

Always use parameters (like :age) to prevent SQL injection.

You can chain multiple where and andWhere calls to add more conditions.

Query builder works well with TypeORM in NestJS for database operations.

Summary

Query builder helps build safe and readable database queries.

Use it to fetch, update, or delete data with conditions.

It avoids raw SQL and reduces errors.