Skip to main content

Next.js Database Transactions

Introduction​

Database transactions are essential for maintaining data integrity in your applications. A transaction is a sequence of operations performed as a single logical unit of work. In simpler terms, it's an all-or-nothing approach—either all operations within the transaction succeed, or none of them do.

In Next.js applications, implementing proper transaction management ensures your database operations remain consistent, especially when multiple related operations need to happen together. For example, if you're building an e-commerce platform, when a customer places an order, you need to:

  1. Create the order record
  2. Update inventory counts
  3. Process payment
  4. Create shipping information

If any of these steps fail, you'd want to roll back all of them to prevent inconsistent data.

In this guide, we'll explore how to implement database transactions in Next.js applications using popular ORMs and database clients.

Understanding Database Transactions​

Core Transaction Properties (ACID)​

Database transactions follow the ACID properties:

  • Atomicity: All operations complete successfully or none of them do
  • Consistency: The database remains in a valid state before and after the transaction
  • Isolation: Transactions are isolated from each other until they're completed
  • Durability: Once a transaction is committed, it remains committed

Why Transactions Matter in Next.js​

In server-side rendering and API routes, Next.js often handles multiple database operations to fulfill a single request. Without transactions, partially completed operations can lead to data inconsistencies if something fails mid-process.

Implementing Transactions with Prisma​

Prisma is one of the most popular ORMs used with Next.js applications. It provides a simple way to handle transactions.

Basic Transaction Example​

Here's how to implement a transaction using Prisma in a Next.js API route:

javascript
// pages/api/create-order.js
import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

export default async function handler(req, res) {
  if (req.method !== 'POST') {
    return res.status(405).json({ message: 'Method not allowed' });
  }
  
  const { userId, productId, quantity } = req.body;
  
  try {
    // Execute operations as a transaction
    const result = await prisma.$transaction(async (prisma) => {
      // 1. Get product and check inventory
      const product = await prisma.product.findUnique({
        where: { id: productId },
      });
      
      if (product.stockCount < quantity) {
        throw new Error('Not enough items in stock');
      }
      
      // 2. Create order
      const order = await prisma.order.create({
        data: {
          userId,
          status: 'PENDING',
        },
      });
      
      // 3. Create order item
      const orderItem = await prisma.orderItem.create({
        data: {
          orderId: order.id,
          productId,
          quantity,
          price: product.price,
        },
      });
      
      // 4. Update product stock count
      const updatedProduct = await prisma.product.update({
        where: { id: productId },
        data: {
          stockCount: {
            decrement: quantity,
          },
        },
      });
      
      return {
        order,
        orderItem,
        updatedProduct,
      };
    });
    
    return res.status(200).json(result);
  } catch (error) {
    console.error('Transaction failed:', error);
    return res.status(500).json({ error: error.message });
  }
}

In this example, if any operation fails (like if there's not enough stock), none of the changes will be applied to the database.

Transaction Isolation Levels​

Prisma allows you to specify isolation levels for your transactions:

javascript
const result = await prisma.$transaction(
  async (tx) => {
    // Your transaction operations
  },
  {
    isolationLevel: Prisma.TransactionIsolationLevel.Serializable, // Highest isolation level
  }
);

Common isolation levels include:

  • ReadUncommitted: Lowest isolation level
  • ReadCommitted: Prevents dirty reads
  • RepeatableRead: Prevents non-repeatable reads
  • Serializable: Highest isolation level, prevents phantom reads

Implementing Transactions with Sequelize​

Sequelize is another popular ORM that works well with Next.js.

javascript
// pages/api/transfer-funds.js
import { sequelize, Account } from '../../models';

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

  const { fromAccountId, toAccountId, amount } = req.body;
  
  // Start a transaction
  const t = await sequelize.transaction();
  
  try {
    // Deduct from source account
    const fromAccount = await Account.findByPk(fromAccountId, { transaction: t });
    if (fromAccount.balance < amount) {
      throw new Error('Insufficient funds');
    }
    
    await Account.update(
      { balance: fromAccount.balance - amount },
      { 
        where: { id: fromAccountId },
        transaction: t 
      }
    );
    
    // Add to destination account
    const toAccount = await Account.findByPk(toAccountId, { transaction: t });
    await Account.update(
      { balance: toAccount.balance + amount },
      { 
        where: { id: toAccountId },
        transaction: t 
      }
    );
    
    // Commit the transaction
    await t.commit();
    
    return res.status(200).json({ 
      message: 'Transfer completed successfully',
      fromBalance: fromAccount.balance - amount,
      toBalance: toAccount.balance + amount
    });
  } catch (error) {
    // If anything fails, roll back the transaction
    await t.rollback();
    console.error('Transaction failed:', error);
    return res.status(500).json({ error: error.message });
  }
}

