Sequelize V5.9.4 MVC模式（二 | 单表增删改查）

大家可以前往 Sequelize中文文档，查看 Sequelize不同版本【5.x、4.x】的文档

本文档分多个篇章，难易程度从低到高，学习此篇章之前，务必确保自己已经掌握 node.js、express、es6语法、mysql等关系型数据库的sql语法等

单表增删改查

需求：新建users表，并设置username、gender、age、headImg字段

对应数据库显示结构

Model

import Sequelize from 'sequelize'
import sequelize from './../connection'

const User = sequelize.define('user', {
    username: Sequelize.STRING,
    gender: {
        type: Sequelize.INTEGER,
        defaultValue: 1,//默认值
    },
    age: {
        type: Sequelize.INTEGER,
        defaultValue: 20
    },
    headImg: {
        type: Sequelize.STRING,
        defaultValue: 'img.png'
    }
});
export default {User}

Controller

在写操作代码在之前，因为 Sequelize 是一个基于 promise 的 Node.js ORM，所以咱们可以用es6的 async/await 实现异步转同步，在使用 async/await 之前需要进行如下配置

$ npm install babel-plugin-transform-runtime --save-dev 

在 .babelrc 文件中添加

"plugins": [[
    "transform-runtime",
    {
      "helpers": false,
      "polyfill": false,
      "regenerator": true,
      "moduleName": "babel-runtime"
    }
  ]]

增加数据

User.Controller.js

import User from './../Model/User.Model'
export default {
    insertUser: async (req, res, next) => {
        let {username} = req.body;
        //方法一
        let user = await User.User.build({
            username
        });
        user = await user.save();
        //方法二
        let user = await User.User.create({
            username
        });
        //插入成功后返回
        res.send({
            code: 200,
            data: user
        })
    }
}

post.js【配置的post请求的路由文件】

import express from 'express'
const router = express.Router();
import User from '../Mysql/Controller/User.Controller'

router.post('/insert', User.insertUser);//调用User中Controller的方法

module.exports = router;

postman测试返回数据

//新增数据成功
{
    "code": 200,
    "data": {
        "gender": 1,//设置的默认值
        "age": 20,//设置的默认值
        "headImg": "img.png",//设置的默认值
        "id": 2,
        "username": "Mjhuu",
        "updatedAt": "2019-07-09T06:19:31.760Z",
        "createdAt": "2019-07-09T06:19:31.760Z"
    }
}

修改数据

User.Controller.js

import User from './../Model/User.Model'
export default {
    updateUser: async (req, res, next)=>{
        let {id, age} = req.body;
        let user = await User.User.findOne({
            where: {id}
        });
       //方法一
        user.age = age; //此过程不会直接修改数据库
        user = await user.save();//调用save保存到数据库
        //方法二直接调用update修改
        user = await user.update({age, username: '我不会被修改'},{'fields': ['age']});//配置修改白名单，只会修改age字段
        res.send({
            code: 200,
            data: '修改成功',
            updateUser: user
        })
    }
}

post.js

router.post('/update', User.updateUser);

postman

{
    "code": 200,
    "data": "修改成功",
    "updateUser": {
        "id": 1,
        "username": "Mjhu",
        "gender": 1,
        "age": "19",
        "headImg": "img.png",
        "createdAt": "2019-07-09T05:13:38.000Z",
        "updatedAt": "2019-07-09T11:13:48.025Z"
    }
}

删除数据

User.Controller.js

import User from './../Model/User.Model'
export default {
    deleteUser: async (req, res, next)=>{
        let {id} = req.body;
        let user = await User.User.findOne({
            where: {id}
        });
        user = await user.destroy();//先查后删
        res.send({
            code: 200,
            data: '注销成功',
            deleteUser: user
        })
    }
}

post.js

router.post('/delete', User.deleteUser);

postman

{
    "code": 200,
    "data": "注销成功",
    "deleteUser": {
        "id": 2,
        "username": "Mjhuu",
        "gender": 1,
        "age": 20,
        "headImg": "img.png",
        "createdAt": "2019-07-09T06:19:31.000Z",
        "updatedAt": "2019-07-09T06:19:31.000Z"
    }
}

查询数据

查询所有数据 findAll

User.Controller.js

import User from './../Model/User.Model'
export default {
    getUserInfo: async (req, res, next)=>{
        let users = await User.User.findAll({
            'attributes': ['username', 'age', ['gender', 'sex']] //限制显示的字段 将gender字段修改为sex显示
        });
        res.send({
            code: 200,
            users
        })
    }
}

get.js

router.get('/getUserInfo', User.getUserInfo);

postman

{
    "code": 200,
    "users": [
        {
            "username": "Mjhu",
            "age": 16,
            "sex": 1
        },
        {
            "username": "张三",
            "age": 20,
            "sex": 1
        }
    ]
}

好了，到此为止单表的增删查改就差不多了，因为条件查询比较多，所以将在下一篇章 Sequelize V5.9.4 条件查询 介绍