| 博客记录点滴

项目开始

项目是 koa + ts + sequelize + mysql
项目 package.json

{
  "name": "koa-api",
  "version": "1.0.0",
  "description": "koa-api",
  "main": "index.ts",
  "scripts": {
    "dev": "nodemon",
    "test": "jest"
  },
  "jest": {
    "preset": "ts-jest"
  },
  "keywords": ["koa", "ts", "mysql"],
  "author": "",
  "license": "MIT",
  "dependencies": {
    "dotenv": "^16.0.3",
    "koa": "^2.13.4",
    "koa-router": "^12.0.0",
    "log4js": "^6.7.0",
    "mysql2": "^2.3.3",
    "reflect-metadata": "^0.1.13",
    "sequelize": "^6.25.8",
    "sequelize-typescript": "^2.1.5",
    "jsonwebtoken": "^8.5.1",
    "koa-body": "^6.0.1",
    "supertest": "^6.3.1"
  },
  "devDependencies": {
    "@types/dotenv": "^8.2.0",
    "@types/jest": "^29.2.3",
    "@types/jsonwebtoken": "^8.5.9",
    "@types/koa": "^2.13.5",
    "@types/koa-router": "^7.4.4",
    "@types/log4js": "^2.3.5",
    "@types/node": "^18.11.9",
    "@types/supertest": "^2.0.12",
    "@types/validator": "^13.7.10",
    "async-validator": "^4.2.5",
    "jest": "^29.3.1",
    "ts-jest": "^29.0.3",
    "ts-node": "^10.9.1",
    "typescript": "^4.9.3"
  }
}

import run from './app'

run(3000)

import Koa from 'koa'
import router from './router'
import { Server } from 'http'
const app = new Koa()

// 注册路由
app.use(router.routes())

const run = (port: any): Server => {
  return app.listen(port, () => {
    console.log(`Server running on port ${port}`)
  })
}

export default run

import Router from 'koa-router'
import IndexController from '../controller/IndexController'
const router = new Router({ prefix: '/admin' })

// 测试
router.get('/', IndexController.index)

export default router

import { Context } from 'koa'

class IndexController {
  async index(ctx: Context) {
    ctx.body = 'hello world hello world'
  }
}

export default new IndexController()

添加 nodemon 热重启

{
  "watch": ["app/**/*.ts", "utils/**/*.ts", "./index.ts"],
  "ignore": ["node_modules"],
  "exec": "ts-node index.ts",
  "ext": ".ts"
}

单元测试

安装 npm i jest ts-jest supertest @types/jest @types/supertest -D
tsc --init 初始化 tsconfig.json

  "jest": {
    "preset": "ts-jest"
  },

import run from '../app'
import { Server } from 'http'
import request from 'supertest'
// 测试
describe('sum set', () => {
  it('sum 1', () => {
    expect(1 + 1).toEqual(2)
  })
})

//  测试服务器
describe('http', () => {
  let server: Server
  beforeAll(() => {
    server = run(3003)
  })
  it('GET /admin', () => {
    return request(server)
      .get('/admin')
      .expect(200)
      .then((res) => {
        expect(res.text).toEqual('admin')
      })
  })
  afterAll(() => {
    server.close()
  })
})

dotenv 文件配置

安装 npm i dotenv @types/dotenv -D

NODE_ENV=dev
SERVER_PORT=3000
SERVER_HOST=localhost
DB_HOST=localhost
DB_PORT=3306
DB_USER=root
DB_PASSWORD=root
DB_NAME=database_name
DB_DEBUG=true
JWT_SECRET=secret
JWT_EXPIRES=30d
STATIC_PATH=statics
Auth=asdfasdfasdfsad

import dotenv from 'dotenv'
dotenv.config()

import run from './app'
import config from './app/config'

run(config.server.port)

log4js 日志

安装 npm i log4js @types/log4js -D

