What happens to a request inside a backend
A backend is the program that sits behind a web address and answers requests. It reads the request, decides what it is for, asks the data, and sends an answer back.
Every backend does these four jobs, whatever language it is written in. In this lesson you watch one do them.
By the end of this lesson
Note
I can trace what a backend does with a request, from the address to the SQL it sends.
Key words
| Word | What it means |
|---|---|
| Backend | the program behind an address that answers requests |
| Route | the rule that sends a request to the code that handles it |
| Handler | the code that answers one kind of request |
| Query string | the part of an address after the question mark |
| Parameter | a value sent to the database apart from the SQL text |
| Response | the status, headers and body a backend sends back |
The cells students fill in are left blank in this preview.
Four steps from request to response
First the backend parses the request: its method, its path and its query string. Then it routes it, working out which handler it is for.
The handler checks the request, then asks the data, then answers with a status and a body. The lab's backend is a program called PostgREST. Its routes are the tables. Make a table to ask about.
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.
Make the books table
psql -h db -U shop -c "drop table if exists books; create table books (id serial primary key, title text not null, year int not null, available boolean not null default true); insert into books (title, year, available) values ('Maps of the Sea', 1998, true), ('Rivers of Europe', 2004, false), ('Night Trains', 2011, true); notify pgrst, 'reload schema'"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Ask about a column that is not there
curl -s -w "\n" "http://api:3000/books?nope=eq.1"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
A backend checks before it asks
The answer says there is no column called nope, with a 400 and a reason. The code 42703 is PostgreSQL's own, so here the database itself spotted the bad name.
A careful backend checks names before it asks the data. A request that cannot work is then turned away at once, and bad requests never reach the data. Your assignment works this way.
The query string is the request's question
The address /books?available=eq.true&select=title&order=id says three things. Keep only the rows where available equals true. Give back only the title column. Put the rows in order of id.
Send it, and then ask the database the same question yourself in SQL.
Send the request
curl -s -w "\n" "http://api:3000/books?available=eq.true&select=title&order=id"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Ask the database the same question
psql -h db -U shop -At -c "select title from books where available = true order by id"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Watch the backend ask the database
The backend wrote that SQL for you. To see it, ask PostgreSQL to log every statement it receives, then read the log with the lab's docker logs. The setting is only for this lesson, and the last Runs turn it off.
docker logs works only in a lab that has its Docker command.
Turn statement logging on
psql -h db -U shop -c "alter system set log_statement = 'all'" -c "select pg_reload_conf()"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Send the same request again
curl -s -o /dev/null "http://api:3000/books?available=eq.true&select=title&order=id"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Read the SQL the backend sent
docker logs --tail 30 db 2>&1 | grep -o 'SELECT "public"."books"[^)]*' | tail -1Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Worked example: request part to SQL part
| Part of the request | What it asks | The SQL that was sent |
|---|---|---|
| /books | The books table | FROM "public"."books" |
| available=eq.true | Only rows where available is true | WHERE "public"."books"."available" = $1 |
| select=title | Only the title column | SELECT "public"."books"."title" |
| order=id | In order of id | ORDER BY "public"."books"."id" |
The cells students fill in are left blank in this preview.
Read the value, sent apart
docker logs --tail 30 db 2>&1 | grep -o 'parameters: .*' | tail -1Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
The value travels apart from the SQL
The SQL says = $1. The value, t for true, was sent separately, so the database treats it as data and never as SQL. That is why a request cannot rewrite the query.
Prove it. Send a title that looks like an attack, and see what the backend does with it.
Send a title that looks like SQL
curl -s -G --data-urlencode "title=eq.x'; drop table books; --" -w "\n" "http://api:3000/books"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
See how it was passed on
docker logs --tail 30 db 2>&1 | grep -o 'parameters: .*' | tail -1Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Count the books
psql -h db -U shop -At -c "select count(*) from books"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Turn statement logging off again
psql -h db -U shop -c "alter system reset log_statement" -c "select pg_reload_conf()"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 backend is just a pipe between the browser and the database. It is not: it checks, decides and limits. It must tell an empty answer from a bad request. No matching rows is a 200 with an empty list. A request that cannot work is a 400.
Going further: a bigger question
Write a request in the same style for this question: the available books published after 2000, newest first, only the title and year. Send it with curl.
Then write the SQL you expect, and read the log to check.