egg-sequelize

安装 egg-sequelize

yarn add egg-sequelize mysql2

初始化项目后，配置 egg-sequelize
sequelize技巧 https://zhuanlan.zhihu.com/p/91195711

config.sequelize配置

egg-sequelize配置文档 https://github.com/eggjs/examples/tree/master/sequelize

config/plugin.js

exports.sequelize = {
    enable: true,
  package: 'egg-sequelize',
}

config/config.default.js

config.sequelize = {
  dialect: 'mysql', // 数据库类型
  host: 'localhost',
  port: 3306, // port一定要写成字符串，可能报错
  database: 'qixiu',
  username: 'root', // user
  password: 'root',
  // 配置数据库时间为北京时间，V6版本配置
  timezone: '+08:00',
  dialectOptions: {
    useUTC: false // for reading from database
  },
  define: {
    timestamps: false, // 自动添加时间戳
    freezeTableName: true, // 使用原始表名称，不需要额外的处理
  },
};

• dialect：数据库类型
• deifne
  • timestamps: false 不要自动添加时间戳
  • freezeTableName: true：使用原始的表名

timezone时间配置

返回时间配置，egg-sequelize在读取时间时，会返回UTC格式，配置如下
https://github.com/sequelize/sequelize/issues/854
https://stackoverflow.com/questions/47367893/sequelize-reads-datetime-in-utc-only

v5版本配置

exports.sequelize = {
  dialect: 'mysql',
  timezone: '+08:00' ,
  dialectOptions: {
    useUTC: false, //for reading from database
    dateStrings: true,
    typeCast(field, next) {
      // for reading from database
      return (field.type === "DATETIME") ? field.string() : next();
    }
  }
}

Model 模型

src/app/model
sequelize中，将每个表都当做一个模型
ctx.model 调用模型

user.js

'use strict';
module.exports = app => {
  const { STRING, INTEGER } = app.Sequelize;
  // 创建表
  const User = app.model.define('user', {
    id: { type: INTEGER, primaryKey: true, autoIncrement: true },
    name: STRING(20),
    password: STRING(1000),
  });
  return User;
};

• app就是 egg应用实例

Service

'use strict';
const { Service } = require('egg');
const dbName = 'blog';
class UserService extends Service {
    async list() {
      const { ctx } = this;
  }
}

Controller

'use strict';
const { Controller } = require('egg');
class UserController extends Controller {
  // 查询数据
    async findAll() {
      const { ctx } = this;
    const res = await ctx.model.User.findAll();
    const res = await ctx.model.User.findAll({
        where: {id : 23},
      limit: 1,
      offset: 10
    });
    return {
        code: 0,
      data: res,
    }
  }
  // 查找一条数据
  async findById() {
      const { ctx } = this;
    const res = await ctx.model.User.findByPk(ctx.query.id);
    return {
        code: 0,
      data: res,
    }
  }
  // 添加一条数据
  async create() {
      const { ctx } = this;
    const res = await ctx.model.User.create(ctx.request.body);
    return {
        code: 0,
      data: res,
    }
  }
  // 编辑数据前，先判断数据是否存在
  async update() {
      const { ctx } = this;
    const user = await ctx.model.User.findByPk(ctx.request.body.id);
    if(!user) {
      ctx.body = {
        code: 404,
        errMsg: '用户不存在',
      }
      return;
    }
    const res = await user.update(ctx.request.body);
    return {
        code: 0,
      data: res,
    }
  }
  // 删除数据前，也要判断数据是否存在
  async delete() {
      const { ctx } = this;
    const {id} = ctx.request.body;
    const user = await ctx.model.User.findByPk(id);
    if(!user) {
      ctx.body = {
        code: 404,
        errMsg: '用户不存在'
      }
      return;
    }
    // 删除数据
    const res = user.destroy(id)
    return {
        code: 204,
    }
  }
}