egg入门——引入sequelize插件创建模型，操作数据库

1.安装插件

npm install sequelize sequelize-cli mysql2 --save

2.config/plugin.js中注册插件

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

3.config.default.js中配置插件

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

/**
 * @param {Egg.EggAppInfo} appInfo app info
 */
module.exports = (appInfo) => {
  config.sequelize = {
    dialect: "mysql", // 数据库类型
    host: "localhost", // 数据库主机
    port: 3307, // 数据库端口
    database: "你的数据库名字", // 数据库名称
    username: "root", // 数据库用户名
    password: "root", // 数据库密码
  };
  return {
    ...config,
  };
};

4.在app文件夹下面新建一个model文件夹用于创建模型（一个表一个模型）

例如：在此我新建了一个（用户模型：文件名为user.js）

// app/model/user.js
module.exports = (app) => {
  const { STRING, INTEGER, BIGINT, DATE } = app.Sequelize;
  const User = app.model.define("user", {
    id: {
      type: BIGINT(20),
      primaryKey: true,
      autoIncrement: true,
      allowNull: false,
    },
    username: {
      type: STRING(20),
      allowNull: false,
      unique: true, // 添加唯一索引
    },
    password: {
      type: STRING(20),
      allowNull: false,
    },
    birth: {
      type: DATE,
      allowNull: true,
    },
    Gender: {
      type: STRING(2),
      allowNull: true,
    },
    telephone: {
      type: STRING(11),
      allowNull: true,
    },
    email: {
      type: STRING(30),
      allowNull: false,
    },
    avatar: {
      type: STRING(200), // 添加头像字段
      allowNull: true,
    },
    code: {
      type: STRING(200), // 添加验证码
      allowNull: true,
    },
    comment: {
      type: STRING(200), // 评论字段
      allowNull: true,
    },
    created_at: DATE,
    updated_at: DATE,
  });

  return User;
};

5.在controller下同样新建一个 名为user.js的控制器，进行user的增删改查操作

const { Controller } = require("egg");
const nodemailer = require("nodemailer");
const _ = require("lodash");

class UserController extends Controller {
  /* 发送验证码 */
  async sendCode() {
    const { ctx } = this;
    // 参数验证
    ctx.validate({
      email: "email",
    });
    const { email } = ctx.request.body;
    // 生成随机验证码
    const code = _.random(1000, 9999).toString();
    // 调用邮件服务发送验证码
    try {
      const transporter = nodemailer.createTransport({
        service: "Gmail",
        auth: {
          user: "2233527098@qq.com",
          pass: "lcy58729187",
        },
      });

      const mailOptions = {
        from: "2233527098@qq.com",
        to: email,
        subject: "验证码",
        text: `您的验证码是：${code}`,
      };

      await transporter.sendMail(mailOptions);

      // 将邮箱和验证码存储到数据库中（具体存储方式根据实际情况调整）
      await ctx.model.User.saveCode(email, code);

      ctx.body = "验证码发送成功";
      ctx.status = 200;
    } catch (err) {
      console.error(err);
      ctx.body = "验证码发送失败";
      ctx.status = 500;
    }
  }

  async getList() {
    const { ctx, app } = this;
    const patients = await app.model.User.findAll();
    const total = patients.length;
    ctx.body = {
      total: total,
      code: "200",
      msg: "success",
      data: patients,
    };
  }

  async addPatient() {
    const { ctx, app } = this;
    const patient = await app.model.User.create(ctx.request.body);
    ctx.body = {
      code: "200",
      msg: "success",
      data: patient,
    };
  }

