koa+ts+mysql后台开发——（五）使用orm框架sequelize操作数据库，自定义格式校验、统一处理返回信息、分页格式

---

前言

1、官方文档：https://www.sequelize.com.cn/
2、sequelize-typescript文档：https://npmmirror.com/package/sequelize-typescript

---

一、引包

这里用到的是 mysql 如果要用其它 sql ，请查看官方文档

npm i sequelize -S
npm i sequelize-typescript -S
npm i mysql2 -S

二、初始化sequelize

1、目录： /app/db 下创建index.ts文件。
code如下：

import path from "path";
import { Sequelize } from "sequelize-typescript";
import config from "../config";
import { dbLogger } from './../logger/index'

const sequelize = new Sequelize(config.db.db_name as string, config.db.db_user as string, config.db.db_password as string, {
  dialect: 'mysql', // 数据库类型
  host: config.db.db_host as string,
  port: config.db.db_port as unknown as number,
  pool: { // 连接池设置
    max: 5, // 最大连接数
    idle: 30000,
    acquire: 60000
  },
  logging: msg => dbLogger.info(msg), // 日志，不打印则可设置为 false
  models: [path.join(__dirname, '..', 'model/**/*.ts'), path.join(__dirname, '..', 'model/**/*.js')],
  // dialectOptions: {
  //   charset: 'utf8mb4', // 字符集
  //   collate: 'utf8mb4_bin' // 排序规则
  // },
  define: {
    freezeTableName: true, // sequelize会给表名自动添加为复数，
    timestamps: true, // 开启时间戳 create_at delete_at update_at
    paranoid: true, // 开启假删除
    createdAt: 'created_at',
    updatedAt: 'updated_at',
    deletedAt: 'deleted_at',
    charset: 'utf8mb4', // 字符集
    collate: 'utf8mb4_bin' // 排序规则
  },
  timezone: '+08:00', // 改为标准时区
})

export const db = async () => {
  try {
    await sequelize.authenticate();
    console.log('Connection has been established successfully.');
  } catch (error) {
    console.error('Unable to connect to the database:', error);
  }
}

export default sequelize

2、在 /app/index.ts 中 添加

import { db } from './db/index'
db()

三、添加模型层

在目录：/app/model 添加 Admin.ts

import { Column, Model, Table } from "sequelize-typescript";

@Table
export default class Admin extends Model {
  @Column
  account!: string
  @Column
  password!: string
  @Column
  name!: string
  @Column
  mobile!: string
  @Column
  email!: string
  @Column
  is_disable!: number
}

即可映射对应的数据表

四、添加服务层

在目录：/app/service 添加 AdminService.ts

import Admin from "../model/Admin";

class AdminService {
  getAdminById(id: number) {
    return Admin.findOne({
      where: {
        id: id,
      },
    })
  }
  getAdminByName(name: string) {
    return Admin.findOne({
      where: {
        name: name
      }
    })
  }
  addAdmin(admin: any) {
    return Admin.create(admin)
  }
  updateAdmin(id: number, admin: any) {
    return Admin.update(admin, { where: { id: id } })
  }
  deleteAdmin(id: number) {
    return Admin.destroy({where:{id}})
  }
}

export default new AdminService

实现用户的增删改查

五、添加控制层 （增删改查）

在目录：/app/controller 添加 AdminController.ts

import { Rules } from "async-validator";
import { createHash } from "crypto";
import { Context } from "koa";
import paginate from "../../utils/paginate";
import response from "../../utils/response";
import validate from "../../utils/validate";
import AdminService from "../service/AdminService";

