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.

About transactions in Prisma

Prisma provides the following options for using transactions:

  • Nested writes: use the Prisma Client API to process multiple operations on one or more related records inside the same transaction.
  • Batch / bulk transactions: to process one or more operations in bulk via updateMany, deleteMany, and createMany.
  • The $transaction calls:
    • $transaction<R>(queries: PrismaPromise<R>[]): Promise<R[]>: Pass an array of Prisma Client queries to be executed sequentially inside of a transaction.
    • $transaction<R>(fn: (prisma: PrismaClient) => R): R: Pass a function that can contain user code including Prisma Client queries, non-Prisma code and other control flow to be executed in a transaction.

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 $transaction API can be used in two ways:

  • Sequential operations: Pass an array of Prisma Client queries to be executed sequentially inside of a transaction.

    $transaction<R>(queries: PrismaPromise<R>[]): Promise<R[]>

  • Interactive transactions (Preview): Pass a function that can contain user code including Prisma Client queries, non-Prisma code and other control flow to be executed in a transaction.

    $transaction<R>(fn: (prisma: PrismaClient) => R): R

Note: to use interactive transactions you will need to enable it as a Preview feature.

Sequential Prisma Client operations

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 raw queries inside of a $transaction:

SQL Databases
MongoDB
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 succeed 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.

Interactive transactions (in Preview)

Sometimes you need more control over what queries execute within a transaction. Interactive transactions are meant to provide you with an escape hatch.

Note: If you use interactive transactions, then you cannot use the Data Proxy at the same time.

To enable interactive transactions, add interactiveTransactions to the generator of your Prisma Schema:

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

Then you can pass an async function into $transaction.

Let's look at an 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()
.then(async () => {
await prisma.$disconnect()
})
.catch(async (e) => {
console.error(e)
await prisma.$disconnect()
process.exit(1)
})

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 value 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
}
)

Use interactive transactions with caution. Keeping transactions open for a long time hurts database performance and can even cause deadlocks. Try to avoid performing network requests and executing slow queries inside your transaction functions. We recommend you get in and out as quick as possible!

Limitations

Transaction Isolation Level

Transaction isolation level is not currently configurable at a Prisma level and is not explicitly set by Prisma.

Join the Conversation on GitHub

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

Edit this page on GitHub