API with NestJS #104. Writing transactions with Prisma

NestJS

This entry is part 104 of 156 in the API with NestJS

As web developers, one of our primary concerns is keeping the integrity of our data. Fortunately, SQL databases come equipped with tools that allow us to ensure data accuracy and consistency.

You can find the code from this article in this repository.

One of the most fundamental examples of when things might go wrong is transferring money from one bank account to another. Let’s say we have two accounts with $1000, and we want to transfer $500 from one account to another. It consists of two steps:

  1. taking $500 from one account,
  2. adding the same sum to the other account.

If the whole operation fails, our database is still intact, and we have the sum of $2000. We can find ourselves in a worse scenario if just half of the above steps run successfully:

  1. reducing the first account balance by $500,
  2. failing to add the money to the second account because we provided the wrong number.

Because of the above, the first account has $500, and the second one remains with $1000. Therefore, a sum of $500 disappeared, and we lost the integrity of our data.

Introducing transactions

We can solve the above issue using a transaction. It can consist of more than one instruction and can be described with a few properties:

Atomicity

A transaction either succeeds wholly or entirely fails.

Consistency

During a transaction, we transition the database from one valid state to another.

Isolation

More than one transaction can run concurrently without risking an invalid state of our database. In our particular case, the second transaction would see the transferred money in one of the accounts but not both.

Durability

The changes from the transaction should persist permanently as soon as we commit them.

Transactions with PostgreSQL

To initiate a transaction block, we need the statement. PostgreSQL will execute all queries after that in a single transaction. When we run the statement, PostgreSQL stores our changes.

Thanks to using a transaction, we can discard the transaction if transferring the money to the second bank account fails for any reason. To do that, we need the statement.

If you want to know more about handling transactions with raw SQL, check out API with NestJS #76. Working with transactions using raw SQL queries

Nested writes with Prisma

Prisma offers quite a few ways of using transactions. One is through nested writes that perform multiple operations on many related records.

In one of the previous articles, we created schemas for users and their addresses.

addressSchema.prisma

userSchema.prisma

What’s important, we allow the creation of the user and the address through a single API request.

To create the user and the address in a single transaction, we can perform a nested write.

users.service.ts

If any of the above operations fail, Prisma rolls back the transaction. For example, if creating the address fails, the user is not added to the database.

Bulk operations

Another way of affecting multiple entities with Prisma is through bulk operations:

  • ,
  • ,
  • .

With the above methods, we can alter many records of the same type in a single transaction.

posts.service.ts

The transaction API in Prisma

The above solutions are helpful in specific situations. Besides them, Prisma also offers a generic API for transactions.

Sequential operations

The first way of using the transactions API is with sequential operations. By passing multiple database operations into the , we can run them sequentially in a transaction.

categories.service.ts

For example, if something goes wrong when deleting the category, the posts are not removed from the database. The transaction either fully succeeds or completely fails.

We can take the above a step further and use the we’ve created before.

categories.service.ts

The crucial part is that the method is not marked with the keyword. The method expects an array of , not regular promises. Because of that, we can’t use the method with , which is a bit unfortunate.

Interactive transactions

The above solution is perfectly fine if the operations in our transactions don’t affect each other. Sometimes, however, we need more control.

To perform interactive transactions, we must pass a function as an argument to the method. Its argument is an instance of a Prisma client. Each use of this client is encapsulated in a transaction.

The method we’ve used before in this article does not throw an error if one of the entities is not deleted. Let’s write an interactive transaction that changes that.

Prisma commits the transaction when it reaches the end of our function passed to the method. If there is any error along the way, Prisma rolls it back.

Interactive transactions are great if we want to operate on the result of a part of our transaction. In the above example, we throw an error if not all posts have been deleted. To do that, we need to check the result of a part of our transaction. We wouldn’t be able to do that using the sequential transactions approach.

Summary

In this article, we’ve discussed the idea of transactions and how to use them with Prisma. When doing that, we’ve compared various solutions, such as nested writes, bulk operations, and the transactions API. We’ve also used both the sequential operations approach and the interactive transactions. All of the above equips us with solutions for many different use cases we might encounter in our applications.

Series Navigation<< API with NestJS #103. Integration tests with PrismaAPI with NestJS #105. Implementing soft deletes with Prisma and middleware >>
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments