CS 257: Software Design

Web app: DB and API design, plus unit tests

Folders: webapp/doc, webapp/tests

Tag: api-design

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

Goals

• Prepare a first draft of designs for the database and API that will provide the data your web application needs.
• Practice using test-driven development in the context of an HTTP-based API.

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 mywebapp.css) and a <script> tag to load a Javascript file (mywebapp.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 Submit 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 Submit button.
• The click triggers a Javascript function in webapp.js (let's call it onSubmit).
• onSubmit 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, onSubmit 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 Submit button lets me search for all the books whose titles contain my search string. Then onSubmit 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 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 2022 winter games added a new sport, like "Below-Freezing Flagpole-Licking".

Testing the API

THIS PART OF THE ASSIGNMENT IS CANCELLED.

You can, of course, test your API implementation (once you do it—that's a later assignment!) by manually pointing a browser at your API or running curl at the command line. This is a manual process, however, and it would be a lot better if you could automate at least some of the testing. So you're going to practice the Test-Driven Development approach by writing some PyUnit unit tests to exercise your API.

Start by completing the PyUnit lab, and remind yourself of the techniques used in the Using HTTP-based APIs lab. Then:

• Pick an endpoint in your API design.
• Think about different ways you can invoke that endpoint, and what JSON you should get back as a result. Think of normal queries, but also weird ones (e.g. empty search strings, search strings of gibberish, etc.)
• For each of your ideas from the previous bullet, write a PyUnit test method.
• Repeat for each endpoint.

Note that you won't be able to test your API yet, and thus you won't be able to test your tests. But write first drafts of your tests so we can talk about them, and we'll actually use the tests later.

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. And finally, you will write unit tests in PyUnit to test your API endpoints. More specifically:

• 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".
• THIS PART OF THE ASSIGNMENT IS CANCELLED. DON'T DO IT. Create a file called api-tests.py in your webapp/tests directory. It should be a PyUnit program modeled after primecheckertests.py from the PyUnit lab.

Have fun!