12. SQL over the API and JOINs
- Write
SELECTs with JOINs that cross several tables following primary keys (PK) and foreign keys (FK). - Use
GROUP BY/HAVING, subqueries,WITH(CTEs), views and SQLite’s JSON functions. - Read an
EXPLAIN QUERY PLANand understand when an index is used. - Know what you cannot do (
DELETE,DROP,PRAGMA,ATTACH) and how the server prevents it.
Theory
Section titled “Theory”An API that accepts SQL is rare in production (it is usually an internal panel or an analytics tool), but it is the most direct way to understand the relations between tables. The test server exposes POST /api/v1/sql with precise rules:
- read statements only (
SELECT,WITH … SELECT): everything else is rejected before touching the data; - at most 200 rows (
truncated: trueif there would be more) and 2 seconds of execution; - parameters with
?and aparamslist, as in/query; - with the sandbox token you query your copy, without a token the public dataset.
Primary and foreign keys
Section titled “Primary and foreign keys”Every table has a primary key (PK), the id column, which identifies a row. A foreign key (FK) is a column that holds the PK of another table: books.author_id “points” to authors.id. GET /api/v1/schema lists them all:
books PK id FK author_id → authors.id, category_id → categories.idcategories PK id FK parent_id → categories.id (gerarchia: una tabella che punta a sé stessa)orders PK id FK customer_id → customers.idorder_items PK id FK order_id → orders.id, book_id → books.id (tabella ponte: N-N tra ordini e libri)reviews PK id FK book_id → books.id, customer_id → customers.idbook_tags PK (book_id, tag_id) FK verso books e tags (chiave primaria composta)A JOIN is the operation that follows an FK: JOIN authors a ON a.id = b.author_id puts the matching author row next to each book. With several JOINs in a row you walk the chain: order → order lines → book → author.
| Kind | What it returns |
|---|---|
JOIN (inner) |
only the rows that have a match in both tables |
LEFT JOIN |
all the rows of the left table, with NULL where the match is missing (e.g. authors with no books) |
curl, explained option by option
Section titled “curl, explained option by option”Two tables: books with the author’s name
Section titled “Two tables: books with the author’s name”curl -s -X POST https://corso-api-json.giuseppebosi.com/api/v1/sql \-H 'Content-Type: application/json' \-d '{"sql": "SELECT b.title, a.name AS author FROM books b JOIN authors a ON a.id = b.author_id ORDER BY b.id LIMIT 3"}'- -X POST … /sql
- the SQL travels in the body of a POST (as for
/query) - -d '{"sql": "…"}'
- a JSON object with the
sqlkey; inside the string avoid SQL double quotes (use aliases, and SQL single quotes only throughparams) - FROM books b
bis an alias: it shortens the table name in the rest of the query- JOIN authors a ON a.id = b.author_id
- the JOIN condition: the PK of
authorsequal to the FK ofbooks - a.name AS author
- renames the column in the output
{ "columns": ["title", "author"], "rows": [["Il silenzio di Ustica", "Laura Esposito"], ["TypeScript e Ferrara", "Beatrice Colombo"], ["Il cervello e Matera", "Lorenzo Vitali"]], "row_count": 3, "truncated": false, "elapsed_ms": 1.77 }The format is columnar: columns only once, rows as lists. More compact than a list of objects (and it forces you to think about column positions).
Three tables with aggregation: how many books per category and author
Section titled “Three tables with aggregation: how many books per category and author”curl -s -X POST https://corso-api-json.giuseppebosi.com/api/v1/sql -H 'Content-Type: application/json' -d @- <<'EOF'{"sql": "SELECT c.name AS category, a.name AS author, COUNT(*) AS books FROM books b JOIN authors a ON a.id = b.author_id JOIN categories c ON c.id = b.category_id GROUP BY c.id, a.id HAVING books >= 2 ORDER BY books DESC, category LIMIT 5"}EOF- -d @-
- reads the body from standard input (
@= from a file,-= stdin): handy for long SQL - <<'EOF' … EOF
- a shell here-document: everything between the two
EOFlines is stdin. The quotes around the first EOF stop the shell from interpreting$and the like - GROUP BY c.id, a.id
- one group for each category-author pair
- HAVING books >= 2
- filters the groups after counting (WHERE could not do it:
booksdoes not exist yet)
{ "columns": ["category", "author", "books"], "rows": [["Saggistica", "Matteo Rinaldi", 4], ["Ragazzi", "Nadia Karim", 3], ["Cucina", "Elena Marchetti", 2], ["Informatica", "Riccardo Fontana", 2], ["Ragazzi", "Samuel Okafor", 2]], "row_count": 5, "truncated": false, "elapsed_ms": 5.62 }Four tables: an order rebuilt in full
Section titled “Four tables: an order rebuilt in full”orders → customers (who ordered), orders → order_items (what), order_items → books (the title):
SELECT o.id AS order_id, cu.name AS customer, b.title, oi.quantity, oi.unit_priceFROM orders oJOIN customers cu ON cu.id = o.customer_idJOIN order_items oi ON oi.order_id = o.idJOIN books b ON b.id = oi.book_idWHERE o.id = 1{ "columns": ["order_id", "customer", "title", "quantity", "unit_price"], "rows": [[1, "Giovanni Costa", "Quello che resta di Montecarvo", 1, 12.04]], "row_count": 1, … }It is exactly what GET /orders/1?expand=items.book,customer does for you (chapter 5): the API “expands” the FKs with JOINs. Now you know what is underneath.
Subqueries, CTEs and views
Section titled “Subqueries, CTEs and views”Books more expensive than the average (a scalar subquery in the WHERE):
SELECT title, price FROM booksWHERE price > (SELECT AVG(price) FROM books)ORDER BY price DESC LIMIT 3-- → [["Quello che resta di Sestri", 34.49], ["Ustica e il Novecento", 34.36], ["La cucina di Ustica", 33.49]]A CTE (WITH name AS (…)) gives a name to a subquery and makes chains of JOINs readable. Copies sold per book, excluding cancelled orders (note the ? parameter):
{ "sql": "WITH vendite AS ( SELECT oi.book_id, SUM(oi.quantity) AS copie FROM order_items oi JOIN orders o ON o.id = oi.order_id WHERE o.status != ? GROUP BY oi.book_id) SELECT b.title, a.name AS author, v.copie FROM vendite v JOIN books b ON b.id = v.book_id JOIN authors a ON a.id = b.author_id ORDER BY v.copie DESC LIMIT 5", "params": ["cancelled"] }{ "columns": ["title", "author", "copie"], "rows": [["Quello che resta di Montecarvo", "Pietro Galli", 15], ["Quello che resta di Sestri", "Elena Marchetti", 14], ["La casa sul Porto Vecchio", "Lorenzo Vitali", 14], ["Contro Volterra", "Sofia Lindqvist", 13], ["Numeri e Trieste", "Giulia Serra", 12]], … }A view is a query saved in the database that you query like a table. book_stats computes reviews and average rating per book:
SELECT b.title, s.reviews_count, s.avg_ratingFROM book_stats s JOIN books b ON b.id = s.book_idWHERE s.reviews_count >= 5 ORDER BY s.avg_rating DESC LIMIT 3-- → [["Ritorno a Montecarvo", 6, 4.83], ["Nebbia su Aosta", 6, 4.83], ["Numeri e Trieste", 5, 4.8]]The category hierarchy (parent_id pointing to the same table) is walked with a self-join:
SELECT p.name AS parent, c.name AS child, COUNT(b.id) AS booksFROM categories cJOIN categories p ON p.id = c.parent_idLEFT JOIN books b ON b.category_id = c.idGROUP BY c.id ORDER BY parent, child-- → [["Narrativa", "Fantascienza", 6], ["Narrativa", "Gialli e thriller", 4], ["Narrativa", "Romanzi storici", 7],-- ["Saggistica", "Informatica", 7], ["Saggistica", "Scienza e tecnologia", 6], ["Saggistica", "Storia", 7]]Inside the JSON column
Section titled “Inside the JSON column”SQLite can read inside attributes (chapter 13 of Section 1):
SELECT title, attributes ->> '$.format' AS format, json_extract(attributes, '$.dimensions_cm.h') AS hFROM books WHERE json_extract(attributes, '$.ebook') = 1 LIMIT 3-- → [["TypeScript e Ferrara", "tascabile", 23], ["Contro Sestri", "brossura", 21], ["Andromeda e il Novecento", "rilegato", 19]]EXPLAIN QUERY PLAN: is the index there or not?
Section titled “EXPLAIN QUERY PLAN: is the index there or not?”With "explain": true the server prepends EXPLAIN QUERY PLAN and shows you how SQLite intends to run the query:
{ "sql": "SELECT * FROM books WHERE author_id = 3", "explain": true }→ "rows": [[3, 0, 0, "SEARCH books USING INDEX idx_books_author (author_id=?)"]]
{ "sql": "SELECT * FROM books WHERE pages > 300", "explain": true }→ "rows": [[2, 0, 0, "SCAN books"]]SEARCH … USING INDEX = jumps straight to the right rows. SCAN = reads the whole table. On 80 rows nothing changes; on 80 million it is the difference between milliseconds and minutes. FKs are almost always indexed precisely because JOINs use them all the time.
What happens if you try to write
Section titled “What happens if you try to write”for sql in "DELETE FROM books" "DROP TABLE books" "PRAGMA table_info(books)" "ATTACH DATABASE 'x.db' AS x" "SELECT * FROM sqlite_master"; docurl -s -X POST https://corso-api-json.giuseppebosi.com/api/v1/sql -H 'Content-Type: application/json' -d "{\"sql\": \"$sql\"}"; echodone- for sql in "…" "…"; do … done
- a shell loop: the
sqlvariable takes each string in turn - -d "{\"sql\": \"$sql\"}"
- here the body is in double quotes because it must expand
$sql; the inner double quotes must be protected with a backslash - ; echo
- prints a newline after each response
{"error":{"code":"sql_not_allowed","message":"Solo SELECT: questa istruzione non è permessa","details":{"sqlite":"not authorized"}}}{"error":{"code":"sql_not_allowed","message":"Solo SELECT: questa istruzione non è permessa","details":{"sqlite":"not authorized"}}}{"error":{"code":"sql_not_allowed","message":"Solo SELECT: questa istruzione non è permessa","details":{"sqlite":"not authorized"}}}{"error":{"code":"sql_not_allowed","message":"Solo SELECT: questa istruzione non è permessa","details":{"sqlite":"not authorized"}}}{"error":{"code":"sql_error","message":"SQL non valido","details":{"sqlite":"access to sqlite_master.type is prohibited"}}}None of these is blocked by a regular expression on the text (easy to get around): the server registers an authorizer in SQLite, a function the engine calls for every action of the query (reading a column, calling a function, deleting, attaching a file) and which answers “allowed” only to SELECT, reads and functions. On top of that the connection is opened in mode=ro (read-only) and a counter interrupts the query after 2 seconds:
{ "sql": "SELECT COUNT(*) FROM order_items a, order_items b, order_items c, order_items d" }→ {"error":{"code":"sql_timeout","message":"Query interrotta: superati 2 secondi"}}(a Cartesian product of 305⁴ ≈ 8.6 billion rows: the defense also protects against mistakes, not only against bad actors).
Python and TypeScript
Section titled “Python and TypeScript”A reusable sql() function that turns the columnar format into a list of dictionaries/objects, with parameters:
# pip install httpx → python3 sql.pyimport httpx
BASE = "https://corso-api-json.giuseppebosi.com/api/v1"
def sql(query: str, *params, explain: bool = False) -> list[dict]: r = httpx.post(f"{BASE}/sql", json={"sql": query, "params": list(params), "explain": explain}, timeout=10) if r.status_code != 200: err = r.json()["error"] raise RuntimeError(f"{err['code']}: {err['message']} {err.get('details', '')}") d = r.json() if d["truncated"]: print("attenzione: risultato troncato a", d["row_count"], "righe") return [dict(zip(d["columns"], riga)) for riga in d["rows"]]
# gli ordini di un cliente con i titoli: 4 tabellerighe = sql(""" SELECT o.id AS ordine, o.status, b.title, oi.quantity FROM customers cu JOIN orders o ON o.customer_id = cu.id JOIN order_items oi ON oi.order_id = o.id JOIN books b ON b.id = oi.book_id WHERE cu.id = ? ORDER BY o.id""", 3)for r in righe: print(r)
print(sql("SELECT * FROM orders WHERE customer_id = ?", 3, explain=True))What you should see:
{'ordine': 12, 'status': 'delivered', 'title': 'Vite di Kepler-7', 'quantity': 1}{'ordine': 12, 'status': 'delivered', 'title': 'Ritorno a Orvieto', 'quantity': 3}{'ordine': 12, 'status': 'delivered', 'title': 'Codice Camogli', 'quantity': 1}{'ordine': 12, 'status': 'delivered', 'title': 'Il valore di Otranto', 'quantity': 1}{'ordine': 24, 'status': 'cancelled', 'title': 'Le avventure di Ravello', 'quantity': 3}{'ordine': 24, 'status': 'cancelled', 'title': "Un'estate a Norcia", 'quantity': 2}{'ordine': 24, 'status': 'cancelled', 'title': 'Le stelle di Ferrara', 'quantity': 1}[{'id': 3, 'parent': 0, 'notused': 0, 'detail': 'SEARCH orders USING INDEX idx_orders_customer (customer_id=?)'}]// node --experimental-strip-types sql.tsconst BASE = "https://corso-api-json.giuseppebosi.com/api/v1";
type Risultato = { columns: string[]; rows: unknown[][]; row_count: number; truncated: boolean; elapsed_ms: number };
async function sql(query: string, params: unknown[] = [], explain = false): Promise<Record<string, unknown>[]> { const r = await fetch(`${BASE}/sql`, { method: "POST", headers: { "Content-Type": "application/json" }, body: JSON.stringify({ sql: query, params, explain }), }); if (!r.ok) { const { error } = await r.json(); throw new Error(`${error.code}: ${error.message} ${JSON.stringify(error.details ?? "")}`); } const d = (await r.json()) as Risultato; if (d.truncated) console.warn("attenzione: risultato troncato a", d.row_count, "righe"); return d.rows.map((riga) => Object.fromEntries(d.columns.map((c, i) => [c, riga[i]])));}
// gli ordini di un cliente con i titoli: 4 tabelleconst righe = await sql(` SELECT o.id AS ordine, o.status, b.title, oi.quantity FROM customers cu JOIN orders o ON o.customer_id = cu.id JOIN order_items oi ON oi.order_id = o.id JOIN books b ON b.id = oi.book_id WHERE cu.id = ? ORDER BY o.id`, [3]);console.table(righe);
console.log(await sql("SELECT * FROM orders WHERE customer_id = ?", [3], true));What you should see:
┌─────────┬────────┬─────────────┬───────────────────────────┬──────────┐│ (index) │ ordine │ status │ title │ quantity │├─────────┼────────┼─────────────┼───────────────────────────┼──────────┤│ 0 │ 12 │ 'delivered' │ 'Vite di Kepler-7' │ 1 ││ 1 │ 12 │ 'delivered' │ 'Ritorno a Orvieto' │ 3 ││ 2 │ 12 │ 'delivered' │ 'Codice Camogli' │ 1 ││ 3 │ 12 │ 'delivered' │ 'Il valore di Otranto' │ 1 ││ 4 │ 24 │ 'cancelled' │ 'Le avventure di Ravello' │ 3 ││ 5 │ 24 │ 'cancelled' │ "Un'estate a Norcia" │ 2 ││ 6 │ 24 │ 'cancelled' │ 'Le stelle di Ferrara' │ 1 │└─────────┴────────┴─────────────┴───────────────────────────┴──────────┘[ { id: 3, parent: 0, notused: 0, detail: 'SEARCH orders USING INDEX idx_orders_customer (customer_id=?)'…Try it now
Section titled “Try it now”Response headers
Response headers
Response headers
Response headers
Exercise
Section titled “Exercise”Try it online, nothing to install:
Python on Google Colab (a free Google account is needed to run the cells); TypeScript on StackBlitz (it opens and starts by itself in the browser). If the practice API is switched off, turn it on from /en/api-attiva/.
- For each tag, how many books have it? (tables
tags,book_tags,books; mind the composite PK ofbook_tags). - The 3 books with the most reviews and their author’s name (use the
book_statsview). - The authors who have never sold a copy (you need a
LEFT JOINand anIS NULL… orNOT INwith a subquery). - Look with
explain: trueat the querySELECT * FROM books WHERE title LIKE '%Trieste%': does it use an index? Why?
Solution
SELECT t.name, COUNT(bt.book_id) AS books FROM tags t LEFT JOIN book_tags bt ON bt.tag_id = t.id GROUP BY t.id ORDER BY books DESCSELECT b.title, a.name, s.reviews_count FROM book_stats s JOIN books b ON b.id = s.book_id JOIN authors a ON a.id = b.author_id ORDER BY s.reviews_count DESC LIMIT 3SELECT a.name FROM authors a WHERE a.id NOT IN (SELECT b.author_id FROM books b JOIN order_items oi ON oi.book_id = b.id)— in the starting dataset the result is empty: everyone has sold at least one copy.SCAN books: aLIKEstarting with%cannot use a B-tree index (it would have to search “in the middle” of the strings). That is why the full-text search of chapter 9 exists.
Common mistakes
Section titled “Common mistakes”- A JOIN without
ON(or with the wrong condition): you get the Cartesian product, multiplied rows and inflated counts. COUNT(*)after aLEFT JOIN: it also counts the rows withNULL. UseCOUNT(column_of_the_right_table).- Ambiguous columns:
SELECT id FROM books b JOIN authors a …→ “ambiguous column” error: always prefix with the alias. - Forgetting
LIMIT: the server truncates at 200 rows and warns you withtruncated: true, but it is better to ask only for what you need. - Expecting to write:
/sqlis read-only by design, not by accident.
Summary
Section titled “Summary”With /sql you followed foreign keys by hand: JOINs over two, three and four tables, per-group aggregations, CTEs, views, JSON functions and the execution plan. You also saw how a server exposes SQL without exposing the database: an authorizer, a read-only connection, row and time limits. In the next chapter you go back to writing, and discover what keeps the data consistent: transactions and integrity constraints.