Using Async/Await with Sequelize Transactions​

You can also use a callback approach for cleaner transaction handling:

javascript
try {
  const result = await sequelize.transaction(async (t) => {
    const fromAccount = await Account.findByPk(fromAccountId, { transaction: t });
    if (fromAccount.balance < amount) {
      throw new Error('Insufficient funds');
    }
    
    await Account.update(
      { balance: fromAccount.balance - amount },
      { 
        where: { id: fromAccountId },
        transaction: t 
      }
    );
    
    await Account.update(
      { balance: sequelize.literal(`balance + ${amount}`) },
      { 
        where: { id: toAccountId },
        transaction: t 
      }
    );
    
    return { message: 'Transfer successful' };
  });
  
  return res.status(200).json(result);
} catch (error) {
  console.error('Transaction failed:', error);
  return res.status(500).json({ error: error.message });
}

Raw SQL Transactions with node-postgres​

If you're using raw SQL with pg (node-postgres), you can implement transactions as follows:

javascript
// pages/api/update-inventory.js
import { Pool } from 'pg';

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
});

export default async function handler(req, res) {
  if (req.method !== 'POST') {
    return res.status(405).json({ message: 'Method not allowed' });
  }
  
  const { productId, quantity } = req.body;
  const client = await pool.connect();
  
  try {
    await client.query('BEGIN'); // Start transaction
    
    // Check if enough inventory
    const inventoryResult = await client.query(
      'SELECT stock FROM products WHERE id = $1 FOR UPDATE',
      [productId]
    );
    
    if (inventoryResult.rows[0].stock < quantity) {
      throw new Error('Not enough inventory');
    }
    
    // Update inventory
    await client.query(
      'UPDATE products SET stock = stock - $1 WHERE id = $2',
      [quantity, productId]
    );
    
    // Record the transaction
    await client.query(
      'INSERT INTO inventory_transactions (product_id, quantity, type) VALUES ($1, $2, $3)',
      [productId, quantity, 'OUTBOUND']
    );
    
    await client.query('COMMIT'); // Commit transaction
    
    return res.status(200).json({ message: 'Inventory updated successfully' });
  } catch (error) {
    await client.query('ROLLBACK'); // Rollback transaction on error
    console.error('Transaction failed:', error);
    return res.status(500).json({ error: error.message });
  } finally {
    client.release(); // Release the client back to the pool
  }
}

Best Practices for Transactions in Next.js​

1. Keep Transactions Short and Focused​

Long-running transactions can lead to performance issues and database locks. Keep your transactions focused on the specific operations that need to happen atomically.

javascript
// Good: Focused transaction
await prisma.$transaction(async (tx) => {
  await tx.order.create({ data: orderData });
  await tx.inventory.update({ data: inventoryData });
});

// Avoid: Mixing transaction with non-critical operations
await prisma.$transaction(async (tx) => {
  await tx.order.create({ data: orderData });
  await tx.inventory.update({ data: inventoryData });
  await sendOrderConfirmationEmail(orderData); // Don't do this in a transaction
});

2. Handle Errors Properly​

Always include proper error handling with transactions:

