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",
    }
  ]
}