Prisma $transaction: Keeping Orders and Inventory Consistent

Problem: Partial Writes in E‑Commerce

Consider a typical order‑processing flow: you insert a new Order record, then decrement the Product stock. If the first query succeeds but the second fails—perhaps due to a network hiccup or a constraint violation—the database ends up with an order that has no matching inventory change. The user sees a successful order status, but the stock count is wrong, leading to overselling.

In relational databases, this inconsistency is avoided by wrapping the related writes in a transaction: either all statements commit or none do. The challenge is to do this cleanly in a Node.js application that uses Prisma Client.

Thesis: Prisma’s $transaction API Gives You All‑Or‑Nothing

Prisma Client exposes a $transaction method that accepts either an array of query objects or a callback function. Internally it opens a database transaction, executes each query sequentially, and commits at the end. If any query throws, Prisma rolls back automatically, leaving the database untouched.

1. Using the Array Form

The array form is the most straightforward. Each element is a Prisma query that returns a promise. Prisma runs them in order inside a single transaction.

// app/orders.js
async function createOrderWithStock(userId, productId, quantity) {
  const { PrismaClient } = require('@prisma/client');
  const client = new PrismaClient();

  try {
    const [order, product] = await client.$transaction([
      client.order.create({
        data: { userId, total: 99.99, status: 'pending' }
      }),
      client.product.update({
        where: { id: productId },
        data: { stock: { decrement: quantity } }
      })
    ]);
    console.log('Order ID:', order.id);
    return order;
  } catch (err) {
    console.error('Transaction failed:', err.message);
    throw err;
  }
}

Key points:

• Run location: Inside an async function that has access to a Prisma client instance.
• Permissions: The database user must have BEGIN, COMMIT, and ROLLBACK rights.
• Result shape: $transaction returns an array of results that matches the order of the queries.
• Risk: If the array contains many heavy queries, the transaction can hold a database connection for a long time, potentially exhausting the pool.

2. Using the Callback Form

When you need to build the query list dynamically or perform some logic between queries, you can pass a callback. Prisma will open a transaction, call your function with the Prisma client, and commit or rollback based on the returned promise.

async function createOrderWithStockCallback(userId, productId, quantity) {
  const { PrismaClient } = require('@prisma/client');
  const client = new PrismaClient();

  try {
    const order = await client.$transaction(async (tx) => {
      // tx is a Prisma client scoped to the transaction
      const newOrder = await tx.order.create({
        data: { userId, total: 99.99, status: 'pending' }
      });

      await tx.product.update({
        where: { id: productId },
        data: { stock: { decrement: quantity } }
      });

      return newOrder; // whatever you return becomes the transaction result
    });
    console.log('Order ID:', order.id);
    return order;
  } catch (err) {
    console.error('Transaction failed:', err.message);
    throw err;
  }
}

Use the callback when you need to decide which queries to run at runtime or when you want to keep the transaction body readable.

3. Trade‑offs and Limitations

• No Nested Transactions: Prisma does not support calling $transaction from within another $transaction. If you need nested logic, split the work into separate transactions, but be aware that partial failures will still leave the database in an inconsistent state unless you design compensating actions.
• Connection Pool Exhaustion: Long‑running transactions hold a connection until commit or rollback. If you perform expensive calculations or call external services inside the transaction callback, keep them outside or use a lightweight approach.
• Edge Runtime: The Prisma Preview 'edge' runtime (e.g., Vercel Edge Functions) does not support transactions. You need a Node.js or similar server environment that can maintain a persistent database connection.

4. Practical Checklist Before Using $transaction

1. Verify that the Prisma client version is ≥ 2.0.0.
2. Confirm that your database user has transaction privileges.
3. Keep the query set small—ideally under 5 statements.
4. Move any heavy computation outside the transaction callback.
5. Wrap the call in a try/catch to surface errors to the caller.
6. Log the transaction duration. In production, a threshold of 500 ms is a good starting point.
7. Test failure paths: throw an error after the first query and confirm that the second query is not applied.

Conclusion

Prisma’s $transaction API turns a series of related database writes into a single, reliable unit of work. By bundling order creation and inventory updates, you protect your data from partial failures and simplify error handling in your Node.js code. Just remember the limits—no nested transactions and be mindful of connection pool usage—and you’ll have a robust foundation for any feature that needs atomicity.