How to use QueryService within a database transaction?

[thehappycoder]

How to use QueryService within a database transaction? In particular, with TypeORM.

@Mutation(() => EntityDTO)
  async updateOneEntity(
    @Args('input') input: UpdateOneEntityDTO,
  ): Promise<EntityDTO> {
    return await this.dbConnection.transaction(async manager => {
          // How do I make `this.service` use the `manager`?
          const entity =  await this.service.updateOne(
            input.id,
            input.update,
          )

          // TODO Make an API call

          return entity
    })
  }

[smolinari]

I'd imagine you'd need to create a custom service using the connection for a transaction as described here:

https://docs.nestjs.com/techniques/database#transactions

and here:

https://doug-martin.github.io/nestjs-query/docs/persistence/typeorm/custom-service

(Disclaimer: I don't user TypeORM so take my comments with a grain of salt.)

Scott

[thehappycoder]

@smolinari In typeorm it is required to use manager instance provided by transaction call. It's not possible to pass this instance to the nestjs-query's QueryService. For now, I not using QueryService and just use the manager instance to get a TypeORM repository instance.

[psteinroe]

Hi @thehappycoder, since you are using TypeORM, I found typeorm-transactional-cls-hooked to be very useful. Just overwrite the methods you want to be transactional and put the @Transactional decorator. You can patch the Repositories so you don't have any changes in you code.

Note: It is based on the unstable Node async hooks API. But until now we did not have any problems.

[doug-martin]

@thehappycoder did the suggestion from @steinroe work for you?

[thehappycoder]

@doug-martin No, I haven't tried since my workaround works with typeorm repositories. I would like to find a workaround to get database transactions work with the nestjs-query query service instead as it's has out of the box data loader support whereas when I work with typeorm repos, I am on my own, right?

[frenchtoast747]

I'd like to :heavy_plus_sign:1 this request as well. I'm currently using a sort of custom Command architecture where a transaction is spun up and commands are executed all within a Context. Upon successful completion of the chain of commands, the transaction is committed. I didn't feel comfortable basing production code on the experimental async-hooks API, so the database "session" is passed as a part of the context.

We're doing something like this as a part of the execution of our commands:

class MyCommand extends Command {
    // ...
    async executeIn(context: Context): Promise<MyResult> {
        // note that context.session is an EntityManager
        // created from connection.createQueryRunner().manager.
        // the entire session is inside of queryRunner.startTransaction()
        const repo = context.session.getCustomRepository(MyModel);
        // ... do stuff ...
        // possibly call another command which gets the same `context.session`
        context.execute(new OtherCommand(...));
    }

So that each repository is using the same connection and thus the same transaction when making queries while running the mutating commands.

It would be really helpful if the QueryService had a static method or something similar that would allow for passing that EntityManager in order to get an instance of the QueryService which will allow for making queries that run within the transaction.

E.g.

const service = QueryService.fromEntityManager(context.session);
// or with custom query services
const service = MyQueryService.fromEntityManager(context.session);

I'm not suggesting this is a good API either, but just something to hopefully illustrate the need. As of right now, I will need to fallback to write custom methods on our repos, but it would be great if we could instead use the awesome filtering provided by the QueryServices.

The part that sucks about the command architecture that we've chosen is that it lives outside of the DI system :disappointed: but there doesn't seem to be any other NestJS way to perform a chain of commands all within the same transaction without the async hook API and/or the concept of thread locals.

[smolinari]

@frenchtoast747 - AFAIK, CQRS, which commands are a part of, is a system made for microservices, which between them, wouldn't be able to share a DI container. So your findings are correct.

Have you looked at the saga pattern (also part of CQRS)? There was a discussion here too about integrating CQRS into nestjs-query. However to me, the only thing missing from nestjs-query is the saga pattern. So, basically a mutation that can kick off such a process. I think commands and queries aren't specifically needed as they are already built into GraphQL as mutations and queries. But, that point of view is only one I have. I still haven't figured this out completely myself, so I wouldn't consider my thoughts as advice, just something to also think about. 😊

Scott

[frenchtoast747]

Hey, @smolinari, thanks for the reply!

The command architecture we've got set up is not NestJS's CQRS system and is a home-rolled library. It's only meant to be used by the running service itself and most of the "commands" shouldn't be leaked outside of the service (a la CQRS). Our business logic occasionally requires that commands call other commands chaining one after another to an arbitrary depth until an entire unit of work is completed (not simple CRUD). All of this should be inside a single database transaction so that if any link in the command chain fails (a business invariant may have been violated), the entire unit is rolled back.

I imagine that CQRS would be implemented on top of what we have inside our services (basing this all of the Microservices Patterns book where each service's business mutations should all be inside of an ACID transaction and the SAGA pattern fulfills ACID across services).

All of that aside, it would still be nice to create a QueryService instance outside of the DI system by supplying a specific connection/entity manager to use.