1. Install Sequelize

Because I’m using an SQL Server database and I can use the data that I’m actually using

NPM install --save egg-sequelize NPM install --save tediousCopy the code

Second, the configuration

1.plugin.js

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

2.default.js

config.sequelize = {
    dialect: 'mssql'// Database type host:'127.0.0.1'Port: 1433,// port: 1433' '// Database name username:'sa'// user name password:'123',// password define: {specify:false// Use custom table name freezeTableName:true// Remove the default add time and update time timestamps:false,
    },
    dialectOptions: {
      options: { "requestTimeout"// Timeout time}, timezone:'+ 08:00'}, / / time zoneCopy the code

Define the data table

In the project directory/app/model/userStudent js define data tables

'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: 'on the id'
    },
    StudentName: {
      type: STRING,
      comment: 'Name of Student on Campus',
      allowNull: false, // do not allow null}, SchoolId: {type: INTEGER,
      comment: 'campus id',
      allowNull: false, // cannot be null}, StudentGradeid: {type: INTEGER,
      comment: 'Student's current grade',
      allowNull: false, // cannot be null}, StudentStage: {type: INTEGER,
      comment: 'Student's current enrolment stage',
    },
    StudentId: {
      type: INTEGER,
      comment: 'student id',
    },
    ParentTel: {
      type: STRING,
      comment: 'Parent Contact Number',
    },
    ParentName: {
      type: STRING,
      comment: 'Parent's Name',
    },
    StudentSchoolname: {
      type: STRING,
      comment: 'Name of student's Campus',
    },
    StudentGender: {
      type: STRING,
      comment: 'Student sex',
    },
    StudentCardid: {
      type: STRING,
      comment: 'Student ID Number',
    },
    StudentAddress: {
      type: STRING,
      comment: 'Student Address',
    },
    StudentAge: {
      type: INTEGER,
      comment: 'Student age',
    },
    UserName: {
      type: STRING,
      comment: 'Student Using system Account',
    },
    CreateTime: {
      typeComment: STRING,// MSSQL ()'Join system Time',
      allowNull: false// Null is not allowedget() {// Format timereturn this.getDataValue('CreateTime')? moment(this.getDataValue('CreateTime')).format('YYYY-MM-DD') : ' '
      },
    },
    StudentStatus: {
      type: INTEGER,
      comment: 'state',
    },
    Operator: {
      type: INTEGER,
      comment: 'Operator',
    },
    Remark: {
      type: STRING,
      comment: 'note',
    },
    Submittime: {
      type: STRING,
      comment: 'Submit time'.get() {
        return this.getDataValue('Submittime')? moment(this.getDataValue('Submittime')).utc().format('YYYY-MM-DD HH:mm:ss') : ' '
      },
      set(val) {// Format the time to store this.setDatavalue ('Submittime', moment(val).format('YYYY-MM-DD HH:mm:ss'},},}, {// Use custom table name freezeTableName:true// Remove the default add time and update time timestamps:false,
    })

  UserStudent.associate = function() {/ / one-to-one association list app. Model. UserStudent. BelongsTo (app) model) Person, {/ / associated with the user table as:'person',
      foreignKey: 'Operator'// The field associated with the side table targetKey:'Id'/ / the default vice table primary key field can customize}) 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'}) / / one-to-many associations app. Model. UserStudent. HasMany (app) model) UserStudentBind, {as:'StudentBinddt',
      foreignKey: 'Id', 
      targetKey: 'Id'})},return UserStudent
}
Copy the code

It is recommended to use the automatic model generation tool, but you still need to manually modify the file

npm install --save sequelize-auto mssql
Copy the code

1. The configuration package. Json

// Add a configuration to scripts"scripts": {
    "model": "Sequelize -auto -o. Models -h 127.0.0.1 -d ce -u sa -x 123 -p 1433 -e MSSQL"}, // -o directory location // -h database address //-dDatabase name // -u user name // -x password // -p port number //-eDatabase typeCopy the code

2. Run commands

npm run model
Copy the code

3. Modify the generated file

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'
  });
};

Copy the code

New 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'
  });
};
Copy the code

Common sequelize methods

First, add data

1. A single data item is added

 await this.ctx.model.UserStudent.create({})
Copy the code

2. Multiple pieces of data are added

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

Second, query data

1. Query a single piece of data

await this.ctx.model.UserStudent.findOne({
    where:{// condition}})Copy the code

2. Query all data

await this.ctx.model.UserStudent.find({
    where:{// condition}})Copy the code

3. Query data by primary key

await this.ctx.model.UserStudent.findByPk(param)
Copy the code

4. Native query

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

5. Query the attributes of the fields that are not needed for filtering

await this.ctx.model.UserStudent.findOne({
    where:{// condition}, attributes:['Id']// Fill in the field name to display})Copy the code

3. Update data

1. Query data and modify it

letUserStudent = await this. CTX. Model. UserStudent. FindByPk (param) UserStudent. Update ({/ / need to modify the data}, {fields:' '.' '.' ']// Do not need to update the field})Copy the code

2. Update data based on conditions

Await this. CTX. Model. UserStudent. Update ({/ / need to modify the data}, {where:{// condition}})Copy the code

4. Delete data

1. Query data and delete it

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

2. Delete data based on the conditions

await this.ctx.model.UserStudent.destroy({
     where:{// condition}})Copy the code

Five, sorting,

Use order for sorting

await this.ctx.model.UserStudent.find({
    where:{// condition}, order:[['Id'.'desc']]})Copy the code

Six, paging

Use limit(limiting how many pieces of data to display) and offset(how many pieces of data to skip) for paging

/ / use findAndCountAll returns to the total number of article await this. CTX. Model. UserStudent. FindAndCountAll ({limit:10,
   offset:0,
    where:{// condition}, order:[['Id'.'desc']]})Copy the code

Table associative query

Use include to associate tables with each other first in model and then in query

   const res = await ctx.model.UserStudent.findAndCountAll({
               limit:10,
               offset:0,
                order: [
                    ['Submittime'.'desc']
                ],
                include: [{
                    model: ctx.model.School,
                    as: 'School', include: [{// perform multiple table associations 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']],}});Copy the code

Eight, the transaction

Start a transaction using transaction

 lettransaction; / / define transaction try {transaction = await this. CTX. Model. The transaction (); / / open transaction await this. CTX. Model. UserStudent. Destroy ({where}}, {transaction}) await transaction.com MIT (); Rollback ();} catch (error) {// error with await transaction. }Copy the code

Some problems with use

1. Customize the latter half of the query criteria

let where= {} / / query conditions await this. CTX. Model. UserStudent. Find ({where}) // Add custom query criterialet sequelize = require('sequelize')
where.$and = sequelize.literal(`User_Student.SchoolId in (select SchoolId from  [dbo].[fn_GetUserhaveSchool](${this.userinfo.UserId}`), 1))Copy the code

2. Version V5 has a problem that the digital storage update program will report errors. The solution is to update data by handwritten update statement, which needs to be fixed officially