typeorm +QueryBuilder 实战

知了清语
1,839 阅读8分钟

在实际开发过程中，会遇到各种各样的语法问题，我个人偏向于 从 场景 + 语法 实现，让你更快的上手typeorm中的 QueryBuilder

内容会持续更新.... 如果你有遇到什么问题，可以持续在下方留言

包版本说明

截止2023-05-23 包都是最新版本

node v18.15.0 (14+ 应该都没问题)
"mysql2": "^3.3.1"
"typeorm": "^0.3.16"
"express": "^4.18.2"

mysql 数据库版本：8.0.32

官网创建 Query Builder 的方式有5种， 选择自己合适的就行； 我个人偏向于使用 基于 repository 的 创建query builder

实战案例

以下都是js版本的实现，TypeScript版本的差不多

表达 is not null

场景： 我想查询一个表里面，某个字段不为null的数据

SELECT * FROM repository as r where r.service_name IS NOT NULL

使用 typeorm 实现

/entity/repository.js 实体

import { EntitySchema } from 'typeorm'

export default new EntitySchema({
  name: 'Repository',
  columns: {
    id: {
      primary: true,
      type: 'int',
      generated: true,
      comment: '仓库id'
    },

    name: {
      type: 'varchar',
      length: 100
    },
    description: {
      type: 'varchar',
      length: 255,
      comment: '项目描述',
      // 可为空
      nullable: true
    },
    web_url: {
      type: 'varchar',
      comment: 'web地址',
      nullable: true,
      // 唯一
      unique: true
    },
    ssh_url_to_repo: {
      type: 'varchar',
      comment: 'SSH地址',
      nullable: true,
      // 唯一
      unique: true
    },

    readme_url: {
      type: 'varchar',
      comment: 'README路径',
      nullable: true
    },
    last_activity_at: {
      type: 'datetime',
      comment: '最后更新时间'
    },
    created_at: {
      type: 'datetime',
      comment: '创建时间'
    },
    default_branch: {
      type: 'varchar',
      length: 50,
      comment: '默认分支',
      // 新仓库可能是没有分支的
      nullable: true
    },

    group_name: {
      type: 'varchar',
      comment: '项目组名称',
      nullable: true
    },
    group_web_url: {
      type: 'varchar',
      comment: '项目组地址',
      nullable: true
    },
    group_id: {
      type: 'int',
      comment: '项目组id',
      nullable: true
    },
    service_name: {
      type: 'varchar',
      nullable: true,
      comment: 'jenkins服务名',
      length: 60
    }
  }
})

service.js

import Repository from '../entity/repository.js'
import { Not, IsNull } from 'typeorm'

const repositoryEntity = dataSource.getRepository(Repository)

const fn = async() => {
    await repositoryEntity
    .createQueryBuilder('repo')
    .select([
      'repo.id',
      'repo.ssh_url_to_repo',
      'repo.name',
      'repo.service_name',
      'repo.description'
    ])
    .where({
      service_name: Not(IsNull())
    })
    .getMany()
}

fn()

注意： 这里使用了 select 一定要使用上面的别名 repo, 否则 getMany() 查询到的数据是 空数组

以下为错误写法

await repositoryEntity
    .createQueryBuilder('repo')
    .select([
        'repo.id',
        'repo.ssh_url_to_repo',
        'repo.name',
        'repo.service_name',
        'repo.description'
    ])
    // 想表达 is not null 场景，这里的语法不能这么写，会报语法异常
    // Cannot convert a Symbol value to a string
    .where('repo.service_name = :service_name', {
        service_name: Not(IsNull())
    })
    .getMany()

上述不能通过 参数来转义数据 的方式来实现 where 条件查询， 按理说，应该可以的， 应该是 typeorm 没有实现， 毕竟使用参数转义，是 typeorm 推荐的写法，防止sql 注入

表达 is null

场景： 查询表 （repository）里面， 分类（repository_category） 字段的值为空的数据

sql 写法

select * from repository r WHERE r.repository_category IS NULL

js代码

const findUniAppRepoList = await repositoryEntity.createQueryBuilder('repo')
    .select(['repo.id'])
    .where({
      repository_category: IsNull()
    })
    .getMany()

image.png

IN , Between, Like