javascript
try {
  await prisma.$transaction(async (tx) => {
    // Transaction operations
  });
  
  // Success response
} catch (error) {
  // Log the error with details
  console.error('Transaction failed:', { 
    error: error.message,
    stack: error.stack,
    context: 'Order creation process'
  });
  
  // Return appropriate error response
  return res.status(500).json({ 
    error: 'Failed to process order',
    details: process.env.NODE_ENV === 'development' ? error.message : undefined
  });
}

3. Use Timeouts for Transactions​

Set timeouts to prevent long-running transactions:

javascript
// Prisma transaction with timeout
await prisma.$transaction(
  async (tx) => {
    // Transaction operations
  },
  {
    timeout: 5000, // 5 seconds timeout
  }
);

// Sequelize transaction with timeout
await sequelize.transaction({
  timeout: 5000 // 5 seconds
}, async (t) => {
  // Transaction operations
});

4. Consider Retry Logic for Transient Failures​

Some database errors are temporary and can be resolved by retrying the transaction:

javascript
async function executeTransactionWithRetry(transactionFn, maxRetries = 3) {
  let retries = 0;
  
  while (retries < maxRetries) {
    try {
      return await transactionFn();
    } catch (error) {
      if (isTransientError(error) && retries < maxRetries - 1) {
        retries++;
        await new Promise(resolve => setTimeout(resolve, 1000 * retries));
        continue;
      }
      throw error;
    }
  }
}

// Usage
await executeTransactionWithRetry(async () => {
  return await prisma.$transaction(async (tx) => {
    // Transaction operations
  });
});

Real-World Examples​

E-Commerce Checkout Process​

Here's a complete example of a checkout process in a Next.js API route:

javascript
// pages/api/checkout.js
import { PrismaClient } from '@prisma/client';
import { processPayment } from '../../lib/payment';
import { sendOrderConfirmation } from '../../lib/email';

const prisma = new PrismaClient();

export default async function handler(req, res) {
  if (req.method !== 'POST') {
    return res.status(405).json({ message: 'Method not allowed' });
  }
  
  const { userId, cartItems, shippingAddress, paymentMethod } = req.body;
  
  try {
    // First, validate the payment method outside the transaction
    const paymentValidation = await validatePaymentMethod(paymentMethod);
    if (!paymentValidation.valid) {
      return res.status(400).json({ error: 'Invalid payment method' });
    }
    
    // Execute checkout process as a transaction
    const result = await prisma.$transaction(async (tx) => {
      // 1. Verify all products are in stock
      for (const item of cartItems) {
        const product = await tx.product.findUnique({
          where: { id: item.productId },
        });
        
        if (!product || product.stockCount < item.quantity) {
          throw new Error(`Product ${item.productId} is out of stock`);
        }
      }
      
      // 2. Calculate order total
      let orderTotal = 0;
      const orderItems = [];
      
      for (const item of cartItems) {
        const product = await tx.product.findUnique({
          where: { id: item.productId },
          select: { id: true, price: true }
        });
        
        const lineTotal = product.price * item.quantity;
        orderTotal += lineTotal;
        
        orderItems.push({
          productId: item.productId,
          quantity: item.quantity,
          price: product.price
        });
      }
      
      // 3. Create the order
      const order = await tx.order.create({
        data: {
          userId,
          total: orderTotal,
          status: 'PENDING',
          shippingAddress: JSON.stringify(shippingAddress),
          items: {
            create: orderItems.map(item => ({
              productId: item.productId,
              quantity: item.quantity,
              price: item.price
            }))
          }
        },
        include: {
          items: true
        }
      });
      
      // 4. Update inventory for each product
      for (const item of cartItems) {
        await tx.product.update({
          where: { id: item.productId },
          data: {
            stockCount: {
              decrement: item.quantity
            }
          }
        });
      }
      
      // 5. Clear the user's cart
      await tx.cart.deleteMany({
        where: { userId }
      });
      
      return { order };
    });
    
    // Process payment after the transaction is complete
    // (This should be idempotent and have its own error handling)
    const paymentResult = await processPayment({
      orderId: result.order.id,
      amount: result.order.total,
      paymentMethod
    });
    
    if (paymentResult.success) {
      // Update order status (in a separate operation)
      await prisma.order.update({
        where: { id: result.order.id },
        data: { status: 'PAID' }
      });
      
      // Send confirmation email (non-critical operation)
      try {
        await sendOrderConfirmation(result.order.id);
      } catch (emailError) {
        console.error('Failed to send confirmation email:', emailError);
        // Don't fail the request if email sending fails
      }
    } else {
      // Handle payment failure
      await prisma.order.update({
        where: { id: result.order.id },
        data: { status: 'PAYMENT_FAILED' }
      });
      
      return res.status(400).json({ 
        error: 'Payment processing failed',
        orderId: result.order.id,
        paymentError: paymentResult.error
      });
    }
    
    return res.status(200).json({
      success: true,
      orderId: result.order.id,
      total: result.order.total,
      status: 'PAID'
    });
  } catch (error) {
    console.error('Checkout process failed:', error);
    return res.status(500).json({ error: error.message });
  }
}

