Skip to content
当前页大纲

先前我们已经创建好了一个基础的Koa工程,接下来我们来给它加上数据库功能

本例子将使用Typeorm连接数据库

创建数据库服务

我这里使用的是一个免费的线上服务进行临时测试,具体可以根据自己手头上的资源进行选择

创建完毕后将相关配置信息填入环境变量文件

Typeorm

如果用 MySQL 的话

sh
pnpm add typeorm mysql2

如果用 MongoDB 的话

sh
pnpm add typeorm mongodb

定义模型

新建模型src/entities/user.entity.ts

ts
import { Entity, Column, PrimaryGeneratedColumn, CreateDateColumn, UpdateDateColumn } from 'typeorm'
import bcrypt from 'bcryptjs'

@Entity({ name: 'user' })
export class User {
  @PrimaryGeneratedColumn({ unsigned: true })
  id: number
  @Column({ type: 'varchar', comment: '用户名' })
  username: string
  @Column({ type: 'varchar', comment: '密码' })
  password: string
  @Column({ type: 'varchar', unique: true, comment: '邮箱' })
  email: string
  @Column({ type: 'varchar', comment: '锁定的token', default: null })
  lock_token?: string
  @CreateDateColumn({ type: 'timestamp', comment: '创建时间' })
  createdAt: Date
  @UpdateDateColumn({ type: 'timestamp', comment: '更新时间' })
  updatedAt?: Date

  // 密码加密
  hashPassword(password: string) {
    this.password = bcrypt.hashSync(password, bcrypt.genSaltSync())
  }
  // 密码比对
  comparePassword(password: string) {
    return bcrypt.compareSync(password, this.password)
  }
}
ts
import {
  Entity,
  Column,
  ObjectId,
  ObjectIdColumn,
  CreateDateColumn,
  UpdateDateColumn,
} from 'typeorm'
import bcrypt from 'bcryptjs'

@Entity({ name: 'user' })
export class User {
  @ObjectIdColumn()
  _id: ObjectId
  @Column({ type: 'string', comment: '用户名' })
  username: string
  @Column({ type: 'string', comment: '密码' })
  password: string
  @Column({ type: 'string', unique: true, comment: '邮箱' })
  email: string
  @Column({ type: 'string', comment: '锁定的token', default: null })
  lock_token?: string
  @CreateDateColumn({ type: 'timestamp', comment: '创建时间' })
  createdAt: Date
  @UpdateDateColumn({ type: 'timestamp', comment: '更新时间' })
  updatedAt?: Date

  // 密码加密
  hashPassword(password: string) {
    this.password = bcrypt.hashSync(password, bcrypt.genSaltSync())
  }
  // 密码比对
  comparePassword(password: string) {
    return bcrypt.compareSync(password, this.password)
  }
}

连接数据库

新建src/services/db.serv.ts,用来创建数据库服务

ts
import { DataSource, DataSourceOptions } from 'typeorm'
const singletonEnforcer = Symbol('DbService')

class DbService {
  private _db: DataSource
  private static _instance: DbService
  constructor(enforcer: any) {
    if (enforcer !== singletonEnforcer) {
      throw new Error('Cannot initialize single instance')
    }
    this.init()
  }
  static get instance() {
    return this._instance || (this._instance = new DbService(singletonEnforcer))
  }
  private init() {
    const MYSQL_URL = process.env.MYSQL_URL
    const config: DataSourceOptions = {
      ...(MYSQL_URL
        ? { url: MYSQL_URL }
        : {
            host: process.env.MYSQL_HOST ?? 'localhost',
            port: Number(process.env.MYSQL_PORT ?? 3306),
            username: process.env.MYSQL_USER ?? 'root',
            password: process.env.MYSQL_PASSWORD ?? 'root',
            database: process.env.MYSQL_DBNAME ?? 'test',
          }),
      type: 'mysql',
      timezone: process.env.TIMEZONE ?? 'Asia/Shanghai',
      charset: process.env.CHARSET ?? 'utf8',
      synchronize: process.env.NODE_ENV === 'production' ? false : true,
      logging: false,
      entities:
        process.env.NODE_ENV === 'development'
          ? ['src/entities/**/*.entity.ts']
          : ['dist/entities/**/*.entity.js'],
    }
    this._db = new DataSource(config)
    this._db
      .initialize()
      .then(() => {
        console.log('数据库连接成功')
      })
      .catch((err) => {
        console.error('数据库连接失败', err)
        process.exit(1)
      })
  }
  get db() {
    return this._db
  }
}
export const db = DbService.instance.db
ts
import { DataSource, DataSourceOptions } from 'typeorm'
const singletonEnforcer = Symbol('DbService')

