All or Nothing in Go

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. In Go, db.Begin() starts one, every change made through tx.Exec waits in it, and tx.Commit() saves them all at once. Its opposite, tx.Rollback(), throws them all away. Here is the idea on a money transfer between two accounts:

app.HandleFunc("POST /transfer", func(w http.ResponseWriter, r *http.Request) {
    var t Transfer
    json.NewDecoder(r.Body).Decode(&t)
    tx, _ := db.Begin()
    tx.Exec("UPDATE accounts SET balance = balance - ? WHERE id = ?", t.Amount, t.From)
    tx.Exec("UPDATE accounts SET balance = balance + ? WHERE id = ?", t.Amount, t.To)
    var balance int
    tx.QueryRow("SELECT balance FROM accounts WHERE id = ?", t.From).Scan(&balance)
    if balance < 0 {
        tx.Rollback()
        writeJSON(w, 409, map[string]string{"error": "Not enough money"})
        return
    }
    tx.Commit()
    writeJSON(w, 200, map[string]bool{"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. Every way out of the handler must end tx with a commit or a rollback: a transaction left open keeps shop.db locked, and the next order can't be written.

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. The line that reads the product is missing from the starting code: write it again, keeping Scan's error in err. 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 var product Product and the line that reads it, before anything is written for that item. The sql.ErrNoRows check comes first: a missing product leaves product empty, with a Stock of 0, and the stock check would wrongly answer 409. Each check ends with return.