一、安装Sequelize

因为我使用的是SQL Server数据库可以根据实际使用的数据来

//使用的是最新是v5版本
npm install --save egg-sequelize 
npm install --save tedious

二、配置

1.plugin.js

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

2.default.js

config.sequelize = {
    dialect: 'mssql',//数据库类型
    host: '127.0.0.1',//链接地址
    port: 1433,//端口
    database: '',//数据库名称
    username: 'sa',//用户名
    password: '123',//密码
    define: {
      underscored: false,
      //使用自定义表名
      freezeTableName: true,
      //去掉默认的添加时间和更新时间
      timestamps: false,
    },
    dialectOptions: {
      options: { "requestTimeout": 300000 }//超时时间
    },
    timezone: '+08:00',//时区
  }

三、定义数据表

在项目目录/app/model/userStudent.js定义数据表

'use strict'
module.exports = app => {
  const {STRING,INTEGER, DECIMAL} = app.Sequelize
  var moment = require('moment')

  const UserStudent = app.model.define('User_Student', {
    Id: {
      type: INTEGER,
      primaryKey: true,
      autoIncrement: true,
      comment: '自增id'
    },
    StudentName: {
      type: STRING,
      comment: '校区学生姓名',
      allowNull: false, //不允许为null
    },
    SchoolId: {
      type: INTEGER,
      comment: '校区id',
      allowNull: false, //不允许为null
    },
    StudentGradeid: {
      type: INTEGER,
      comment: '学生目前年级',
      allowNull: false, //不允许为null
    },
    StudentStage: {
      type: INTEGER,
      comment: '学生目前报读阶段',
    },
    StudentId: {
      type: INTEGER,
      comment: '学生id',
    },
    ParentTel: {
      type: STRING,
      comment: '家长联系电话',
    },
    ParentName: {
      type: STRING,
      comment: '家长姓名',
    },
    StudentSchoolname: {
      type: STRING,
      comment: '学生所在校区名称',
    },
    StudentGender: {
      type: STRING,
      comment: '学生性别',
    },
    StudentCardid: {
      type: STRING,
      comment: '学生身份证号',
    },
    StudentAddress: {
      type: STRING,
      comment: '学生住址',
    },
    StudentAge: {
      type: INTEGER,
      comment: '学生年龄',
    },
    UserName: {
      type: STRING,
      comment: '学生使用系统账号',
    },
    CreateTime: {
      type: STRING,//mssql获取的时间格式有问题所以用string类型
      comment: '加入系统时间',
      allowNull: false, //不允许为null
      get() {//格式化时间
        return this.getDataValue('CreateTime') ? moment(this.getDataValue('CreateTime')).format('YYYY-MM-DD') : ''
      },
    },
    StudentStatus: {
      type: INTEGER,
      comment: '状态',
    },
    Operator: {
      type: INTEGER,
      comment: '操作者',
    },
    Remark: {
      type: STRING,
      comment: '备注',
    },
    Submittime: {
      type: STRING,
      comment: '提交时间',
      get() {
        return this.getDataValue('Submittime') ? moment(this.getDataValue('Submittime')).utc().format('YYYY-MM-DD HH:mm:ss') : ''
      },
      set(val) {//保存时格式化时间 以便存储
        this.setDataValue('Submittime', moment(val).format('YYYY-MM-DD HH:mm:ss'))
      },
    },

  }, {
      //使用自定义表名
      freezeTableName: true,
      //去掉默认的添加时间和更新时间
      timestamps: false,
    })

  UserStudent.associate = function () {
    //一对一关联表
    app.model.UserStudent.belongsTo(app.model.Person, {//关联用户表
      as: 'person',
      foreignKey: 'Operator',//与副表关联的字段
      targetKey:'Id'//默认副表主键字段可以自定义
    })
    app.model.UserStudent.belongsTo(app.model.School, {
      as: 'School',
      foreignKey: 'SchoolId'
    })
    app.model.UserStudent.belongsTo(app.model.Grade, {
      as: 'Grade',
      foreignKey: 'StudentGradeid'
    })

    app.model.UserStudent.belongsTo(app.model.Stage, {
      as: 'Stagedt',
      foreignKey: 'StudentStage'
    })
    //一对多关联
    app.model.UserStudent.hasMany(app.model.UserStudentBind, {
      as: 'StudentBinddt',
      foreignKey: 'Id', 
      targetKey: 'Id',
    })
  }
  return UserStudent
}

推荐使用自动生成model工具但是还是需要手动改下文件

npm install --save sequelize-auto mssql

1.配置package.json

//在scripts 增加一条配置
"scripts": {
    "model": "sequelize-auto -o .models -h 127.0.0.1 -d ce -u sa -x 123 -p 1433 -e mssql"
 },
 // -o 目录位置
 // -h 数据库地址
 // -d 数据库名称
 // -u 用户名
 // -x 密码
 // -p 端口号
 // -e 数据库类型

