Skip to content
POLARISAI ACADEMY
R02-201 · Navigator

APIs, SQL, Tests First and Spec-Driven Code

You can write a seven-line contract for one item, pick status 201, 200, 400, or 404 from those lines, bind a name and a qty with question marks, and watch pytest report 1 failed before post_item matches the contract.

Level
Navigator
Time
28 h (estimate)
Prerequisites
R02-101
Course 2 of 4
Jump to

After this course you can

The student can write a test suite that catches seeded contract violations and implement a SQL-backed REST API that passes 100% of contract, concurrency, and SQL-injection tests.

Seven lines before any function

Write the contract on paper before the function post_item exists. The contract is a spec, a specification the tests will read line by line. Line 1 names the table items and its three columns, id, name, and qty. Line 2 says POST /items reads a JSON object with name and qty. The object {"name":"oats","qty":2} is 23 characters: {"name": is 8, "oats" is 6, ,"qty": is 7, the digit 2 is 1, and the closing brace is 1. 8 plus 6 plus 7 plus 1 plus 1 is 23. Line 3 says a valid first insert returns status 201, the body {"id":1,"name":"oats","qty":2}, and a Location header whose value is /items/1, 8 characters. That body is 30 characters: {"id": is 6, the digit 1 is 1, ,"name": is 8, "oats" is 6, ,"qty": is 7, the digit 2 is 1, and the closing brace is 1. 6 plus 1 plus 8 plus 6 plus 7 plus 1 plus 1 is 30. Line 4 says a name that is empty after strip returns status 400 and the body {"error":"empty name"}, 22 characters, with no Location header. strip removes spaces at both ends.

Line 5 says a qty that fails the integer rule returns status 400 and {"error":"qty"}, 15 characters, with no Location header. Line 6 says GET /items/1 after that first insert returns status 200 and the same 30-character body. Line 7 says GET /items/99, when that row is absent, returns status 404 and {"error":"missing"}, 19 characters. The integer rule sits on the same sheet. name is a str. The stripped length runs from 1 through 40. qty makes type(qty) is int evaluate to true, and qty sits from 1 through 99. True fails the rule. type(True) is bool evaluates to true, type(True) is int evaluates to false, and isinstance(True, int) evaluates to true. A name of 41 a's has length 41 after strip, so the status is 400 and the body is {"error":"name"}, 16 characters. A missing name key uses that same 16-character body. A missing qty key uses the 15-character qty body. REST means one method, one path, and the body these lines already fixed. POST /items, GET /items/1, and GET /items are three routes. After the first insert, GET /items returns status 200 and {"items":[{"id":1,"name":"oats","qty":2}]}. The wrapper {"items":[ plus ]} is 12 characters around the 30-character object, and 30 plus 12 is 42. You already append one word to a list on a page. These seven lines start where that word leaves the page.

Status on one POST and one GET

Walk POST /items with a name of 10 characters, three spaces, then oats, then three spaces, and with qty 2. strip leaves oats, length 4. type(2) is int evaluates to true, and 2 sits inside 1 through 99. Line 3 applies. The status is 201. The body is the 30-character object with id 1, because the table was empty and the first INTEGER PRIMARY KEY value on an empty SQLite table is 1. The Location value is /items/1. Walk POST with a name of three spaces and qty 2. strip leaves length 0. Line 4 applies. The status is 400, the body is the 22-character empty-name object, the location is absent, and the table gains no row. Walk POST with name oats and qty True. type(True) is int evaluates to false, so line 5 applies. The status is 400, the body is the 15-character qty object, and the table gains no row. Walk POST with name oats and qty 100. type(100) is int evaluates to true, and 100 sits past 99, so line 5 applies again: status 400, the same 15-character body, and no new row. Walk POST with name oats and qty 0. 0 sits below 1, so the result is that same 400 triple.

Walk GET /items/1 after the oats row is stored. Line 6 applies. The status is 200 and the body is the 30-character object. Walk GET /items/99 when the stored ids stop at 1, or at 2 after a later milk insert. Line 7 applies. The status is 404 and the body is the 19-character missing object. A handler that stores the row and returns status 200 still misses line 3, because the test reads the integer 201. The usual reason phrase beside 201 is Created, beside 400 is Bad Request, beside 404 is Not Found, and beside 200 is OK. Those phrases sit on the status line. The checks read the integers 201, 200, 400, and 404. Count a response by writing the integer first, then the body, then the Location value or the word absent.

The table and the question mark

The SQL text that creates the table is CREATE TABLE items (id INTEGER PRIMARY KEY, name TEXT NOT NULL, qty INTEGER NOT NULL). That string is 85 characters. The insert text is INSERT INTO items (name, qty) VALUES (?, ?). That string is 43 characters. The two question marks stay in the text. A parameterized query keeps that text fixed and sends the values beside it. In Python's sqlite3 the values travel in the second argument to execute, the tuple ("oats", 2). On an empty table the first execute stores id 1, name oats, and qty 2. A second execute with ("milk", 1) stores id 2. Reading both rows gives (1, "oats", 2) and (2, "milk", 1), and COUNT() is 2. Open a second connection to the same database file after BEGIN and after the insert, and before commit. That connection's COUNT() is 0. After commit, its COUNT(*) is 1 and the row is (1, "oats", 2). The other connection sees the row when the commit lands.

The select text is SELECT id, name, qty FROM items WHERE name = ?. That string is 46 characters. Bind the 11-character string ' OR '1'='1 as the single value. The comparison asks for a name equal to those 11 characters. oats has length 4 and milk has length 4, so the select returns an empty list, and COUNT(*) stays 2. Closing quotes around that same string builds one statement, SELECT id, name, qty FROM items WHERE name = '' OR '1'='1'. The OR keeps the condition true on every row of that single SELECT, so the call returns both rows. The question-mark form keeps the 11 characters as data. The stored table still holds 2 rows either way, because this SELECT only reads. D1 runs this SQLite SQL for these statements. The same 85-character CREATE TABLE and the same 43-character INSERT apply there. On D1 the values go to bind in the same left-to-right order as the question marks. The stored name is the string you bound, and a select for id 1 returns oats and 2.

The test that fails first

Writing the test before the function is test-driven development. The tool for this pass is pytest. Write test_post_oats_returns_201 in test_items.py while post_item is still absent. The test calls post_item with the object whose name is oats and whose qty is 2, then asserts three things: status equals 201, body equals the 30-character object, and location equals /items/1. The command python -m pytest test_items.py -q runs that one test. The call raises NameError, and pytest reports 1 failed. Put in a stub that ignores its argument and always returns 201, the oats body, and /items/1. Add test_blank_name_returns_400. That test calls post_item with a name of three spaces and qty 2, then asserts status 400, the 22-character body, and location None. The oats test passes its three asserts. The blank test stops on the first assert, because status is 201. pytest reports 1 failed, 1 passed.

Replace the stub with the checks from the contract. Strip name. When the length is 0, return 400, the empty-name body, and None. When type(qty) is int evaluates to false, or qty is below 1, or qty is above 99, return 400, the qty body, and None. Otherwise return 201, a body with id 1, the stripped name, and the qty, plus the location /items/1. For oats and 2, strip leaves oats and the qty check passes, so the 201 triple comes back. For three spaces, the length is 0, so the 400 triple comes back. pytest reports 2 passed. A third test sends name oats and qty True. It passes when the function uses type(qty) is int. A function that used isinstance(qty, int) would let True through and return a 201 whose qty is True, and that third test would fail. Paste the seven lines into the chat service if you want a draft of post_item, then run this same pytest from the folder that contains the file. The draft earns its place when the report says 2 passed. A later browser run can open the page with Playwright and read this same status on the response. The run you do today is pytest.

An example

One item, from the seven lines to two passing tests

Write the seven lines. The table is empty. POST the 23-character body {"name":"oats","qty":2}. post_item returns 201, the 30-character body, and /items/1. The insert uses the 43-character SQL and the tuple ("oats", 2). A second connection to the same file sees COUNT(*) 0 before commit and 1 after commit, and the row is (1, "oats", 2). GET /items/1 returns 200 and that same body. GET /items/99 returns 404 and the 19-character body. GET /items returns 200 and the 42-character list body.

POST a name of three spaces and qty 2. The status is 400, the body is the 22-character empty-name object, the location is None, and COUNT() stays 1. POST name oats and qty True. The status is 400, the body is the 15-character qty object, and COUNT() stays 1. POST name oats and qty 100. The result is that same 400 triple, and COUNT() stays 1. Insert ("milk", 1) with the question marks, so the ids are 1 and 2. Bind the 11-character string in the 46-character select. The select returns an empty list. The quote-joined SELECT returns both rows. COUNT() stays 2.

With post_item absent and only the oats test in the file, pytest reports 1 failed. With the stub that always returns the oats 201, and with both tests in the file, pytest reports 1 failed, 1 passed. With the contract checks in post_item, pytest reports 2 passed. The blank test is the one that moves from failed to passed when the stripped length is checked.

Exercise

Check it on paper

Count the characters in {"error":"qty"} and in {"items":[{"id":1,"name":"oats","qty":2}]}. Show the 42 as 30 plus 12. The first POST uses a name of 10 characters, three spaces, then oats, then three spaces, and qty 2, on an empty table. Write the status, the body, the Location value, and the stripped length. The next POST uses name oats and qty 100, with that oats row already stored. Write the status, the body, and COUNT(). Two rows are stored, oats and milk. The 46-character select binds the 11-character string ' OR '1'='1. Write how many rows come back, and write COUNT(). The stub returns the oats 201 for every call, and the file holds the oats test and the blank-name test. Write how many pass and how many fail. For qty True and name oats, write what type(True) is int gives, what isinstance(True, int) gives, and what the contract function returns.

The qty error body is 15 characters. The list body is 42 characters, 30 plus 12. The first POST returns status 201, the body {"id":1,"name":"oats","qty":2}, and Location /items/1. The stripped length is 4. The qty 100 POST returns status 400 and {"error":"qty"}, and COUNT() stays 1. The bound select returns 0 rows, and COUNT() stays 2. The stub run has 1 passed and 1 failed. type(True) is int gives false, isinstance(True, int) gives true, and the contract function returns 400, {"error":"qty"}, and None.

Check questions

Answer on your own first. Then open a question to see the answer.

How many characters are in {"name":"oats","qty":2}, and what status and Location value does line 3 require when that body is the first insert?

23 characters. Status 201, and the Location value is /items/1. The body that comes back is the 30-character object with id 1.

What status, body, and location does the contract require for a name of three spaces and qty 2, and does the table gain a row?

Status 400, body {"error":"empty name"}, and no Location header. The table gains no row, because strip leaves length 0 and line 4 stops before the insert.

Two rows are stored, oats and milk. The select binds the 11-character string ' OR '1'='1 to the question mark. How many rows does that select return, and what does COUNT(*) report?

The select returns 0 rows, because neither name equals those 11 characters. COUNT(*) reports 2. The quote-joined form of that SELECT returns both rows, and the table still holds 2.

The stub returns 201 and the oats body for every payload. pytest runs the oats test and the blank-name test. How many pass, and why does the other fail?

1 passed and 1 failed. The oats test passes. The blank-name test fails on its first assert, because the status is 201 and line 4 requires 400.

Take with you

Write the seven lines, return the status integer those lines name, keep every value beside the SQL text, and let pytest report 1 failed before post_item matches the contract.

Final task

Submit a REST API + SQLite/D1 schema and pytest suite; the runner verifies that the student's tests catch 8 seeded buggy implementations and that the student's API passes all 40 fixed contract and security tests.

The final task is marked against fixed answer keys and automated tests once the tests open.

Terms in this course

This course has no terms of its own in the glossary yet. Terms from earlier courses are used here too.

More lessons coming

The first lesson is open to read. The rest of the course is being written, and tests and diplomas come later. Join the waitlist