SQL 方案：QueryBuilder

用于构建复杂的 SQL 查询 / 操作，相比手写原生 SQL 更安全（防 SQL 注入）、更易维护，相比 TypeORM Repository 基础方法更灵活，能满足多条件、联表、聚合等复杂业务场景。

创建 QueryBuilder

两种方式：Repository 单实体、dataSource 跨实体

// src/user/user.service.ts
import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository, DataSource } from 'typeorm';
import { User } from './entities/user.entity';

@Injectable()
export class UserService {
  constructor(
    @InjectRepository(User)
    private readonly userRepository: Repository<User>, // 注入 User 仓库
    private readonly dataSource: DataSource // 注入数据源
  ) {}

  // 方式一：从 Repository 创建，操作单个实体的查询 / 操作
  async 方式一() {
    return this.userRepository.createQueryBuilder('user');
  }

  // 方式二：从 DataSource 创建，跨实体、批量插入 / 更新 / 删除
  async 方式二() {
    return this.dataSource.createQueryBuilder();
  }
}

// 注意：createQueryBuilder('user') 中的 user 是实体的别名，后续所有操作（select/where/join）都基于该别名，需保证唯一（联表时避免冲突）。

QueryBuilder 常用 API

参考：https://typeorm.nodejs.cn/select-query-builder#what-is-querybuilder

createQueryBuilder 参数

createQueryBuilder(User); // 参数1 实体作为参数
createQueryBuilder('user'); // 参数1 实体名称字符串
createQueryBuilder(User, 'u'); // 参数1 实体作为参数，参数2 别名
createQueryBuilder('user', 'u', secondaryConnection); // 参数1 实体作为参数，参数2 别名，参数3 指定使用的数据库连接

// 等价于
createQueryBuilder().from('user', 'u');

查询相关 select() addSelect() from distinct()

createQueryBuilder().select(['user.id', 'user.name']); // 指定查询的列，参数1 字符串或者字符串数组
createQueryBuilder().select('user'); // 返回整个实体

createQueryBuilder().addSelect('user.email'); // 添加额外的选择列，参数1 字符串或者字符串数组

createQueryBuilder().from(User, 'user'); // 设置主查询表，参数1 实体类或表名，参数2 表别名

createQueryBuilder().distinct(true); // 添加 DISTINCT 关键字，去重的作用

createQueryBuilder().setParameter('age', 18); // 设置主查询参数，参数1 key，参数2 value
createQueryBuilder().setParameters({ age: 18, gender: 'male' }); // 批量设置主查询参数，参数对象

条件查询 where() andWhere() orWhere() having()

createQueryBuilder().where('user.age > :age', { age: 18 }); // where 条件，参数1 条件字符串或函数或 Brackets 对象，参数2 参数对象
createQueryBuilder().andWhere('user.status = :status', { status: 'active' }); // where and，参数同 where
createQueryBuilder().orWhere('user.isAdmin = :isAdmin', { isAdmin: true }); // where or，参数同 where

createQueryBuilder().having('COUNT(post.id) > :minPosts', { minPosts: 5 }); // HAVING 条件，参数1 条件字符串，参数2 参数对象
createQueryBuilder().andHaving('COUNT(post.id) > :minPosts', { minPosts: 5 }); // HAVING and，参数同having
createQueryBuilder().orHaving('COUNT(post.id) > :minPosts', { minPosts: 5 }); // HAVING or，参数同having

关联查询 innerJoin() leftJoin() rightJoin()，innerJoinAndSelect() leftJoinAndSelect()

