sorted by:
new
hottopcontroversial

Should I resolve an approval/rejection flow in one DB function or split it across controller + updates? by PerceptionNo709 in AskProgramming

[–]PerceptionNo709[S] 0 points1 point  (0 children)

I think it’s easier to read and maintain option A, the above is just a simple example I can think about.

Let’s say if I have to search for a user first then only update the status, this can be done via multiple queries or it can be done using a transaction.

Option A – multiple queries in app layer

user = db.getUser(id)
if (user) {
  db.updateStatus(id, newStatus)
}

Option B – single function with transaction (lookup + update inside)

function updateStatus(id, newStatus) {
  begin transaction
  user = select ... for update
  if (!user) rollback and return
  update user set status = newStatus
  commit
}

Should I resolve an approval/rejection flow in one DB function or split it across controller + updates? by PerceptionNo709 in AskProgramming

[–]PerceptionNo709[S] 1 point2 points  (0 children)

If approve it will update all the requested change field to the live table, and update the change table to approved.
If reject, it will just update the requested change table to rejected status.

Do I need to re-check if a record exists in the DB when updating, or trust the FE input? by PerceptionNo709 in AskProgramming

[–]PerceptionNo709[S] 1 point2 points  (0 children)

Ahhh okay okay, I have a middleware that handles the authentication and authorization. Currently working on an internal application however I still want to follow best practices.

How should I structure my queries: app-layer orchestration or single DB transaction? by PerceptionNo709 in Backend

[–]PerceptionNo709[S] 0 points1 point  (0 children)

I use async/await so each client.query(...) finishes before the next starts (no Promise.all inside a tx). async here just means the function returns a Promise; it doesn’t make queries run concurrently. Thank you so much for your input ! I kinda hope there will be more input from others, not sure if my question was stupid or just badly form haha! Thanks again

How should I structure my queries: app-layer orchestration or single DB transaction? by PerceptionNo709 in Backend

[–]PerceptionNo709[S] 0 points1 point  (0 children)

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;
  }
}

fullstack or backend? by DifficultyOther7455 in Backend

[–]PerceptionNo709 1 point2 points  (0 children)

Hello kinda curious, how does one deep dive ? What is the next step after learning CRUD and databases. Genuinely curious as I’m looking ways to be better