Project: Customers

Time to put the module together. The shop wants a small API for its customers. GET /customers lists them, and GET /customers/<id> shows one of them, with how many orders they placed and how much they spent. Your query helper does the talking to the database.

Numbers worked out from tables: the order count and the money spent are not stored anywhere. Every request works them out from orders joined with order_items, with COUNT and SUM, so they are always up to date. Watch for two traps. A JOIN gives one row per item, so count orders with COUNT(DISTINCT ...). SUM of no rows is NULL, so wrap it in COALESCE, which gives the first of its values that isn't NULL. For example, here is a route for the stock of one category:

app.get("/categories/:name/stock", (req, res) => {
  const sql = "SELECT COUNT(*) AS products, COALESCE(SUM(stock), 0) AS in_stock FROM products WHERE category = ?";
  const totals = query(sql, [req.params.name], true);
  res.json(totals);
});

A category with no products gets "in_stock": 0, not null. Notice the name sql. In a route that calls your helper, a constant named query would hide the helper, and query(...) would fail. Your two routes must answer:

GET /customers     →  200  [{"id": 1, "name": "Ana Pop", "city": "Cluj"}, {"id": 2, "name": "Ben Ortiz", "city": "Madrid"}, …, {"id": 5, "name": "Eva Rossi", "city": "Rome"}]
GET /customers/1   →  200  {"id": 1, "name": "Ana Pop", "email": "ana@example.com", "city": "Cluj", "joined": "2026-01-10", "orders": 2, "spent": 75}
GET /customers/2   →  200  {"id": 2, "name": "Ben Ortiz", …, "orders": 1, "spent": 29}
GET /customers/3   →  200  {"id": 3, "name": "Chloe Ng", …, "orders": 2, "spent": 89}
GET /customers/5   →  200  {"id": 5, "name": "Eva Rossi", …, "orders": 0, "spent": 0}
GET /customers/99  →  404  {"error": "Customer not found"}
POST /orders {"customer_id": 5, "items": [{"product_id": 1, "quantity": 2}, {"product_id": 4, "quantity": 3}]}  →  201  {"id": 8}
GET /customers/5   →  200  {"id": 5, "name": "Eva Rossi", …, "orders": 1, "spent": 30}

Your task: at the end of the file, add two routes. GET /customers answers the id, name and city of every customer. GET /customers/:customerId answers the whole customer plus orders and spent (cancelled orders don't count), or a 404 when no customer has that id.

Assignment
Follow the Coding Tutorial to build the two customer routes on top of your query helper.

Hint
Read the customer with true for one and check for undefined first. The totals also come back as one row, because COUNT and SUM turn all the rows into one. Copy its orders and spent into the customer object before you send it.