This article has participated in the activity of “New person creation Ceremony”, and started the road of digging gold creation together.

The last article introduced the sequelize operation on a single table (User table) in NestJS. This time we will continue with the multi-table operation. The Role table has a many-to-many relationship with the User table (each User can have multiple roles, and each Role contains multiple users). There is a one-to-many relationship between the License table and the User table (each car can have only one owner, and each owner can have multiple cars).

See the first article in this column for more on creating instance objects, associating data tables, introducing Sequelize into NestJS, and more. This article focuses on multi-table operations. # Nestjs uses sequelize to perform single table operations

Prefacing instructions. So let’s take a look at the ER diagram and just visualize the relationship between the four tables

Deal with one-to-many associations first. Establish a License table

// licenses/license.model.ts
import { Column, Table, Model, ForeignKey, BelongsTo } from 'sequelize-typescript';
import { User } from '.. /users/user.model';
@Table({
  tableName: 'user_license'.timestamps: false,})export class License extends Model<License> {
  @Column
  license_num: string;
  
  @ForeignKey(() = > User) // define the user_id field as a foreign key and associate it with the User table
  @Column
  user_id: number;
  
  @BelongsTo(() = > User) // Define many-to-one relationships. Note that BelongsTo is a many-to-one table
  user: User; 
}
Copy the code

Reprocess many-to-many associations. Many-to-many relationships require an intermediate association table. So let’s create a RoleUser table. Declare a foreign key in a table. Associate User and Role. Note that the relationship between Role/User and RoleUser is one-to-many. The relationship between User and Role is many-to-many.)

// role_users/role_user.model.ts
import { Column, Table, Model, BelongsTo, ForeignKey } from 'sequelize-typescript';
import { Role } from '.. /roles/role.model'; // add table User,
import { User } from '.. /users/user.model'; // Select * from User; // Select * from User
@Table({
  tableName: 'admin_role_user'.// There is a change table in the database
  timestamps: false,})export class RoleUser extends Model<RoleUser> {

  @ForeignKey(() = > User)
  @Column
  user_id: number; The foreign key user_id is associated with the primary key ID in the User table
  @BelongsTo(() = > User) // Define many-to-one relationships in the same way. Note that BelongsTo is a many-to-one table
  user: User[];

  @ForeignKey(() = > Role)
  @Column
  role_id: number;// role_id is associated with the primary key ID in the User table
  @BelongsTo(() = > Role) 
  role: Role[];
}
Copy the code

Redefine the Role instance (the Role table no longer needs to be associated with the RoleUser. Just associate with User. Similarly, User only needs to be associated with Role.

// roles/role.model.ts
import { Column, Table, Model, BelongsToMany } from 'sequelize-typescript';
import { RoleUser } from '.. /role_users/role_user.model';
import { User } from '.. /users/user.model';

@Table({
  tableName: 'admin_role'.timestamps: false,})export class Role extends Model<Role> {
  @Column
  name: string;
  @Column
  remarks: string;

  @BelongsToMany(() = > User, () = > RoleUser) // Establish a many-to-many association. The second parameter is the intermediate table UserRole
  users: User[];
}

Copy the code

You can also imagine the structure in the User table

import { Column, Table, Model, HasMany, BelongsToMany } from 'sequelize-typescript';
import { License } from '.. /licenses/license.model';
import { RoleUser } from '.. /role_users/role_user.model';
import { Role } from '.. /roles/role.model';
@Table({
  tableName: 'admin_user'.timestamps: false,})export class User extends Model<User> {
  @Column
  username: string;
  @Column
  password: string;
  @Column
  email: string;
  @Column
  mobile: string;
  @Column
  create_time: Date;

  @HasMany(() = > License) // Hasmany is used by the [1] table in the one-to-many relationship. A semantic
  licenses: License[];

  @BelongsToMany(() = > Role, () = > RoleUser)
  roles: Role[];
}
Copy the code

Now let’s look at how to use the User instance in users.service.ts

// users.service.ts
import { Injectable } from '@nestjs/common';
import { InjectModel } from '@nestjs/sequelize';
import { User } from './user.model';
import { License } from '.. /licenses/license.model';
import { Role } from '.. /roles/role.model';
import { RoleUser } from '.. /role_users/role_user.model';
@Injectable()
export class UsersService {
  constructor(
    @InjectModel(User)
    private userModel: typeof User
  ) {}

  findAll(): Promise<User[]> {
    return this.userModel.findAll({ 
      attributes: ['username'].// specify to find some of the fields in the User table
      include: [{model: Role, // Table lookup includes the Role table and License table. And develop specific fields to find out. Examples of specific methods can be learned on the official website
          attributes: ['remarks'.'name'],}, {model: License,
          attributes: ['license_num'],},]}); }}Copy the code

Postman table lookup verification

So far the operation of multi-table joint check is completed. If you need to delete or insert or update multiple tables, there is no solution to do it all at once. I hope you will be generous with your advice.Sequelize-typescript English documents for NPM