How to use TypeORM's QueryBuilder? Including complex queries, relationship queries, pagination, sorting and other advanced features

QueryBuilder is the most powerful and flexible query tool in TypeORM, allowing developers to build complex SQL queries while maintaining type safety and readability.

QueryBuilder Basic Usage

Creating QueryBuilder

typescript
import { DataSource } from 'typeorm';

const dataSource = new DataSource(/* configuration */);

// Method 1: Create via Repository
const userRepository = dataSource.getRepository(User);
const queryBuilder = userRepository.createQueryBuilder('user');

// Method 2: Create via DataSource
const queryBuilder = dataSource.createQueryBuilder(User, 'user');

Basic Query Operations

typescript
// Query all users
const users = await dataSource
  .createQueryBuilder('user')
  .getMany();

// Query single user
const user = await dataSource
  .createQueryBuilder('user')
  .where('user.id = :id', { id: 1 })
  .getOne();

// Query and count
const count = await dataSource
  .createQueryBuilder('user')
  .getCount();

Conditional Queries

Where Clause

typescript
// Simple condition
const users = await dataSource
  .createQueryBuilder('user')
  .where('user.age > :age', { age: 18 })
  .getMany();

// Multiple conditions (AND)
const users = await dataSource
  .createQueryBuilder('user')
  .where('user.age > :age', { age: 18 })
  .andWhere('user.isActive = :isActive', { isActive: true })
  .getMany();

// OR condition
const users = await dataSource
  .createQueryBuilder('user')
  .where('user.age > :age', { age: 18 })
  .orWhere('user.role = :role', { role: 'admin' })
  .getMany();

// Complex condition combination
const users = await dataSource
  .createQueryBuilder('user')
  .where(
    new Brackets(qb => {
      qb.where('user.age > :age', { age: 18 })
        .orWhere('user.role = :role', { role: 'admin' });
    })
  )
  .andWhere('user.isActive = :isActive', { isActive: true })
  .getMany();

Operators

typescript
import { Like, Between, In, MoreThan, LessThan } from 'typeorm';

// LIKE query
const users = await dataSource
  .createQueryBuilder('user')
  .where('user.name LIKE :name', { name: '%John%' })
  .getMany();

// Or use Like operator
const users = await dataSource
  .createQueryBuilder('user')
  .where('user.name = :name', { name: Like('%John%') })
  .getMany();

// BETWEEN query
const users = await dataSource
  .createQueryBuilder('user')
  .where('user.age = :age', { age: Between(18, 30) })
  .getMany();

// IN query
const users = await dataSource
  .createQueryBuilder('user')
  .where('user.id IN :ids', { ids: [1, 2, 3] })
  .getMany();

// Or use In operator
const users = await dataSource
  .createQueryBuilder('user')
  .where('user.id = :ids', { ids: In([1, 2, 3]) })
  .getMany();

// Comparison operators
const users = await dataSource
  .createQueryBuilder('user')
  .where('user.age = :age', { age: MoreThan(18) })
  .andWhere('user.score = :score', { score: LessThan(100) })
  .getMany();

Relationship Queries

Left Join and Inner Join

typescript
// Left Join (includes records without related data)
const users = await dataSource
  .createQueryBuilder('user')
  .leftJoinAndSelect('user.posts', 'post')
  .where('user.id = :id', { id: 1 })
  .getMany();

// Inner Join (only includes records with related data)
const users = await dataSource
  .createQueryBuilder('user')
  .innerJoinAndSelect('user.posts', 'post')
  .where('user.id = :id', { id: 1 })
  .getMany();

// Multi-level relationships
const users = await dataSource
  .createQueryBuilder('user')
  .leftJoinAndSelect('user.posts', 'post')
  .leftJoinAndSelect('post.comments', 'comment')
  .leftJoinAndSelect('comment.author', 'commentAuthor')
  .getMany();

Join Conditions

typescript
const users = await dataSource
  .createQueryBuilder('user')
  .leftJoin('user.posts', 'post', 'post.status = :status', { status: 'published' })
  .addSelect(['post.title', 'post.createdAt'])
  .getMany();

Sorting and Pagination

Sorting

typescript
// Single field sorting
const users = await dataSource
  .createQueryBuilder('user')
  .orderBy('user.createdAt', 'DESC')
  .getMany();

// Multi-field sorting
const users = await dataSource
  .createQueryBuilder('user')
  .orderBy('user.createdAt', 'DESC')
  .addOrderBy('user.name', 'ASC')
  .getMany();

// Random sorting (MySQL)
const users = await dataSource
  .createQueryBuilder('user')
  .orderBy('RAND()')
  .getMany();

Pagination

typescript
// Basic pagination
const page = 1;
const pageSize = 10;
const users = await dataSource
  .createQueryBuilder('user')
  .skip((page - 1) * pageSize)
  .take(pageSize)
  .getMany();

// Get total count and paginated data
const [users, total] = await dataSource
  .createQueryBuilder('user')
  .skip((page - 1) * pageSize)
  .take(pageSize)
  .getManyAndCount();