createQueryBuilder().innerJoin('user.posts', 'post'); // 内接其他表，参数1 实体类或表名，参数2 别名，参数3 连接条件，参数4 参数对象
createQueryBuilder().leftJoin('user.posts', 'post'); // 左接其他表，参数1 实体类或表名，参数2 别名，参数3 连接条件，参数4 参数对象
createQueryBuilder().rightJoin('user.posts', 'post'); // 右接其他表，参数1 实体类或表名，参数2 别名，参数3 连接条件，参数4 参数对象
createQueryBuilder().innerJoinAndSelect('user.posts', 'post'); // 内接其他表，参数1 实体类或表名，参数2 别名，参数3 连接条件，参数4 参数对象
createQueryBuilder().leftJoinAndSelect('user.posts', 'post'); // 左接其他表，参数1 实体类或表名，参数2 别名，参数3 连接条件，参数4 参数对象

排序分组分页

createQueryBuilder().orderBy('user.name', 'DESC'); // 排序，参数1 排序字段，参数2 升序ASC（默认）& 降序DESC，参数3 null 值的处理方式
createQueryBuilder().addOrderBy('user.name', 'DESC'); // 排序，参数1 排序字段，参数2 升序ASC（默认）& 降序DESC，参数3 null 值的处理方式

createQueryBuilder().groupBy('user.country'); // 添加分组，参数1 分组字段
createQueryBuilder().addGroupBy('user.country'); // 添加额外的分组，参数1 分组字段

// skip与limit作用相同，take与 offset 作用相同
createQueryBuilder().skip(10); // 跳过的记录数，参数1 记录数，示例：.skip((currentPage - 1) * pages)，模仿第几页
createQueryBuilder().limit(10); // 跳过的记录数，参数1 记录数，示例：.skip((currentPage - 1) * pages)，模仿第几页
createQueryBuilder().take(5); // 返回的记录数，参数1 记录数，
createQueryBuilder().offset(5); // 返回的记录数，参数1 记录数，

缓存事务

createQueryBuilder().cache(true, 60000); // 启用/禁用查询缓存，参数1 是否启用，参数2 缓存时间（毫秒）
createQueryBuilder().useTransaction(true); // 是否使用事务，参数1 是否使用

最终方法（无参）

createQueryBuilder().getQuery(); // 获取生成的 SQL

createQueryBuilder().getOne(); // 查询返回单个实体
createQueryBuilder().getOneOrFail(); // 查询返回单个实体，如果没有匹配则抛出错误

createQueryBuilder().getMany(); // 查询返回实体列表
createQueryBuilder().getManyAndCount(); // 查询返回实体列表和统计数

createQueryBuilder().getRawOne(); // 查询返回单个实体（原始值）

createQueryBuilder().getRawMany(); // 查询返回实体列表（原始值）

createQueryBuilder().getCount(); // 查询返回计数

createQueryBuilder().execute(); // 执行原始查询（用于 INSERT/UPDATE/DELETE）

插入（Insert）

// 插入&批量插入
await this.userRepository
  .createQueryBuilder()
  .insert()
  .into(User)
  .values([
    { firstName: 'Timber', lastName: 'Saw' },
    { firstName: 'Phantom', lastName: () => "CONCAT('S', 'A', 'W')" }, // 支持原始 sql
  ])
  .execute();

// 出现不可重复冲突时，不执行插入操作，也不执行修改操作
await this.userRepository
  .createQueryBuilder()
  .insert()
  .into(User)
  .values({
    firstName: 'Timber',
    lastName: 'Saw',
    externalId: 'abc123',
  })
  .orIgnore()
  .execute();

// 存在即更新，不存在则插入
await this.userRepository
  .createQueryBuilder()
  .insert()
  .into(User)
  .values({
    firstName: 'Timber',
    lastName: 'Saw',
    externalId: 'abc123',
  })
  .orUpdate(['firstName', 'lastName'], ['externalId'], {
    skipUpdateIfNoValuesChanged: true, // 如果值未更改则跳过数据更新，节省性能
  })
  .execute();

删除（Delete）

物理删除

async deleteUser(id: number) {
  const result = await this.dataSource
    .createQueryBuilder()
    .delete()
    .from(User)
    .where('id = :id', { id })
    .execute();

  return result.affected;
}

软删除（配合 @DeleteDateColumn）