import { configure, getLogger } from 'log4js'
import config from '../config'

// 配置
configure({
  appenders: {
    cheese: { type: 'file', filename: 'logs/cheese.log' },
    access: { type: 'file', filename: 'logs/access.log' },
  },
  categories: {
    default: { appenders: ['cheese'], level: 'info' },
    access: { appenders: ['access'], level: 'info' },
  },
})

export const accessLogger = getLogger('access')
export default getLogger()

// 使用 logger
// const logStr = `method: ${ctx.method}, url: ${ctx.url}, ua: ${ctx.headers['user-agent']}`
// accessLogger.info('access', logStr)

import { configure, getLogger } from 'log4js'
import config from '../config'

// 配置
configure(config.log)

export const accessLogger = getLogger('access')
export default getLogger()

数据库 sequelize

安装 npm i @types/node @types/validator
安装 npm i sequelize reflect-metadata sequelize-typescript mysql2 -S

import { Sequelize } from 'sequelize-typescript'
import config from '../config'
import { dbLogger } from '../logger'
// sequelize 配置
const sequelize = new Sequelize(
  config.db.db_name as string,
  config.db.db_user as string,
  config.db.db_password as string,
  {
    host: config.db.db_host as string,
    port: config.db.db_port as number,
    dialect: 'mysql',
    logging: (msg) => dbLogger.info(msg),
    define: {
      timestamps: false,
      createdAt: 'created_at',
      updatedAt: 'updated_at',
      deletedAt: 'deleted_at',
    },
    models: [__dirname + '/models/**/*.ts', __dirname + '/models/**/*.js'],
  }
)

// 测试连接
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 db

import { Model, Table, Column } from 'sequelize-typescript'
// 对应表中的 admins 表
@Table
export default class Admin extends Model {
  @Column
  name!: string
  @Column
  mobile!: string
  @Column
  email!: string
}

import db from './db'
db()

sequelize 常用操作

// # 1, 查询全部
const data = await Admin.findAll()

// # 2，指定字段查询
const data = await Admin.findAll({
  attributes: ['id', 'name', 'mobile', 'email'],
})

// # 3，条件查询
const data = await Admin.findAll({
  where: {
    name: 'admin',
  },
})

// # 4，分页查询
const data = await Admin.findAll({
  offset: 0,
  limit: 10,
  order: [['id', 'DESC']],
})

// # 5，条件查询（id 大于 6） + 分页查询
// Op 是 sequelize 的操作符
// Op.gt 大于 Op.lt 小于 Op.gte 大于等于 Op.lte 小于等于 Op.ne 不等于 Op.eq 等于 Op.not 不等于
const data = await Admin.findAll({
  where: {
    id: {
      [Op.gt]: 6,
    },
  },
  offset: 0,
  limit: 10,
  order: [['id', 'DESC']],
})

// 增加数据
const data = await Admin.create({
  name: 'admin',
  mobile: '13800013800',
  email: '123@qq.com',
})

// 根据主键查询数据
const data = await Admin.findByPk(1)

// 根据主键更新数据
const data = await Admin.update(
  {
    name: 'admin1',
    mobile: '13800013800',
    email: '456@qq.com',
  },
  {
    where: {
      id: 1,
    },
  }
)

// 根据主键删除数据
const data = await Admin.destroy({
  where: {
    id: 1,
  },
})

sequelize 表关联

一对一

// 一对一： hasOne 或者 belongsTo, 需要借助外键
//  例如 一个文章分类下面有多个文章，查询文章时候，需要查询文章分类
//  定义文章模型
const ArticleModel = sequelize.define('article', {
  title: Sequelize.STRING,
  content: Sequelize.TEXT,
})
ArticleModel.ArticleCate = ArticleModel.belongsTo(CategoryModel, {
  foreignKey: 'cate_id',
})
// 定义文章分类模型
const CategoryModel = sequelize.define('category', {
  name: Sequelize.STRING,
})

