Typeorm(Nestjs) QueryBuilder更新多对多关系触发非空约束报错

问题现象 #

在TypeORM中配置多对多关系后，使用QueryBuilder的relation API添加关联时，触发数据库非空约束错误，生成的SQL中usersId字段使用DEFAULT值（为空），导致关联表插入失败。

报错详情 #

执行的关键SQL及错误场景：

query: SELECT "Enterprise"."id" AS "Enterprise_id", "Enterprise"."createdAt" AS "Enterprise_createdAt", "Enterprise"."updatedAt" AS "Enterprise_updatedAt", "Enterprise"."name" AS "Enterprise_name", "Enterprise"."erpCustomerCode" AS "Enterprise_erpCustomerCode", "Enterprise"."isActive" AS "Enterprise_isActive", "Enterprise"."street" AS "Enterprise_street", "Enterprise"."houseNumber" AS "Enterprise_houseNumber", "Enterprise"."city" AS "Enterprise_city", "Enterprise"."state" AS "Enterprise_state", "Enterprise"."country" AS "Enterprise_country", "Enterprise"."zip" AS "Enterprise_zip", "Enterprise"."geoLocation" AS "Enterprise_geoLocation", "Enterprise"."unitsSystem" AS "Enterprise_unitsSystem", "Enterprise"."weekNumberingSystem" AS "Enterprise_weekNumberingSystem", "Enterprise"."yearStart" AS "Enterprise_yearStart", "Enterprise"."weekStart" AS "Enterprise_weekStart" FROM "enterprises" "Enterprise" WHERE ("Enterprise"."id" = $1) LIMIT 1 -- PARAMETERS: ["d2f16db8-63be-4b09-a5ea-e04336069df4"]
User {}
query: INSERT INTO "enterprise_users"("enterprisesId", "usersId") VALUES ($1, DEFAULT) -- PARAMETERS: ["d2f16db8-63be-4b09-a5ea-e04336069df4"]
query failed: INSERT INTO "enterprise_users"("enterprisesId", "usersId") VALUES ($1, DEFAULT) -- PARAMETERS: ["d2f16db8-63be-4b09-a5ea-e04336069df4"]

错误提示截图：

实体与业务代码配置 #

enterprise.entity.ts #

@Entity('enterprises')
export class Enterprise {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @CreateDateColumn({ type: 'timestamp', default: () => 'CURRENT_TIMESTAMP' })
  createdAt: Date;

  @UpdateDateColumn({ type: 'timestamp', default: () => 'CURRENT_TIMESTAMP' })
  updatedAt: Date;

  @Column({ unique: true, length: 50 })
  name: string;

  // 省略其他字段

  @ManyToMany(() => User, (users) => users.enterprises, { cascade: true })
  @JoinTable({ name: 'enterprise_users' })
  users: User[];
}

user.entity.ts #

@Entity('users')
export class User {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @Column()
  name: string;

  @ManyToMany(() => Enterprise, (enterprise) => enterprise.users)
  enterprises: Enterprise[];
}

enterprise.service.ts #

@Injectable()
export class EnterpriseService {
  constructor(
    @InjectRepository(Enterprise)
    private enterpriseRepository: Repository<Enterprise>,
  ) {}

  async update(id: string, enterprise: UpdateEnterpriseDto) {
    // 测试用新用户
    const user = new User();
    user.name = 'test123';

    // 触发错误的代码
    await this.enterpriseRepository.createQueryBuilder().relation(Enterprise, 'users').of(id).add(user);

    return this.enterpriseRepository.save({ id, ...enterprise });
  }
}

问题解答 #

猜想确认 #

你的猜想完全正确：必须先将User实体保存到数据库（生成主键ID），才能进行多对多关联操作。

无法自动创建User的原因 #

虽然在Enterprise的ManyToMany关系上配置了{ cascade: true }，但这个级联规则仅对TypeORM的save方法生效。而QueryBuilder的relation API是直接操作中间关联表的底层方法，它默认认为传入的关联实体已经存在于数据库中（拥有有效主键ID），不会自动触发级联创建新实体的逻辑。当传入未保存的User对象时，其id为空，TypeORM只能在SQL中给usersId设置DEFAULT值，最终触发数据库的非空约束错误。

解决方案 #

方案1：先保存User，再执行关联 #

@Injectable()
export class EnterpriseService {
  constructor(
    @InjectRepository(Enterprise)
    private enterpriseRepository: Repository<Enterprise>,
    @InjectRepository(User) // 注入User仓库
    private userRepository: Repository<User>,
  ) {}

  async update(id: string, enterprise: UpdateEnterpriseDto) {
    const user = new User();
    user.name = 'test123';
    // 先保存用户到数据库，获取带ID的实体
    const savedUser = await this.userRepository.save(user);
    // 执行关联操作
    await this.enterpriseRepository.createQueryBuilder().relation(Enterprise, 'users').of(id).add(savedUser);

    return this.enterpriseRepository.save({ id, ...enterprise });
  }
}

方案2：利用save方法的级联特性 #

直接通过save方法更新企业实体，借助配置的cascade: true自动创建User并完成关联：

@Injectable()
export class EnterpriseService {
  constructor(
    @InjectRepository(Enterprise)
    private enterpriseRepository: Repository<Enterprise>,
  ) {}

  async update(id: string, enterprise: UpdateEnterpriseDto) {
    const user = new User();
    user.name = 'test123';
    // 查询目标企业并加载已关联用户，避免覆盖
    const targetEnterprise = await this.enterpriseRepository.findOne({ 
      where: { id },
      relations: ['users']
    });
    // 添加新用户到关联列表
    targetEnterprise.users = [...(targetEnterprise.users || []), user];
    // 保存时触发级联创建User并更新关联表
    await this.enterpriseRepository.save(targetEnterprise);

    return this.enterpriseRepository.save({ id, ...enterprise });
  }
}

内容的提问来源于stack exchange，提问作者Julez