Two Buyers, One Lamp

The shop has one lamp left, and two customers press Buy at the same moment. A route that first reads the stock and then writes stock - 1 can be fooled: a busy shop runs several copies of the app side by side, all on the same database, so two requests can both read 1 before either one writes. Both customers get the lamp, and the stock ends at -1. This is a race condition: the result depends on which request gets there first.

One statement: let the database check and change the value in a single UPDATE, with the check in its WHERE. The database runs one write at a time, so nobody can slip in between the check and the change. Afterwards, result.changes, on what .run gives back, is the number of rows the UPDATE changed: 1 if it worked, 0 if not. Here is the idea for a coupon with a limited number of uses:

const result = db.prepare("UPDATE coupons SET uses_left = uses_left - 1 WHERE code = ? AND uses_left > 0").run(code);
if (result.changes === 0) {
  return res.status(409).json({ error: "Coupon used up" });
}

409 Conflict says the request is fine, but the current state says no. An UPDATE also changes no row when the id doesn't exist, so when nothing changed, look the product up to choose between 409 and 404.

POST /products/1/buy   →  200  {"bought": 1, "id": 1}
GET /products/1        →  200  {"category": "kitchen", "id": 1, "name": "Mug", "price": 12, "stock": 9}
POST /products/3/buy   →  409  {"error": "Out of stock"}   (another customer bought the last lamp first)
GET /products/3        →  200  {"category": "home", "id": 3, "name": "Lamp", "price": 29, "stock": 0}
POST /products/2/buy   →  409  {"error": "Out of stock"}   (the notebook's stock is 0)
POST /products/99/buy  →  404  {"error": "Product not found"}
POST /products/8/buy   →  200  {"bought": 1, "id": 8}

Your task: add POST /products/:productId/buy. Take one item off the stock with a single UPDATE … WHERE id = ? AND stock > 0 run through runQuery, and answer {"id": …, "bought": 1}. If no row changed, answer 404 Product not found when there is no such product, and 409 Out of stock otherwise.

Assignment
Follow the Coding Tutorial to build the buy route, one that never sells more than the shop has.

Hint
Don't read the stock first. Keep what .run(productId) gives back in result and check result.changes === 0. Only then run SELECT id FROM products WHERE id = ?, to tell a missing product from a sold-out one.