TypeORM QueryBuilder

The repository helpers (find, findOne, save) cover most day-to-day persistence, but real applications eventually need queries the object-style API cannot express: multi-table joins, conditional filters built at runtime, aggregations, or paginated search. TypeORM’s QueryBuilder is a fluent, type-aware API that generates SQL while still mapping results back to entities. Because every clause is chained programmatically, you can assemble queries dynamically without ever concatenating raw SQL strings.

Creating a query builder

You obtain a QueryBuilder from any injected repository (or from the DataSource/EntityManager). Inside a NestJS service, inject the repository as usual and call createQueryBuilder, passing an alias that names the root entity for the rest of the query.

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

@Injectable()
export class UsersService {
  constructor(
    @InjectRepository(User)
    private readonly users: Repository<User>,
  ) {}

  findActive() {
    return this.users
      .createQueryBuilder('user')
      .where('user.isActive = :active', { active: true })
      .orderBy('user.createdAt', 'DESC')
      .getMany();
  }
}

The alias user is used to qualify every column reference. getMany() returns an array of fully hydrated User entities, while getOne() returns a single entity or null.

Filtering with parameters

Always pass values as named parameters rather than interpolating them into the string. TypeORM binds them safely, which prevents SQL injection and lets the database cache the query plan. Use where to start the predicate and andWhere / orWhere to extend it.

async search(term: string, minAge?: number) {
  const qb = this.users
    .createQueryBuilder('user')
    .where('user.name ILIKE :term', { term: `%${term}%` });

  if (minAge !== undefined) {
    qb.andWhere('user.age >= :minAge', { minAge });
  }

  return qb.getMany();
}

Because the builder is mutable, you can conditionally append clauses — ideal for filter forms where each field is optional. For grouped OR logic, wrap conditions with a Brackets instance:

import { Brackets } from 'typeorm';

qb.andWhere(
  new Brackets((b) => {
    b.where('user.role = :admin', { admin: 'admin' })
      .orWhere('user.role = :owner', { owner: 'owner' });
  }),
);

Reusing the same parameter name with different values across andWhere calls overwrites the binding. Give each placeholder a unique name (:term1, :term2) when values differ.

Joins are where the builder shines. leftJoinAndSelect joins a relation and includes its columns in the result, populating the entity’s relation property. Use plain leftJoin (without AndSelect) when you only need the join for filtering and don’t want the extra columns.

findWithPosts(userId: number) {
  return this.users
    .createQueryBuilder('user')
    .leftJoinAndSelect('user.posts', 'post')
    .leftJoinAndSelect('post.comments', 'comment')
    .where('user.id = :userId', { userId })
    .getOne();
}

You can attach conditions to a join itself, which is different from filtering in the outer where — it limits which rows are joined rather than which root rows survive.

.leftJoinAndSelect('user.posts', 'post', 'post.published = :pub', { pub: true })

Method	Joins relation	Selects columns	Effect on root rows
`innerJoin`	Yes	No	Excludes roots with no match
`innerJoinAndSelect`	Yes	Yes	Excludes roots with no match
`leftJoin`	Yes	No	Keeps all roots
`leftJoinAndSelect`	Yes	Yes	Keeps all roots

Ordering, pagination, and counts

Combine orderBy/addOrderBy with skip and take to paginate. Use getManyAndCount() to fetch a page and the total row count in one call — essential for rendering page controls.

async paginate(page: number, size: number) {
  const [items, total] = await this.users
    .createQueryBuilder('user')
    .orderBy('user.createdAt', 'DESC')
    .addOrderBy('user.id', 'ASC')
    .skip((page - 1) * size)
    .take(size)
    .getManyAndCount();

  return { items, total, page, pages: Math.ceil(total / size) };
}

When a query has joins, prefer skip/take (which TypeORM applies with a correct subquery) over offset/limit. The raw offset/limit operate on the joined row set and can return fewer root entities than expected.

Subqueries

A subquery is built with a callback receiving a fresh sub-builder. This is useful for IN filters or correlated lookups.

findWithRecentOrders() {
  return this.users
    .createQueryBuilder('user')
    .where((qb) => {
      const sub = qb
        .subQuery()
        .select('order.userId')
        .from('orders', 'order')
        .where('order.createdAt > :since', { since: '2026-01-01' })
        .getQuery();
      return 'user.id IN ' + sub;
    })
    .getMany();
}

Aggregations and raw results

For aggregates, computed columns, or anything that does not map cleanly to an entity, use the raw getters. getRawMany() returns plain objects keyed by the selected column aliases.

async postCounts() {
  return this.users
    .createQueryBuilder('user')
    .leftJoin('user.posts', 'post')
    .select('user.id', 'userId')
    .addSelect('COUNT(post.id)', 'postCount')
    .groupBy('user.id')
    .getRawMany<{ userId: number; postCount: string }>();
}

Output:

[
  { "userId": 1, "postCount": "12" },
  { "userId": 2, "postCount": "3" }
]

Note that COUNT returns a string in PostgreSQL drivers — cast it with Number() before use. When you need both the entity and raw aggregates, getRawAndEntities() returns { entities, raw }.

Best Practices

Always bind values with named parameters; never interpolate user input into the query string.
Use leftJoinAndSelect only when you actually need the related data — joins without AndSelect are cheaper for filter-only relations.
Prefer getManyAndCount() for paginated endpoints so you fetch items and totals in a single round trip.
Wrap grouped OR conditions in Brackets to avoid accidental operator-precedence bugs.
Reach for getRawMany() only for aggregations or computed columns; keep entity-shaped queries on getMany() for type safety.
Extract complex builders into named repository methods so the SQL intent stays discoverable and testable.