egg-sequelize教程，保存查询、修改、删除、新增，多表查询

modal

user

 

import { Application } from 'egg'; import * as uuidv4 from 'uuid/v4'; /** 用户资料表，修改邮箱和手机号时，需要同步到userAuth表 */ export default function(app: Application) { const { STRING, BOOLEAN, DATE, UUID, ARRAY } = app.Sequelize; const tableName = 'user'; const User = app.model.define( tableName, { id: { type: UUID, unique: true, primaryKey: true, allowNull: false, defaultValue: () => { return uuidv4().replace(/-/g, ''); }, }, username: { type: STRING(32), unique: true, allowNull: false, comment: '账号名', }, // password: { // type: STRING(255), // allowNull: false, // comment: '密码', // }, email: { type: STRING(64), unique: true, allowNull: true, comment: '邮箱地址', }, phone: { type: STRING(20), unique: true, allowNull: true, comment: '手机号码', }, avatar: { type: STRING(150), allowNull: true, comment: '头像', }, alias: { type: STRING(30), comment: '别名', }, realName: { type: STRING(30), allowNull: true, comment: '真实姓名', }, signature: { type: STRING(255), allowNull: true, comment: '签名', }, status: { type: BOOLEAN, allowNull: false, defaultValue: 1, comment: '用户状态: 1 正常； 0 禁用', }, lastActivedAt: DATE, createdAt: DATE, updatedAt: DATE, }, { tableName, comment: '用户表', timestamps: true, underscored: false, } ); class UserModal extends User { id: string; username: string; email: string; phone: string; avatar: string; alias: string; realName: string; signature: string; status: boolean; roles: any[]; lastActivedAt: number; static associate() { // app.model.User.hasMany(app.model.UserAuth, { as: 'userAuths', sourceKey: 'id', foreignKey: 'userId' }); app.model.User.belongsToMany(app.model.Role, { as: 'roles', through: app.model.UserRoleRelation, foreignKey: 'userId' }); } } return UserModal; } role

 

import { Application } from 'egg'; import * as uuidv4 from 'uuid/v4'; /** 角色表 */ export default function(app: Application) { const { STRING, BOOLEAN, DATE, UUID } = app.Sequelize; const tableName = 'role'; const Role = app.model.define( tableName, { id: { type: UUID, unique: true, primaryKey: true, allowNull: false, defaultValue: () => { return uuidv4().replace(/-/g, ''); }, }, name: { type: STRING(32), allowNull: false, comment: '角色名称', }, remark: { type: STRING(128), comment: '角色说明', }, createdAt: DATE, updatedAt: DATE, }, { tableName, comment: '角色表', timestamps: true, underscored: false, } ); return class RoleModal extends Role { id: string; /** 角色名称 */ name: string; /** 角色说明 */ remark: string; rights?: any[]; createdAt: number; updatedAt: number; static associate() { app.model.Role.belongsToMany(app.model.User, { as: 'users', through: app.model.UserRoleRelation, foreignKey: 'roleId' }); app.model.Role.belongsToMany(app.model.Right, { as: 'rights', through: app.model.RoleRightRelation, foreignKey: 'roleId' }); } }; }

增加 row

 

await this.ctx.model.UserAuth.create({ identityType: IdentityType.username, identifier: username, credential: password, });

删除 row

 

await this.ctx.model.User.destroy({ where: { id: userId } });

改 row

 

await ctx.model.User.update({ alias: '233' }, { where: { id: userId } });

查 row

 

findAll; findAndCountAll; findOne;

单对单

每个权限都归属于某个菜单

 

app.model.Right.hasOne(app.model.Menu, { as: 'menu', sourceKey: 'menuId', foreignKey: 'id' }); const rows = await this.ctx.model.Right.findAll({ { as: 'menu', model: this.ctx.model.Menu, attributes: ['name'] }, }); rows = [ { id: '1901', name: '查看', code: 'read', menu: { name: '个人设置' }, }, ];

单对多

一个菜单里有多个权限

 

app.model.Menu.hasMany(app.model.Right, { as: 'rights', sourceKey: 'id', foreignKey: 'menuId' }); this.ctx.model.Menu.findAll({ include: [ { as: 'rights', model: this.ctx.model.Right, attributes: ['id', 'name', 'code', 'menuId'], }, ], // 内排行 order: [[col('rights.createdAt'), 'ASC']], }); const rows = [ { id: 1, name: '首页', rights: [ { id: 1, name: '查看', code: 'read', menuId: 1, }, ], }, ];

多对多

一个用户 拥有 多个角色 一个角色 可以被 很多用户 拥有

 

// 需要一个关联表，里面有字段 userId 和 roleId // 当删除用户或角色时，需要删除对应的关联行 app.model.Role.belongsToMany(app.model.User, { as: 'users', through: app.model.UserRoleRelation, foreignKey: 'roleId' }); app.model.User.belongsToMany(app.model.Role, { as: 'roles', through: app.model.UserRoleRelation, foreignKey: 'userId' }); const user = await this.ctx.model.User.findOne({ include: [ { model: this.ctx.model.Role, as: 'roles', through: { attributes: [] }, }, ], where: { id: userId, }, }); { id: "1", username: "admin", avatar: null, alias: "管理员", roles: [ { id: "1", name: "超级管理员", remark: "网站总管理员，满权限", type: "superAdmin", } ] }

转载：https://www.jianshu.com/p/d1977d86993a