Crud: Adding queries to the .save transaction

[Bnaya]

TL; DR;
I want to manually add/remove rows from other table in the public async updateOne( of a spesific entity, say, Brands, in the same transaction of the builtin .save operation,
With overriding little of code possible.

General motivation/background:
I have mysql database that the schema is created and managed manually.
I have Many To Many relations there, that i cannot express using TypeORM properly,
As TypeORM junction table is generated internally with a specific and and schema, and configureable.
So i can't teach it to use that table.
So, i've setup a OneToMany <-> ManyToOne relation between each of the entities and my custom Junction table.
(bt_brands <-> bt_brands_to_categories)
(bt_categories <-> bt_brands_to_categories)

Now the problem starts:
When posting new array to brands.categories (lets say empty array)
The generated query is
UPDATE bt_brands_to_categories SET bt_brand_id = null WHERE bt_brand_id = 123
My expectation is that to be a DELETE QUERY.
But ok, i left that (maybe you can help?)
i figured i can just DELETE / UPDATE rows myself, but i want to do it as part of the .save transaction.
And as i must completely override the updateOne operation, and i can't use the super.updateOne for that

[Bnaya]

I want to share my current implementation:

//https://github.com/nestjsx/crud/blob/a6a5f3d54351bfb93231aeeac3a27e1409b1ecb0/src/typeorm/repository-service.class.ts#L139
  public async updateOne(
    data: DeepPartial<Brand>,
    params?: FilterParamParsed[],
    routeOptions?: UpdateOneRouteOptions,
  ): Promise<Brand> {
    data.categories = undefined;
    delete data.updated_at;
    delete data.created_at;

    const categoryIds: number[] = (data as any).categoryIds;

    // const relRepo = this.repo.manager.getRepository(Bt_brands_to_categories);
    // const manager = this.repo.manager;

    return await this.repo.manager.transaction(async transaction => {
      const brandRepo = transaction.getRepository(Brand);
      const categoriesRelRepo = transaction.getRepository(Bt_brands_to_categories);

      const brand = await brandRepo.findOneOrFail({
        id: data.id,
      });

      // delete removed
      await categoriesRelRepo.delete({
        bt_brand_id: data.id,
        bt_category_id: Not(In(categoryIds)),
      });

      const existingRelsToKeep = await categoriesRelRepo.find({
        bt_brand_id: data.id,
        bt_category_id: In(categoryIds),
      });
      const relsToAdd = categoryIds.filter(cId => !existingRelsToKeep.some(toKeep => toKeep.bt_category_id === cId));

      if (relsToAdd.length > 0) {
        await categoriesRelRepo.insert(
          relsToAdd.map(cId => ({
            bt_brand_id: data.id,
            bt_category_id: cId,
          })),
        );
      }

      return brandRepo.save(data);
    });
  }

[zMotivat0r]

@Bnaya I'm trying to cover all TypeORM issues we have here. And this one is one of the oldest.
Nevertheless, I think it will be very challenging to implement this in CRUD lib since TypeORM itself suffers to solve this.
https://github.com/typeorm/typeorm/issues/3938
https://github.com/typeorm/typeorm/issues/2121
But, I'll try to do this.

[prevostc]

I want to participate with another use case for this kind of feature.

I wrote many e2e tests where each test is wrapped in an auto-rollbacked transaction.
The main benefits of this approach is speed. There is no need to reset the database between tests as any change gets rollbacked. Also, it forces everyone to wrap db access inside a transaction (debatable, but ok for my use case)

I found a very hacky solution involving injecting a transaction in some awkward places, using nestjs inject scope and some good amount of "any", "unknown" and "@ts-ignore". Here is a gist:

/!\ THIS IS AN INFORMATIONAL HACK: PLEASE DO NOT USE (or roast me) /!\

export class SessionCrudService<T> extends TypeOrmCrudService<T> {

  // .... many more overrides

  // inject some repo (does not matter, won't be used) 
  // and all needed infos to create a runtime session
  constructor(
    protected baseRepo: Repository<T>,
    protected db: DbSessionService,
    protected repoKey: keyof ScopedRepoBag,
    protected request: any,
  ) {
    super(baseRepo)
  }

  // replace all getters with smth like this
  // might not be needed anymore with the new @CrudAuth decorator
  get find() {
    const that = this
    return function() {
      const user = getCurrentUserFromRequest(that.request)
      return that.db.newUserSession(user, db => {
        return db.repo[that.repoKey].find.call(db.repo[that.repoKey], arguments)
      })
    }
  }

  // replace all non-read only method like this
  // basically a copy of the TypeOrmCrudService code, with some changes
  public async createOne(req: any, dto: any) {
    // @ts-ignore
    const entity = this.prepareEntityBeforeSave(dto, req.parsed)
    if (!entity) {
      this.throwBadRequestException(`Empty data. Nothing to save.`)
    }
    const user = getCurrentUserFromRequest(this.request)
    return this.db.newUserSession(user, db => {
      return ((db.repo[this.repoKey] as unknown) as Repository<T>).save(entity)
    })
  }

  // .... many more overrides
}

// usage: 

@Injectable({ scope: Scope.REQUEST })
export class DayContextService extends SessionCrudService<DayContext> {
  constructor(
    @InjectRepository(DayContext) repo: Repository<DayContext>,
    db: DbSessionService,
    @Inject(REQUEST) public request: any,
  ) {
    super(repo, db, "dayContext", request)
  }
}

/!\ THIS IS AN INFORMATIONAL HACK: PLEASE DO NOT USE (or roast me) /!\