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

NestJS TypeScript

This entry is part 15 of 19 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   and commands.

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 table failed for some reason, we wouldn’t remove the file from the table.

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

The above transaction will never drop the table because we always run a 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 .

If we look into the library internals, we can see that it calls the method under the hood. It returns the object that we now need to create an instance of the .

Fortunately, we can inject the object within our constructor.

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

Using QueryRunner to define transactions

We can now use the object inside the :

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

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

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

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

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

If anything goes wrong inside of our method and it throws an error, we catch it and call . You may remember the keyword from our SQL query example.

In the end, we call 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 is not using the 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 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 table still refers to it.

The simplest solution is to pass the to the method.

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


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 >>
Notify of
Inline Feedbacks
View all comments