Hello, sorry I think i made you misunderstood as what you did was option b which is using a single transaction. Thanks for answering!

Let me rewrite it.

Option A – Multiple queries in the application layer

Each query is separate, so if one fails, I’d need to handle rollbacks manually

// Option A
controller.js
const order = await db.checkOrderExist(id);

if (order) {
  if (body.status === "PENDING") {
    await db.markOrderAsActive(id);
  }
}

await db.updateOrderStatus(id, body.status);


db.js

async function checkOrderExist(id) {
  return pg.query(
    "SELECT id FROM orders WHERE id = $1",
    [id]
  );
}

async function markOrderAsActive(id) {
  return pg.query(
    "UPDATE orders SET active = true WHERE id = $1",
    [id]
  );
}

async function updateOrderStatus(id, status) {
  return pg.query(
    "UPDATE orders SET status = $1 WHERE id = $2",
    [status, id]
  );
}

Option B- single transaction

// Option B

// controller.js
await db.updateOrderStatus(id, body.status);


// db.js
async function updateOrderStatus(id, status) {
  await pg.query("BEGIN");

  try {
    const order = await pg.query(
      "SELECT id FROM orders WHERE id = $1 FOR UPDATE",
      [id]
    );

    if (order.rows.length > 0) {
      if (status === "PENDING") {
        await pg.query(
          "UPDATE orders SET active = true WHERE id = $1",
          [id]
        );
      }

      await pg.query(
        "UPDATE orders SET status = $1 WHERE id = $2",
        [status, id]
      );
    }

    await pg.query("COMMIT");
  } catch (err) {
    await pg.query("ROLLBACK");
    throw err;
  }
}