All or Nothing

An order changes several rows: the order itself, one row per item, and the stock of every product it buys. If an item fails halfway, the shop must not keep the first half. An order has to be all or nothing.

The database gives you this with a transaction. Until now each statement was a transaction of its own, saved the moment it ran. After BEGIN, every change waits until COMMIT saves them all at once. Its opposite, ROLLBACK, throws them all away. db.exec runs a statement like these, which has no ? values. Here is the idea on a money transfer between two accounts:

app.post("/transfer", (req, res) => {
  const data = req.body;
  db.exec("BEGIN");
  db.prepare("UPDATE accounts SET balance = balance - ? WHERE id = ?").run(data.amount, data.from);
  db.prepare("UPDATE accounts SET balance = balance + ? WHERE id = ?").run(data.amount, data.to);
  const sender = db.prepare("SELECT balance FROM accounts WHERE id = ?").get(data.from);
  if (sender.balance < 0) {
    db.exec("ROLLBACK");
    return res.status(409).json({ error: "Not enough money" });
  }
  db.exec("COMMIT");
  res.json({ ok: true });
});

Both updates are saved, or neither: money never leaves one account without reaching the other. 409 Conflict is the status for this: the request is fine in itself, but it clashes with the state of the data. The ROLLBACK comes before the return, since nothing after a return runs.

Run in this order on a fresh shop, your POST /orders must give these answers:

POST /orders  {"customer_id": 5, "items": [{"product_id": 3, "quantity": 1}, {"product_id": 1, "quantity": 2}]}
    →  201  {"id": 8}
GET /products/3  →  200  {"id": 3, "name": "Lamp", "price": 29, "category": "home", "stock": 0}
POST /orders  {"customer_id": 4, "items": [{"product_id": 4, "quantity": 10}, {"product_id": 3, "quantity": 1}]}
    →  409  {"error": "Not enough stock", "product_id": 3}
GET /orders/9/items  →  404  {"error": "Order not found"}
GET /products/4  →  200  {"id": 4, "name": "Pen", "price": 2, "category": "office", "stock": 120}
POST /orders  {"customer_id": 2, "items": [{"product_id": 7, "quantity": 1}, {"product_id": 99, "quantity": 1}]}
    →  404  {"error": "Product not found", "product_id": 99}
POST /orders  {"customer_id": 4, "items": [{"product_id": 4, "quantity": 10}]}
    →  201  {"id": 9}

The second order fails on the lamp, so its pens are not saved either: there is no order 9, the pens' stock is still 120, and the next good order takes id 9.

Your task: in POST /orders, take each item's quantity off its product's stock, and make the whole order one transaction. Answer 409 when there isn't enough stock and 404 when the product doesn't exist, and roll back before both answers, so a failed order leaves nothing behind.

Assignment
Follow the Coding Tutorial to update the stock, check it before each item, and roll back every order that can't be completed.

Hint
Both checks go right below the line that reads product, before anything is written for that item. The undefined check comes first: when there is no product, product.stock crashes.