egg-sequelize使用教程

不念不忘少年蓝@ 2022-12-16 13:08 114阅读 0赞

# 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