// 查询语句
const data = await ArticleModel.findAll({
  include: [
    {
      model: CategoryModel,
    },
  ],
})

// 例如2： 系统设置表 和 系统设置详情表
// 定义系统设置模型
const SettingModel = sequelize.define(
  'setting',
  {
    id: {
      type: Sequelize.INTEGER,
      primaryKey: true,
      autoIncrement: true,
    },
    name: Sequelize.STRING,
    icon: Sequelize.STRING,
  },
  {
    timestamps: false,
    tableName: 'setting',
  }
)

SettingModel.SettingDetail = SettingModel.hasOne(SettingDetailModel, {
  foreignKey: 'setting_id',
})

// 定义系统设置详情模型
const SettingDetailModel = sequelize.define(
  'setting_detail',
  {
    id: {
      type: Sequelize.INTEGER,
      primaryKey: true,
      autoIncrement: true,
    },
    setting_id: Sequelize.INTEGER,
    title: Sequelize.STRING,
    value: Sequelize.STRING,
  },
  {
    timestamps: false,
    tableName: 'setting_detail',
  }
)

// 查询语句
const data = await SettingModel.findAll({
  include: [
    {
      model: SettingDetailModel,
    },
  ],
})

一对多

// 一对多： hasMany 需要借助外键
// 例如： 一个文章分类下面有多个文章，查询文章分类时候，需要查询文章

// 上面 文章模型 ArticleModel 已经定义过了
// 上面 文章分类模型 CategoryModel 已经定义过了
// 定义文章分类模型 加上 关联
CategoryModel.ArticleList = CategoryModel.hasMany(ArticleModel, {
  foreignKey: 'cate_id',
})

// 查询语句
const data = await CategoryModel.findAll({
  include: [
    {
      model: ArticleModel,
    },
  ],
})

多对多

//多对多: belongsToMany 需要借助第三张表
// 例如：一个学生可以选择多个课程，一个课程可以被多个学生选择
// 定义学生模型
const StudentModel = sequelize.define('student', {
  id: {
    type: Sequelize.INTEGER,
    primaryKey: true,
    autoIncrement: true,
  },
  name: Sequelize.STRING,
})

StudentModel.CourseList = StudentModel.belongsToMany(CourseModel, {
  through: 'student_course',
  foreignKey: 'student_id',
  otherKey: 'course_id',
})

// 定义课程模型
const CourseModel = sequelize.define('course', {
  id: {
    type: Sequelize.INTEGER,
    primaryKey: true,
    autoIncrement: true,
  },
  name: Sequelize.STRING,
})

CourseModel.StudentList = CourseModel.belongsToMany(StudentModel, {
  through: 'student_course',
  foreignKey: 'course_id',
  otherKey: 'student_id',
})

// 查询语句: 学生选择了哪些课程
const data = await StudentModel.findAll({
  include: [
    {
      model: CourseModel,
    },
  ],
})

sequelize 常见数据类型

// 1. 数字类型
Sequelize.INTEGER // 整型
Sequelize.BIGINT // 大整型
Sequelize.FLOAT // 浮点型
Sequelize.DOUBLE // 双精度浮点型
Sequelize.DECIMAL // 小数类型
Sequelize.REAL // 浮点型
Sequelize.NUMBER // 数字类型
Sequelize.TINYINT // 小整型
Sequelize.SMALLINT // 小整型
Sequelize.MEDIUMINT // 中整型

// 2. 字符串类型
Sequelize.STRING // 字符串类型 varchar(255)
Sequelize.CHAR // 字符串类型
Sequelize.TEXT // 文本类型
Sequelize.String.BINARY // 二进制类型
Sequelize.TINYTEXT // 文本类型
Sequelize.MEDIUMTEXT // 文本类型
Sequelize.LONGTEXT // 文本类型

