Repositories, QueryBuilder, and advanced queries

The Repository pattern

TypeORM exposes a Repository<Entity> for each entity that provides a complete set of database operations. NestJS injects it via @InjectRepository(Entity).

The repository is the data access layer — it translates your TypeScript method calls into SQL queries. Services use repositories to read and write data without writing raw SQL.

Basic CRUD operations

Finding records

// Find all with basic options
await this.booksRepo.find({
  where: { isAvailable: true },
  order: { title: 'ASC' },
  skip: 0,
  take: 10,
  relations: { author: true },
});

// Find one by primary key
await this.booksRepo.findOneBy({ id: 1 });

// Find one with complex where
await this.booksRepo.findOne({
  where: { isbn: '9780132350884' },
  relations: { author: true, categories: true },
});

// Find and count (for pagination)
const [books, total] = await this.booksRepo.findAndCount({
  where: { authorId: 5 },
  take: 10,
  skip: 0,
});

Creating records

// create() builds the entity from a plain object
const book = this.booksRepo.create({
  title: 'Clean Code',
  isbn: '9780132350884',
  price: 39.99,
  authorId: 1,
});

// save() persists the entity (INSERT or UPDATE based on whether id exists)
const savedBook = await this.booksRepo.save(book);

Updating records

// update() — partial update by criteria (does not load the entity first)
await this.booksRepo.update({ id }, { price: 29.99 });

// preload() — load existing entity and merge with new data
const toUpdate = await this.booksRepo.preload({ id, ...updateDto });
if (!toUpdate) throw new NotFoundException();
return this.booksRepo.save(toUpdate);

Deleting records

// Hard delete by criteria
await this.booksRepo.delete({ id });

// Soft delete (requires @DeleteDateColumn on entity)
await this.booksRepo.softDelete({ id });

// Remove entity instance (triggers @BeforeRemove hook)
const book = await this.findOne(id);
await this.booksRepo.remove(book);

Where operators

TypeORM exports a set of operator functions for complex where clauses:

import {
  ILike, Like, In, Not, IsNull, Between, MoreThan, LessThan,
  MoreThanOrEqual, LessThanOrEqual, ArrayContains,
} from 'typeorm';

// Case-insensitive LIKE (PostgreSQL)
await this.booksRepo.find({ where: { title: ILike('%clean%') } });

// IN clause
await this.booksRepo.find({ where: { authorId: In([1, 2, 3]) } });

// NOT
await this.booksRepo.find({ where: { price: Not(0) } });

// NULL check
await this.booksRepo.find({ where: { deletedAt: IsNull() } });

// Range
await this.booksRepo.find({ where: { price: Between(10, 50) } });

// Multiple conditions (OR)
await this.booksRepo.find({
  where: [
    { title: ILike('%nestjs%') },
    { author: { lastName: ILike('%martin%') } },
  ],
});

Relations in find queries

Load relations explicitly in find queries:

// Object syntax (recommended for type safety)
await this.booksRepo.find({
  relations: {
    author: true,
    categories: true,
  },
});

// Nested relations
await this.booksRepo.find({
  relations: {
    author: {
      profile: true, // Load author AND their profile
    },
  },
});

QueryBuilder for complex queries

The Repository.createQueryBuilder() method provides a fluent API for constructing complex SQL queries that the find API cannot express.

Basic QueryBuilder

const books = await this.booksRepo
  .createQueryBuilder('book')
  .leftJoinAndSelect('book.author', 'author')
  .where('book.price > :minPrice', { minPrice: 20 })
  .andWhere('author.country = :country', { country: 'US' })
  .orderBy('book.price', 'ASC')
  .getMany();

Subqueries

const booksWithOrders = await this.booksRepo
  .createQueryBuilder('book')
  .where((qb) => {
    const subQuery = qb
      .subQuery()
      .select('order.bookId')
      .from(Order, 'order')
      .where('order.status = :status', { status: 'completed' })
      .getQuery();
    return `book.id IN ${subQuery}`;
  })
  .getMany();

Aggregate functions

const stats = await this.booksRepo
  .createQueryBuilder('book')
  .select('book.authorId', 'authorId')
  .addSelect('COUNT(book.id)', 'bookCount')
  .addSelect('AVG(book.price)', 'avgPrice')
  .groupBy('book.authorId')
  .having('COUNT(book.id) > :min', { min: 5 })
  .getRawMany<{ authorId: number; bookCount: string; avgPrice: string }>();

Note that aggregate results come as getRawMany() with string values for numbers — parse them explicitly.

Custom repositories

For reusable complex queries, create a custom repository class:

import { Repository, DataSource } from 'typeorm';
import { Injectable } from '@nestjs/common';

@Injectable()
export class BooksRepository extends Repository<Book> {
  constructor(private readonly dataSource: DataSource) {
    super(Book, dataSource.createEntityManager());
  }

  async findByAuthorWithStats(authorId: number): Promise<Book[]> {
    return this.createQueryBuilder('book')
      .where('book.authorId = :authorId', { authorId })
      .leftJoinAndSelect('book.categories', 'category')
      .orderBy('book.createdAt', 'DESC')
      .getMany();
  }
}

Register it as a provider and inject it instead of the generic Repository<Book>.

Transactions

Use the DataSource.transaction() method for multi-step operations that must be atomic:

async transferAuthor(bookId: number, newAuthorId: number): Promise<void> {
  await this.dataSource.transaction(async (manager) => {
    const book = await manager.findOneByOrFail(Book, { id: bookId });
    const author = await manager.findOneByOrFail(Author, { id: newAuthorId });

    book.authorId = author.id;
    await manager.save(book);

    // Log the transfer
    const log = manager.create(AuditLog, {
      action: 'transfer_author',
      bookId,
      newAuthorId,
    });
    await manager.save(log);
  });
  // If any operation fails, the entire transaction rolls back
}

Pagination utilities

A reusable pagination helper:

interface PaginatedResult<T> {
  data: T[];
  meta: {
    total: number;
    page: number;
    lastPage: number;
    limit: number;
  };
}

async paginate<T>(
  repository: Repository<T>,
  page: number,
  limit: number,
  options?: FindManyOptions<T>,
): Promise<PaginatedResult<T>> {
  const [data, total] = await repository.findAndCount({
    ...options,
    skip: (page - 1) * limit,
    take: limit,
  });

  return {
    data,
    meta: {
      total,
      page,
      lastPage: Math.ceil(total / limit),
      limit,
    },
  };
}