egg.js sequelize数据库操作配置

egg.js sequelize数据库操作配置

1. 数据库配置

• 安装并配置egg-sequelize插件（它会辅助我们将定义好的 Model 对象加载到 app 和 ctx 上）和mysql2模块：

npm install --save egg-sequelize mysql2

• 在config/plugin.js中引入 egg-sequelize插件

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

• 在config/config.default.js

config.sequelize = {dialect: "mysql",host: "127.0.0.1",username: "root",password: "root",port: 3306,database: "egg-wechat",// 中国时区timezone: "+08:00",define: {// 取消数据表名复数freezeTableName: true,// 自动写入时间戳 created_at updated_attimestamps: true,// 字段生成软删除时间戳 deleted_at// paranoid: true,createdAt: "created_at",updatedAt: "updated_at",// deletedAt: 'deleted_at',// 所有驼峰命名格式化underscored: true,},
};

2. 迁移配置

sequelize 提供了sequelize-cli工具来实现Migrations，我们也可以在 egg 项目中引入 sequelize-cli。

npm install --save-dev sequelize-cli

egg 项目中，我们希望将所有数据库 Migrations 相关的内容都放在database目录下，所以我们在项目根目录下新建一个.sequelizerc配置文件：

"use strict";const path = require("path");module.exports = {config: path.join(__dirname, "database/config.json"),"migrations-path": path.join(__dirname, "database/migrations"),"seeders-path": path.join(__dirname, "database/seeders"),"models-path": path.join(__dirname, "app/model"),
};

初始化 Migrations 配置文件和目录

npx sequelize init:config
npx sequelize init:migrations
# npx sequelize init:models

运行完后会生成database/config.json文件和database/migrations目录，我们修改一下database/config.json中的内容，将其改成我们项目中使用的数据库配置：

{"development": {"username": "root","password": null,"database": "eggapi","host": "127.0.0.1","dialect": "mysql","timezone": "+08:00"}
}

创建数据库

npx sequelize db:create
# 升级数据库
npx sequelize db:migrate
# 如果有问题需要回滚，可以通过 `db:migrate:undo` 回退一个变更
# npx sequelize db:migrate:undo
# 可以通过 `db:migrate:undo:all` 回退到初始状态
# npx sequelize db:migrate:undo:all

3.数据表设计和迁移

创建数据迁移表

npx sequelize migration:generate --name=user

1.执行完命令后，会在database / migrations / 目录下生成数据表迁移文件，然后定义

"use strict";module.exports = {up: async (queryInterface, Sequelize) => {const { INTEGER, STRING, DATE, ENUM } = Sequelize;// 创建表await queryInterface.createTable("user", {id: {type: INTEGER(20).UNSIGNED,primaryKey: true,autoIncrement: true,},username: {type: STRING(30),allowNull: false,defaultValue: "",comment: "用户名称",unique: true,},nickname: {type: STRING(30),allowNull: false,defaultValue: "",comment: "...",},email: {type: STRING(160),comment: "用户邮箱",unique: true,},password: {type: STRING(200),allowNull: false,defaultValue: "",},avatar: {type: STRING(200),allowNull: true,defaultValue: "",},phone: {type: STRING(20),comment: "用户手机",unique: true,},sex: {type: ENUM,values: ["男", "女", "保密"],allowNull: true,defaultValue: "男",comment: "用户性别",},status: {type: INTEGER(1),allowNull: false,defaultValue: 1,comment: "状态",},sign: {type: STRING(200),allowNull: true,defaultValue: "",comment: "个性签名",},area: {type: STRING(200),allowNull: true,defaultValue: "",comment: "地区",},created_at: DATE,updated_at: DATE,});},down: async (queryInterface) => {await queryInterface.dropTable("user");},
};

执行 migrate 进行数据库变更

npx sequelize db:migrate

4.模型创建

// app/model/user.js
"use strict";
module.exports = (app) => {const { STRING, INTEGER, DATE, ENUM, TEXT } = app.Sequelize;// 配置（重要：一定要配置详细，一定要！！！）const User = app.model.define("user", {id: {type: INTEGER(20).UNSIGNED,primaryKey: true,autoIncrement: true,},username: {type: STRING(30),allowNull: false,defaultValue: "",comment: "用户名称",unique: true,},nickname: {type: STRING(30),allowNull: false,defaultValue: "",comment: "...",},email: {type: STRING(160),comment: "用户邮箱",unique: true,},password: {type: STRING(200),allowNull: false,defaultValue: "",},avatar: {type: STRING(200),allowNull: true,defaultValue: "",},phone: {type: STRING(20),comment: "用户手机",unique: true,},sex: {type: ENUM,values: ["男", "女", "保密"],allowNull: true,defaultValue: "男",comment: "用户性别",},status: {type: INTEGER(1),allowNull: false,defaultValue: 1,comment: "状态",},sign: {type: STRING(200),allowNull: true,defaultValue: "",comment: "个性签名",},area: {type: STRING(200),allowNull: true,defaultValue: "",comment: "地区",},created_at: DATE,updated_at: DATE,});return User;
};