Implementing Database Transactions with Prisma in Next.js API Routes

User avatar placeholder
Written by Tamzid Ahmed

June 1, 2026

When building complex data operations in Next.js, ensuring data consistency across multiple database tables is critical. A single failure in a multi-step process can leave your database in an inconsistent state. Prisma’s transaction API provides a robust solution for these scenarios in your API routes, guaranteeing atomicity even in serverless environments.

Why Database Transactions Matter in Next.js API Routes

Imagine creating an order: you need to insert a customer record, an order record, and multiple line items. If the line item creation fails after the customer is created, you’re left with orphaned data. Database transactions ensure all steps succeed or none do, preserving data integrity. This is especially crucial in e-commerce, financial systems, and any application where partial updates are unacceptable.

Prisma Transaction Basics: A Quick Reference

Prisma’s $transaction method is designed for exactly this. It wraps multiple database operations in a single transaction, automatically rolling back on error and committing only when all operations succeed. The transaction uses the same Prisma Client instance but with a special transactional context.

Key Prisma Transaction Methods

  • $transaction: The core method for executing transactions. It takes a callback function that receives a transaction client (tx).
  • Prisma Client: Always use the transaction client (tx) inside the callback, not the global client, to ensure operations are part of the transaction.
  • Atomic operations: Each operation within the transaction is treated as a single unit, with isolation level set by your database (usually READ_COMMITTED).

Step-by-Step: Implementing a Transaction in a Next.js API Route

Let’s walk through a practical example: creating an order with customer and line items. First, ensure your Prisma client is set up correctly in a shared module.

  1. Import your Prisma client from a shared module: This ensures a single instance is reused across requests, avoiding connection leaks. Create a lib/prisma.ts file that exports a singleton:
  2. Validate input data before starting the transaction. Use a library like Zod to validate the request body. This prevents invalid data from causing transaction failures and ensures only clean data enters the database.
  3. Wrap operations in prisma.$transaction: The transaction callback receives a transaction client (tx). Always use this client for all database operations within the transaction.
  4. Perform all database operations using the transaction client: For example, create the customer (if not exists), create the order, then create line items. Each operation uses tx instead of the global prisma instance.
  5. Return the result from the callback to commit the transaction. If any operation fails, Prisma automatically rolls back all changes.
  6. Handle errors outside the transaction block: Catch errors and return appropriate HTTP responses. Never let database errors expose sensitive information to clients.

Here’s a complete code example:

import { prisma } from '@/lib/prisma';
import { orderSchema } from '@/schemas/order';

export default async function handler(req, res) {
  if (req.method !== 'POST') {
    return res.status(405).json({ error: 'Method not allowed' });
  }

  try {
    const validatedData = orderSchema.parse(req.body);
    const { customerId, items } = validatedData;

    const order = await prisma.$transaction(async (tx) => {
      const customer = await tx.customer.findUnique({ where: { id: customerId } });
      if (!customer) throw new Error('Customer not found');

      const newOrder = await tx.order.create({
        data: {
          customerId,
          total: items.reduce((sum, item) => sum + item.price * item.quantity, 0),
          status: 'PENDING'
        }
      });

      const lineItems = await Promise.all(
        items.map(item => 
          tx.lineItem.create({
            data: {
              orderId: newOrder.id,
              productId: item.productId,
              quantity: item.quantity,
              price: item.price
            }
          })
        )
      );

      return { order, lineItems };
    });

    res.status(201).json(order);
  } catch (error) {
    console.error('Transaction failed:', error);
    res.status(500).json({ error: 'Failed to create order' });
  }
}

Handling Errors and Rollbacks

Prisma automatically rolls back the transaction if any operation fails. However, you must catch the error and return a user-friendly response. Never let the transaction error bubble up without handling—it could leak sensitive data. Always log the error server-side and return a generic message to the client.

For specific errors (e.g., validation), you might want to catch them inside the transaction and rethrow with a custom message. But typically, Prisma will throw an error for database constraints (like unique key violation), which you can catch and handle appropriately. For example:

try {
  await tx.user.create({ data: { email: 'duplicate@example.com' } });
} catch (error) {
  if (error.code === 'P2002') {
    throw new Error('Email already exists');
  }
  throw error;
}

Serverless Considerations and Best Practices

Next.js API routes run on serverless infrastructure, which has constraints that impact transactions:

  • Timeouts: Most serverless platforms (like Vercel) have a maximum execution time of 10-15 seconds. Keep transactions short—ideally under 5 seconds—to avoid timeouts. Avoid complex operations like bulk imports inside transactions.
  • Connection pooling: Prisma uses a connection pool to manage database connections. In serverless environments, cold starts may create new connections. Configure your pool size appropriately in your prisma.schema file:
  • Idempotency: For critical operations, implement idempotency keys to prevent duplicate transactions on retries. Store the key in a database table and check before processing.
  • Database-specific limits: Some databases (like PostgreSQL) have transaction timeout settings. Ensure your database’s statement_timeout is set higher than your expected transaction time.

Remember: transactions lock rows or tables. Long-running transactions can block other operations. Always test your transaction logic under load to identify bottlenecks.

Conclusion

Implementing database transactions with Prisma in Next.js API routes ensures data integrity for complex operations. By following the step-by-step guide and best practices for serverless environments, you can build reliable systems that handle failures gracefully. Always test your transaction logic thoroughly, especially for edge cases like network failures or database constraints. Start small—use transactions for critical multi-table operations—and expand as needed. For additional security, integrate JWT authentication to protect your API routes from unauthorized access.

Leave a Comment