User Registration with Profile Creation​

Another common use case for transactions is user registration with related profile data:

javascript
// pages/api/register.js
import { PrismaClient } from '@prisma/client';
import { hash } from 'bcrypt';

const prisma = new PrismaClient();

export default async function handler(req, res) {
  if (req.method !== 'POST') {
    return res.status(405).json({ message: 'Method not allowed' });
  }
  
  const { email, password, name, bio, location } = req.body;
  
  try {
    // Check if user already exists
    const existingUser = await prisma.user.findUnique({
      where: { email }
    });
    
    if (existingUser) {
      return res.status(400).json({ error: 'Email already registered' });
    }
    
    // Hash the password
    const hashedPassword = await hash(password, 10);
    
    // Create user and profile in a transaction
    const result = await prisma.$transaction(async (tx) => {
      // Create user
      const user = await tx.user.create({
        data: {
          email,
          password: hashedPassword
        }
      });
      
      // Create profile
      const profile = await tx.profile.create({
        data: {
          userId: user.id,
          name,
          bio,
          location
        }
      });
      
      // Create initial settings
      const settings = await tx.userSettings.create({
        data: {
          userId: user.id,
          emailNotifications: true,
          theme: 'light'
        }
      });
      
      return { 
        user: {
          id: user.id,
          email: user.email
        },
        profile,
        settings
      };
    });
    
    return res.status(201).json({ 
      message: 'User registered successfully',
      user: result.user
    });
  } catch (error) {
    console.error('Registration failed:', error);
    return res.status(500).json({ error: error.message });
  }
}

Summary​

Database transactions are a critical aspect of building reliable Next.js applications that interact with databases. By ensuring operations are atomic, consistent, isolated, and durable, you can maintain data integrity even when complex sequences of operations are required.

In this guide, we've explored:

  • The fundamentals of database transactions and their ACID properties
  • Implementing transactions with popular ORMs like Prisma and Sequelize
  • Working with raw SQL transactions using node-postgres
  • Best practices for transaction management in Next.js
  • Real-world examples for e-commerce and user registration scenarios

Remember that transactions should be:

  • Short and focused on operations that must succeed together
  • Well-protected with proper error handling
  • Used appropriately for critical data operations, not for everything

Additional Resources​

Exercises​

  1. Basic Transaction: Create a simple Next.js API route that transfers funds between two accounts using transactions.

  2. Error Handling: Modify the e-commerce checkout example to include specific error handling for different scenarios (out of stock, payment failure, etc.)

  3. Advanced: Implement a content management system where publishing an article involves updating multiple tables in a transaction (articles, tags, author stats, etc.)

  4. Optimistic Locking: Research and implement optimistic locking with version numbers to handle concurrent updates to the same record.

  5. Performance Testing: Create a test that compares the performance of individual queries versus the same operations wrapped in a transaction.


If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)