Writing transactions with Prisma

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:

• taking $500 from one account,
• 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:

• reducing the first account balance by $500,
• 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 BEGIN statement. PostgreSQL will execute all queries after that in a single transaction. When we run the COMMIT statement, PostgreSQL stores our changes.

1BEGIN;
2 
3UPDATE bank_accounts
4SET balance = 500
5WHERE id = 1;
6 
7UPDATE bank_accounts
8SET balance = 1500
9WHERE id = 2;
10 
11COMMIT;

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 ROLLBACK 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.

1model Address {
2  id        Int      @default(autoincrement()) @id
3  street    String
4  city      String
5  country   String
6  user      User?
7}

1model User {
2  id        Int      @id @default(autoincrement())
3  email     String   @unique
4  name      String
5  password  String
6  address   Address? @relation(fields: [addressId], references: [id])
7  addressId Int?     @unique
8  posts     Post[]
9}

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.

1import { Injectable } from '@nestjs/common';
2import { PrismaService } from '../prisma/prisma.service';
3import { CreateUserDto } from './dto/createUser.dto';
4 
5@Injectable()
6export class UsersService {
7  constructor(private readonly prismaService: PrismaService) {}
8 
9  async create(user: CreateUserDto) {
10    const address = user.address;
11    return this.prismaService.user.create({
12      data: {
13        ...user,
14        address: {
15          create: address,
16        },
17      },
18      include: {
19        address: true,
20      },
21    });
22  }
23 
24  // ...
25}

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:

• deleteMany,
• updateMany,
• createMany.

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

1import { Injectable } from '@nestjs/common';
2import { PrismaService } from '../prisma/prisma.service';
3 
4@Injectable()
5export class PostsService {
6  constructor(private readonly prismaService: PrismaService) {}
7 
8  // ...
9 
10  deleteMultiplePosts(ids: number[]) {
11    return this.prismaService.post.deleteMany({
12      where: {
13        id: {
14          in: ids,
15        },
16      },
17    });
18  }
19}

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 prismaService.$transaction, we can run them sequentially in a transaction.

1import { Injectable } from '@nestjs/common';
2import CategoryNotFoundException from './exceptions/categoryNotFound.exception';
3import { PrismaService } from '../prisma/prisma.service';
4 
5@Injectable()
6export default class CategoriesService {
7  constructor(private readonly prismaService: PrismaService) {}
8 
9  async deleteCategoryWithPosts(id: number) {
10    const category = await this.getCategoryById(id);
11 
12    const postIds = category.posts.map((post) => post.id);
13 
14    return this.prismaService.$transaction([
15      this.prismaService.post.deleteMany({
16        where: {
17          id: {
18            in: postIds,
19          },
20        },
21      }),
22      this.prismaService.category.delete({
23        where: {
24          id,
25        },
26      }),
27    ]);
28  }
29 
30  async getCategoryById(id: number) {
31    const category = await this.prismaService.category.findUnique({
32      where: {
33        id,
34      },
35      include: {
36        posts: true,
37      },
38    });
39    if (!category) {
40      throw new CategoryNotFoundException(id);
41    }
42    return category;
43  }
44 
45  // ...
46}

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 deleteMultiplePosts we’ve created before.

1import { Injectable } from '@nestjs/common';
2import { PrismaService } from '../prisma/prisma.service';
3import { PostsService } from '../posts/posts.service';
4 
5@Injectable()
6export default class CategoriesService {
7  constructor(
8    private readonly prismaService: PrismaService,
9    private readonly postsService: PostsService,
10  ) {}
11 
12  async deleteCategoryWithPosts(id: number) {
13    const category = await this.getCategoryById(id);
14 
15    const postIds = category.posts.map((post) => post.id);
16 
17    return this.prismaService.$transaction([
18      this.postsService.deleteMultiplePosts(postIds),
19      this.prismaService.category.delete({
20        where: {
21          id,
22        },
23      }),
24    ]);
25  }
26 
27  // ...
28}

The crucial part is that the deleteMultiplePosts method is not marked with the async keyword. The prismaService.$transaction method expects an array of Prisma.PrismaPromise, not regular promises. Because of that, we can’t use the deleteCategory method with prismaService.$transaction, 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 prismaService.$transaction method. Its argument is an instance of a Prisma client. Each use of this client is encapsulated in a transaction.

The deleteMany 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.

1import { Injectable, NotFoundException } from '@nestjs/common';
2import { PrismaService } from '../prisma/prisma.service';
3 
4@Injectable()
5export class PostsService {
6  constructor(private readonly prismaService: PrismaService) {}
7  
8  deleteMultiplePosts(ids: number[]) {
9    return this.prismaService.$transaction(async (transactionClient) => {
10      const deleteResponse = await transactionClient.post.deleteMany({
11        where: {
12          id: {
13            in: ids,
14          },
15        },
16      });
17      if (deleteResponse.count !== ids.length) {
18        throw new NotFoundException('One of the posts cold not be deleted');
19      }
20    });
21  }
22  
23  // ...
24}

Prisma commits the transaction when it reaches the end of our function passed to the prismaService.$transaction 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.