The app that never sees the database
A help-desk app on a phone shows open tickets. It does not connect to the database.
It sends a web request to a small server. The server runs the query and sends the rows back.
That server is a web API. Almost every app you use talks to its data this way.
By the end of this lesson
Note
I can read and write rows over HTTP.
Key words
| Word | What it means | Example |
|---|---|---|
| API | a way for one program to ask another for data | |
| Endpoint | the web address of one table or action | /tickets |
| HTTP method | the verb of a request: read, add, change or remove | GET |
| JSON | text that writes rows as lists and name-value pairs | |
| Status code | a number saying how a request went | 404 |
| Filter | a test in the address that picks which rows come back | status=eq.open |
The cells students fill in are left blank in this preview.
An API puts a table on the web
In this lab a tool called PostgREST turns each table into a web address. /tickets is the table tickets.
You send a request with curl. The server answers with rows written as JSON: a list of items in square brackets, each row in curly brackets.
First make a help-desk table with psql, the database client. Run these in order.
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 clean
psql -h db -U shop -c "drop table if exists tickets"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Make the tickets table
psql -h db -U shop -c "create table tickets (id serial primary key, title text not null, status text not null default 'open', priority 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 tickets
psql -h db -U shop -c "insert into tickets (title, status, priority) values ('Printer jams', 'open', 2), ('No wifi in room 4', 'open', 3), ('Reset a password', 'closed', 1), ('Laptop will not boot', 'open', 3)"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.
Read the whole table
curl -s -w "\n" "http://api:3000/tickets?order=id"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
The address says which rows you want
Everything after the ? is a filter, like a WHERE. status=eq.open means status equals open. priority=gte.3 means priority is 3 or more.
select=id,title picks columns. order=priority.desc sorts. limit=2 cuts the list. Join them with &.
Put the whole address in double quotes, or the shell reads the &.
Only the open tickets
curl -s -w "\n" "http://api:3000/tickets?status=eq.open&order=id"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Two columns, urgent tickets only
curl -s -w "\n" "http://api:3000/tickets?priority=gte.3&select=id,title&order=id"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
The two most urgent
curl -s -w "\n" "http://api:3000/tickets?order=priority.desc,id&limit=2"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Worked example: SQL and HTTP side by side
| SQL | HTTP method | Address and body |
|---|---|---|
| SELECT ... WHERE | GET | /tickets?status=eq.open |
| INSERT INTO | POST | /tickets, with the new row as JSON |
| UPDATE ... WHERE | PATCH | /tickets?id=eq.1, with the changes as JSON |
| DELETE ... WHERE | DELETE | /tickets?id=eq.3 |
The cells students fill in are left blank in this preview.
POST, PATCH and DELETE change data
To add a row, POST it as JSON. To change rows, PATCH the changes. To remove rows, send DELETE.
Just as in SQL, the filter in the address decides which rows are touched. A PATCH or DELETE with no filter would touch every row.
The server says how it went with a status code.
Add a ticket and see it come back
curl -s -w "\n" -X POST -H "Content-Type: application/json" -H "Prefer: return=representation" -d '{"title":"Monitor flickers","priority":2}' "http://api:3000/tickets"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Close ticket 1
curl -s -o /dev/null -w "%{http_code}\n" -X PATCH -H "Content-Type: application/json" -d '{"status":"closed"}' "http://api:3000/tickets?id=eq.1"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Delete ticket 3
curl -s -o /dev/null -w "%{http_code}\n" -X DELETE "http://api:3000/tickets?id=eq.3"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
A row the database refuses
curl -s -w "\n%{http_code}\n" -X POST -H "Content-Type: application/json" -d '{"priority":1}' "http://api:3000/tickets"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 a 2xx answer means the request did what they wanted. The code only says the server understood and accepted it. A PATCH whose filter matches nothing still answers 204. Read the table again to check.
Close ticket 99, which does not exist
curl -s -o /dev/null -w "%{http_code}\n" -X PATCH -H "Content-Type: application/json" -d '{"status":"closed"}' "http://api:3000/tickets?id=eq.99"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Read the table again
curl -s -w "\n" "http://api:3000/tickets?order=id"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Worked example: reading status codes
| Code | Meaning | Seen in this lesson |
|---|---|---|
| 200 | Here are the rows | Reading the table |
| 201 | Created a new row | A POST that does not ask for the row back |
| 204 | Done, nothing to send back | The PATCH and the DELETE |
| 400 | The request was wrong or broke a rule | The ticket with no title |
| 404 | No such table or address | Asking for /nothing |
The cells students fill in are left blank in this preview.
Ask for a table that is not there
curl -s -w "\n%{http_code}\n" "http://api:3000/nothing"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.