Web app: DB and API design, plus unit tests

Folder: webapp/doc

You will work with your web application team for this assignment.

Goals

Prepare a first draft design for the API that will provide the data your web application needs.
Prepare a first draft design for the database the API will use to implement its endpoints.

Our web application architecture

Any page of your web application will consist of HTML code, which (among other things) uses a <link> tag to load a CSS file (pretend it's called webapp.css) and a <script> tag to load a Javascript file (webapp.js). This means that if a person has navigated to your page in a browser, the browser will also have retrieved webapp.css (using it to display the page) and webapp.js (parsing it and executing any of its code that needs to be executed right away).

With those assumptions, this is how a typical interaction with your web application will go.

The person has loaded into a browser your home page, which includes a search box and a Search button. (You might not have these two things on your home page, but for the sake of concreteness in this discussion, let's pretend you do.)
The person types "pangolin" into the search box and clicks the Search button.
The click triggers a Javascript function in webapp.js (let's call it onSearch).
onSearch sends a query to one of your API endpoints (whichever one is relevant), asking it to do a search for the string "Pangolin". At the same time, onSearch specifies some code (let's say it's a function called onSearchResults) to be called when the API response arrives.
Your API endpoint translates the API query into a SQL query and sends it to PostgreSQL on your server.
PostgreSQL does the query and returns the results to your API as database rows.
Your API endpoint accesses these rows using a psycopg2 cursor object, and transforms them into JSON.
Your API endpoint function returns the JSON, which causes the Flask code to send the JSON back to the browser.
The Javascript function onSearchResults, running in the person's browser, turns the JSON into Javascript dictionaries, lists, etc. as appropriate, and then uses that data to change the HTML of the page (presumably to show a table or a graph or something to show the search results).
The browser updates the display to reflect the new HTML.
The person is happy.

More concisely, the queries and responses flow like this:

person clicking → Javascript → API server → database → API server → Javascript → browser display → user

I'll talk another time about how this wouldn't have to be our architecture, and what pedagogical and engineering motivations I have for choosing it.

How do we put that all together?

We'll need to implement as many of these pieces independently as possible. This is one of the hardest parts of complex projects—how to put sequence the work so that you can focus your attention on one small part at a time, and test those small parts independently of the system as a whole.

For the moment, let's focus on how the needs of the user interface can be served by the API. Thinking about what the UI needs (and more to the point, what the user needs) will give you insight into what endpoints your API should include.

Suppose, for example, that the Search button lets me search for all the books whose titles contain my search string. Then onSearch will probably want an API endpoint that looks something like this:

REQUEST: /books/title_contains/{search_text}

or, depending on your preference and your other endpoints:

REQUEST: /books?title_contains={search_text}

Either way, onSearchResults (or probably a function named onBookTitleSearchResults in this context) will want the response from the endpoint to look like this:

RESPONSE: a JSON list of dictionaries, each of which represents one book, sorted by publication date. Each book dictionary will have the following fields. title -- (string) the title of the book author -- (string) the full name(s) of the author(s) publication_year -- (int) the year the book was published

For another example, suppose I want a drop-down list (implemented as a <select> element in HTML) containing the alphabetized list of Olympic sports. Then the Javascript that initializes our HTML page might want to invoke an API endpoint like this:

REQUEST: /sports RESPONSE: an alphabetized JSON list of strings, each of which is an Olympic sport.

after which the onSportsSearchResults function would insert the list of sports into the <select> element as a sequence of <option> elements. That is, the Javascript would call the API so it could populate the dropdown list with the complete list of sports in the database. This would be better than hard-coding the sports into the HTML, because it would adapt appropriately if the 2026 winter games added a new sport, like "Below-Freezing Flagpole-Licking".

Your tasks

In summary, for this assignment you're going to decide, based on your user stories and wireframes, what endpoints your API will require. You'll then design a database table structure to support those endpoints.

Create a file called api-design.txt in your webapp/doc directory. It should contain:
- Your names
- A list of REQUEST and RESPONSE descriptions like the above for each of the endpoints you expect your API to need.
Create a file called database-schema.sql in your webapp/doc directory. It should contain your CREATE TABLE statements and nothing else. As with your previous database design assignment, this file should be executable via "psql -U USER_NAME DATABASE_NAME < database-schema.sql".

By the way...

If we were a startup and I were your manager, at this stage of the project I would also require you to write a suite of unit tests for the API. For this project, I am not going to require unit tests, but we will spend a day in class working through how you would do so.

Sample API documentation

Suppose I'm doing a books/authors API. I like to have documentation with REQUEST (including GET parameters), RESPONSE, and EXAMPLES. Like this.

REQUEST: /authors GET parameters search_text (Optional, default: '') -- return only authors whose first or last names contain search_text, case-insensitively start_year (Optional, default: -infinity) -- return only authors who were born no earlier than start_year end_year (Optional, default: infinity) -- return only authors who died no later than end_year RESPONSE: a JSON list of dictionaries, each of which represents one author, sorted alphabetically by last name (and sorted alphabetically by first name when last names are equal). Each dictionary in this list will have the following fields. last_name -- (TEXT) the author's last or family name first_name -- (TEXT) the author's first or given name or names birth_year -- (INTEGER) the year the author was born death_year -- (INTEGER) the year the author died (or 'NULL' if still alive) EXAMPLE: /authors?search_text=bront&start_year=1817 [{"last_name":"Brontë", "first_name":"Ann", "birth_year":1820, "death_year":1849}, {"last_name":"Brontë", "first_name":"Emily", "birth_year":1818, "death_year":1848}] (no Charlotte, since she was born in 1816)