Sequelize.UUID // UUID 类型
Sequelize.ENUM // 枚举类型
Sequelize.JSON // JSON 类型
Sequelize.JSONB // JSONB 类型
Sequelize.BOOLEAN // 布尔类型

// 3. 日期类型
Sequelize.DATE // 日期类型
Sequelize.DATEONLY // 日期类型
Sequelize.TIME // 时间类型
Sequelize.NOW // 时间类型
Sequelize.BLOB // 二进制类型
Sequelize.BINARY // 二进制类型

jwt 认证

安装 npm i jsonwebtoken @types/jsonwebtoken -D

import jwt from 'jsonwebtoken'

const sign = (payload: any) => {
  return jwt.sign({ admin: payload }, process.env.JWT_SECRET as string, {
    expiresIn: process.env.JWT_EXPIRES as string,
  })
}

const verify = (token: string) => {
  return jwt.verify(token, process.env.JWT_SECRET as string)
}

export { sign, verify }

import { sign } from '../../utils/auth'
import { Context } from 'koa'
import AdminService from '../service/AdminService'

class LoginController {
  async login(ctx: Context) {
    const admin = await AdminService.getAdmin()
    const token = sign(admin)
    ctx.body = {
      token,
    }
  }
}

export default new LoginController()

//  登录
router.post('/login', LoginController.login)

import { Context, Next } from 'koa'
import { verify } from '../../utils/auth'

const AuthMiddleware = async (ctx: Context, next: Next) => {
  const token = ctx.headers.authorization as string
  const data = verify(token.split(' ')[1])
  if (data) {
    console.log('data=> ', data)
    await next()
    return
  }

  ctx.body = {
    code: 401,
    msg: 'token is required',
  }
}

export default AuthMiddleware

统一异常处理

// 统一返回数据格式

import { Context } from 'koa'

export default class Response {
  static success(ctx: Context, data: any) {
    ctx.body = {
      code: 200,
      data,
    }
  }

  static fail(ctx: Context, msg: string, code: number = 500) {
    ctx.body = {
      code,
      msg,
    }
  }
}

统一分页处理

// 统一分页处理
import { Model } from 'sequelize-typescript'

const paginate = async <T extends Model[]>(
  data: T,
  currentPage: number = 1,
  total: number = 0,
  limit: number = 15
) => {
  const totalPage = Math.ceil(total / limit)
  const result = {
    data,
    currentPage,
    totalPage,
    total,
  }
  return result
}

export default paginate

 getAdminListByPage(page: number = 1, limit: number = 15) {
    const offset = (page - 1) * limit
    return Admin.findAndCountAll({
      offset,
      limit,
    })
  }

import { Context } from 'koa'
import paginate from '../../utils/paginate'
import Response from '../../utils/response'
import AdminService from '../service/AdminService'

class AdminControoler {
  async getAdminList(ctx: Context) {
    const usp = new URLSearchParams(ctx.querystring)
    console.log('usp', usp)
    const currentPage = usp.get('page') || 1
    const limit = usp.get('limit') || 15
    const { rows, count } = await AdminService.getAdminListByPage(
      Number(currentPage),
      Number(limit)
    )
    Response.success(
      ctx,
      await paginate(rows, Number(currentPage), count, Number(limit))
    )
  }
}

export default new AdminControoler()

自定义数据校验

安装 npm i async-validator koa-body -D

// 数据校验
import Schema, { Rules, Values } from 'async-validator'
import { Context } from 'koa'

async function validate<T extends Values>(
  ctx: Context,
  rules: Rules
): Promise<{ data: T; error: any | null }> {
  const validator = new Schema(rules)
  let data = {}
  if (ctx.method === 'GET') {
    data = ctx.query
  } else if (ctx.method === 'POST') {
    data = getFormData(ctx) as T
  }
  return await validator
    .validate(data)
    .then(() => {
      return { data: data as T, error: null }
    })
    .catch((error) => {
      return {
        data: {} as T,
        error,
      }
    })
}