class DbService {
  private _db: DataSource
  private static _instance: DbService
  constructor(enforcer: any) {
    if (enforcer !== singletonEnforcer) {
      throw new Error('Cannot initialize single instance')
    }
    this.init()
  }
  static get instance() {
    return this._instance || (this._instance = new DbService(singletonEnforcer))
  }
  private init() {
    const MONGODB_URL = process.env.MONGODB_URL
    const config: DataSourceOptions = {
      ...(MONGODB_URL
        ? { url: MONGODB_URL }
        : {
            host: process.env.MONGODB_HOST ?? 'localhost',
            port: Number(process.env.MONGODB_PORT ?? 27017),
            username: process.env.MONGODB_USER ?? 'root',
            password: process.env.MONGODB_PWD ?? 'root',
            database: process.env.MONGODB_DBNAME ?? 'test',
          }),
      type: 'mongodb',
      synchronize: process.env.NODE_ENV === 'production' ? false : true,
      logging: false,
      entities:
        process.env.NODE_ENV === 'development'
          ? ['src/entities/**/*.entity.ts']
          : ['dist/entities/**/*.entity.js'],
    }
    this._db = new DataSource(config)
    this._db
      .initialize()
      .then(() => {
        console.log('数据库连接成功')
      })
      .catch((err) => {
        console.error('数据库连接失败', err)
        process.exit(1)
      })
  }
  get db() {
    return this._db
  }
}
export const db = DbService.instance.db

CURD

编辑src/dto/auth.ts,补充注册接口的验证规则

ts
import { Length, IsNotEmpty, IsString, IsEmail } from 'class-validator'
// ...
export class SignUpDto {
  @Length(4, 20, { message: '用户名长度为4-20' })
  @IsString({ message: '用户名必须为字符串' })
  @IsNotEmpty({ message: '用户名不能为空' })
  username: string

  @IsString({ message: '密码必须为字符串' })
  @IsNotEmpty({ message: '密码不能为空' })
  password: string

  @IsString({ message: '邮箱必须为字符串' })
  @IsNotEmpty({ message: '邮箱不能为空' })
  @IsEmail({}, { message: '邮箱格式不正确' })
  email: string
}

编辑src/controllers/auth.ctrl.ts,把之前的模拟数据删掉,改成操作数据库和Redis

ts
import { request, summary, body, middlewares, tagsAll } from 'koa-swagger-decorator'
import jwt from 'jsonwebtoken'
import { genToken, Redis, Success, Failed, HttpException } from '../utils'
import { ValidateContext, validator } from '../middlewares'
import { SignUpDto, SignInDto, TokenDto } from '../dto'
import { User } from '../entities/user.entity'
import { db } from '../services/db.serv'

@tagsAll(['Auth'])
export default class AuthController {
  @request('post', '/signup')
  @summary('注册接口')
  @middlewares([validator(SignUpDto)])
  @body({
    username: { type: 'string', required: true, example: 'admin' },
    password: { type: 'string', required: true, example: '123456' },
    email: { type: 'string', required: true, example: 'admin@example.com' },
  })
  async signUp(ctx: ValidateContext) {
    const userRepository = db.getRepository(User)
    // 1.检查邮箱是否已存在
    if (await userRepository.findOne({ where: { email: ctx.dto.email } })) {
      throw new Failed({ msg: '该邮箱已被注册' })
    } else {
      const user = userRepository.create()
      Object.assign(user, ctx.dto)
      user.hashPassword(ctx.dto.password)
      await userRepository.save(user)
      const { password, lock_token, ...rest } = user
      const accessToken = genToken(rest)
      const refreshToken = genToken(rest, 'REFRESH', '1d')
      // 2.将token保存到redis中
      await Redis.set(`${rest.id}:token`, JSON.stringify([refreshToken]), 24 * 60 * 60)
      throw new Success({
        status: 201,
        msg: '注册成功',
        data: { user: rest, accessToken, refreshToken },
      })
    }
  }

  @request('post', '/signin')
  @summary('登录接口')
  @middlewares([validator(SignInDto)])
  @body({
    username: { type: 'string', required: true, example: 'admin' },
    password: { type: 'string', required: true, example: '123456' },
  })
  async signIn(ctx: ValidateContext) {
    const userRepository = db.getRepository(User)
    const user = await userRepository.findOneBy({ username: ctx.dto.username })
    // 1.检查用户是否存在
    if (!user) {
      throw new HttpException('not_found', { msg: '用户不存在' })
    }
    // 2.校验用户密码
    if (!user.comparePassword(ctx.dto.password)) {
      throw new HttpException('auth_denied', { msg: '密码错误' })
    }
    // 3.生成token
    const { password, lock_token, ...rest } = user
    const accessToken = genToken(rest)
    const refreshToken = genToken(rest, 'REFRESH', '1d')
    // 4.拿到redis中的token
    const refreshTokens = JSON.parse(await Redis.get(`${rest.id}:token`)) ?? []
    // 5.将刷新token保存到redis中
    refreshTokens.push(refreshToken)
    await Redis.set(`${rest.id}:token`, JSON.stringify(refreshTokens), 24 * 60 * 60)
    throw new Success({ msg: '登录成功', data: { accessToken, refreshToken } })
  }