  async delPatient() {
    const { ctx } = this;
    const { id } = ctx.request.body;

    try {
      const result = await ctx.model.User.destroy({
        where: { id },
      });

      if (result) {
        ctx.body = {
          code: "200",
          msg: "success",
        };
      } else {
        ctx.body = {
          code: "404",
          msg: "not found",
        };
      }
    } catch (error) {
      ctx.body = {
        code: "500",
        msg: "server error",
      };
    }
  }
  async resetPassword() {
    const { ctx } = this;
    const { id } = ctx.request.body;

    try {
      const patient = await ctx.model.User.findByPk(id);

      if (patient) {
        patient.password = "123456";
        await patient.save();

        ctx.body = {
          code: "200",
          msg: "success",
        };
      } else {
        ctx.body = {
          code: "404",
          msg: "not found",
        };
      }
    } catch (error) {
      ctx.body = {
        code: "500",
        msg: "server error",
      };
    }
  }
  async updatePatient() {
    const { ctx, app } = this;
    const { id, ...updatedFields } = ctx.request.body;

    try {
      const patient = await app.model.User.findByPk(id);
      if (!patient) {
        ctx.status = 404;
        return;
      }

      await patient.update(updatedFields);

      ctx.body = {
        code: "200",
        msg: "success",
        data: patient,
      };
    } catch (error) {
      // 处理更新过程中的错误
      console.error(error);
      ctx.status = 500;
      ctx.body = {
        code: "500",
        msg: "更新失败",
      };
    }
  }
  async searchPatient() {
    const { ctx, app } = this;
    const { username, Gender, page, limit } = ctx.query;
    const where = {};
    if (username) {
      where.username = { [app.Sequelize.Op.like]: `%${username}%` };
    }
    if (Gender) {
      where.Gender = { [app.Sequelize.Op.like]: `%${Gender}%` };
    }
    const pageNumber = parseInt(page);
    const limitNumber = parseInt(limit);
    console.log(`pageNumber: ${pageNumber}, limitNumber: ${limitNumber}`);
    const patients = await app.model.User.findAndCountAll({
      where,
      offset: (pageNumber - 1) * limitNumber,
      limit: limitNumber,
    });
    ctx.body = {
      code: "200",
      msg: "success",
      data: patients.rows,
      total: patients.count,
    };
  }

  async getPatientById() {
    const { ctx, app } = this;
    const id = ctx.params.id;
    console.log(111111111111, id);
    try {
      const patient = await app.model.User.findByPk(id, {
        attributes: { exclude: ["password"] },
      });
      if (!patient) {
        ctx.throw(404, "消息不存在");
      }
      ctx.body = {
        code: 200,
        data: patient,
      };
    } catch (err) {
      ctx.body = {
        code: 1,
        message: err.message,
      };
    }
  }
  /* 登录接口 */
  async login() {
    const { ctx, app } = this;
    const { username, password } = ctx.request.body;

    try {
      // 检查用户是否已注册
      const isRegistered = await app.model.User.findOne({
        where: {
          username,
        },
      });

      if (!isRegistered) {
        ctx.body = {
          code: "404",
          msg: "该用户尚未注册",
        };
        return;
      }

      // 检查用户名和密码是否匹配
      const patient = await app.model.User.findOne({
        where: {
          username,
          password,
        },
      });

      if (patient) {
        ctx.body = {
          code: "200",
          msg: "登录成功",
          data: patient,
        };
      } else {
        ctx.body = {
          code: "401",
          msg: "用户名或密码错误",
        };
      }
    } catch (error) {
      ctx.body = {
        code: "500",
        msg: "服务器错误",
      };
    }
  }
}

module.exports = UserController;

6.控制器中的接口在router.js中注册

/**
 * @param {Egg.Application} app - egg application
 */
module.exports = (app) => {
  const { router, controller } = app;
  // 登录接口
  router.post("/login", controller.user.login);
  /* 发送验证码 */
  router.post("/sendCode", controller.user.sendCode);
  /* 患者接口 */
  router.get("/user/getPatientById/:id", controller.user.getPatientById);
  router.get("/", controller.home.index);
  router.get("/user/list", controller.user.getList);
  router.post("/user/add", controller.user.addPatient);
  router.post("/user/delete", controller.user.delPatient);
  router.post("/user/reset", controller.user.resetPassword);
  router.post("/user/update", controller.user.updatePatient);
  router.get("/user/search", controller.user.searchPatient);
  app.beforeStart(async () => {
    // 同步数据库表结构
    await app.model.sync({ force: true });
  });
};

7.注意设置force：为true的话，每次更新model都会自动清空数据库

  app.beforeStart(async () => {
    // 同步数据库表结构
    await app.model.sync({ force: true });
  });