How to execute multiple QueryBuilders in a transaction using TypeORM

When using TypeORM for database operations, understanding how to execute multiple QueryBuilder operations within a transaction is crucial. Transactions ensure database integrity by rolling back all operations if any operation fails, preventing partial updates.

1. Start a Transaction Using getConnection or entityManager

First, obtain a database connection from TypeORM and start a transaction. This can be done using entityManager or directly from the connection.

typescript
import { getConnection } from "typeorm";

// Obtain connection and start transaction
const connection = getConnection();
const queryRunner = connection.createQueryRunner();

await queryRunner.connect();
await queryRunner.startTransaction();

2. Execute Multiple Query Operations Within a Transaction

Within a transaction, use queryRunner.manager to execute multiple QueryBuilder operations. Each operation will utilize the same database connection and transaction context.

typescript
try {
    // Use QueryBuilder to create the first query
    const userInsertResult = await queryRunner.manager
        .createQueryBuilder()
        .insert()
        .into(User)
        .values({ name: "John Doe", email: "john@example.com" })
        .execute();

    // Assume you need to use the user ID from the previous insert
    const newUserId = userInsertResult.identifiers[0].id;

    // Use the new user ID as a foreign key to create another query
    const profileInsertResult = await queryRunner.manager
        .createQueryBuilder()
        .insert()
        .into(Profile)
        .values({ userId: newUserId, bio: "Hello World" })
        .execute();

    // Commit the transaction
    await queryRunner.commitTransaction();
} catch (error) {
    // If an error occurs, roll back the transaction
    await queryRunner.rollbackTransaction();
} finally {
    // Finally, release the queryRunner
    await queryRunner.release();
}

3. Error Handling and Transaction Rollback

When executing multiple operations within a transaction, if any operation fails, roll back the transaction to maintain data consistency. This is achieved by catching exceptions and calling the rollbackTransaction method within the exception block. After completing the transaction, remember to release the queryRunner.

The above is a basic example of using transactions to execute multiple QueryBuilder operations in TypeORM. Using transactions ensures the atomicity of data operations, preventing data inconsistency issues caused by partially successful operations.