REST API for a notes app

What you will practise

• designing resource URLs and choosing HTTP methods deliberately
• status codes, including the ones for failure
• validating a request body before it touches the database
• parameterised SQL and why string-built SQL is the bug
• pagination with limit, offset and a total count
• configuration from the environment instead of from the source
• writing API tests through a test client

Requirements

The project is done when every one of these is true.

• Five routes work: create a note, list notes, get one note, update one note, and delete one note, using the HTTP methods that match those meanings.
• Creating a note returns 201 with the created note including its id and a Location header pointing at that note's URL; deleting returns 204 with an empty body, and deleting the same id again returns 404.
• Requesting an id that does not exist returns 404, and a body missing a required field or holding a field of the wrong type is rejected with a 4xx status and a message naming the field. Both come back as JSON error bodies, never an HTML error page or a stack trace, and neither writes anything to the database.
• Data lives in a database file on disk, and a schema step that creates the table if it is missing runs at startup, so a fresh clone works with no manual setup.
• Every SQL statement passes its values as parameters; there is no string concatenation or interpolation anywhere near a query.
• The list route accepts limit and offset, returns the total count alongside the page, caps limit at a documented maximum, and rejects a negative offset.
• The list route accepts a q parameter that matches notes by title or body, case-insensitively, and returns an empty list rather than a 404 when nothing matches.
• Configuration such as the database path and the port comes from environment variables with sensible defaults, and no secret or absolute path is committed.
• At least eight automated tests run against the API through a test client, each against a fresh temporary database, and they all pass from a clean checkout.

Milestones

A sensible order to build it in, so something works at every step.

1. 1

   One route, no database

   Get the server running with a single route that returns a hard-coded note as JSON. This proves your toolchain and your JSON shape before anything can go wrong underneath.

2. 2

   Add the database and the schema step

   Open a database file, create the notes table if it is missing at startup, and move your one route onto real rows with parameterised SQL.

3. 3

   Finish the five routes with their status codes

   Add create, update and delete, and decide the status code for every outcome, including not found and invalid body, writing each decision down as you go.

4. 4

   Add validation at the edge

   Reject bad bodies before any database call, with a message that names the offending field, and confirm a rejected request leaves the row count unchanged.

5. 5

   Write the test suite

   Set up a test client against a fresh temporary database per test, and write one test per outcome you decided on, including the failures.

6. 6

   Add pagination and search

   Add limit, offset, a total count and the q filter, then test the edges: past the end, limit above the cap, negative offset, and a q that matches nothing.

7. 7

   Move configuration out and write the README

   Pull the database path and port into environment variables with defaults, then write the few lines someone else needs to clone it, run it and run the tests.

Hints

Open one only when you are stuck. Each gives a little more away.

How to test it

Run these checks yourself, or turn them into automated tests once you know how.

• POST a valid note: expect 201, a body containing an id, and a Location header whose URL, fetched with GET, returns 200 and exactly the title and body you sent.
• GET an id you know does not exist, such as 999999: expect 404, a JSON error body and a JSON content type.
• POST an empty object, then POST a note whose title is a number rather than a string: expect a 4xx status both times, a message naming the offending field, and no change in the number of rows. The second case catches validation that only checks for presence.
• DELETE a note: expect 204 and an empty body. GET the same id: expect 404. DELETE it again: expect 404.
• Insert 25 notes, then GET the list with limit 10 and offset 20: expect exactly 5 items and a reported total of 25. Then GET with a q that matches nothing: expect 200, an empty list and a total of 0, not a 404.
• GET the list with limit set far above your cap: expect at most your documented maximum number of items and no error. GET it with offset -1: expect a 4xx rejection rather than a database error leaking through.
• POST a note whose title is the text '; DROP TABLE notes; -- then GET it back: the title must come back as those exact characters, and the table must still exist and still hold every note.
• Stop the server, start it again, and GET the list: every note must still be there, which is the only real proof the data is on disk.

Stretch goals

• Add tags as a second table with a many-to-many join, and filter the list route by tag.
• Add optimistic concurrency: return a version with each note and reject an update whose version is stale with 409.
• Add cursor-based pagination alongside offset pagination and write down which one you would keep and why.
• Add a rate limit per client with a documented window, and a test that proves it triggers and then recovers.
• Write an OpenAPI description of the API by hand, then compare it with whatever your framework generates.

Learn what you need

• REST APIs →
• HTTP and HTTPS →
• Node.js and Express →
• SQL and NoSQL compared →
• Secrets and credential management →
• Common web vulnerabilities →