Project: a menu API

What are you building?

This project pulls the Data and APIs lessons into one server: the bakery’s menu, stored in SQLite, with a complete JSON API on top. A page, a phone app or the till could all use it. The starting code lists the menu and nothing else. By the end it handles five routes, filters by category, validates everything it saves and answers with the right status every time.

The menu table has four columns: id, name, category and price. Open the Data view to see the five rows. Every edit restarts the server with a fresh copy, so you can add and delete as much as you like while testing.

• GET /api/menu lists everything, and ?category=pastry narrows it.
• GET /api/menu/:id returns one item or 404.
• POST /api/menu validates and adds an item, answering 201 or 400.
• PATCH /api/menu/:id changes the price, answering 200, 400 or 404.
• DELETE /api/menu/:id removes an item, answering 204 or 404.

Where should you start?

Build the reading routes first, because you will use them to check everything else. GET /api/menu/:id is one prepared statement with a ? and an undefined check. Once it works, every POST, PATCH and DELETE can be confirmed by reading the item back.

Test each route with its Try button as soon as you write it, and read the Requests view before moving on. A project like this goes wrong when five routes are written at once and the first test shows five problems. One route, one test, then the next.

How do you add an optional filter?

Query strings are optional by nature, so the route has to work with and without one. Read req.query.category. If it’s missing, run the plain list query. If it’s there, run a second query with WHERE category = ? and pass the value in.

Keep the two queries as separate, complete SQL strings. It is tempting to build one query by adding a WHERE clause on the fly, and with more filters that becomes worthwhile, but every value still goes through a placeholder. An unknown category matches no rows and returns [], which is a correct answer: there are no soups today.

const { category } = req.query;
const items = category
  ? db.prepare('SELECT * FROM menu WHERE category = ? ORDER BY id').all(category)
  : db.prepare('SELECT * FROM menu ORDER BY id').all();
res.json(items);

What should POST check before saving?

Three fields, three rules. The name must be a string that isn’t empty after trimming. The category must be one of bread, pastry or cake, checked against the CATEGORIES list. The price must be a number, finite and above zero. typeof price === 'number' rules out "cheap" and "4", and Number.isFinite() rules out NaN and Infinity in case the function is ever reused for values that didn’t come from JSON.

Collect every problem, answer 400 with all of them, and return before the insert. Then insert with .run(), read the new row back with its lastInsertRowid, and answer 201 with a Location header. The database will also refuse a missing name, thanks to NOT NULL. That refusal arrives as a SqliteError and a 500. Your own checks give the client a 400 and a readable reason instead.

Why read the row back after a change?

After an insert or an update, the safest answer is the row as the database now holds it. You could echo the request body, but that hides differences: an id the database chose, a default it filled in, or a value it stored differently from how it arrived. Reading back with the same findItem statement means the client always gets the truth.

Preparing findItem once at the top and reusing it is also a small win. better-sqlite3 lets you keep prepared statements and run them as often as you like, which saves parsing the same SQL on every request.

const findItem = db.prepare('SELECT * FROM menu WHERE id = ?');

db.prepare('UPDATE menu SET price = ? WHERE id = ?').run(price, req.params.id);
res.json(findItem.get(req.params.id));

How do PATCH and DELETE know the id was wrong?

An UPDATE or DELETE that matches no rows isn’t an error to SQLite. It runs, changes nothing and returns { changes: 0 }. If your route ignores that, it answers success for ids that never existed, and a client with a typo believes the job is done.

There are two ways to catch it. Look the item up first and answer 404 if .get() returns undefined, which also lets you validate against the current row. Or run the statement and check changes. The solution uses the first for PATCH, so it can say 404 before complaining about the price, and the second for DELETE, which needs no lookup.

How do you test the finished API?

Run through the Try row in order and read each status: 200 for the list and the filter, 200 for item 3, 201 for the scone, 400 for the bad item, 200 for the new rye price, 204 for the deleted tart. Then repeat the delete and expect 404. Check the menu table in the Data view after each change.

Then try the edges by hand in the request bar: a PATCH with a price of 0, a POST with "price": "4", GET /api/menu/abc, a category with an apostrophe in it. Each should get a calm 400 or 404, never a 500. A 500 here means some input reached code that didn’t expect it. Read the Log line for that request; it names the error and the line.

It helps to write the test list down once and keep it next to the code. Each time you change a route, run the whole list again, because a fix in one route can break another without any error if they share a prepared statement or a helper like goodPrice. Automated tests do exactly this on real projects, sending the same requests and comparing the answers every time the code changes.

What would change in a real deployment?

The code would be almost identical. The server would read its port from the environment and the database path from configuration, so the same file runs on your laptop and on the host. The database file would sit in a data folder outside anything served statically, with regular backups. Write routes like POST, PATCH and DELETE would sit behind a login, so only bakery staff could change the menu; the Users and safety lessons build that. Prices would usually be stored as whole pence or cents to avoid rounding surprises with decimals.

If an AI tool wrote this API for you, the checks you just ran are the review: every value through a placeholder, every input validated by type as well as presence, every route answering 404 for unknown ids, 201 and 204 where they belong, and an early return in front of every early answer.

Field notes

• Build the reading routes first so you can use them to check the rest.
• A filter in the query string is optional, so the route must work with and without it.
• Check every field before saving, and answer 400 when something is wrong.
• An update or delete that matched no rows reports zero changes, which should become a 404.

Easy to get wrong

Checking that fields exist isn’t enough. A string price slips past price <= 0, and any category text is accepted.

A delete that matches nothing still succeeds in SQLite. Check changes so a wrong id gets 404.

Query strings are typed by anyone. Joined into the SQL, they can break the query or change what it does.

Questions people ask

Should an unknown category answer 404 or an empty list?

An empty list. The collection /api/menu exists; the filter just matched nothing. Save 404 for a path that names something specific, like /api/menu/99.

Why does PATCH only change the price?

To keep the project a sensible size. A fuller PATCH would accept any of name, category and price, validate whichever were sent, and update only those columns. The pattern is the same, with a list of allowed fields.

Should prices be stored as decimals?

For a small menu, REAL works. Shops that add up many prices usually store whole pence or cents as integers, because decimal fractions like 0.1 can’t be stored exactly and totals can drift by a penny.

Can a page use this API straight away?

Yes. A page served by the same server can call it with fetch(), as in the previous lesson. Pages on other sites would need CORS headers first.