Transactions and batch queries

A database transaction refers to a sequence of read/write operations that are guaranteed to either succeed or fail as a whole. This section describes the ways in which the Prisma Client API supports transactions.

• For more in-depth examples and use cases, refer to the 📖 transactions guide.
• For information about transactions in general and the reasoning behind Prisma's current solutions, see ✍ Blog: How Prisma supports transactions.

Nested writes

A nested write lets you perform a single Prisma Client API call with multiple operations that touch multiple related records. For example, creating a user together with a post or updating an order together with an invoice. Prisma Client ensures that all operations succeed or fail as a whole.

The following example demonstrates a nested write with create:

// Create a new user with two posts in a
// single transaction
const newUser: User = await prisma.user.create({
  data: {
    email: 'alice@prisma.io',
    posts: {
      create: [
        { title: 'Join the Prisma Slack on https://slack.prisma.io' },
        { title: 'Follow @prisma on Twitter' },
      ],
    },
  },
})

The following example demonstrates a nested write with update:

// Change the author of a post in a single transaction
const updatedPost: Post = await prisma.post.update({
  where: { id: 42 },
  data: {
    author: {
      connect: { email: 'alice@prisma.io' },
    },
  },
})

Refer to the 📖 transactions guide for more examples.

Batch/bulk operations

The following bulk operations run as transactions:

• deleteMany
• updateMany
• createMany

Refer to the 📖 transactions guide for more examples.

The $transaction API

The following query returns all posts that match the provided filter as well as a count of all posts:

const [posts, totalPosts] = await prisma.$transaction([
  prisma.post.findMany({ where: { title: { contains: 'prisma' } } }),
  prisma.post.count(),
])

You can also use $queryRaw and $executeRaw inside a $transaction:

const [userList, updateUser] = await prisma.$transaction([
  prisma.$queryRaw`SELECT 'title' FROM User`,
  prisma.$executeRaw`UPDATE User SET name = 'Hello' WHERE id = 2;`,
])

Instead of immediately awaiting the result of each operation when it's performed, the operation itself is stored in a variable first which later is submitted to the database via a method called $transaction. Prisma Client will ensure that either all three create-operations or none of them succeed.

Note: Operations are executed according to the order they are placed in the transaction. Using a query in a transaction does not influence the order of operations in the query itself.

Refer to the 📖 transactions guide for more examples.

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["interactiveTransactions"]
}

Example

Imagine that you are building an online banking system. One of the actions to perform is to send money from one person to another.

As experienced developers, we want to make sure that during the transfer,

• the amount doesn't disappear
• the amount isn't doubled

This is a great use-case for interactive transactions because we need to perform logic in-between the writes to check the balance.

In the example below, Alice and Bob each have $100 in their account. If they try to send more money than they have, the transfer is rejected.

Alice is expected to be able to make 1 transfer for $100 while the other transfer would be rejected. This would result in Alice having $0 and Bob having $200.

import { PrismaClient } from '@prisma/client'
const prisma = new PrismaClient()

async function transfer(from: string, to: string, amount: number) {
  return await prisma.$transaction(async (prisma) => {
    // 1. Decrement amount from the sender.
    const sender = await prisma.account.update({
      data: {
        balance: {
          decrement: amount,
        },
      },
      where: {
        email: from,
      },
    })
    // 2. Verify that the sender's balance didn't go below zero.
    if (sender.balance < 0) {
      throw new Error(`${from} doesn't have enough to send ${amount}`)
    }
    // 3. Increment the recipient's balance by amount
    const recipient = prisma.account.update({
      data: {
        balance: {
          increment: amount,
        },
      },
      where: {
        email: to,
      },
    })
    return recipient
  })
}

async function main() {
  // This transfer is successful
  await transfer('alice@prisma.io', 'bob@prisma.io', 100)
  // This transfer fails because Alice doesn't have enough funds in her account
  await transfer('alice@prisma.io', 'bob@prisma.io', 100)
}

main()
  .catch(console.error)
  .finally(() => {
    prisma.$disconnect()
  })

In the example above, both update queries run within a database transaction. When the application reaches the end of the function, the transaction is committed to the database.

If your application encounters an error along the way, the async function will throw an exception and automatically rollback the transaction.

To catch the exception, you can wrap $transaction in a try-catch block:

try {
  await prisma.$transaction(async (prisma) => {
    // Code running in a transaction...
  })
} catch (err) {
  // Handle the rollback...
}

The interactive transaction API has a 2nd parameter with the following configuration options:

• maxWait: The maximum amount of time the Prisma Client will wait to acquire a transaction from the database. The default is 2 seconds.
• timeout: The maximum amount of time the interactive transaction can run before being canceled and rolled back. The default value is 5 seconds.

For example:

await prisma.$transaction(
  async (prisma) => {
    // Code running in a transaction...
  },
  {
    maxWait: 5000, // default: 2000
    timeout: 10000, // default: 5000
  }
)

Limitations

Join the Conversation on GitHub

If you'd like to see transactions supported in the future, please join the discussion on GitHub.