async softDeleteUser(id: number) {
  return this.userRepository
    .createQueryBuilder('user')
    .softDelete() // 软删除（自动填充 deleteTime 字段）
    .where('id = :id', { id })
    .execute();
}

// 查询包含软删除的用户
async findDeletedUser(id: number) {
  return this.userRepository
    .createQueryBuilder('user')
    .withDeleted() // 包含软删除记录
    .where('id = :id', { id })
    .getOne();
}

更新（Update）

async updateUser(id: number, user: Partial<User>) {
  const result = await this.dataSource
    .createQueryBuilder()
    .update(User)
    .set(user) // 要更新的字段
    .where('user.id = :id', { id }) // 更新条件
    // .andWhere('user.status = :status', { status: 1 }) // 多条件
    .execute();

  return result.affected; // 返回受影响的行数
}

查询（Select）

查询结果

// 查询全部字段
this.userRepository.createQueryBuilder('user').getMany(); // 返回数组

// 查询指定字段
return this.userRepository
  .createQueryBuilder('user')
  .select(['user.id', 'user.name', 'user.age']) // 指定字段
  .getMany();

// 字段别名
async findUserWithAlias() {
  return this.userRepository
    .createQueryBuilder('user')
    .select('user.name as userName') // 字段别名
    .addSelect('user.age as userAge')
    .getRawMany(); // getRawMany() 用于获取别名字段（返回原始对象）
}

条件查询（Where）

基础查询

async findUserByCondition() {
  return this.userRepository
    .createQueryBuilder('user')
    .where('user.age > :age AND user.status = :status', { age: 18, status: 1 }) // 命名参数（推荐）
    .getMany();
}

动态条件（andWhere/orWhere）

async findUsers(query: { name?: string; age?: number; status?: number }) {
  const { name, age, status } = query;
  const queryBuilder = this.userRepository.createQueryBuilder('user');

  // 动态拼接条件（避免空条件导致 SQL 错误）
  if (name) {
    queryBuilder.andWhere('user.name LIKE :name', { name: `%${name}%` }); // 模糊查询
  }
  if (age) {
    queryBuilder.andWhere('user.age = :age', { age });
  }
  if (status !== undefined) {
    queryBuilder.andWhere('user.status = :status', { status });
  }

  return queryBuilder.getMany();
}

条件分组（括号优先级）

async findUserByGroupCondition() {
  return this.userRepository
    .createQueryBuilder('user')
    .where('user.id = :id', { id: 1 })
    // 分组条件：(name = 张三 OR age = 20)
    .orWhere('(user.name = :name AND user.age = :age)', { name: '张三', age: 20 })
    .getMany();
}

排序（OrderBy）

async findUsersWithSort() {
  return this.userRepository
    .createQueryBuilder('user')
    .orderBy('user.createTime', 'DESC') // 按创建时间降序
    .addOrderBy('user.age', 'ASC') // 多字段排序：再按年龄升序
    // 也可传入对象：.orderBy({ 'user.createTime': 'DESC', 'user.age': 'ASC' })
    .getMany();
}

分页（Skip/Take）

async findUsersWithPagination(page: number = 1, pageSize: number = 10) {
  const queryBuilder = this.userRepository.createQueryBuilder('user');

  // 分页计算：skip(偏移量) = (页码-1)*页大小，take(页大小)
  queryBuilder.skip((page - 1) * pageSize).take(pageSize);

  // 获取列表 + 总数（常用分页返回格式）
  const [list, total] = await queryBuilder.getManyAndCount();

  return { list, total, page, pageSize };
}

联表查询

// 语法
leftJoin('user.profile', 'profile') // 左联表（不查询关联字段）
leftJoinAndSelect('user.profile', 'profile') // 左联表 + 查询关联字段（常用）
innerJoin('user.profile', 'profile') // 内联表（仅返回关联数据存在的记录）

async findUserWithProfile(id: number) {
  return this.userRepository
    .createQueryBuilder('user')
    .leftJoinAndSelect('user.profile', 'profile') // 左联 Profile 并查询字段
    .where('user.id = :id', { id })
    .getOne();
}

