13. Transactions and integrity
- Understand what a transaction is and why
POST /orderswrites two tables “all or nothing”. - Tell the constraints the database enforces (FK,
CHECK,UNIQUE,NOT NULL) from those the application enforces (stock, statuses). - Recognize
409 Conflictand422as “the data would become inconsistent” responses. - See
ON DELETE CASCADEand the order state machine in action.
You need the sandbox (all writes happen on your copy):
No active sandbox in this browser.
Theory
Section titled “Theory”An order is a row in orders plus one or more rows in order_items. If the server wrote the first and then failed on the second, you would have an empty order with a wrong total: inconsistent data. A transaction groups the writes: either they all succeed (COMMIT) or none does (ROLLBACK). It is the “A” of ACID (Atomicity); the other three — Consistency, Isolation, Durability — say that the rules stay true, that transactions do not disturb each other and that what has been committed is not lost.
The rules that keep the database consistent fall into two families:
| Where the rule lives | Examples in the dataset | If you break it |
|---|---|---|
| In the database (schema) | books.author_id must exist in authors (FK); rating BETWEEN 1 AND 5 (CHECK); customers.email UNIQUE; status IN ('pending', …) |
the engine rejects the write; the API translates it into 422 or 409 |
| In the application (server code) | you cannot order a book with in_stock = false; from pending you can only go to paid or cancelled; you cannot delete a book that appears in orders |
the API answers 409 Conflict with an explained code |
Foreign keys can also decide what happens when you delete the row they point to: order_items.order_id … ON DELETE CASCADE means “if the order disappears, its lines disappear too”. books, on the other hand, has no CASCADE towards order_items: an ordered book cannot be deleted (and the API explains it with 409 book_in_orders).
curl, explained option by option
Section titled “curl, explained option by option”First the token (if you do not already have it from the panel above):
TOKEN=$(curl -s -X POST https://corso-api-json.giuseppebosi.com/api/v1/sandbox | python3 -c 'import sys, json; print(json.load(sys.stdin)["token"])')echo $TOKEN- TOKEN=$( … )
- command substitution: the shell runs what is in the parentheses and puts its output into the variable
- python3 -c '…'
- a one-line Python program that reads the JSON from stdin and prints only the
tokenfield
An order with two lines, in one transaction
Section titled “An order with two lines, in one transaction”curl -s -i -X POST https://corso-api-json.giuseppebosi.com/api/v1/orders \-H "Authorization: Bearer $TOKEN" \-H 'Content-Type: application/json' \-d '{ "customer_id": 3, "items": [ { "book_id": 5, "quantity": 2 }, { "book_id": 9, "quantity": 1 } ], "shipping_address": { "street": "Via Garibaldi 12", "city": "Torino", "zip": "10121", "country": "IT" } }'- -i
- include: also prints the status line and the response headers, not just the body
- -H "Authorization: Bearer $TOKEN"
- double quotes are needed here: the shell must replace
$TOKENwith its value - "items": [ … ]
- an array of lines: each with the
book_idFK and the quantity. The price is set by the server, read frombooks - "shipping_address": { … }
- an object saved as JSON in a column: it is validated (5-digit postal code, 2-letter country)
HTTP/1.1 201 Createdlocation: https://corso-api-json.giuseppebosi.com/api/v1/orders/121
{"id":121,"customer_id":3,"status":"pending","shipping_address":{"street":"Via Garibaldi 12","city":"Torino","zip":"10121","country":"IT"}, "total":68.98,"created_at":"2026-09-18T20:05:50Z", "items":[{"id":306,"order_id":121,"book_id":5,"quantity":2,"unit_price":27.82},{"id":307,"order_id":121,"book_id":9,"quantity":1,"unit_price":13.34}]}The server, inside one transaction: checks the customer (FK), checks each book (FK + stock), computes total (2 × 27.82 + 13.34 = 68.98), inserts the row into orders, inserts the two rows into order_items with the price of that moment (unit_price: if the book’s price changes tomorrow, the order stays as it was), and commits.
All or nothing: one wrong line cancels the whole order
Section titled “All or nothing: one wrong line cancels the whole order”curl -s -X POST https://corso-api-json.giuseppebosi.com/api/v1/orders \-H "Authorization: Bearer $TOKEN" -H 'Content-Type: application/json' \-d '{ "customer_id": 3, "items": [ { "book_id": 5, "quantity": 1 }, { "book_id": 9999, "quantity": 1 } ], "shipping_address": { "street": "Via Garibaldi 12", "city": "Torino", "zip": "10121" } }'curl -s "https://corso-api-json.giuseppebosi.com/api/v1/orders?limit=1" -H "Authorization: Bearer $TOKEN"- "book_id": 9999
- an FK that points to nothing
- second curl
- checks how many orders there are now:
meta.total
{"error":{"code":"unknown_book","message":"book_id 9999 non esiste"}}… "meta": { "page": 1, "limit": 1, "total": 121, … }total is still 121: book 5 was valid, but the order was not created, not even partially. No orphan rows in order_items.
An application rule: the out-of-stock book
Section titled “An application rule: the out-of-stock book”Let’s take book 5 out of stock and try again:
curl -s -X PATCH https://corso-api-json.giuseppebosi.com/api/v1/books/5 \ -H "Authorization: Bearer $TOKEN" -H 'Content-Type: application/json' -d '{"in_stock": false}'curl -s -X POST https://corso-api-json.giuseppebosi.com/api/v1/orders \ -H "Authorization: Bearer $TOKEN" -H 'Content-Type: application/json' \ -d '{"customer_id": 3, "items": [{"book_id": 5, "quantity": 1}], "shipping_address": {"street": "Via Garibaldi 12", "city": "Torino", "zip": "10121"}}'{"error":{"code":"out_of_stock","message":"'Andromeda e il Novecento' non è disponibile"}}409: the request is correct, it is the book’s state that prevents it. No database constraint could express this: it is application logic.
The order state machine
Section titled “The order state machine”An order goes pending → paid → shipped → delivered, with cancelled possible only from pending or paid. Trying to skip a step:
curl -s -X PATCH https://corso-api-json.giuseppebosi.com/api/v1/orders/121 \ -H "Authorization: Bearer $TOKEN" -H 'Content-Type: application/json' -d '{"status": "shipped"}'{"error":{"code":"invalid_transition","message":"Da 'pending' non si può passare a 'shipped'","details":{"allowed":["cancelled","paid"]}}}With {"status": "paid"} the transition succeeds ("status": "paid"); from there delivered is rejected again (allowed: ["cancelled", "shipped"]). And a value outside the set ("bho") does not even reach the logic: 422 validation_error, “Input should be ‘pending’, ‘paid’, ‘shipped’, ‘delivered’ or ‘cancelled’” — that is the schema’s CHECK, mirrored in the validation model.
Foreign keys: what they protect, what they delete in cascade
Section titled “Foreign keys: what they protect, what they delete in cascade”curl -s -X DELETE https://corso-api-json.giuseppebosi.com/api/v1/books/1 -H "Authorization: Bearer $TOKEN"{"error":{"code":"book_in_orders","message":"Il libro compare in ordini esistenti: la chiave esterna impedisce di cancellarlo"}}Deleting the book would leave rows in order_items with a book_id that points to nothing. Conversely, deleting order 121 takes away its lines, because the FK is declared ON DELETE CASCADE. Check with /sql before and after:
Q='{"sql": "SELECT COUNT(*) AS righe FROM order_items WHERE order_id = 121"}'curl -s -X POST https://corso-api-json.giuseppebosi.com/api/v1/sql -H "Authorization: Bearer $TOKEN" -H 'Content-Type: application/json' -d "$Q"curl -s -o /dev/null -w '%{http_code}\n' -X DELETE https://corso-api-json.giuseppebosi.com/api/v1/orders/121 -H "Authorization: Bearer $TOKEN"curl -s -X POST https://corso-api-json.giuseppebosi.com/api/v1/sql -H "Authorization: Bearer $TOKEN" -H 'Content-Type: application/json' -d "$Q"- Q='…'
- saves the body in a variable so you do not repeat it
- -H "Authorization: Bearer $TOKEN" on /sql
- with the token,
/sqlreads your sandbox (where order 121 exists) - -o /dev/null
- throws away the response body (a successful DELETE answers 204 with no body)
- -w '%{http_code}\n'
- write-out: prints only the status code followed by a newline
{"columns":["righe"],"rows":[[2]],"row_count":1,…}204{"columns":["righe"],"rows":[[0]],"row_count":1,…}Two rows, then 204 No Content, then zero rows: the cascade did its job. You can read these rules in GET /api/v1/schema: for order_items you find foreign_keys: [{column: "book_id", references: books.id}, {column: "order_id", references: orders.id}] and, for the quantity and unit_price columns, the flag nullable: false.
Python and TypeScript
Section titled “Python and TypeScript”A script that runs through the whole sequence, handling every kind of rejection:
# pip install httpx → python3 ordini.pyimport httpx
BASE = "https://corso-api-json.giuseppebosi.com/api/v1"token = httpx.post(f"{BASE}/sandbox").json()["token"]api = httpx.Client(base_url=BASE, headers={"Authorization": f"Bearer {token}"}, timeout=10)INDIRIZZO = {"street": "Via Garibaldi 12", "city": "Torino", "zip": "10121"}
def ordina(customer_id: int, righe: list[tuple[int, int]]) -> dict | None: r = api.post("/orders", json={"customer_id": customer_id, "shipping_address": INDIRIZZO, "items": [{"book_id": b, "quantity": q} for b, q in righe]}) if r.status_code == 201: return r.json() err = r.json()["error"] print(f"ordine rifiutato ({r.status_code} {err['code']}): {err['message']}") return None
ordine = ordina(3, [(5, 2), (9, 1)])print("creato:", ordine["id"], ordine["total"])ordina(3, [(5, 1), (9999, 1)]) # 422 unknown_book: niente ordine parziale
api.patch("/books/5", json={"in_stock": False})ordina(3, [(5, 1)]) # 409 out_of_stock
for stato in ["shipped", "paid", "delivered", "shipped", "delivered"]: r = api.patch(f"/orders/{ordine['id']}", json={"status": stato}) esito = r.json()["status"] if r.status_code == 200 else f"409 {', '.join(r.json()['error']['details']['allowed'])}" print(f"→ {stato:<10} {esito}")
print("DELETE libro 1:", api.delete("/books/1").json()["error"]["code"]) # book_in_ordersprint("DELETE ordine:", api.delete(f"/orders/{ordine['id']}").status_code) # 204, righe cancellate a cascataapi.delete("/sandbox")What you should see:
creato: 121 68.98ordine rifiutato (422 unknown_book): book_id 9999 non esisteordine rifiutato (409 out_of_stock): 'Andromeda e il Novecento' non è disponibile→ shipped 409 cancelled, paid→ paid paid→ delivered 409 cancelled, shipped→ shipped shipped→ delivered deliveredDELETE libro 1: book_in_ordersDELETE ordine: 204// node --experimental-strip-types ordini.tsconst BASE = "https://corso-api-json.giuseppebosi.com/api/v1";const { token } = await (await fetch(`${BASE}/sandbox`, { method: "POST" })).json();const headers = { Authorization: `Bearer ${token}`, "Content-Type": "application/json" };const INDIRIZZO = { street: "Via Garibaldi 12", city: "Torino", zip: "10121" };
async function ordina(customerId: number, righe: [number, number][]) { const r = await fetch(`${BASE}/orders`, { method: "POST", headers, body: JSON.stringify({ customer_id: customerId, shipping_address: INDIRIZZO, items: righe.map(([book_id, quantity]) => ({ book_id, quantity })) }) }); if (r.status === 201) return r.json(); const { error } = await r.json(); console.log(`ordine rifiutato (${r.status} ${error.code}): ${error.message}`); return null;}
const ordine = await ordina(3, [[5, 2], [9, 1]]);console.log("creato:", ordine.id, ordine.total);await ordina(3, [[5, 1], [9999, 1]]); // 422 unknown_book: niente ordine parziale
await fetch(`${BASE}/books/5`, { method: "PATCH", headers, body: JSON.stringify({ in_stock: false }) });await ordina(3, [[5, 1]]); // 409 out_of_stock
for (const stato of ["shipped", "paid", "delivered", "shipped", "delivered"]) { const r = await fetch(`${BASE}/orders/${ordine.id}`, { method: "PATCH", headers, body: JSON.stringify({ status: stato }) }); const j = await r.json(); console.log(`→ ${stato.padEnd(10)} ${r.ok ? j.status : `409 ${j.error.details.allowed.join(", ")}`}`);}
console.log("DELETE libro 1:", (await (await fetch(`${BASE}/books/1`, { method: "DELETE", headers })).json()).error.code);console.log("DELETE ordine:", (await fetch(`${BASE}/orders/${ordine.id}`, { method: "DELETE", headers })).status);await fetch(`${BASE}/sandbox`, { method: "DELETE", headers });Expected output:
creato: 121 68.98ordine rifiutato (422 unknown_book): book_id 9999 non esisteordine rifiutato (409 out_of_stock): 'Andromeda e il Novecento' non è disponibile→ shipped 409 cancelled, paid→ paid paid→ delivered 409 cancelled, shipped→ shipped shipped→ delivered deliveredDELETE libro 1: book_in_ordersDELETE ordine: 204Try it now
Section titled “Try it now”Response headers
Response headers
Response headers
Response headers
Response headers
Exercise
Section titled “Exercise”Try it online, nothing to install:
Python on Google Colab (a free Google account is needed to run the cells); TypeScript on StackBlitz (it opens and starts by itself in the browser). If the practice API is switched off, turn it on from /en/api-attiva/.
- Create a customer with
POST /customers, then try again with the same email: which status and which code do you get? Which database constraint is behind it? - Take an order to
cancelledfrompending, then try to pay it. What doesdetails.allowedsay? - With
/sqlon your sandbox, check that afterDELETE /orders/{id}no rows remain inorder_itemswith thatorder_id. - Why is
unit_pricecopied intoorder_itemsinstead of readingbooks.pricewith a JOIN?
Solution
409 duplicate_email: theUNIQUEconstraint oncustomers.email.allowed: []—cancelledis a final state, likedelivered.{"sql": "SELECT COUNT(*) FROM order_items WHERE order_id = ?", "params": [ID]}→[[0]].- Because the price paid is a historical fact: if the price list changes, the order (and the invoice) must stay the same. It is a deliberate denormalization.
Common mistakes
Section titled “Common mistakes”- Writing two tables with two separate calls from the client (first the order, then the lines): if the second fails, an empty order is left behind. The server must offer an atomic operation, and you must use it.
- Reading a 409 as “retry”: retrying the same request will give the same 409. Something must change (or the user must be told).
- Relying only on client-side validation: the real rules live in the database and the server; the client can only anticipate them.
- Mistaking CASCADE for “the server cleans up for me”: it is a schema choice, table by table. Read it in
/schemabefore deleting.
Summary
Section titled “Summary”Transactions and constraints are what make a database more than a pile of files: POST /orders is atomic, FKs prevent orphans or delete in cascade, CHECKs and the state machine keep values sensible, and the API turns it all into readable 422/409 responses. In the next chapter the focus moves from correctness to speed: caching, ETags, compression and rate limits.