场景： 前端传来了一个分类，数组类型， 需要查询 查询分类在某个范围的数据

image.png

sql写法

select * from repository where repository_category IN (1,2)

js代码

import { Like, Between, In } from 'typeorm'
const repositoryEntity = dataSource.getRepository(Repository)

export const getProjects = async (req, res) => {
 let { currentPage, pageSize, endUpdateTime, startUpdateTime, ...rest } = req.body
 
    currentPage = currentPage || 1
    pageSize = pageSize || 20

    let query = {}

    if (rest.id) {
      query.id = rest.id
    }
    
    // 使用模糊匹配
    if (rest.name) {
      query.name = Like(`%${rest.name}%`)
    }

    if (rest.description) {
      query.description = Like(`%${rest.description}%`)
    }
    
    
    if (startUpdateTime && endUpdateTime) {
      query['last_activity_at'] = Between(startUpdateTime, endUpdateTime)
    }
    
    // in的场景
    if (rest.repository_category && rest.repository_category.length) {
      query.repository_category = In(rest.repository_category)
    }

    const result = await repositoryEntity.findAndCount({
      skip: (currentPage - 1) * pageSize,
      take: pageSize,
      where: query,
      order: {
        last_activity_at: 'DESC'
      }
    })

    const list = result[0] || []

    res.json({
      code: 1000,
      data: {
        currentPage: currentPage,
        pageSize: pageSize,
        list,
        total: result[1]
      },
      message: '响应成功'
    })
})

上述代码已经实现了，基础的 分页 + 多条件组合查询

image.png

In createQueryBuilder方式调用 案例

2023-11-1 记录

入参 rest.repository_category = [1,2]

使用 In函数包装后

In(rest.repository_category)

// 打印的结果如下

FindOperator {
  '@instanceof': Symbol(FindOperator),
  _type: 'in',
  _value: [ 1, 2 ],
  _useParameter: true,
  _multipleParameters: true,
  _getSql: undefined,
  _objectLiteralParameters: undefined
}

上面使用 In操作符是没有问题的， 如果我们使用 createQueryBuilder的方式使用 In， 出现报错，请排查语句是否存在语法问题

Cannot convert a Symbol value to a string

 at RegExp.exec (<anonymous>)
    at escapeString (D:\owner\node-base-example\jwt\node_modules\.pnpm\sqlstring@2.3.3\node_modules\sqlstring\lib\SqlString.js:201:39)
    at Object.escape (D:\owner\node-base-example\jwt\node_modules\.pnpm\sqlstring@2.3.3\node_modules\sqlstring\lib\SqlString.js:56:21)
    at Object.objectToValues (D:\owner\node-base-example\jwt\node_modules\.pnpm\sqlstring@2.3.3\node_modules\sqlstring\lib\SqlString.js:180:89)
    at Object.escape (D:\owner\node-base-example\jwt\node_modules\.pnpm\sqlstring@2.3.3\node_modules\sqlstring\lib\SqlString.js:54:26)
    at Object.format (D:\owner\node-base-example\jwt\node_modules\.pnpm\sqlstring@2.3.3\node_modules\sqlstring\lib\SqlString.js:100:19)
    at PoolConnection.format (D:\owner\node-base-example\jwt\node_modules\.pnpm\mysql2@3.6.2\node_modules\mysql2\lib\connection.js:525:22)
    at PoolConnection.query (D:\owner\node-base-example\jwt\node_modules\.pnpm\mysql2@3.6.2\node_modules\mysql2\lib\connection.js:570:25)
    at D:\owner\node-base-example\jwt\node_modules\.pnpm\typeorm@0.3.17_mysql2@3.6.2\node_modules\typeorm\driver\mysql\MysqlQueryRunner.js:148:36
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)

image.png

let updateIds = [6]

// 查询出用户信息
let userList = await userRepository.createQueryBuilder('user')
    .where('user.userId In(:...list)', {
      list: updateIds
    })
    .getMany()

list字段是占位符

或者直接写 sql

let updateIds = [6]

let userList = await userRepository.createQueryBuilder('user')
    .where(`user.userId In(${updateIds})`)
    .getMany()

读取到的数据

