typeorm - how to add extra field to "many to many" table?

Question

for example:

3 tables

user
user_business_lines_business_line
business_line

those created by typeorm with the declaration in User

@ManyToMany(type => BusinessLine)
@JoinTable()
businessLines: BusinessLine[]

then, how to add columns fields like

@CreateDateColumn({ type: 'timestamp' })
createdAt: Date

@UpdateDateColumn({ type: 'timestamp' })
updatedAt: Date

to user_business_lines_business_line

Answer 1

It is not possible to add custom column in the auto created many-to-many bridge table. So create another table and give one-to-many and many-to-one relationship between them.

for example:

Three tables

User -> Table 1

BusinessLine -> Table 2

UserBusinessLine -> Bridge Table between User table and BusinessLine table

UserBusinessLine table will contain the foreign key of both parent tables and also we can add custom columns into it.

In User Table

@OneToMany(() => UserBusinessLine, (userBusinessLine) => userBusinessLine.user)
public userBusinessLines: UserBusinessLine[];

In BusinessLine Table

@OneToMany(() => UserBusinessLine, (userBusinessLine) => userBusinessLine.businessLine)
public userBusinessLines: UserBusinessLine[];

In UserBusinessLine Table

@ManyToOne(() => User, (user) => user.userBusinessLines)
public user: User;

@ManyToOne(() => BusinessLine, (businessLine) => businessLine.userBusinessLines)
public businessLine: BusinessLine;

// Custom Colums
@CreateDateColumn({ type: 'timestamp' })
createdAt: Date;

@UpdateDateColumn({ type: 'timestamp' })
updatedAt: Date;

So now the custom table has the foreign keys of User table and BusinessLine table. Also the CreateddateColumn and UpdatedDateColumn

Answer 2

You can specify custom-join table for ManyToMany relation

@Entity()
export class User {
  @PrimaryGeneratedColumn()
  id: number

  @Column()
  name: string

  @ManyToMany(type => BusinessLine, businessLine => businessLine.users)
  @JoinTable({
    name: 'user_business_line',
    joinColumn: {
      name: 'userId',
      referencedColumnName: 'id',
    },
    inverseJoinColumn: {
      name: 'businessLineId',
      referencedColumnName: 'id',
    },
  })
  businessLines: BusinessLine[]
}

@Entity('user_business_line')
export class UserBusinessLine {
  @CreateDateColumn({ type: 'timestamp' })
  createdAt: Date

  @UpdateDateColumn({ type: 'timestamp' })
  updatedAt: Date

  @Column()
  @IsNotEmpty()
  @PrimaryColumn()
  userId: number;

  @Column()
  @IsNotEmpty()
  @PrimaryColumn()
  businessLineId: number;
}

@Entity()
export class BusinessLine {
  @PrimaryGeneratedColumn()
  id: number

  @Column()
  name: string

  @ManyToMany(type => User, user => user.businessLines)
  users: User[]
}