console.log(`Total: ${total}, Page: ${page}, PageSize: ${pageSize}`);

Aggregation Queries

Group By and Having

typescript
// Count users by role
const result = await dataSource
  .createQueryBuilder('user')
  .select('user.role', 'role')
  .addSelect('COUNT(*)', 'count')
  .groupBy('user.role')
  .getRawMany();

// Filter groups using Having
const result = await dataSource
  .createQueryBuilder('user')
  .select('user.role', 'role')
  .addSelect('COUNT(*)', 'count')
  .groupBy('user.role')
  .having('COUNT(*) > :minCount', { minCount: 5 })
  .getRawMany();

Aggregation Functions

typescript
// Count total
const count = await dataSource
  .createQueryBuilder('user')
  .select('COUNT(*)', 'count')
  .getRawOne();

// Calculate average
const avgAge = await dataSource
  .createQueryBuilder('user')
  .select('AVG(user.age)', 'avgAge')
  .getRawOne();

// Sum
const totalScore = await dataSource
  .createQueryBuilder('user')
  .select('SUM(user.score)', 'totalScore')
  .getRawOne();

// Max and Min
const result = await dataSource
  .createQueryBuilder('user')
  .select('MAX(user.age)', 'maxAge')
  .addSelect('MIN(user.age)', 'minAge')
  .getRawOne();

Subqueries

Using SubQueryFactory

typescript
import { SubQueryFactory } from 'typeorm';

const users = await dataSource
  .createQueryBuilder('user')
  .where((qb: SelectQueryBuilder<User>) => {
    const subQuery = qb
      .subQuery()
      .select('post.userId')
      .from(Post, 'post')
      .where('post.title LIKE :title', { title: '%TypeORM%' })
      .getQuery();
    return 'user.id IN ' + subQuery;
  })
  .setParameter('title', '%TypeORM%')
  .getMany();

Using EXISTS

typescript
const users = await dataSource
  .createQueryBuilder('user')
  .where((qb: SelectQueryBuilder<User>) => {
    const subQuery = qb
      .subQuery()
      .select('1')
      .from(Post, 'post')
      .where('post.userId = user.id')
      .getQuery();
    return 'EXISTS ' + subQuery;
  })
  .getMany();

Update and Delete

Update Operations

typescript
// Simple update
await dataSource
  .createQueryBuilder(User, 'user')
  .update()
  .set({ name: 'Updated Name' })
  .where('id = :id', { id: 1 })
  .execute();

// Conditional update
await dataSource
  .createQueryBuilder(User, 'user')
  .update()
  .set({ isActive: false })
  .where('user.lastLoginAt < :date', { date: new Date('2024-01-01') })
  .execute();

// Update based on subquery
await dataSource
  .createQueryBuilder(User, 'user')
  .update()
  .set({ score: () => 'score + 10' })
  .where('user.id IN :ids', { ids: [1, 2, 3] })
  .execute();

Delete Operations

typescript
// Simple delete
await dataSource
  .createQueryBuilder(User, 'user')
  .delete()
  .where('id = :id', { id: 1 })
  .execute();

// Conditional delete
await dataSource
  .createQueryBuilder(User, 'user')
  .delete()
  .where('user.createdAt < :date', { date: new Date('2023-01-01') })
  .andWhere('user.isActive = :isActive', { isActive: false })
  .execute();

Advanced Features

Native SQL

typescript
const users = await dataSource
  .createQueryBuilder(User, 'user')
  .where('user.id = :id', { id: 1 })
  .andWhere('JSON_CONTAINS(user.preferences, :preferences)', { 
    preferences: JSON.stringify({ theme: 'dark' }) 
  })
  .getMany();

Caching

typescript
const users = await dataSource
  .createQueryBuilder('user')
  .where('user.isActive = :isActive', { isActive: true })
  .cache(60000) // Cache for 60 seconds
  .getMany();

Transactions

typescript
await dataSource.transaction(async transactionalEntityManager => {
  const queryRunner = transactionalEntityManager.queryRunner;
  
  await queryRunner.manager
    .createQueryBuilder(User, 'user')
    .insert()
    .values({ name: 'John', email: 'john@example.com' })
    .execute();
    
  await queryRunner.manager
    .createQueryBuilder(Post, 'post')
    .insert()
    .values({ title: 'New Post', authorId: 1 })
    .execute();
});

Performance Optimization Tips

1. Avoid N+1 queries: Use leftJoinAndSelect to load related data in one query
2. Select only needed fields: Use select() to explicitly specify needed columns
3. Use indexes wisely: Add database indexes for frequently queried conditions
4. Use caching: Enable query caching for data that doesn't change often
5. Limit results: Use take() and skip() for pagination
6. Monitor query performance: Use getQuery() and getSql() to view generated SQL

QueryBuilder is the most powerful query tool in TypeORM. Mastering its use allows you to build efficient and flexible database queries.