1. egg官方文档
2.下载依赖
npm install --save egg-sequelize mysql2
3.配置文件
找到config/plugin.ts文件，加入以下代码
import { EggPlugin } from 'egg';
const plugin: EggPlugin = {
// static: true,
// nunjucks: {
// enable: true,
// package: 'egg-view-nunjucks',
// },
// jwt token鉴权
jwt: {
enable: true,
package: "egg-jwt"
},
cors: {
enable: true,
package: 'egg-cors',
},
sequelize: {
enable: true,
package: 'egg-sequelize'
}
};
export default plugin;
config/config.default.ts，若想设置不同数据源，同级文件夹新建config.${env}.js配置即可
config.sequelize = {
dialect: 'mysql', // support: mysql, mariadb, postgres, mssql
database: 'hit-plum', // 数据库名
host: '127.0.0.1',
port: 3306,
username: 'root', // mysql 用户
password: '123456',// 密码
define: {
//查询是对字段驼峰类型和下划线类型进行转换
underscored: true,
//使用自己配置的表名，避免sequelize自动将表名转换为复数
freezeTableName: true,
//对于create_at,和update_at，禁止查出createAt和updateAt字段，不设置为false，会同时查出来create_at和createAt
timestamps: false
}
};
4.编写model
在app下新建model文件夹，然后新建对应的modeljs，例如我这里是取菜单表的一个model
app/model/menu.ts
'use strict';
module.exports = (app) => {
const { STRING, INTEGER, DATE } = app.Sequelize;
// system_menu是表名，对象里面是表中的字段
const Menu = app.model.define('system_menu', {
id: { type: INTEGER, primaryKey: true, autoIncrement: true },
name: STRING(30),
parent_id: INTEGER,
sort: INTEGER,
path: STRING(500),
type: INTEGER,
enabled:INTEGER,
perms: STRING(100),
cache:INTEGER,
out_link: INTEGER,
icon: STRING(4000),
icon_type: INTEGER,
remark: STRING(500),
create_by: STRING(100),
create_time: DATE,
update_by: STRING(100),
update_time: DATE
});
return Menu;
};
5.使用查询
我这里直接使用service/test.ts调用；
app/service/test.ts
public async test(){
const ctx = this.ctx;
return await ctx.model.Menu.findAll();
}
app/controller/test/ts
public async test(){
const {ctx} = this;
ctx.body={
code: 200,
data: await ctx.service.test()
}
}
6.一对多查询
好。到目前为止，单表查询基本上是木有什么问题了，那么接下来就是一对多的查询。所以我们去看sequelize的官方文档。
我们找到 Associations 这一部分
举个栗子！有一个班下面有那个学生，显然这个班和学生的关系就是一对多。
我们可以如下定义：
班级
'use strict';
module.exports = (app) => {
const { STRING, INTEGER,} = app.Sequelize;
const Classroom = app.model.define('class', {
id: { type: INTEGER, primaryKey: true, autoIncrement: true },
name: STRING(30),
create_by: STRING(100),
create_time: DATE,
update_by: STRING(100),
update_time: DATE
});
// 声明关系
Classroom.associate = function (){
// 一个班有很多学生，所以用hasMany
Classroom.hasMany(app.model.Student, {as: "students"});
}
return Classroom ;
};
学生
'use strict';
module.exports = (app) => {
const { STRING, INTEGER,} = app.Sequelize;
const Student = app.model.define('student', {
id: { type: INTEGER, primaryKey: true, autoIncrement: true },
name: STRING(30),
age: INTEGER,
sex: INTEGER,
class_id: INTEGER,
create_by: STRING(100),
create_time: DATE,
update_by: STRING(100),
update_time: DATE
});
// 声明关系
Student.associate = function (){
// 学生属于一个班级，外键关联是班级id
Student.belongsTo(app.model.Classroom, { foreignKey: "class_id"});
}
return ;
};
查询
public async test(){
const ctx = this.ctx;
return await ctx.model.Classroom.findAll({
include: {
as: "students",
model: app.model.Student
}
});
}
7.多对多
然后是最常见的多对多的情况，我上学的时候老师举的例子就是，一个大学生学n多门科目，一个科目有m多大学生来学习，科目和大学生就是多对多的关系，他们两个是通过成绩表或者选课表来联系在一起的，所以一个第三方的关联表格就很重要，这一点在sequelize上很好的展现出来。
放在常见的后端管理系统当中，在角色和用户之间就很好的展现出来，一个角色不止一个用户，一个用户不止一个角色，通过一个关联表格如下：
关联表
'use strict';
module.exports = (app) => {
const { INTEGER } = app.Sequelize;
const UserRole = app.model.define("system_user_role", {
id: {primaryKey: true,type: INTEGER,},
userId: {type: INTEGER, field: "user_id"},
roleId: {type: INTEGER, field: "role_id"}
})
return UserRole;
}
角色表
'use strict';
module.exports = (app) => {
const { STRING, INTEGER, DATE, TEXT } = app.Sequelize;
const Role = app.model.define('system_role', {
roleId: { type: INTEGER, primaryKey: true, autoIncrement: true, field: "role_id"},
roleName: { type: STRING(30), field: "role_name"},
roleKey: { type: STRING(100), field: "role_key"},
roleSort: { type: INTEGER, field: "role_sort"},
dataScope: { type: STRING(2), field: "data_scope"},
menuCheckStrictly: { type: INTEGER, field: "menu_check_strictly"},
deptCheckStrictly: { type: INTEGER, field: "dept_check_strictly"},
status: INTEGER,
delFlag: { type: INTEGER, field: "del_flag"},
createBy: { type: STRING(64), field: "create_by"},
createTime: { type: DATE, field: "create_time"},
updateBy: { type: STRING(64), field: "update_by"},
updateTime: { type: DATE, field: "update_time"},
remark: TEXT
});
Role.associate = function (){
Role.belongsToMany(app.model.User,{
through: app.model.UserRole,
foreignKey: "roleId",
otherKey: "userId"
});
Role.belongsToMany(app.model.Menu,{
as: "menus",
through: app.model.RoleMenu,
foreignKey: "roleId",
otherKey: "menuId"
});
Role.belongsToMany(app.model.Dept,{
as: "departments",
through: app.model.RoleDept,
foreignKey: "roleId",
otherKey: "deptId"
});
};
return Role;
};
用户表
'use strict';
module.exports = (app) => {
const { STRING, INTEGER, DATE, TEXT } = app.Sequelize;
const User = app.model.define('system_user', {
id: { type: INTEGER, primaryKey: true, autoIncrement: true },
username: STRING,
password: STRING,
realName: {type: STRING, field: "real_name"},
userType: {type: INTEGER, field: "user_type"},
email: STRING,
phone: STRING,
gender: INTEGER,
avatar: STRING,
enabled: INTEGER,
delFlag: {type: INTEGER, field: "del_flag"},
loginIp: {type: STRING, field: "login_ip"},
loginDate: {type: DATE, field: "login_date"},
remark: TEXT,
createBy: {type: STRING, field: "create_by"},
createTime: {type: DATE, field: "create_time"},
updateBy: {type: STRING, field: "update_by"},
updateTime: {type: DATE, field: "create_time"}
});
User.associate = function (){
User.belongsToMany(app.model.Role, {
as: "roles",
through: app.model.UserRole,
foreignKey: "userId",
otherKey: "roleId"
})
}
return User;
}
查询
public async test(){
const ctx = this.ctx;
return await await ctx.model.User.findOne({
where: {username: useName, enabled: 1, del_flag: 0}, include: {
as: "roles",
model: app.model.Role,
where: {delFlag: 0, status: 0}
}
});
}
4254
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
