API with NestJS #15. Defining transactions with PostgreSQL and TypeORM

October 26, 2020

This entry is part 15 of 180 in the API with NestJS

One of the important concepts to understand when dealing with databases is a transaction. It is a unit of work that we treat as a whole. It either happens fully or not happens at all.

To emphasize the importance of transactions, let’s use an example that makes the data loss especially painful. When transferring money from one bank account to another, we need to withdraw the amount from the first account. We also need to add the same amount to the destination account. Doing just one of the above operations would break the integrity of our system.

The ACID properties

Fortunately, we can bundle multiple steps into a single operation, known as a transaction. To be valid, a transaction needs to have four properties:

Atomicity
- operations in the transaction are a single unit that either succeeds fully or fails
Consistency
- the transaction brings the database from one valid state to another
Isolation
- transactions can occur concurrently without resulting in the inconsistency of the database state
- the intermediate state of a transaction should be invisible to other transactions
- following up on our banking transaction example from above, another transaction should see the funds in one account or the other, but not in both, nor in either
Durability
- changes made by a transaction that is successfully committed should survive permanently, even in the case of a system failure

Transactions in PostgresSQL

Postgres equips us with some tools that we can use to ensure all of the above. To create a transaction block, we need to surround a group of statements with BEGIN and COMMIT commands.

BEGIN;

UPDATE "user"

SET "avatarId"=NULL

WHERE id=10;

DELETE FROM public_file

WHERE id=15;

COMMIT;

Thanks to using the transaction above, there will not be a situation when the user loses the avatar, but the file is not removed from our database. If the update on the user table failed for some reason, we wouldn’t remove the file from the public_file table.

Another important command here is ROLLBACK. With it, we can abort the current transaction. It discards all updates made by the transaction.

BEGIN;

DROP TABLE "user";

ROLLBACK;

The above transaction will never drop the user table because we always run a ROLLBACK at the end.

Understanding all of the above will come in handy when implementing transactions with ORMs such as TypeORM.

Transactions with TypeORM

In our NestJS series, we’ve written quite a few SQL queries, but we’ve focused on creating an application using TypeORM. Unsurprisingly, it also supports transactions.

The official TypeORM documentation mentions a few options for defining transactions. On the other hand, the NestJS documentation seems to be set on just one of them that involves using the QueryRunner.

If we look into the @nestjs/typeorm library internals, we can see that it calls the createConnection method under the hood. It returns the Connection object that we now need to create an instance of the QueryRunner.

Fortunately, we can inject the Connection object within our constructor.

import { Injectable } from '@nestjs/common';

import { Connection } from 'typeorm';

@Injectable()

export class UsersService {

constructor(

private connection: Connection,

// ...

) {}

The Connection does not represent a single database connection but a whole connection pool. To refer to a real database connection, we need QueryRunner. Each instance of it is a separate isolated database connection.

Using QueryRunner to define transactions

We can now use the Connection object inside the UsersService:

1	const queryRunner = this.connection.createQueryRunner();

With QueryRunner, we get full control over the transaction. Let’s use it within our deleteAvatar method:

async deleteAvatar(userId: number) {

const queryRunner = this.connection.createQueryRunner();

const user = await this.getById(userId);

const fileId = user.avatar?.id;

if (fileId) {

await queryRunner.connect();

await queryRunner.startTransaction();

try {

await queryRunner.manager.update(User, userId, {

...user,

avatar: null

});

await this.filesService.deletePublicFile(fileId);

await queryRunner.commitTransaction();

} catch (error) {

await queryRunner.rollbackTransaction();

throw new InternalServerErrorException();

} finally {

await queryRunner.release();

}

With queryRunner.connect(), we tell the query runner to use a connection from our connection pool. We use it to perform any further operations.

By using queryRunner.startTransaction() we start a transaction. We can think of it as the BEGIN command from our SQL example from the previous paragraph.

Since we want the update on the user table to be a part of our transaction, we perform the update using the queryRunner.

If everything goes well, we use the queryRunner.commitTransaction() method to finalize our transaction. It works as the COMMIT command that we’ve used previously.

If anything goes wrong inside of our this.filesService.deletePublicFile method and it throws an error, we catch it and call queryRunner.rollbackTransaction(). You may remember the ROLLBACK keyword from our SQL query example.

In the end, we call queryRunner.release() to indicate that we will not perform any more queries using this database connection for now.

Passing the instance of the QueryRunner instance between methods

There is still one small issue with the above code. Unfortunately, the filesService.deletePublicFile is not using the queryRunner that we initialized. This might produce unexpected results, such as:

ERROR: update or delete on table “public_file” violates foreign key constraint “FK_58f5c71eaab331645112cf8cfa5” on table “user”
DETAIL: Key (id)=(12) is still referenced from table “user”.

This happens because of the Isolation property of transactions. Inside our transaction, we remove the id of the avatar from the user table, but this change is isolated from other queries that might run on our database.

We try to remove the avatar outside of the transaction. By doing that, we violate a constraint because the user table still refers to it.

The simplest solution is to pass the queryRunner to the filesService.deletePublicFile method.

async deletePublicFileWithQueryRunner(fileId: number, queryRunner: QueryRunner) {

const file = await queryRunner.manager.findOne(PublicFile, { id: fileId });

const s3 = new S3();

await s3.deleteObject({

Bucket: this.configService.get('AWS_PUBLIC_BUCKET_NAME'),

Key: file.key,

}).promise();

await queryRunner.manager.delete(PublicFile, fileId);

}

Running the above method instead of deletePublicFile solves the described problem. Now we perform all of the operations within a single, isolated transaction.

Summary

Using transactions in the above example improved our application quite a bit. We’ve dealt with the possibility of our database being out of sync. Now, the process of detaching the avatar from the user and removing the file can’t partially succeed. Thanks to defining a transaction, it either succeeds fully or fails completely. Implementing such error handling makes our app a lot easier to manage.

Series Navigation<< API with NestJS #14. Improving performance of our Postgres database with indexesAPI with NestJS #16. Using the array data type with PostgreSQL and TypeORM >>