Working with Transactions in Prisma

Transactions are an essential database concept, ensuring that all of your read and write operations succeed, or if any of them fail, none of them commit changes to the database. This can be helpful when you have multiple operations that depend on each other, and you only want all of them to be committed together. In this blog post, we will explore how to work with transactions in Prisma, and I will walk you through a simple example of how to use Prisma transactions to manage dependencies between multiple operations.

The Scenario

We have a very basic schema set up, which consists of a user, a post, a profile, and a comment. The situation that we are going to demonstrate is how things can go wrong if we do not use transactions. To begin, we will first create a profile and then a post. This might represent a case where a new user signs up, and by default, a new profile and an initial post are created for them.

You can take a look at the schema below:

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "sqlite"
  url      = "file:./dev.db"
}

model User {
  id        Int       @id @default(autoincrement())
  email     String    @unique
  name      String?
  posts     Post[]
  comments  Comment[]
  profile   Profile?
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt
}

model Post {
  id        Int       @id @default(autoincrement())
  published Boolean   @default(false)
  title     String
  content   String?
  author    User      @relation(fields: [authorId], references: [id])
  authorId  Int
  comments  Comment[]
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt
}

model Profile {
  id        Int      @id @default(autoincrement())
  bio       String?
  user      User     @relation(fields: [userId], references: [id])
  userId    Int      @unique
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

model Comment {
  id        Int      @id @default(autoincrement())
  content   String
  published Boolean  @default(false)
  post      Post     @relation(fields: [postId], references: [id])
  postId    Int
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

For demonstration purposes, we are assuming the following:

1. We have a user with id=1 in the database.
2. There are no records in the Profile and Post tables for this user.

The Successful Case

First, let’s take a look at the success case, where we create a profile and a post without any issues. The following script accomplishes this:

const { PrismaClient } = require("@prisma/client");
const prisma = new PrismaClient();

async function main() {
  // Create a new profile for the user with id 1
  const profile = await prisma.profile.create({
    data: {
      bio: "Ryan is a software developer",
      user: {
        connect: {
          id: 1,
        },
      },
    },
  });

  // Create a first post for the user with id 1
  const post = await prisma.post.create({
    data: {
      title: "Hello World",
      content: "This is my first post",
      published: true,
      author: {
        connect: {
          id: 1,
        },
      },
    },
  });

  console.log("Created profile:", profile);
  console.log("Created post:", post);
}

main();

Running this script will create a new profile and a post for the user with id=1. Everything works as expected.

The Failed Case

Now, let’s see what happens when we attempt to create a post connected to a user that doesn’t exist. In the following script, we try to create a post connected to the user with id=2, which does not exist in the database:

const { PrismaClient } = require("@prisma/client");
const prisma = new PrismaClient();

async function main() {
  // Create a new profile for the user with id 1
  const profile = await prisma.profile.create({
    data: {
      bio: "Ryan is a software developer",
      user: {
        connect: {
          id: 1,
        },
      },
    },
  });

  // Create a first post for the user with id 1
  const post = await prisma.post.create({
    data: {
      title: "Hello World",
      content: "This is my first post",
      published: true,
      author: {
        connect: {
          id: 2, // This user does not exist!
        },
      },
    },
  });

  console.log("Created profile:", profile);
  console.log("Created post:", post);
}

main();

Running this script will throw an error, and the post creation will fail. However, the profile creation succeeds, and we will have a new profile without an associated post. This is not the desired behavior, as we want both the profile and the post creation to fail if any one of them does not succeed.

Using Prisma Transactions to Fix the Failed Case

To ensure that both the profile and post creation are either successful or fail together, we can use Prisma transactions. We will use a sequential transaction, which waits for one operation to complete before starting the next one.

Here’s how we can rewrite the script using a Prisma transaction:

import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();

async function main() {
  const [profile, post] = await prisma.$transaction([
    prisma.profile.create({
      data: {
        bio: "Ryan is a software developer",
        user: {
          connect: {
            id: 1,
          },
        },
      },
    }),
    prisma.post.create({
      data: {
        title: "Hello World",
        content: "This is my first post",
        published: true,
        author: {
          connect: {
            id: 2,
          },
        },
      },
    }),
  ]);

  console.log(profile);
  console.log(post);
}

main();

Now, if we run the script again with the non-existent user id=2, both the profile and post creation will fail, and no new records will be added to the database.

Conclusion

Transactions are great if you have sequential operations, or if you need to do some work in between before any records are committed to the database. In this example, we have seen how using Prisma transactions can help us manage dependencies between multiple operations and ensure that either all of them succeed, or none of them are committed to the database.

We used a sequential transaction in this example, which waits for one operation to complete before starting the next one. However, Prisma also supports interactive transactions, which allow for more flexibility and control over the transaction process. Stay tuned for another blog post where we will explore interactive transactions in Prisma.