Atomic Multi‑Row Writes in Sequelize: Choosing Managed vs Unmanaged Transactions

Problem & Takeaway

When a Node.js service updates several tables in a single business step, the data must stay consistent. Sequelize offers two ways to wrap those writes in a database transaction: managed transactions, which automatically commit or rollback based on the callback result, and unmanaged transactions, where you manually control commit/rollback. Choosing the right style and correctly passing the transaction object to every query is essential for true atomicity.

Desired Outcome

Execute a multi‑row write that either fully succeeds or leaves the database unchanged, regardless of errors, using Sequelize’s transaction API. Verify that:

• All writes are part of the same transaction boundary.
• On success, the transaction commits and rows persist.
• On error, the transaction rolls back and no rows remain.
• Connection pool resources are released.

Prerequisites

• Node.js 18+ and Sequelize v6 or v7 installed.
• Configured Sequelize instance pointing to a supported dialect (PostgreSQL, MySQL, MariaDB, SQLite, or MSSQL).
• Models defined with proper associations.
• Sufficient database privileges to create, commit, and rollback transactions.

Step‑by‑Step Procedure

1. Verify Isolation Levels Exposed by Your Dialect

const { Sequelize } = require('sequelize');
const sequelize = new Sequelize(/* connection string */);
console.log(Sequelize.Transaction.ISOLATION_LEVELS);

This prints the available levels; use them when starting a transaction. For example, PostgreSQL supports READ COMMITTED, REPEATABLE READ, and SERIALIZABLE.

2. Managed Transaction Example

async function createOrder(orderData) {
  return sequelize.transaction({
    isolationLevel: Sequelize.Transaction.ISOLATION_LEVELS.REPEATABLE_READ
  }, async (t) => {
    // All queries must include { transaction: t }
    const user = await User.findByPk(orderData.userId, { transaction: t });
    if (!user) throw new Error('User missing');

    const order = await Order.create({ userId: user.id, total: orderData.total }, { transaction: t });
    const items = await Promise.all(orderData.items.map(item =>
      OrderItem.create({ orderId: order.id, ...item }, { transaction: t })
    ));

    // If any line throws, the transaction auto‑rolls back
    return { order, items };
  });
}

Key points:

• The callback returns a promise; resolving it commits automatically.
• Throwing an error (or returning a rejected promise) triggers an automatic rollback.
• Every query includes { transaction: t }; omission causes the query to run outside the transaction.

3. Unmanaged Transaction Example

async function createOrderUnmanaged(orderData) {
  const t = await sequelize.transaction();
  try {
    const user = await User.findByPk(orderData.userId, { transaction: t });
    if (!user) throw new Error('User missing');

    const order = await Order.create({ userId: user.id, total: orderData.total }, { transaction: t });
    const items = await Promise.all(orderData.items.map(item =>
      OrderItem.create({ orderId: order.id, ...item }, { transaction: t })
    ));

    await t.commit();
    return { order, items };
  } catch (err) {
    await t.rollback();
    throw err;
  }
}

In this style you must explicitly commit or rollback. The try/catch ensures a rollback on any error. A finally block is optional if you prefer to always commit or rollback in the catch.

4. Nested Transactions (Savepoints)

await sequelize.transaction(async (t) => {
  await User.create({ name: 'Alice' }, { transaction: t });
  await sequelize.transaction({ transaction: t }, async (t1) => {
    // t1 is a savepoint; failure here rolls back only this block
    await Order.create({ userId: 1, total: 100 }, { transaction: t1 });
  });
});

Savepoints are supported only if the underlying dialect implements them. If unsupported, Sequelize throws an error.

Expected Checks

• After a successful managed transaction, query the target tables to confirm rows exist.
• After a forced error (e.g., throw new Error('boom')), re‑query to ensure no rows were inserted.
• Run the same insert without passing { transaction: t } inside the callback; the row should persist even if the outer transaction rolls back.
• Monitor sequelize.connectionPool or your database’s connection counter to verify no leaked connections after an unmanaged transaction.
• For nested transactions, observe that a failure in the inner block does not affect the outer block’s committed data.

Recovery & Best Practices

• Always include { transaction: t } on every query that must participate in the transaction.
• Keep transaction callbacks short; avoid long‑running operations or external HTTP calls inside them to prevent holding locks and exhausting the pool.
• In unmanaged transactions, wrap commit and rollback in finally if you have additional cleanup to perform regardless of outcome.
• Use the appropriate isolation level for your business logic. For most CRUD operations READ COMMITTED suffices; use SERIALIZABLE only when strict consistency is required.
• Test both success and failure paths in a staging environment before deploying to production.
• When using nested transactions, verify that your dialect supports savepoints; otherwise, Sequelize will throw an error.

Limitations & Dialect Variations

• Not all dialects expose the same isolation levels; check Sequelize.Transaction.ISOLATION_LEVELS for your version.
• MySQL’s default isolation level is REPEATABLE READ, but can be overridden per transaction.
• SQLite does not support nested transactions; attempting to start one will throw.
• Connection leaks in unmanaged transactions can silently exhaust the pool, leading to request timeouts.
• Transactions acquire locks that may cause contention; design your application to minimize lock duration.

Conclusion

Managed transactions offer convenience and reduce boilerplate, but require that every query be explicitly tied to the transaction. Unmanaged transactions give you explicit control, which is useful when you need to split logic across multiple async boundaries. In both cases, always pass { transaction: t } to every write, handle errors properly, and verify that the database state matches expectations after commit or rollback.