[
  {
    userId: 6,   
    name: '张三',
    password: '$2b$10$jG1mAa273qEHCAEafvXMteH5x7MSV8YKFEFUa1WgArV2eRex0SlYK',
    phone: '',
    status: 0,
    nickname: null,
    email: null,
    avatar: null,
    createTime: 2023-08-29T01:47:38.000Z,
    updateTime: 2023-08-29T01:47:38.000Z
  }
]

错误用法1

let userList = await userRepository.createQueryBuilder('user')
    .where(`user.userId = :userId`, {
      userId: In(updateIds)
    })
    .getMany()

这种写法其实是等于的意思， 我们知道 In是表示一个范围， :userId 这样写就不对， 这种写法就会出现上面的报错信息

错误用法2

let userList = await userRepository.createQueryBuilder('user')
    .where(`user.userId = :userId`, {
      userId: updateIds
    })
    .getMany()

这种其实是拿 updateIds 作为一个字符串 使用，其实这个类型是 数组类型，也不对

Like

当我们的入参中存在特殊字符，发现上面的方式并不行，执行查询语句会报错

2023-10-26 记录

例如：

image.png

parentIds: '[0],[11]'
parentIds: '[0],[11],[13]'

... 等等

我希望查询出，以xxx开头的数据

let item.parentIds = '[0],[11]'

let children = await menuRepository
    .createQueryBuilder('menu')
    .select(['menu.menuId', 'menu.parentIds', 'menu.isDelete', 'menu.menuName'])
    .where('menu.parentIds = :parentIds', {
       parentIds: Like(`${item.parentIds},%`)
    })
    .getMany()

执行会报错

\node_modules\.pnpm\sqlstring@2.3.3\node_modules\sqlstring\lib\SqlString.js:201
  while ((match = CHARS_GLOBAL_REGEXP.exec(val))) {
                                      ^

TypeError: Cannot convert a Symbol value to a string

应该是字符串被 typeorm转义了，导致存在问题

改为如下方式，使用原始 sql 可行

  let children = await menuRepository
    .createQueryBuilder('menu')
    .select(['menu.menuId', 'menu.parentIds', 'menu.isDelete', 'menu.menuName'])
    .where(`menu.parentIds Like('${item.parentIds},%')`)
    .getMany()

image.png

排序 order by

场景： 现在有一个用户表，表里面有一些用户的状态（state）是 启用(active)， 已停用(blocked)， 我想根据 用户状态做排序

sql

select * from user order by state, id ASC

js代码

import User from '../entity/user.js'
const userRepositoryEntity = dataSource.getRepository(User)

export const getUsers = async (req, res) => {
    let query = {}
    // ... 业务场景的 查询条件

    try {
        let result = await userRepositoryEntity.createQueryBuilder('user')
        .where(query)
        .skip((currentPage - 1) * pageSize)
        .take(pageSize)
        .orderBy("user.state", 'ASC')
        .addOrderBy('user.id', "ASC")
        .getManyAndCount()

        if (result.length) {
          res.json({
            code: 1000,
            data: {
              list: result[0],
              currentPage: currentPage,
              pageSize: pageSize,
              total: result[1]
            },
            message: '操作成功'
          })
        } else {
          throw new Error('查询失败')
        }
    } catch (error) {
        res.json({
            code: 1,
            data: [],
            message: '操作失败' + error.message
        })
    }
})

注意： 这里做排序，必须加上主键 id， 否则在第二页，可能还会出现 第一页的数据， 如果没有 id作为 order by 的条件， 只能保证当前页是排序的

replace 批量更新

场景： 现在数据库中的表，我需要使用 replace 去批量替换一些数据，假设 包版本号 有 ^ , 现在我需要去掉， 方便其他接口做数据统计

import { dataSource } from '../config/db.js'
import PackageRepository from '../entity/package.js'
const packageEntity = dataSource.getRepository(PackageRepository)

async function run() {
   const list = await packageEntity.find({
        // 这里只需要 包 的table 这个表里面的id字段即可
        select: ['id']
    })
    
    // 更新数据
    await packageEntity
        .createQueryBuilder()
        .update(PackageRepository)
        .set(
          {
            package_version: () => `REPLACE(package.package_version, '^', '')`
          }
         )
         .where("id IN (:...ids)", {
           ids: t.map(p => p.id)
         })
        .execute()
}