  @request('put', '/token')
  @summary('刷新token')
  @middlewares([validator(TokenDto)])
  @body({
    token: { type: 'string', required: true, example: 'asdasd' },
  })
  async token(ctx: ValidateContext) {
    // 1.先检查前端是否有提交token
    if (!ctx.dto.token) {
      throw new HttpException('unauthorized')
    }
    // 2.解析token中的用户信息
    let user: any
    jwt.verify(ctx.dto.token, process.env.REFRESH_TOKEN_SECRET ?? 'secret', (err, decode) => {
      if (err) {
        throw new HttpException('forbidden', { msg: '无效令牌,请重新登录' })
      }
      user = decode
    })
    // 3.拿到缓存中的token
    let refreshTokens: string[] = JSON.parse(await Redis.get(`${user.id}:token`)) ?? []
    // 4.再检查此用户在redis中是否有此token
    if (!refreshTokens.includes(ctx.dto.token)) {
      throw new HttpException('forbidden', { msg: '无效令牌,请重新登录' })
    }
    // 5.生成新的token
    const { iat, exp, ...rest } = user
    const accessToken = genToken(rest)
    const refreshToken = genToken(rest, 'REFRESH', '1d')
    // 6.将新token保存到redis中
    refreshTokens = refreshTokens.filter((token) => token !== ctx.dto.token).concat([refreshToken])
    await Redis.set(`${rest.id}:token`, JSON.stringify(refreshTokens), 24 * 60 * 60)
    throw new Success({ msg: '刷新token成功', data: { accessToken, refreshToken } })
  }

  @request('delete', '/logout')
  @summary('退出')
  @middlewares([validator(TokenDto)])
  @body({
    token: { type: 'string', required: true, example: 'asdasd' },
  })
  async logout(ctx: ValidateContext) {
    // 1.先检查前端是否有提交token
    if (!ctx.dto.token) {
      throw new HttpException('unauthorized')
    }
    // 2.解析token中的用户信息
    let user: any
    jwt.verify(ctx.dto.token, process.env.REFRESH_TOKEN_SECRET ?? 'secret', (err, decode) => {
      if (err) {
        throw new HttpException('forbidden', { msg: '无效令牌,请重新登录' })
      }
      user = decode
    })
    // 3.拿到缓存中的token
    let refreshTokens: string[] = JSON.parse(await Redis.get(`${user.id}:token`)) ?? []
    // 4.再检查此用户在redis中是否有此token
    if (!refreshTokens.includes(ctx.dto.token)) {
      throw new HttpException('forbidden', { msg: '无效令牌,请重新登录' })
    }
    // 5.移除redis中保存的此客户端token
    refreshTokens = refreshTokens.filter((token) => token !== ctx.dto.token)
    await Redis.set(`${user.id}:token`, JSON.stringify(refreshTokens), 24 * 60 * 60)
    throw new Success({ status: 204, msg: '退出成功' })
  }
}
export const authController = new AuthController()
ts
import { request, summary, body, middlewares, tagsAll } from 'koa-swagger-decorator'
import jwt from 'jsonwebtoken'
import { genToken, Redis, Success, Failed, HttpException } from '../utils'
import { ValidateContext, validator } from '../middlewares'
import { SignUpDto, SignInDto, TokenDto } from '../dto'
import { User } from '../entities/user.entity'
import { db } from '../services/db.serv'

@tagsAll(['Auth'])
export default class AuthController {
  @request('post', '/signup')
  @summary('注册接口')
  @middlewares([validator(SignUpDto)])
  @body({
    username: { type: 'string', required: true, example: 'admin' },
    password: { type: 'string', required: true, example: '123456' },
    email: { type: 'string', required: true, example: 'admin@example.com' },
  })
  async signUp(ctx: ValidateContext) {
    const userRepository = db.getRepository(User)
    // 1.检查邮箱是否已存在
    if (await userRepository.findOne({ where: { email: ctx.dto.email } })) {
      throw new Failed({ msg: '该邮箱已被注册' })
    } else {
      const user = userRepository.create()
      Object.assign(user, ctx.dto)
      user.hashPassword(ctx.dto.password)
      await userRepository.save(user)
      const { password, lock_token, ...rest } = user
      const accessToken = genToken(rest)
      const refreshToken = genToken(rest, 'REFRESH', '1d')
      // 2.将token保存到redis中
      await Redis.set(`${rest._id}:token`, JSON.stringify([refreshToken]), 24 * 60 * 60)
      throw new Success({
        status: 201,
        msg: '注册成功',
        data: { user: rest, accessToken, refreshToken },
      })
    }
  }