聚合查询

// 统计用户总数
async countUsers() {
  return this.userRepository.createQueryBuilder('user').getCount();
}

// 统计总年龄、平均年龄
async getUserAgeStats() {
  const result = await this.userRepository
    .createQueryBuilder('user')
    .select('SUM(user.age)', 'totalAge') // 求和
    .addSelect('AVG(user.age)', 'avgAge') // 平均值
    .getRawOne(); // getRawOne() 获取单行聚合结果

  return {
    totalAge: Number(result.totalAge),
    avgAge: Number(result.avgAge).toFixed(2), // 保留两位小数
  };
}

子查询

async findUserWithSubQuery() {
  // 1. 定义子查询：查询年龄 > 20 的用户 ID
  const subQuery = this.userRepository
    .createQueryBuilder('u')
    .select('u.id')
    .where('u.age > :age', { age: 20 });

  // 2. 主查询：IN 子查询结果
  return this.userRepository
    .createQueryBuilder('user')
    .where('user.id IN (' + subQuery.getQuery() + ')')
    .setParameters(subQuery.getParameters()) // 继承子查询参数
    .getMany();
}

原生 SQL 片段（Raw）

复杂场景可嵌入原生 SQL（需谨慎，避免注入）

async findUserWithRawSql() {
  return this.userRepository
    .createQueryBuilder('user')
    .select([
      'user.id',
      'user.name',
      // 原生 SQL：格式化时间
      this.dataSource.createQueryBuilder().raw('DATE_FORMAT(user.create_time, "%Y-%m-%d") as createDate'),
    ])
    .where(this.dataSource.createQueryBuilder().raw('user.age > 18')) // 原生条件
    .getRawMany();
}

事务

复杂业务需保证原子性时，结合 QueryRunner 实现事务

async transactionDemo() {
  // 1. 创建 QueryRunner
  const queryRunner = this.dataSource.createQueryRunner();
  await queryRunner.connect();
  await queryRunner.startTransaction(); // 开启事务

  try {
    // 2. 事务内执行操作（需绑定 queryRunner）
    await queryRunner.manager
      .createQueryBuilder()
      .insert()
      .into(User)
      .values({ name: '王五', age: 25 })
      .execute();

    await queryRunner.manager
      .createQueryBuilder()
      .update(User)
      .set({ status: 0 })
      .where('id = :id', { id: 1 })
      .execute();

    // 3. 提交事务
    await queryRunner.commitTransaction();
  } catch (error) {
    // 4. 回滚事务
    await queryRunner.rollbackTransaction();
    throw error;
  } finally {
    // 5. 释放连接
    await queryRunner.release();
  }
}

中间件

守卫

异常过滤器

拦截器

管道

SQL 方案：QueryBuilder

创建 QueryBuilder

QueryBuilder 常用 API

插入（Insert）

删除（Delete）

更新（Update）

查询（Select）

查询结果

条件查询（Where）

排序（OrderBy）

分页（Skip/Take）

联表查询

聚合查询

子查询

原生 SQL 片段（Raw）

事务

SQL 方案：QueryBuilder ​

创建 QueryBuilder ​

QueryBuilder 常用 API ​

插入（Insert） ​

删除（Delete） ​

更新（Update） ​

查询（Select） ​

查询结果 ​

条件查询（Where） ​

排序（OrderBy） ​

分页（Skip/Take） ​

联表查询 ​

聚合查询 ​

子查询 ​

原生 SQL 片段（Raw） ​

事务 ​

SQL 方案：QueryBuilder

创建 QueryBuilder

QueryBuilder 常用 API

插入（Insert）

删除（Delete）

更新（Update）

查询（Select）

查询结果

条件查询（Where）

排序（OrderBy）

分页（Skip/Take）

联表查询

聚合查询

子查询

原生 SQL 片段（Raw）

事务