A leaderboard that is not a page
Open the leaderboard in a game on your phone. The top scores appear in the game's own style, with its colours and icons.
The game did not download a web page. It asked a server for the scores alone, as plain data, and drew them itself. Today you ask a server for data in the same way.
By the end of this lesson
Note
I can read and write data through an API.
Key words
| Word | What it means | Example |
|---|---|---|
| API | a door that lets one program ask another for data | |
| JSON | a way of writing data as plain text | {"player":"Chen","points":990} |
| Object | names and values held together in curly brackets | |
| GET | the kind of request that reads data | |
| POST | the kind of request that sends new data | |
| Query string | the part of an address after the question mark | ?id=eq.3 |
The cells students fill in are left blank in this preview.
Before you start
Note
Press Start the lab and wait until it says it is running. The lab has a database called db and an API called api. The API turns every table in the database into an address. Press the Runs in order: each one builds on the one before.
First, some data to read
The first five Runs use psql, the database's own command line, to make a table of game scores. The table is called scores. Each row has an id, a player and their points.
The last of the five tells the API to look at the database again, so it sees the new table.
Give psql the password
export PGPASSWORD=shopShown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Start with a clean table
psql -h db -U shop -c "drop table if exists scores"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Make the scores table
psql -h db -U shop -c "create table scores (id serial primary key, player text not null, points int not null)"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Add four players
psql -h db -U shop -c "insert into scores (player, points) values ('Aisha', 820), ('Ben', 450), ('Chen', 990), ('Dev', 610)"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Tell the API to look again
psql -h db -U shop -c "notify pgrst, 'reload schema'"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
JSON is data written as text
The API replies in JSON. Square brackets [ ] hold a list. Curly brackets { } hold one object, here one player.
Inside an object, each name is in double quotes, then a colon, then its value. Text values have quotes. Numbers do not.
Read the whole table
curl -s -w "\n" "http://api:3000/scores?order=id"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Worked example: one object, piece by piece
| Name | Value | Kind of value |
|---|---|---|
| "id" | 3 | a number, so no quotes |
| "player" | "Chen" | text, so it has quotes |
| "points" | 990 | a number, so no quotes |
| The whole object | {"id":3,"player":"Chen","points":990} | one row of the table |
The cells students fill in are left blank in this preview.
The query string chooses the rows
Everything after ? in the address is the query string. This API reads it as instructions.
points=gte.600 keeps players with 600 points or more: gte means greater than or equal. order=points.desc puts the biggest first. An & joins two instructions.
Only players on 600 or more
curl -s -w "\n" "http://api:3000/scores?points=gte.600&order=points.desc"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
POST sends new data
So far every request was a GET, which only reads. A POST sends data for the server to store.
The data goes in the body of the request, after -d. The header Content-Type: application/json says the body is JSON. The Prefer header asks the API to send the new row back.
Add a player and see the reply
curl -s -w "\n" -X POST -H "Content-Type: application/json" -H "Prefer: return=representation" -d '{"player":"Ella","points":700}' "http://api:3000/scores"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
A common mistake
Warning
Many people think GET and POST are the same, because both go to an address. A GET should only read, so it is safe to repeat. A POST changes what is stored. Pressing it twice adds the row twice.
Going further: asking for nobody
Type curl -s -w "\n" "http://api:3000/scores?player=eq.Zoe" in the terminal. There is no player called Zoe. What comes back?
Now add -o /dev/null -w "%{http_code}\n" in place of -w "\n" to see only the status code. Why is this not a 404?