run()

image.png

join 连表（级联）查询

2023-06-07 新增

场景：希望将2个表做连表查询， 返回2个表中的内容, 例如： 我要查询出 package表中对应的在 仓库表中的 仓库名是什么

分析： 一个项目仓库 repository, 会对应多个 依赖（package）, 所以是 1->N 的关系

src/entity/package.js 实体

import { EntitySchema} from 'typeorm'
import repositoryEntity from './repository.js'

export default new EntitySchema({
  name: 'Package',
  columns: {
    id: {
      type: 'int',
      primary: true,
      generated: true
    },

    project_id: {
      type: 'int',
      index: true,
      comment: '项目仓库id'
    },

    package_name: {
      type: 'varchar',
      length: 100,
      comment: 'npm 包名'
    },

    package_version: {
      type: 'varchar',
      length: 40,
      comment: '当前包版本'
    }
  },
  relations: {
    // 与仓库关联 - 多个包 属于 一个仓库
    repository: {
      target: 'Repository',
      // 固定的几个字符串
      type: 'many-to-one',
      // 不创建外键约束
      createForeignKeyConstraints: false,
      joinColumn: {
        // 这里的关联字段是package实体的字段名
        name: "project_id"
      }
    }
  }
})

仓库表 src/entity/repository.js

import { EntitySchema } from 'typeorm'

export default new EntitySchema({
  name: 'Repository',
  columns: {
    id: {
      primary: true,
      type: 'int',
      generated: true,
      comment: '仓库id'
    },

    name: {
      type: 'varchar',
      length: 100
    },
    description: {
      type: 'varchar',
      length: 255,
      comment: '项目描述',
      // 可为空
      nullable: true
    }
  }
})

现在想要查询出package 表中名对应的repository表仓库名(name)是什么

sql

select p.id, p.project_id ,p.package_version, r.description from test_db.package p join test_db.repository r on p.project_id = r.id;

结果

image.png

js 代码实现

import { dataSource } from '../config/db.js'
import PackageRepository from '../entity/package.js'

async function run() {
    const packageEntity = dataSource.getRepository(PackageRepository)

    const c1 = await packageEntity.createQueryBuilder("p")
    .select([ 'p.id', 'p.project_id','p.package_name', 'p.package_version', 'p.repository'])
    .innerJoinAndSelect("p.repository", 'r', 'p.project_id=r.id')
    .getManyAndCount() 
}

run()

拓展1： 加点条件判断进去

import { dataSource } from '../config/db.js'
import PackageRepository from '../entity/package.js'

async function run() {
    const packageEntity = dataSource.getRepository(PackageRepository)

    const c1 = await packageEntity.createQueryBuilder("p")
    .select([ 'p.id', 'p.project_id','p.package_name', 'p.package_version', 'p.repository'])
    .where({
      package_name: 'vue',
      package_version: Like('2.5%')
    })
    .innerJoinAndSelect("p.repository", 'r', 'p.project_id=r.id')
    .getManyAndCount() 
}

run()

翻译： 联表查询出 package 表和 repository表的内容， 且只查询出 包名为 vue, 包的版本在 2.5.x的数据

innerJoinAndSelect 是会返回所有的 关联表（respository） 字段名

image.png

拓展2： repository返回的内容太多了， 我只想返回name字段

import { dataSource } from '../config/db.js'
import PackageRepository from '../entity/package.js'

async function run() {
    const packageEntity = dataSource.getRepository(PackageRepository)

    const c1 = await packageEntity.createQueryBuilder("p")
    .select([ 'p.id', 'p.project_id','p.package_name', 'p.package_version', 'p.repository'])
    .where({
      package_name: 'vue',
      package_version: Like('2.5%')
    })
    .innerJoin("p.repository", 'r','p.project_id=r.id')
    .addSelect(['r.name', 'r.description'])
    .getManyAndCount() 
}

run()

也可以这样

const c1 = await packageEntity.createQueryBuilder("p")
    .select([ 'p.id', 'p.project_id','p.package_name', 'p.package_version', 'r.name', 'r.description'])
    .where({
      package_name: 'vue',
      package_version: Like('2.5%')
    })
    .innerJoin("p.repository", 'r','p.project_id=r.id')
    .getManyAndCount()

