数据库操作 - orm 模型

安装 包依赖

npm i egg-sequelize --save
npm i mysql2 --save

配置

• 插件里面配置

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

• config.default.js 配置

config.sequelize = {
  dialect: "mysql",
  host: "localhost",
  port: 3306,
  database: "testdemo",
  // 用户名
  user: "root",
  // 密码
  password: "123456",
};

创建模型

• 在 app/model 目录下创建 studentscore.js(表名.js)

// 这个模型的文件名是小写的
// 所以要大写首字母，是egg的约定，不然会访问不到
// 一些其他的命名规则
// user.js			app.model.User
// person.js		app.model.Person
// user_group.js	app.model.UserGroup
// user/profile.js	app.model.User.Profile

module.exports = (app) => {
  const { STRING, INTEGER } = app.Sequelize;
  const Studentscore = app.model.define(
    "student_score",
    {
      number: {
        type: INTEGER,
        primaryKey: true,
      },
      subject: STRING,
      score: INTEGER,
    },
    {
      freezeTableName: true,
      timestamps: false,
    }
  );
  return Studentscore;
};

路由

router.post("/sequelize/getcontent", controller.sequelize.getcontent);
router.post("/sequelize/getById", controller.sequelize.getById);
router.post("/sequelize/insert", controller.sequelize.insert);
router.post("/sequelize/update", controller.sequelize.update);
router.post("/sequelize/del", controller.sequelize.del);

控制器

"use strict";

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

class SequelizeController extends Controller {
  async getcontent() {
    const { ctx } = this;
    const { result } = await ctx.service.sequelize.findAll();
    ctx.body = {
      status: 200,
      data: result,
    };
  }
  async getById() {
    const { ctx } = this;
    console.log(ctx.request.body.id);
    const { result } = await ctx.service.sequelize.findOne(ctx.request.body.id);
    ctx.body = {
      status: 200,
      data: result,
    };
  }
  async insert() {
    const { ctx } = this;
    const { result } = await ctx.service.sequelize.insert(ctx.request.body);
    ctx.body = {
      status: 200,
      data: result,
    };
  }
  async update() {
    const { ctx } = this;
    const { result } = await ctx.service.sequelize.update(
      ctx.request.body.number,
      {
        subject: ctx.request.body.subject,
        score: ctx.request.body.score,
      }
    );
    ctx.body = {
      status: 200,
      data: result,
    };
  }
  async del() {
    const { ctx } = this;
    const { result } = await ctx.service.sequelize.del(ctx.request.body.number);
    ctx.body = {
      status: 200,
      data: result,
    };
  }
}

module.exports = SequelizeController;

Service 层

• Sequelize.js

"use strict";

const Service = require("egg").Service;

class SequelizeService extends Service {
  async findAll() {
    console.log("----------");
    console.log(this.ctx.model);
    console.log(this.ctx.model.Studentscore);
    const result = await this.ctx.model.Studentscore.findAll({
      attributes: ["subject", "score"],
    });
    return { result };
  }
  async findOne(id) {
    const result = await this.ctx.model.Studentscore.findOne({
      where: { number: id },
      attributes: ["subject", "score"],
    });
    return { result };
  }
  async insert(data) {
    console.log("---service------");
    console.log(data);
    const result = await this.ctx.model.Studentscore.create(data);
    return { result };
  }
  async update(id, data) {
    console.log("---service------");
    console.log(data);
    const result = await this.ctx.model.Studentscore.findByPk(id);
    if (!result) {
      this.ctx.throw(404, "not found");
    }
    return result.update(data);
  }
  async del(id) {
    const result = await this.ctx.model.Studentscore.findByPk(id);
    if (!result) {
      this.ctx.throw(404, "not found");
    }
    return result.destroy();
  }
}

module.exports = SequelizeService;

具体的 API 查询 https://www.sequelize.cn/