function getFormData(ctx: Context) {
  return ctx.request.body
}

export default validate

import { sign } from '../../utils/auth'
import { Context } from 'koa'
import AdminService from '../service/AdminService'
import Response from '../../utils/response'
import { Rules } from 'async-validator'
import validate from '../../utils/validate'

class LoginController {
  async login(ctx: Context) {
    const rules: Rules = {
      name: [
        {
          type: 'string',
          required: true,
          message: '用户名不能为空',
        },
      ],
      password: [
        {
          type: 'string',
          required: true,
          message: '密码不能为空',
        },
      ],
    }
    interface IAdmin {
      name: string
      password: string
    }
    const { data, error } = await validate<IAdmin>(ctx, rules)

    if (error !== null) {
      Response.fail(ctx, error)
      return
    }

    const admin = await AdminService.getAdmin(
      ctx.request.body.name as string,
      ctx.request.body.password as string
    )
    if (admin) {
      const token = sign(admin)
      ctx.body = {
        token,
      }
      Response.success(ctx, token)
    } else {
      Response.fail(ctx, '用户名或密码错误', 401)
    }
  }
}

export default new LoginController()

文件上传

使用 koa-body 中间件

管理员管理 Api

添加管理员
编辑挂历员
删除管理员

import Admin from '../db/models/Admin'

class AdminService {
  getAdmin(name: string = '', password: string = '') {
    return Admin.findOne({
      where: {
        name,
        password,
      },
    })
  }
  getadminById(id: number) {
    return Admin.findByPk(id)
  }

  getAdminListByPage(page: number = 1, limit: number = 15) {
    const offset = (page - 1) * limit
    return Admin.findAndCountAll({
      offset,
      limit,
    })
  }
  // 添加管理员
  addAdmin(name: string, password: string, mobile: string, email: string) {
    return Admin.create({
      name,
      password,
      mobile,
      email,
    })
  }

  // 更新管理员
  updateAdminById(id: number, admin: any) {
    return Admin.update(admin, {
      where: {
        id,
      },
    })
  }
  // 删除管理员
  deleteAdminById(id: number) {
    return Admin.destroy({
      where: {
        id,
      },
    })
  }
}

export default new AdminService()

import { Context } from 'koa'
import paginate from '../../utils/paginate'
import Response from '../../utils/response'
import AdminService from '../service/AdminService'

class AdminControoler {
  // 获取管理员列表
  async getAdminList(ctx: Context) {
    const usp = new URLSearchParams(ctx.querystring)
    console.log('usp', usp)
    const currentPage = usp.get('page') || 1
    const limit = usp.get('limit') || 15
    const { rows, count } = await AdminService.getAdminListByPage(
      Number(currentPage),
      Number(limit)
    )
    Response.success(
      ctx,
      await paginate(rows, Number(currentPage), count, Number(limit))
    )
  }

  // 添加管理员
  import { Context } from 'koa'
import paginate from '../../utils/paginate'
import Response from '../../utils/response'
import Admin from '../db/models/Admin'
import AdminService from '../service/AdminService'

class AdminControoler {
  // 获取管理员列表
  async getAdminList(ctx: Context) {
    const usp = new URLSearchParams(ctx.querystring)
    console.log('usp', usp)
    const currentPage = usp.get('page') || 1
    const limit = usp.get('limit') || 15
    const { rows, count } = await AdminService.getAdminListByPage(
      Number(currentPage),
      Number(limit)
    )
    Response.success(
      ctx,
      await paginate(rows, Number(currentPage), count, Number(limit))
    )
  }

  // 添加管理员
  async addAdmin(ctx: Context) {
    const { name, password, mobile, email } = ctx.request.body
    const admin = await AdminService.addAdmin(name, password, mobile, email)
    Response.success(ctx, admin)
  }

