Node.js ORM Transactions Across Multiple Tables

This article provides a practical guide on how to safely execute database transactions across multiple tables using popular Node.js Object-Relational Mappers (ORMs). You will learn the core concepts of database transactions, explore step-by-step code implementations using Prisma and Sequelize, and discover best practices to maintain data integrity and prevent partial database writes when errors occur.

Understanding Database Transactions

A database transaction is a sequence of multiple operations performed as a single logical unit of work. To maintain data integrity, transactions must adhere to the ACID properties (Atomicity, Consistency, Isolation, Durability).

In multi-table operations—such as creating a user account and immediately generating a corresponding profile or processing an e-commerce order while deducting stock—atomicity is crucial. If any single query fails, the entire transaction must roll back, leaving the database in its original state.

Implementing Transactions with Prisma

Prisma handles multi-table transactions cleanly using its interactive transactions API. This allows you to pass a transaction client through a function block, ensuring all database calls inside that block execute under the same transaction.

const { PrismaClient } = require('@prisma/client');
const prisma = new PrismaClient();

async function createOrderAndDeductStock(userId, items) {
  try {
    // Start an interactive transaction
    const result = await prisma.$transaction(async (tx) => {
      // 1. Create the order
      const order = await tx.order.create({
        data: {
          userId: userId,
          total: items.reduce((sum, item) => sum + item.price, 0),
        },
      });

      // 2. Loop through items to update inventory and create order items
      for (const item of items) {
        // Update product stock
        const product = await tx.product.update({
          where: { id: item.productId },
          data: {
            stock: { decrement: item.quantity },
          },
        });

        // Ensure stock did not go negative
        if (product.stock < 0) {
          throw new Error(`Insufficient stock for product ID: ${item.productId}`);
        }

        // Create order item link
        await tx.orderItem.create({
          data: {
            orderId: order.id,
            productId: item.productId,
            quantity: item.quantity,
          },
        });
      }

      return order;
    });

    console.log('Transaction completed successfully:', result);
    return result;
  } catch (error) {
    console.error('Transaction rolled back due to error:', error.message);
    throw error;
  }
}

Key Takeaway for Prisma:

Inside the $transaction block, you must execute your queries using the transaction client (tx) rather than the global prisma client. If any error is thrown inside the block, Prisma automatically rolls back all changes.

Implementing Transactions with Sequelize

Sequelize supports both manual (unmanaged) and automatic (managed) transactions. Managed transactions are recommended because Sequelize automatically commits the transaction if the block resolves successfully, or rolls it back if an error is thrown.

const { Sequelize, DataTypes } = require('sequelize');
const sequelize = new Sequelize('database', 'username', 'password', {
  dialect: 'postgres',
});

async function createUserAndProfile(userData, profileData) {
  try {
    // Pass a callback to sequelize.transaction for a managed transaction
    const result = await sequelize.transaction(async (t) => {
      
      // 1. Create User (pass the transaction object in options)
      const user = await User.create(userData, { transaction: t });

      // 2. Create Profile linked to the new User ID
      const profile = await Profile.create(
        {
          ...profileData,
          userId: user.id,
        },
        { transaction: t }
      );

      return { user, profile };
    });

    console.log('User and Profile created successfully:', result);
    return result;
  } catch (error) {
    console.error('Transaction failed and was rolled back:', error.message);
    throw error;
  }
}

Key Takeaway for Sequelize:

You must explicitly pass the transaction instance ({ transaction: t }) as an option to every query within the block. Forgetting to pass the transaction object will result in that query running outside of the transaction scope, leading to potential data mismatches.

Best Practices for Safe Multi-Table Transactions

To ensure maximum safety and application performance when executing transactions: