node+egg+sequelize+mysql增删改查CRUD

写在前面叫前言

https://github.com/ning8341/test-dongwn

一、功能：

•  使用sequelize这款ORM针对mysql数据库，实现简单的CRUD,第二版集成了一个聊天的小机器人，感兴趣的可以去看看

二、准备工作：

•  node环境，自行配置
• 参考实体类/app/model/user.js自行创建user表
• 请创建好文件夹 test-dongwn（你的工程名），接下来的命令请在test-dongwn路径下执行

 

三、安装依赖：

• egg-init test-dongwn --type=simple

• npm i

• npm install --save egg-sequelize mysql2

四、目录结构：

                                    

五、说明：

• 核心代码放在/app目录下，/app/model文件夹存放的是实体类，个人理解是实体类，也就是与数据库的映射；
• /app目录下的router.js是总的路由，/app/router文件夹是特意写的一层路由，也可以直接在 /app/router.js中写路由；
• /app/controller是控制层，如果实际生产请自行封装service层，此处CRUD，所以没有写service；
• /config是配置文件，配置了sequelize，/config/plugin.js配置了sequelize生效，具体请参考下面代码；
• package.json此处看成pom.xml就可以了;

六、代码示例：

• test-dongwn\package.json

{
  "name": "test-dongwn",
  "version": "1.0.0",
  "description": "",
  "private": true,
  "egg": {
    "declarations": true
  },
  "dependencies": {
    "egg": "^2.15.1",
    "egg-scripts": "^2.11.0",
    "egg-sequelize": "^6.0.0",
    "mysql2": "^2.2.5"
  },
  "devDependencies": {
    "autod": "^3.0.1",
    "autod-egg": "^1.1.0",
    "egg-bin": "^4.11.0",
    "egg-ci": "^1.11.0",
    "egg-mock": "^3.21.0",
    "eslint": "^5.13.0",
    "eslint-config-egg": "^7.1.0"
  },
  "engines": {
    "node": ">=10.0.0"
  },
  "scripts": {
    "start": "egg-scripts start --daemon --title=egg-server-test-dongwn",
    "stop": "egg-scripts stop --title=egg-server-test-dongwn",
    "dev": "egg-bin dev",
    "debug": "egg-bin debug",
    "test": "npm run lint -- --fix && npm run test-local",
    "test-local": "egg-bin test",
    "cov": "egg-bin cov",
    "lint": "eslint .",
    "ci": "npm run lint && npm run cov",
    "autod": "autod"
  },
  "ci": {
    "version": "10"
  },
  "repository": {
    "type": "git",
    "url": ""
  },
  "author": "",
  "license": "MIT"
}

 

• test-dongwn\config\config.default.js

/* eslint valid-jsdoc: "off" */

'use strict';

/**
 * @param {Egg.EggAppInfo} appInfo app info
 */
module.exports = appInfo => {
  /**
   * built-in config
   * @type {Egg.EggAppConfig}
   **/
  const config = exports = {};

  // use for cookie sign key, should change to your own and keep security
  config.keys = appInfo.name + '_1605166329902_2739';

  // add your middleware config here
  config.middleware = [];

  // add your user config here
  const userConfig = {
    // myAppName: 'egg',
  };

  //放开post请求
  config.security = {
    csrf: {
      enable: false,
    }
  }

  config.sequelize = {
    dialect: 'mysql', // l类型
    host: '127.0.0.1', // 地址
    username: 'root', // 账号
    password: '123456', // 密码
    port: 3306, // 端口号
    database: 'blog', // 数据库名称
    dialectOptions: {
      // requestTimeout: 60000,
      // useUTC: false, //for reading from database
      dateStrings: true,
      typeCast: function (field, next) { // for reading from database
        if (field.type === 'DATETIME') {
          return field.string()
        }
        return next()
      },
    },
    timezone: '+08:00'
  };


  return {
    ...config,
    ...userConfig,
  };
};

• test-dongwn\config\plugin.js

'use strict';

/** @type Egg.EggPlugin */
module.exports = {
  sequelize: {
    enable: true,
    package: 'egg-sequelize',
  },
};

• test-dongwn\app\model\user.js

module.exports = app => {
  /* model definition */
  const { STRING, INTEGER, DATE, FLOAT, DOUBLE, NOW, UUID, UUIDV1, Op } = app.Sequelize

  const User = app.model.define('User', {
    id: {
      type: UUID,
      primaryKey: true,
      defaultValue: UUIDV1()
    },
    name: {
      field: "name",
      type: STRING(64),
      allowNull: false,
      defaultValue: '',
      validate: {
        notEmpty: { msg: 'name 不能为空' }
      }
    },
    createdAt: {
      field: 'createdAt',
      type: DATE,
      defaultValue: NOW
    },
    updatedAt: {
      field: 'updatedAt',
      type: DATE,
      defaultValue: NOW
    }
  }, {
    tableName: 'user',
    timestamps: false
  })

  /* class methods */
  User.updateById = async function (id, data) {
    return await this.update(data, { where: { id } })
  }

  User.findByUserId = async function (userId) {
    return this.findOne({
      where: {
        userId
      }
    })
  }

  User.findByUserIds = async function (userIds) {
    return this.findAll({
      where: {
        userId: userIds,
      }
    })
  }

  return User
}

• test-dongwn\app\controller\user.js

const Controller = require('egg').Controller;

class UserController extends Controller {

  //列表
  async index() {
    const users = await this.ctx.model.User.findAll()
    this.ctx.body = {
      object: users,
      meta: '列表'
    }
  }

  //根据id查询
  async show() {
    const { id } = this.ctx.params
    const userObject = await this.ctx.model.User.findByPk(id)
    this.ctx.body = {
      object: userObject,
      meta: '根据id查询'
    }
  }
  //创建
  async create() {
    const data = this.ctx.request.body
    const result = await this.ctx.model.User.create(this.ctx.request.body)
    this.ctx.body = {
      object: result,
      meta: '创建'
    }

  }
  //修改
  async update() {
    const data = this.ctx.request.body
    const { id } = this.ctx.params
    const result = await this.ctx.model.User.update(data, { where: { id } })
    this.ctx.body = {
      object: result,
      meta: '修改'
    }

  }
  //删除
  async destroy() {
    const { id } = this.ctx.params
    const ids = id.split(',');
    const result = await this.ctx.model.User.destroy({ where: { id: ids } })

    this.ctx.body = {
      object: result,
      meta: '删除'
    }
  }
}
module.exports = UserController;

 

•  test-dongwn\app\router.js

'use strict';

/**
 * @param {Egg.Application} app - egg application
 */
module.exports = app => {
  const { router, controller } = app;
  //router.get('/list', controller.user.index);
  
  require('./router/dongwn')(app);
};

 

•  test-dongwn\app\router\dongwn.js

module.exports = ({ router, controller }) => {
  
 // router.get('/user/:id', controller.user.getById);

  /* RESTful CRUD routes */
  router.resources('user', '/api/user', controller.user)

}