Back to all posts
October 14, 2025Charlie BrownDevelopment

NestJS TypeORM Advanced Query Techniques: Beyond the Basics

Explore advanced TypeORM query techniques in NestJS including query builders, subqueries, raw SQL, database views, and performance optimization strategies.

NestJS TypeORM Advanced Query Techniques: Beyond the Basics

NestJS TypeORM Advanced Query Techniques: Beyond the Basics

TypeORM provides powerful query capabilities beyond basic CRUD operations. In NestJS applications, mastering advanced query techniques can significantly improve performance and enable complex data operations. This article explores advanced patterns and techniques.

Query Builder Fundamentals

Basic Query Builder

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

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

  async findActiveUsers() {
    return this.userRepository
      .createQueryBuilder('user')
      .where('user.isActive = :isActive', { isActive: true })
      .getMany();
  }
}

Advanced Select Queries

Selecting Specific Fields

typescript
async findUserSummary() {
  return this.userRepository
    .createQueryBuilder('user')
    .select([
      'user.id',
      'user.firstName',
      'user.lastName',
      'user.email',
    ])
    .getMany();
}

Aggregations

typescript
async getUserStats() {
  return this.userRepository
    .createQueryBuilder('user')
    .select('COUNT(user.id)', 'totalUsers')
    .addSelect('AVG(user.age)', 'averageAge')
    .addSelect('MAX(user.createdAt)', 'latestUser')
    .getRawOne();
}

Group By

typescript
async getUsersByRole() {
  return this.userRepository
    .createQueryBuilder('user')
    .select('user.role', 'role')
    .addSelect('COUNT(user.id)', 'count')
    .groupBy('user.role')
    .getRawMany();
}

Joins and Relations

Inner Join

typescript
async findUsersWithOrders() {
  return this.userRepository
    .createQueryBuilder('user')
    .innerJoin('user.orders', 'order')
    .where('order.status = :status', { status: 'completed' })
    .getMany();
}

Left Join

typescript
async findUsersWithOptionalProfile() {
  return this.userRepository
    .createQueryBuilder('user')
    .leftJoinAndSelect('user.profile', 'profile')
    .getMany();
}

Multiple Joins

typescript
async findUsersWithOrdersAndItems() {
  return this.userRepository
    .createQueryBuilder('user')
    .leftJoinAndSelect('user.orders', 'order')
    .leftJoinAndSelect('order.items', 'item')
    .where('order.createdAt > :date', { date: new Date('2024-01-01') })
    .getMany();
}

Subqueries

Scalar Subqueries

typescript
async findUsersWithOrderCount() {
  return this.userRepository
    .createQueryBuilder('user')
    .addSelect(
      (subQuery) => {
        return subQuery
          .select('COUNT(order.id)', 'count')
          .from('orders', 'order')
          .where('order.userId = user.id');
      },
      'orderCount',
    )
    .getRawMany();
}

EXISTS Subqueries

typescript
async findUsersWithOrders() {
  return this.userRepository
    .createQueryBuilder('user')
    .where((qb) => {
      const subQuery = qb
        .subQuery()
        .select('1')
        .from('orders', 'order')
        .where('order.userId = user.id')
        .getQuery();
      return `EXISTS ${subQuery}`;
    })
    .getMany();
}

IN Subqueries

typescript
async findUsersWithRecentOrders() {
  return this.userRepository
    .createQueryBuilder('user')
    .where((qb) => {
      const subQuery = qb
        .subQuery()
        .select('order.userId')
        .from('orders', 'order')
        .where('order.createdAt > :date', { date: new Date('2024-01-01') })
        .getQuery();
      return `user.id IN ${subQuery}`;
    })
    .getMany();
}

Complex Where Conditions

Dynamic Conditions

typescript
async findUsers(filters: UserFiltersDto) {
  const queryBuilder = this.userRepository.createQueryBuilder('user');

  if (filters.role) {
    queryBuilder.andWhere('user.role = :role', { role: filters.role });
  }

  if (filters.minAge) {
    queryBuilder.andWhere('user.age >= :minAge', { minAge: filters.minAge });
  }

  if (filters.maxAge) {
    queryBuilder.andWhere('user.age <= :maxAge', { maxAge: filters.maxAge });
  }

  if (filters.search) {
    queryBuilder.andWhere(
      '(user.firstName LIKE :search OR user.lastName LIKE :search)',
      { search: `%${filters.search}%` },
    );
  }

  return queryBuilder.getMany();
}

Conditional Logic

typescript
async findUsersAdvanced(filters: UserFiltersDto) {
  const queryBuilder = this.userRepository.createQueryBuilder('user');

  queryBuilder.where('1=1'); // Base condition

  if (filters.role) {
    queryBuilder.andWhere('user.role = :role', { role: filters.role });
  }

  if (filters.isActive !== undefined) {
    queryBuilder.andWhere('user.isActive = :isActive', {
      isActive: filters.isActive,
    });
  }

  // Complex condition
  queryBuilder.andWhere(
    new Brackets((qb) => {
      qb.where('user.age >= :minAge', { minAge: 18 })
        .orWhere('user.role = :adminRole', { adminRole: 'admin' });
    }),
  );

  return queryBuilder.getMany();
}