返回部分字段改为 innerJoin 和 addSelect 添加要返回的字段名

image.png

关键点：

  • 在 package.js 实体代码中加入 relations, 并指定 joinColumn, 还需要指定是哪一种关系
  • innerJoinAndSelect 第一个参数是 属性， 第二个参数是表别名（alias）， 第三个参数是条件， 这里的条件就是 package 表中的 project_id 和 项目表（project）中的id是 一个意思

关系-js版本表达

typeorm中，案例大都是 基于typescript写的, typescript语法中，表达关系，使用的是装饰器 ， 如果是js版本，怎么表达呢？

js版本

实体

import { EntitySchema} from 'typeorm'

export default new EntitySchema({
  name: 'Package',
  columns: {
      ... 表字段
  },
  // 关系
  relations: {
      target： '目标是谁-字符串类型',
      // 关系的类型
      type: ''
  }
}
  • 一对一 type: "one-to-one"
  • 一对多 type: "one-to-many"
  • 多对一 type: "many-to-one"
  • 多对多 type: "many-to-many"

官网ts版本

import { Entity, PrimaryGeneratedColumn, Column, ManyToMany } from "typeorm";
import { Question } from "./Question";

@Entity()
export class Category {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;
   
   // 这里的type没啥意义
  @ManyToMany(type => Question, question => question.categories)
  questions: Question[];
}

使用原生sql

2023-10-16 新增

import { dataSource } from '../db/index.js'
const shopGoldRepository = dataSource.getRepository(ShopGoldEntiry)

// 最近15天
let startTime = dayjs().subtract(15, 'day').startOf('day').format('YYYY-MM-DD HH:mm:ss')
let endTime = dayjs().endOf('day').format('YYYY-MM-DD HH:mm:ss')

// 其他代码...

let type = list[0].type


let result = await shopGoldRepository.query(`select * from shop_gold where type="${type}" and createTime Between "${startTime}" and "${endTime}"`)

// result 返回的就是一个数组

该案例使用原生sql， sql解释： 查询最近15天，type =xxx的数据

transformer 使用

2023-10-20更新

/**
 * 角色
 *
 * 一个权限可以给多个角色
 * 一个角色可以拥有多个权限
 *
 * 所以 是N-N（多对多，ManyToMany）的关系
 */

import { EntitySchema } from 'typeorm'
import dayjs from 'dayjs'

export default new EntitySchema({
  name: 'Role',
  columns: {
    roleId: {
      type: 'int',
      primary: true,
      generated: 'increment',
      comment: '角色Id'
    },

    roleName: {
      type: 'varchar',
      length: 40,
      comment: '角色名称'
    },

    status: {
      type: 'int',
      default:  0,
      comment: '角色状态 1-启用; 0-禁用'
    },

    createTime: {
      type: 'datetime',
      default: () => 'CURRENT_TIMESTAMP',
      transformer: {
        from (value) {
          if (value) {
            return dayjs(value).format('YYYY-MM-DD HH:mm:ss')
          }

          return
        },
        to(val) {
          console.log('创建日期');
          console.log(val);
        }
      }
    },

    updateTime: {
      type: 'datetime',
      default: () => 'CURRENT_TIMESTAMP',
      transformer: {
        from (value) {
          if (value) {
            return dayjs(value).format('YYYY-MM-DD HH:mm:ss')
          }

          return
        },
        // 需要加上这个， 否则， 在更新 role表，会报错，TypeError: transformer.to is not a function
        to(val) {
          return val
        }
      }
    }
  },

  relations: {
    // 最终在数据库会自动创建 role_permissions_permission 表
    permissions: {
      target: 'Permission',
      type: 'many-to-many',
      joinTable: true,
      cascade: true
    },

    // 角色表和菜单表 多对多
    menus: {
      target: 'Menu',
      type: 'many-to-many',
      joinTable: true
    }
  }
})

注意： 如果上面没有提供 transformer 的to 函数, 在执行下面的 save方法 会报错 （TypeError: transformer.to is not a function）， typeorm@0.3.7 文档没有对这个进行描述，没有说明 to函数是必传。 比较难意识到 实体类 需要加上这个，所以，最好做数据转换不要在 实体里面处理

更新 role 表里面的部分字段。

import { datasource } from '../../db/index.js'
const roleRepository = datasource.getRepository(roleEntity)

export const updateRoleStatus = async(req, res) => {
  const {id, status} = req.body

  try {
    let  row = await roleRepository.findOne({
      where: {
        roleId: id
      }
    })

    if (!row) {
      res.json({
        code: -1,
        data: null,
        messaeg: '角色不存在'
      })
      return
    }

    // 如果更新时间没有生效，需要检查 Model中 transformer中的to函数是否有返回值，如果什么都没有返回，则updateTime字段是不会更新的
    row.updateTime = dayjs().format('YYYY-MM-DD HH:mm:ss')
    row.status = Number(status)

    await roleRepository.save(row)

    res.json({
      code: 1000,
      data: null,
      message: '操作成功'
    })
  } catch (error) {
    console.log(error);
    res.json({
      code: -1,
      data: [],
      message: '操作失败'+error.message
    })
  }

updateTime字段不更新？

逻辑层确定是写了 row.updateTime = dayjs().format('YYYY-MM-DD HH:mm:ss') , 打印日志row也是对的。 数据库中 updateTime字段还是没有更新, 需要检查 表实体中 transformer中的to函数是否有返回值

如果没有返回值， 是不会更新的

updateTime: {
  type: 'datetime',
  default: () => 'CURRENT_TIMESTAMP',
  transformer: {
    from (value) {
      if (value) {
        return dayjs(value).format('YYYY-MM-DD HH:mm:ss')
      }

      return
    },
    // 需要加上这个， 否则， 在更新 role表，会报错，TypeError: transformer.to is not a function
    to(val) {
      return val
    }
  }

不等于

2023-11-1 记录

示例：

mysql 查询 user表， 条件是， name = '张三' 且 status 不为0 的数据 。

mysql

select * from `user` u where name = '张三' AND u.status <> 1

image.png

使用代码实现， 下面使用 userId 也是一样的意思。

let userId = 9
let row = await userRepository.createQueryBuilder('user')
  .where('user.userId =:userId', {
    userId
  })
  .andWhere('user.status <> :userId', {
    status: 1
  })
  .getOne()

上面使用 <> 和 mysql 语法一样的， 使用 :userId 作为占位符， 可以防止SQL注入， typeorm 对传递的参数进行了转义处理。

结果

{
  userId: 9,
  name: '张三',
  password: '$2b$10$2RKl8XcwYovku/CMX4DZM.iNzf.0/8Zq2cekJwUuogbF8QNHrLYHS',
  phone: 'xxxxxx',
  status: 0,
  nickname: null,
  email: null,
  avatar: null,
  createTime: 2023-11-01T05:50:36.000Z,
  updateTime: 2023-11-01T05:50:36.000Z
}

使用 find 查找

import { Not } from 'typeorm'

let userId = 9
let row = await userRepository.find({
    where: {
        userId,
        status: Not(1)
    }
})

这里find 找到后，返回的结果是一个 数组对象

avatar
文章
阅读
粉丝
相关推荐
Nest.js + Typeorm 尝试动态建表
5.1k阅读
 · 
52点赞
小白可能不太好学会的nestjs+grpc+docker完整流程
2.9k阅读
 · 
47点赞
Nest+GrqphQL+Prisma+React全栈开发(二)-Prisma篇
1.7k阅读
 · 
7点赞
基于nest的grpc crud解决方案
457阅读
 · 
3点赞
[二期 - 6] 关于Nest的工程规范（CLi、项目结构，monorepo）
1.2k阅读
 · 
2点赞
NestJS小技巧18-如何使用Elasticsearch、Kibana、NestJS和React创建完整的自动完成搜索应用程序(第三章/共四章)
731阅读
 · 
4点赞
基于Node.js的ORM框架 Prisma的上手使用
3.2k阅读
 · 
14点赞
[二期 - 7] Nest 中的openAPI - 在文档意义上的Nest完结篇
833阅读
 · 
8点赞
NestJS小技巧35-最近，我尝试在微服务架构中用SAGA模式
419阅读
 · 
4点赞