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:
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:
or, depending on your preference and your other endpoints:
Either way, onSearchResults (or probably a function named onBookTitleSearchResults in this context) will want the response from the endpoint to look like this:
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:
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.