  @request('post', '/signin')
  @summary('登录接口')
  @middlewares([validator(SignInDto)])
  @body({
    username: { type: 'string', required: true, example: 'admin' },
    password: { type: 'string', required: true, example: '123456' },
  })
  async signIn(ctx: ValidateContext) {
    const userRepository = db.getRepository(User)
    const user = await userRepository.findOneBy({ username: ctx.dto.username })
    // 1.检查用户是否存在
    if (!user) {
      throw new HttpException('not_found', { msg: '用户不存在' })
    }
    // 2.校验用户密码
    if (!user.comparePassword(ctx.dto.password)) {
      throw new HttpException('auth_denied', { msg: '密码错误' })
    }
    // 3.生成token
    const { password, lock_token, ...rest } = user
    const accessToken = genToken(rest)
    const refreshToken = genToken(rest, 'REFRESH', '1d')
    // 4.拿到redis中的token
    const refreshTokens = JSON.parse(await Redis.get(`${rest._id}:token`)) ?? []
    // 5.将刷新token保存到redis中
    refreshTokens.push(refreshToken)
    await Redis.set(`${rest._id}:token`, JSON.stringify(refreshTokens), 24 * 60 * 60)
    throw new Success({ msg: '登录成功', data: { accessToken, refreshToken } })
  }

  @request('put', '/token')
  @summary('刷新token')
  @middlewares([validator(TokenDto)])
  @body({
    token: { type: 'string', required: true, example: 'asdasd' },
  })
  async token(ctx: ValidateContext) {
    // 1.先检查前端是否有提交token
    if (!ctx.dto.token) {
      throw new HttpException('unauthorized')
    }
    // 2.解析token中的用户信息
    let user: any
    jwt.verify(ctx.dto.token, process.env.REFRESH_TOKEN_SECRET ?? 'secret', (err, decode) => {
      if (err) {
        throw new HttpException('forbidden', { msg: '无效令牌,请重新登录' })
      }
      user = decode
    })
    // 3.拿到缓存中的token
    let refreshTokens: string[] = JSON.parse(await Redis.get(`${user._id}:token`)) ?? []
    // 4.再检查此用户在redis中是否有此token
    if (!refreshTokens.includes(ctx.dto.token)) {
      throw new HttpException('forbidden', { msg: '无效令牌,请重新登录' })
    }
    // 5.生成新的token
    const { iat, exp, ...rest } = user
    const accessToken = genToken(rest)
    const refreshToken = genToken(rest, 'REFRESH', '1d')
    // 6.将新token保存到redis中
    refreshTokens = refreshTokens.filter((token) => token !== ctx.dto.token).concat([refreshToken])
    await Redis.set(`${rest._id}:token`, JSON.stringify(refreshTokens), 24 * 60 * 60)
    throw new Success({ msg: '刷新token成功', data: { accessToken, refreshToken } })
  }

  @request('delete', '/logout')
  @summary('退出')
  @middlewares([validator(TokenDto)])
  @body({
    token: { type: 'string', required: true, example: 'asdasd' },
  })
  async logout(ctx: ValidateContext) {
    // 1.先检查前端是否有提交token
    if (!ctx.dto.token) {
      throw new HttpException('unauthorized')
    }
    // 2.解析token中的用户信息
    let user: any
    jwt.verify(ctx.dto.token, process.env.REFRESH_TOKEN_SECRET ?? 'secret', (err, decode) => {
      if (err) {
        throw new HttpException('forbidden', { msg: '无效令牌,请重新登录' })
      }
      user = decode
    })
    // 3.拿到缓存中的token
    let refreshTokens: string[] = JSON.parse(await Redis.get(`${user._id}:token`)) ?? []
    // 4.再检查此用户在redis中是否有此token
    if (!refreshTokens.includes(ctx.dto.token)) {
      throw new HttpException('forbidden', { msg: '无效令牌,请重新登录' })
    }
    // 5.移除redis中保存的此客户端token
    refreshTokens = refreshTokens.filter((token) => token !== ctx.dto.token)
    await Redis.set(`${user._id}:token`, JSON.stringify(refreshTokens), 24 * 60 * 60)
    throw new Success({ status: 204, msg: '退出成功' })
  }
}
export const authController = new AuthController()

MIT License.