Sequelize transaction boundaries for atomic multi-row writes

The problem: partial writes leave orphaned rows

Your service creates a user record and an order record in the same request. If the order insert fails after the user insert succeeds, the database contains a user with no order. When two related writes must succeed or fail together, handling failures manually in application code is error-prone.

Useful takeaway: use sequelize.transaction() as the boundary, pass the transaction object to every model call inside it, and assume rollback only undoes database changes. External calls, file writes, and messages already sent to a queue are not reversed.

Requirements for atomic multi-row writes

• All writes in the group must succeed or none must persist.
• A failure in any step must roll back all prior steps in the group.
• The transaction must be held for the shortest time possible to avoid locks and connection-pool exhaustion.

Smallest suitable design

Assume Sequelize v6 and a configured sequelize instance. Define models, then open one transaction and pass it to each query:

const { Sequelize, DataTypes } = require('sequelize');

const sequelize = new Sequelize(process.env.DATABASE_URL, {
  pool: { max: 10, min: 0, acquire: 30000, idle: 10000 },
});

const User = sequelize.define('User', {
  username: { type: DataTypes.STRING, allowNull: false },
});

const Order = sequelize.define('Order', {
  userId: { type: DataTypes.INTEGER, allowNull: false },
  total: { type: DataTypes.DECIMAL(10, 2), allowNull: false },
});

async function createUserAndOrder(userData, orderData) {
  const transaction = await sequelize.transaction();

  try {
    const user = await User.create(userData, { transaction });
    const order = await Order.create(
      { ...orderData, userId: user.id },
      { transaction }
    );

    await transaction.commit();
    return { user, order };
  } catch (error) {
    await transaction.rollback();
    throw error;
  }
}

The transaction object is the trust boundary. Sequelize automatically rolls back if the promise chain throws, but only for queries that received { transaction }.

What happens when you forget the transaction option

async function brokenExample(userData, orderData) {
  const transaction = await sequelize.transaction();
  try {
    await User.create(userData, { transaction });
    // Missing { transaction }: this write is outside the transaction.
    await Order.create({ userId: 1, total: 10.00 });
    await transaction.commit();
  } catch (error) {
    await transaction.rollback();
    throw error;
  }
}

If Order.create throws, the user row rolls back, but the order row already committed because it never joined the transaction. This is the most common boundary mistake.

Trust and data boundaries

Application code decides where the transaction starts and ends. The database engine enforces atomicity for the rows inside it. Sequelize does not track side effects outside the database.

Inside transaction	Outside transaction / not rolled back
INSERT, UPDATE, DELETE on Sequelize models	External HTTP API calls
Raw queries passed { transaction }	File system writes
Database-level constraints and locks	Messages published to a message queue
	Emails already sent

If your workflow must update a database row and call a payment API, do not call the API inside the transaction. Commit the transaction first, then call the API, or use an outbox table and a separate worker to send the message after commit.

Operational checks before deploy

1. Start a transaction, insert one row, intentionally throw an error, and confirm the row is invisible from a separate database connection after rollback.
2. Review every model call inside the transaction block. Confirm each passes { transaction }. Omit it on one call in a staging environment and confirm the row persists despite rollback.
3. Check your installed Sequelize major version. Transaction options and promise handling changed between major versions; do not copy v5 examples into a v6 project without review.
4. Inspect connection-pool settings. Long transactions hold connections until commit or rollback, which can exhaust the pool under load.

Run verification scripts from the service directory with node verify-script.js. The database user needs INSERT, UPDATE, and transaction privileges on the target schema. Risk: a failed rollback or crashed process can leave locks until the database terminates the idle connection.

Failure modes

• Connection loss during a transaction: the database rolls back on disconnect, but your application may not receive the error cleanly depending on dialect and pool configuration.
• Deadlocks: two transactions touching the same rows in different orders can deadlock. One is aborted; retry the whole transaction if your business logic allows it.
• Query timeouts: a statement timeout inside a transaction does not always abort the entire transaction in every dialect; handle the error and roll back explicitly.
• Unhandled rejections: if you never await transaction.commit() or rollback(), the connection can remain in an uncertain state until the pool recycles it.
• Long-lived transactions: waiting for user input or slow external calls inside a transaction holds row locks and can cause table bloat in PostgreSQL.

Conditions that would change the design

• Cross-service consistency: if the write must be atomic across multiple services, a local Sequelize transaction is insufficient. Use a saga, transactional outbox, or two-phase commit pattern instead.
• Nested transactions: Sequelize supports savepoints when autocommit is enabled, but behavior differs by dialect (PostgreSQL, MySQL, SQLite, MariaDB). Savepoints can mask errors if a nested block rolls back but the outer transaction continues. Avoid unless you have verified dialect-specific behavior.
• High write volume: if transactions routinely exceed a few hundred milliseconds, consider whether the work can be split into smaller committed units or moved to a background job.

Limitations and practical result check

Sequelize transactions do not make your application logic atomic across systems. They also do not prevent application-level bugs such as committing before a dependent validation completes.

Practical check: after a rollback, query the table from a second connection:

-- Example for PostgreSQL or MySQL; adjust dialect syntax as needed
SELECT * FROM "Users" WHERE username = 'rollback-test';

If the row is absent, the rollback worked. If the row is present, one of the writes omitted { transaction } or the transaction was committed unintentionally.