  // 编辑管理员
  async editAdmin(ctx: Context) {
    const { adminId, admin } = ctx.request.body
    const data = await AdminService.getadminById(adminId)
    if (!data) {
      Response.fail(ctx, '管理员不存在')
      return
    }
    admin.name = admin.name
    admin.mobile = admin.mobile
    admin.email = admin.email
    await AdminService.updateAdminById(adminId, admin)
    Response.success(ctx, admin)
  }

  // 删除管理员
  async deleteAdmin(ctx: Context) {
    const { adminId } = ctx.request.body
    const admin = await AdminService.getadminById(adminId)
    if (!admin) {
      Response.fail(ctx, '管理员不存在')
      return
    }
    await AdminService.deleteAdminById(adminId)
    Response.success(ctx, admin)
  }
}

export default new AdminControoler()

import Router from 'koa-router'
import IndexController from '../controller/IndexController'
import LoginController from '../controller/LoginController'
import AdminController from '../controller/AdminController'
const router = new Router({ prefix: '/admin' })

// 测试
router.get('/', IndexController.index)
//  登录
router.post('/login', LoginController.login)

// 获取管理员列表
router.get('/adminList', AdminController.getAdminList)

// 添加管理员
router.post('/addAdmin', AdminController.addAdmin)

// 编辑管理员
router.post('/editAdmin', AdminController.editAdmin)

// 删除管理员
router.post('/deleteAdmin', AdminController.deleteAdmin)

export default router

接口测试工具



@BASE_URL = http://localhost:3000/admin
@TOKEN = eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJhZG1pbiI6eyJpZCI6MSwibmFtZSI6ImFkbWluIiwibW9iaWxlIjoiMTM4MDAwMTM4MDAiLCJlbWFpbCI6ImFkbWluQGFkbWluLmNvbSIsInBhc3N3b3JkIjoiMTIzNDU2In0sImlhdCI6MTY2OTM2Mjk2MCwiZXhwIjoxNjY5NDQ5MzYwfQ.MmT1157Hc1jk74px0lt9g3KBbAr7sSA4rg0w4DEx4AM

### 测试
GET {{BASE_URL}}/ HTTP/1.1
Authorization: Bearer {{TOKEN}}

###  登录
POST {{BASE_URL}}/login HTTP/1.1
Content-Type: application/json

{
  "name": "admin",
  "password": "123456"
}

### 管理员列表
GET {{BASE_URL}}/adminList?page=1&limit=15 HTTP/1.1

### 添加管理员
POST {{BASE_URL}}/addAdmin HTTP/1.1
Content-Type: application/json

{
  "name":"admin1",
  "password":"123456",
  "mobile":"13800013800",
  "email":"123123@163.com"
}

### 编辑管理员
POST {{BASE_URL}}/editAdmin HTTP/1.1
Content-Type: application/json

{
  "adminId": 2,
  "name": "admin2",
  "password": "123456"
}


### 删除管理员
POST {{BASE_URL}}/deleteAdmin HTTP/1.1
Content-Type: application/json

{
  "adminId": 2
}

参考资料

koa+node+ts+mysql 开发接口open in new window

# 项目开始

# 单元测试

# dotenv 文件配置

# log4js 日志

# 数据库 sequelize

# sequelize 常用操作

# sequelize 表关联

# 一对一

# 一对多

# 多对多

# sequelize 常见数据类型

# jwt 认证

# 统一异常处理

# 统一分页处理

# 自定义数据校验

# 文件上传

# 管理员管理 Api

# 接口测试工具

# 参考资料

项目开始

单元测试

dotenv 文件配置

log4js 日志

数据库 sequelize

sequelize 常用操作

sequelize 表关联

一对一

一对多

多对多

sequelize 常见数据类型

jwt 认证

统一异常处理

统一分页处理

自定义数据校验

文件上传

管理员管理 Api

接口测试工具

参考资料