2.使用命令

npm run model

3.修改生成好的文件

User_Student.js

module.exports = function(sequelize, DataTypes) {
  return sequelize.define('User_Student', {
    Id: {
      type: DataTypes.INTEGER,
      allowNull: false,
      primaryKey: true,
      autoIncrement: true
    },
    StudentName: {
      type: DataTypes.STRING,
      allowNull: false
    },
   ....
  }, {
    tableName: 'User_Student'
  });
};

新的User_Student.js

module.exports = app => {

  const {STRING,INTEGER, DECIMAL} = app.Sequelize
  
  return app.model.define('User_Student', {
    Id: {
      type: DataTypes.INTEGER,
      allowNull: false,
      primaryKey: true,
      autoIncrement: true
    },
    StudentName: {
      type: DataTypes.STRING,
      allowNull: false
    },
   ....
  }, {
    tableName: 'Yx_User_Student'
  });
};

常用的sequelize方法

一、增加数据

1.单条数据增加

 await this.ctx.model.UserStudent.create({})

2.多条数据增加

await this.ctx.model.UserStudent.bulkCreate([{},{}])

二、查询数据

1.查询单条数据

await this.ctx.model.UserStudent.findOne({
    where:{
        //条件
    }
})

2.查询所有数据

await this.ctx.model.UserStudent.find({
    where:{
        //条件
    }
})

3.根据主键查询数据

await this.ctx.model.UserStudent.findByPk(param)

4.原生查询

await this.ctx.model.query('select * from UserStudent', {
   type: 'SELECT'
 })

5.查询过滤不需要的字段 attributes

await this.ctx.model.UserStudent.findOne({
    where:{
        //条件
    },
    attributes:['Id']//填写需要显示的字段名称
})

三、更新数据

1.先查询数据后修改

let UserStudent=await this.ctx.model.UserStudent.findByPk(param)
UserStudent.update({
//需要修改的数据
},{
    fields:['','','']//不需要更新的字段
})

2.根据条件直接更新数据

await this.ctx.model.UserStudent.update({
   //需要修改的数据
},{
    where:{
        //条件
    }
})

四、删除数据

1.先查询数据后删除

let UserStudent=await this.ctx.model.UserStudent.findByPk(param)
UserStudent.destroy()

2.根据条件直接删除数据

await this.ctx.model.UserStudent.destroy({
     where:{
        //条件
    }
})

五、排序

使用order进行排序

await this.ctx.model.UserStudent.find({
    where:{
        //条件
    },
    order:[
      ['Id','desc']
    ]
})

六、分页

使用 limit(限制显示多少条数据) 和 offset(跳过多少条数据) 进行分页

//使用findAndCountAll返回总条数
await this.ctx.model.UserStudent.findAndCountAll({
   limit:10,
   offset:0,
    where:{
        //条件
    },
    order:[
      ['Id','desc']
    ]
})

七、表关联查询

使用 include 进行表之间的关联首先在model里面进行表之间的关系在查询也可以临时关联

   const res = await ctx.model.UserStudent.findAndCountAll({
               limit:10,
               offset:0,
                order: [
                    ['Submittime', 'desc']
                ],
                include: [{
                    model: ctx.model.School,
                    as: 'School',
                    include: [{//进行多重表关联
                        model: ctx.model.AreaData,
                        as: 'ProvinceDt',
                        required: false,
                        attributes: ['Name', 'Code']
                    },
                    {
                        model: ctx.model.AreaData,
                        as: 'CityDt',
                        required: false,
                        attributes: ['Name', 'Code']
                    },
                    {
                        model: ctx.model.AreaData,
                        as: 'ZoneDt',
                        required: false,
                        attributes: ['Name', 'Code']
                    },
                    ],
                },
                {
                    model: ctx.model.Person,
                    as: 'person',
                    attributes: ['PersonName', 'Id'],
                },
                {
                    model: ctx.model.Grade,
                    as: 'Grade',
                    attributes: ['GradeName', 'Id'],
                }
                ],
            });

八、事务

使用 transaction 开启事务

 let transaction;//定义事务
 try {
       transaction = await this.ctx.model.transaction();//开启事务
       await this.ctx.model.UserStudent.destroy({
             where:{
                //条件
            }
        }, { transaction })
         await transaction.commit();//提交事务
       
 } catch (error) {
      //报错进行事务回滚
      await transaction.rollback();
}

使用的一些问题

1.当要需要在查询的时候使用自定义后半部分的查询条件时

let where={
    //查询条件
}
await this.ctx.model.UserStudent.find({
    where
})
//增加自定义查询条件
let sequelize = require('sequelize')
where.$and = sequelize.literal(`User_Student.SchoolId in (select SchoolId from  [dbo].[fn_GetUserhaveSchool](${this.userinfo.UserId},1))`)

2.V5的版本有一个数字存储更新程序会报错的问题解决方案是手写更新语句来更新数据有待官方修复