How can I use single transaction with multiple query in repository mode in nestjs-typeorm

Question

I have a setup for my NestJS application in which I am using typeorm with a PostgreSQL database. In the setup, I am using repository mode to query the database. Now I want to use database transactions with my queries, but I am not able to use transactions because I am using one transaction with multiple queries from different repositories, and a transaction uses an entity manager, which has to be used to query the database in order to access the same transaction for all the queries in the scope.

Entity One

@Injectable()
export class EntityOneService extends BaseService<EntityOne> {
  repository: Repository<EntityOne>;
  constructor(private connection: Connection) {
    super();
    this.repository = this.connection.getRepository(EntityOne);
  }
}

Entity Two

import { Injectable } from '@nestjs/common';
import { Connection, Repository } from 'typeorm';

import { EntityTwo } from '../entities/lesson.entity';
import { BaseService } from './baseService/base-service.service';

@Injectable()
export class EntityTwoService extends BaseService<EntityTwo> {
  repository: Repository<EntityTwo>;
  constructor(private connection: Connection) {
    super();
    this.repository = this.connection.getRepository(EntityTwo);
  }
}

This is how to initialize the repository from a database table. Now I want to query the database using transaction in repository mode.

const entityOne = await this.entityOneService.find()
const entityTwo = await this.entityTwoService.find()

I want to query both with the same transaction. I have user sequelize before typeorm, and in sequelize there is a method named transaction which can used as follows.

this.sequelize.transaction(async transaction => {
    const entityOne = await this.entityOneService.findAll({transaction})
    const entityTwo = await this.entityTwoService.findAll({transaction})
})

I want to do something like that in typeorm, if possible how can I implement it?

Stefan Schneider · Accepted Answer · 2022-10-02 10:06:00Z

1

https://stackoverflow.com/a/70140732 I think this is an example for a solution with multiple repositories.

answered Oct 2, 2022 at 10:06

Stefan Schneider

111 bronze badge

Sign up to request clarification or add additional context in comments.

1 Comment

Community Over a year ago

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.

Parsifal · Accepted Answer · 2022-10-01 00:24:10Z

Transactions

Creating and using transactions
Using QueryRunner to create and control state of single database connection

Creating and using transactions

Transactions are created using DataSource or EntityManager. Examples:

await myDataSource.transaction(async (transactionalEntityManager) => {
  // execute queries using transactionalEntityManager
});

or

await myDataSource.manager.transaction(async (transactionalEntityManager) => {
  // execute queries using transactionalEntityManager
});

Everything you want to run in a transaction must be executed in a callback:

await myDataSource.manager.transaction(async (transactionalEntityManager) => {
  await transactionalEntityManager.save(users);
  await transactionalEntityManager.save(photos);
  // ...
});

Using `QueryRunner` to create and control state of single database connection

QueryRunner provides a single database connection. Transactions are organized using query runners. Single transactions can only be established on a single query runner. You can manually create a query runner instance and use it to manually control transaction state. Example:

// create a new query runner
const queryRunner = dataSource.createQueryRunner();

// establish real database connection using our new query runner
await queryRunner.connect();

// now we can execute any queries on a query runner, for example:
await queryRunner.query("SELECT * FROM users");

// we can also access entity manager that works with connection created by a query runner:
const users = await queryRunner.manager.find(User);

// lets now open a new transaction:
await queryRunner.startTransaction();

try {
  // execute some operations on this transaction:
  await queryRunner.manager.save(user1);
  await queryRunner.manager.save(user2);
  await queryRunner.manager.save(photos);

  // commit transaction now:
  await queryRunner.commitTransaction();
} catch (err) {
  // since we have errors let's rollback changes we made
  await queryRunner.rollbackTransaction();
} finally {
  // you need to release query runner which is manually created:
  await queryRunner.release();
}

There are 3 methods to control transactions in QueryRunner:

startTransaction - starts a new transaction inside the query runner instance.
commitTransaction - commits all changes made using the query runner instance.
rollbackTransaction - rolls all changes made using the query runner instance back.

Source: https://typeorm.io/transactions

Yes, I have seen this example and went through the documentation, but my use case is different. I want to use the transaction with multiple repositories.
Repeating a documentation that doesn't answer the original problem is not helpful.

KESHAV KABRA · Accepted Answer · 2023-06-09 09:55:42Z

0

I have found a solution, where I can use the repository and transaction just like sequelize, it is using the manager provided when we start a transaction, there is a method withRepository inside the manager object, it can be used to do query using a specific repository. So what I have done is created a BaseService and everytime I have to query the database I pass the manager with in the function as last parameter which is optional as well. There I use the manager and do the below.

manager.withRepository(this.repository).<queryFunction>()

Thats it, If you want an example let me know I will implement it and share in a github repository.

answered Jun 9, 2023 at 9:55

KESHAV KABRA

211 silver badge6 bronze badges

Collectives™ on Stack Overflow

How can I use single transaction with multiple query in repository mode in nestjs-typeorm

3 Answers 3

1 Comment

Transactions

Creating and using transactions

Using `QueryRunner` to create and control state of single database connection

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Transactions

Creating and using transactions

Using QueryRunner to create and control state of single database connection

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related

Using `QueryRunner` to create and control state of single database connection