Working with Transactions in Prisma

In some cases, we may need to perform multiple operations against a database in a single workflow. We can run into issues when one or more of those operations fails but the others succeed. This is especially true if we need perform some work in between database operations, like getting some information about a user from a third party API or similar.

Transactions help us to guard against database inconsistencies. They help to ensure that all of our read and write operations succeed, or if any of them fail, none of them commit changes to the database. This can be helpful when we have multiple operations that depend on each other, and we only want all of them to be committed together.

In this blog post, we will explore how to work with interactive transactions in Prisma. We’ll look at a real-world example where we need to store information about a user both in our local database and in Auth0.

Understanding Interactive Transactions in Prisma

In this blog post, we will explore interactive transactions in Prisma, a powerful feature to ensure data consistency when working with multiple data sources or in between complex application logic. We will learn how to set up and use interactive transactions to maintain valid data states and prevent discrepancies.

Sequential Operations in Prisma Recap

Previously, we discussed sequential operations in Prisma, where we execute multiple Prisma calls one after another, making sure each call succeeds before moving on to the next one. If something goes wrong, the whole operation is canceled, which ensures data consistency and prevents messy scenarios. This works best when all operations in the sequence are Prisma calls.

A sequential operation example:

await prisma.$transaction([
  prisma.user.create({...}),
  prisma.post.create({...})
]);

Interactive Transactions: A more Complex Scenario

Interactive transactions come into play when we have non-Prisma calls, such as network requests or other async functions, sandwiched between Prisma calls. For instance, we may want to:

• Create a user account in our database using Prisma
• Create and get information about the user from an external API (e.g., Auth0)
• Update the user record in our database with the newly fetched data

This scenario is a good candidate for an interactive transaction, as errors can cause an inconsistent state between the local and external database (Auth0).

Setting up the Example: Creating a Local User and Auth0 User

Prisma provides excellent support for interactive transactions in its latest versions. Let’s look at an example where we create a user account locally in our database and, simultaneously, create an account in Auth0, with Auth0 being our authentication provider.

We aim to create a user record in both databases (local and Auth0) and store Auth0 user ID information back in our local database. If this operation fails, it may result in an invalid state between the two databases.

Here’s the initial code setup:

// Setting up test email and initial local user creation
const email = "test@example.com";
const localUser = await prisma.user.create({
  data: {
    email,
    name: "Jane Doe",
  },
});

// Creating a user in Auth0 using the Auth0 Management API via Node.js SDK
const tempPassword = generateRandomPassword();
const auth0User = await createUser(email, tempPassword);

// Updating the local user record with Auth0 user ID
const updatedLocalUser = await prisma.user.update({
  where: { id: localUser.id },
  data: { authProviderId: auth0User.user_id },
});

Running this will create the user in our local database, then create it in Auth0, and finally update our local database with the user’s ID form Auth0.

The problem that we will run into is that if Auth0 returns an error, we’ll still end up with a record in our local database. This is problematic because we’ll be out of sync between our database and Auth0. An easy way to produce an error here is to just try creating the same user again. If we don’t have our User model set up to have the email field be unique, our database will gladly accept a new user record. However, Auth0 will throw an error because the email already exists. The end result is we’ll have a record in our database without a corresponding record in Auth0.

Adding Interactive Transactions for Better Protection

We will now introduce the interactive transaction to the above example. Instead of directly executing the operations, we will wrap them inside the $transaction method, which provides a new instance of Prisma client tied to the transaction context.

await prisma.$transaction(async (tx) => {
  const localUser = await tx.user.create({
    data: {
      email,
      name: "Jane Doe",
    },
  });

  const auth0User = await createUser(email, tempPassword);

  const updatedLocalUser = await tx.user.update({
    where: { id: localUser.id },
    data: { authProviderId: auth0User.user_id },
  });
});

With this change, if any step fails, none of the database operations are committed, and our data remains in a consistent state.

Important Note: Use the Transaction-Specific Prisma Client Instance

The tx parameter here is a dedicated instance of Prisma Client that is specific ot the context of the transaction. It’s important that we use this instance and now the prisma instance that is set up initially, otherwise the transaction protections won’t be applied.

Conclusion

In this blog post, we looked at interactive transactions in Prisma and learned how to use them in complex scenarios involving both local and external databases. By applying interactive transactions along with sequential operations, we now have powerful tools at our disposal to ensure our applications’ data integrity.

To dive deeper into transactions with Prisma, check out the full course at howtoprisma.com.