class AdminController {
  async getAdminList(ctx: Context) {
    const usp = new URLSearchParams(ctx.querystring)
    // console.log(usp.get('page'), usp.get('pageSize'))
    let page = 1, pageSize = 10;
    // 进行类型转换的时候一定要判断一下能否转化为数字，保证程序的健壮性
    if (usp.get('page') !== null && !isNaN(Number(usp.get('page')))) {
      page = Number(usp.get('page'))
    }
    if (usp.get('pageSize') !== null && !isNaN(Number(usp.get('pageSize')))) {
      pageSize = Number(usp.get('pageSize'))
    }
    const { rows, count } = await AdminService.getAdminListByPage(page, pageSize)
    response.success(ctx, paginate(rows, page, pageSize, count))
  }
  async addAdmin(ctx: Context) {
    const rules: Rules = {
      name: [
        {
          type: 'string',
          required: true,
          message: '用户名不能为空'
        }
      ],
      password: [
        {
          type: 'string',
          required: true,
          message: '密码不能为空'
        }, {
          type: 'string',
          min: 6,
          max: 20,
          message: '密码长度6-20位'
        }
      ]
    }
    interface IAdmin {
      id: number,
      name: string,
      password: string
    }
    const { data, error } = await validate<IAdmin>(ctx, rules)
    if (error !== null) {
      return response.error(ctx, {}, error)
    }
    // 判断是否存在 该管理员
    const adminName = await AdminService.getAdminByName(data.name)
    if (adminName !== null) {
      return response.error(ctx, '', '管理员已存在')
    }

    data.password = createHash('md5').update(data.password).digest('hex'); // 密码加密
    // console.log('密码：', data.password)
    try {
      const row = await AdminService.addAdmin(data)
      // console.log(row)
      if (row.id > 0) {
        return response.success(ctx)
      }
      return response.error(ctx, '', '插入失败')
    } catch (error) {
      console.log(error)
      return response.error(ctx, '', 'sql异常')
    }
  }
  async updateAdmin(ctx: Context) {
    const id = ctx.params['id'] as number
    const admin = await AdminService.getAdminById(id)
    if (admin === null) {
      return response.error(ctx, '', '管理员不存在')
    }
    const rules: Rules = {
      name: [{
        type: 'string',
        required: true,
        message: '用户名不能为空'
      }]
    }
    interface IAdmin {
      name: string,
      password: string,
      mobile: string,
      email: string,
    }
    const { data, error } = await validate<IAdmin>(ctx, rules)
    if (error !== null) {
      return response.error(ctx, '', error)
    }
    if (data.password !== undefined && data.password !== '') {
      data.password = createHash('md5').update(data.password).digest('hex'); // 密码加密
    }
    console.log(id, data)
    try {
      const [number] = await AdminService.updateAdmin(id, data)
      if (number > 0) {
        return response.success(ctx)
      } else {
        return response.error(ctx, '', '更新失败')
      }
    } catch (error) {
      console.log(error)
      return response.error(ctx, '', 'sql异常')
    }
  }
  async deleteAdmin(ctx: Context) {
    const id = ctx.params['id'] as number
    const admin = await AdminService.getAdminById(id)
    if (admin === null) {
      return response.error(ctx, '', '管理员不存在')
    }
    // const [number] = await AdminService.deleteAdmin(id)
    // if (number > 0) {
    //   return response.success(ctx)
    // }
    const number = await AdminService.deleteAdmin(id)
    if (number > 0) {
      return response.success(ctx)
    }
    return response.error(ctx, '', '删除失败')
  }
}

export default new AdminController

tip:
1、这里引入了async-validator 自定义数据校验。需要 npm i async-validator -S
2、封装统一处理里返回信息、分页格式。
3、nodejs 环境自带 md5

如下所示：

六、自定义数据校验

目录：/utils/validate.ts
添加如下代码：

import Schema, { Rules, Values } from "async-validator";
import { Context } from "koa";

/**
 * @description 表单校验
 * @param {Context} ctx 上下文
 * @param {Rules} rules 校验规则
 * @param {boolean} flag 是否返回完整的的错误信息
 * @return {*}
 */
async function validate<T extends Values>(ctx: Context, rules: Rules, flag: boolean = false): Promise<{ data: T, error: any | null }> {
  const validator = new Schema(rules)
  let data: any = {}

  switch (ctx.method) {
    case "GET": break;
    case "POST": data = getFormData(ctx); break;
    case "PUT": data = getFormData(ctx); break;
    case "DELETE": data = getFormData(ctx); break;

  }
  return await validator.validate(data).then((data) => {
    return {
      data: data as T,
      error: null
    }
  }).catch(err => {
    if (flag) {
      return {
        data: {} as T,
        error: err
      }
    }
    return {
      data: {} as T,
      error: err.errors[0].message
    }
  })
}

function getFormData(ctx: Context) {
  // console.log(JSON.stringify(ctx.request.body))
  return ctx.request.body
}

export default validate

七、统一处理返回信息

目录：/utils/response.ts
添加如下代码：

import { Context } from "koa";

/**
 * @description: 统一处理返回格式
 * @param {Context} ctx
 * @param {*} data  返回的数据
 * @param {string} msg 提示信息
 * @param {number} code 状态码
 */
function success(ctx: Context, data: any = [], msg: string = 'success', code: number = 0) {
  ctx.body = {
    code,
    msg,
    data
  }
}

/**
 * @description: 统一处理返回格式
 * @param {Context} ctx
 * @param {*} data  扩展提示
 * @param {string} msg 错误提示信息
 * @param {number} code 状态码
 */
function error(ctx: Context, data: any = [], msg: string = 'error', code: number = 1) {
  ctx.body = {
    code,
    msg,
    data
  }
}

export default {
  success,
  error
}

八、统一处理分页格式

目录：/utils/paginate.ts
添加如下代码：

// import { Model } from "sequelize-typescript";

/**
 * @description 统一处理分页格式
 * @param {*} data 列表数据
 * @param {number} currentPage 当前页
 * @param {number} pageSize 每页数量
 * @param {number} total 总数
 */
function paginate(data: any = [], currentPage: number = 1, pageSize: number = 100, total: number = 0) {
  return {
    data,
    currentPage,
    pageSize,
    total,
    totalPage: Math.ceil(total / pageSize)
  }
}

export default paginate

九、在路由中引入导出接口

目录：/app/router/index.ts

import koaRouter from 'koa-router'
import AdminController from '../controller/AdminController'

router.post('/admin/add', AdminController.addAdmin)
router.put('/admin/:id', AdminController.updateAdmin)
router.delete('/admin/:id', AdminController.deleteAdmin)

export default router

再在 /app/index.ts 中使用app.use 引入router 即可

---

总结

Sequelize 是一个基于 promise 的 Node.js ORM, 目前支持 Postgres, MySQL, MariaDB, SQLite 以及 Microsoft SQL Server. 它具有强大的事务支持, 关联关系, 预读和延迟加载,读取复制等功能。

建议多查阅官方文档。