Pagination

Offset Pagination

typescript
async findUsersPaginated(page: number, limit: number) {
  const skip = (page - 1) * limit;

  const [users, total] = await this.userRepository
    .createQueryBuilder('user')
    .skip(skip)
    .take(limit)
    .orderBy('user.createdAt', 'DESC')
    .getManyAndCount();

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

Cursor Pagination

typescript
async findUsersCursor(cursor?: string, limit: number = 10) {
  const queryBuilder = this.userRepository
    .createQueryBuilder('user')
    .orderBy('user.id', 'ASC')
    .take(limit);

  if (cursor) {
    queryBuilder.where('user.id > :cursor', { cursor });
  }

  const users = await queryBuilder.getMany();

  return {
    data: users,
    nextCursor: users.length === limit ? users[users.length - 1].id : null,
  };
}

Raw SQL Queries

Raw Select

typescript
async findUsersRaw() {
  return this.userRepository.query(`
    SELECT 
      u.id,
      u.first_name,
      u.last_name,
      COUNT(o.id) as order_count
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    GROUP BY u.id, u.first_name, u.last_name
    HAVING COUNT(o.id) > 5
  `);
}

Raw with Parameters

typescript
async findUsersByAgeRange(minAge: number, maxAge: number) {
  return this.userRepository.query(
    `
    SELECT * FROM users 
    WHERE age BETWEEN $1 AND $2
    ORDER BY created_at DESC
    `,
    [minAge, maxAge],
  );
}

Database Views

Creating Views

typescript
async createUserStatsView() {
  await this.userRepository.query(`
    CREATE OR REPLACE VIEW user_stats AS
    SELECT 
      u.id,
      u.first_name,
      u.last_name,
      COUNT(o.id) as total_orders,
      SUM(o.amount) as total_spent
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    GROUP BY u.id, u.first_name, u.last_name
  `);
}

Querying Views

typescript
async getUserStats() {
  return this.userRepository.query('SELECT * FROM user_stats');
}

Transactions with Query Builder

typescript
async transferOrder(userId: string, orderId: string) {
  return this.dataSource.transaction(async (manager) => {
    // Use query builder in transaction
    await manager
      .createQueryBuilder()
      .update(Order)
      .set({ userId })
      .where('id = :id', { id: orderId })
      .execute();

    // Update user stats
    await manager
      .createQueryBuilder()
      .update(User)
      .set({ orderCount: () => 'order_count + 1' })
      .where('id = :id', { id: userId })
      .execute();
  });
}

Performance Optimization

Index Hints

typescript
async findUsersOptimized() {
  return this.userRepository
    .createQueryBuilder('user')
    .useIndex('idx_user_email') // MySQL
    .where('user.email = :email', { email: 'test@example.com' })
    .getOne();
}

Query Caching

typescript
async findUsersCached() {
  return this.userRepository
    .createQueryBuilder('user')
    .cache(true)
    .cache(60000) // Cache for 60 seconds
    .getMany();
}

Eager Loading Optimization

typescript
async findUsersWithRelations() {
  return this.userRepository
    .createQueryBuilder('user')
    .leftJoinAndSelect('user.orders', 'order')
    .leftJoinAndSelect('order.items', 'item')
    .where('user.isActive = :isActive', { isActive: true })
    .getMany();
}

Custom Repository Methods

typescript
// users.repository.ts
import { Repository } from 'typeorm';
import { User } from './entities/user.entity';

export class UsersRepository extends Repository<User> {
  async findActiveUsersWithOrders() {
    return this.createQueryBuilder('user')
      .leftJoinAndSelect('user.orders', 'order')
      .where('user.isActive = :isActive', { isActive: true })
      .andWhere('order.status = :status', { status: 'completed' })
      .getMany();
  }

  async getUserStats(userId: string) {
    return this.createQueryBuilder('user')
      .select('user.id', 'id')
      .addSelect('COUNT(order.id)', 'orderCount')
      .addSelect('SUM(order.amount)', 'totalSpent')
      .leftJoin('user.orders', 'order')
      .where('user.id = :userId', { userId })
      .groupBy('user.id')
      .getRawOne();
  }
}

Best Practices

  1. Use Query Builder: For complex queries
  2. Avoid N+1: Use joins and preloading
  3. Index Properly: Add indexes for frequently queried fields
  4. Limit Results: Always use pagination for large datasets
  5. Use Transactions: For related operations
  6. Cache Strategically: Cache expensive queries
  7. Monitor Performance: Use query logging in development

Conclusion

Advanced TypeORM query techniques enable complex data operations and performance optimization in NestJS applications. By mastering query builders, subqueries, joins, and optimization strategies, you can build efficient, scalable database layers. Remember to use these techniques judiciously and always monitor query performance.

References

Want more insights?

Subscribe to our newsletter or follow us for more updates on software development and team scaling.

Contact Us