12. SQL via API e JOIN
Obiettivi
Sezione intitolata “Obiettivi”- Scrivere
SELECTcon JOIN che attraversano più tabelle seguendo le chiavi primarie (PK) ed esterne (FK). - Usare
GROUP BY/HAVING, sottoquery,WITH(CTE), viste e funzioni JSON di SQLite. - Leggere un
EXPLAIN QUERY PLANe capire quando un indice viene usato. - Sapere cosa non puoi fare (
DELETE,DROP,PRAGMA,ATTACH) e come il server lo impedisce.
Un’API che accetta SQL è rara in produzione (di solito è un pannello interno o uno strumento di analisi), ma è il modo più diretto per capire le relazioni tra tabelle. Il server di prova espone POST /api/v1/sql con regole precise:
- solo istruzioni di lettura (
SELECT,WITH … SELECT): il resto è rifiutato prima di toccare i dati; - massimo 200 righe (
truncated: truese ce ne sarebbero di più) e 2 secondi di esecuzione; - parametri con
?e listaparams, come in/query; - con il token sandbox interroghi la tua copia, senza token il dataset pubblico.
Chiavi primarie ed esterne
Sezione intitolata “Chiavi primarie ed esterne”Ogni tabella ha una chiave primaria (PK), la colonna id, che identifica una riga. Una chiave esterna (FK) è una colonna che contiene la PK di un’altra tabella: books.author_id “punta” ad authors.id. GET /api/v1/schema le elenca tutte:
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)Un JOIN è l’operazione che segue una FK: JOIN authors a ON a.id = b.author_id mette accanto a ogni libro la riga dell’autore corrispondente. Con più JOIN in fila attraversi la catena: ordine → righe d’ordine → libro → autore.
| Tipo | Cosa restituisce |
|---|---|
JOIN (inner) |
solo le righe che hanno una corrispondenza in entrambe le tabelle |
LEFT JOIN |
tutte le righe della tabella a sinistra, con NULL dove manca la corrispondenza (es. autori senza libri) |
curl, spiegato opzione per opzione
Sezione intitolata “curl, spiegato opzione per opzione”Due tabelle: libri con il nome dell’autore
Sezione intitolata “Due tabelle: libri con il nome dell’autore”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
- l'SQL viaggia nel body di una POST (come per
/query) - -d '{"sql": "…"}'
- un oggetto JSON con la chiave
sql; dentro la stringa i doppi apici SQL vanno evitati (usa alias e apici singoli SQL solo tramiteparams) - FROM books b
bè un alias: abbrevia il nome della tabella nel resto della query- JOIN authors a ON a.id = b.author_id
- la condizione di JOIN: PK di
authorsuguale alla FK dibooks - a.name AS author
- rinomina la colonna nell'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 }Il formato è colonnare: columns una volta sola, rows come liste. Più compatto di una lista di oggetti (e ti costringe a ragionare sulla posizione delle colonne).
Tre tabelle con aggregazione: quanti libri per categoria e autore
Sezione intitolata “Tre tabelle con aggregazione: quanti libri per categoria e autore”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 @-
- legge il body dallo standard input (
@= da file,-= stdin): comodo per SQL lungo - <<'EOF' … EOF
- un here-document della shell: tutto ciò che sta tra le due righe
EOFè lo stdin. Gli apici intorno al primo EOF impediscono alla shell di interpretare$e simili - GROUP BY c.id, a.id
- un gruppo per ogni coppia categoria-autore
- HAVING books >= 2
- filtra i gruppi dopo il conteggio (con WHERE non potresti:
booksnon esiste ancora)
{ "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 }Quattro tabelle: un ordine ricostruito per intero
Sezione intitolata “Quattro tabelle: un ordine ricostruito per intero”orders → customers (chi ha ordinato), orders → order_items (cosa), order_items → books (il titolo):
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, … }È esattamente ciò che GET /orders/1?expand=items.book,customer fa per te (capitolo 5): l’API “espande” le FK con dei JOIN. Ora sai cosa c’è sotto.
Sottoquery, CTE e viste
Sezione intitolata “Sottoquery, CTE e viste”Libri più cari della media (sottoquery scalare nel 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]]Una CTE (WITH nome AS (…)) dà un nome a una sottoquery e rende leggibili le catene di JOIN. Copie vendute per libro, escludendo gli ordini annullati (nota il parametro ?):
{ "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]], … }Una vista è una query salvata nel database che si interroga come una tabella. book_stats calcola recensioni e voto medio per libro:
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]]La gerarchia delle categorie (parent_id che punta alla stessa tabella) si attraversa con un 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]]Dentro la colonna JSON
Sezione intitolata “Dentro la colonna JSON”SQLite sa leggere dentro attributes (capitolo 13 della Sezione 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: l’indice c’è o no?
Sezione intitolata “EXPLAIN QUERY PLAN: l’indice c’è o no?”Con "explain": true il server antepone EXPLAIN QUERY PLAN e ti mostra come SQLite intende eseguire la 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 = salta dritto alle righe giuste. SCAN = legge tutta la tabella. Su 80 righe non cambia nulla; su 80 milioni è la differenza tra millisecondi e minuti. Le FK sono quasi sempre indicizzate proprio perché i JOIN le usano di continuo.
Cosa succede se provi a scrivere
Sezione intitolata “Cosa succede se provi a scrivere”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
- un ciclo della shell: la variabile
sqlassume a turno ogni stringa - -d "{\"sql\": \"$sql\"}"
- qui il body è tra doppi apici perché deve espandere
$sql; i doppi apici interni vanno protetti con la barra - ; echo
- stampa un a capo dopo ogni risposta
{"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"}}}Nessuna di queste è bloccata da un’espressione regolare sul testo (facile da aggirare): il server registra in SQLite un authorizer, una funzione che il motore chiama per ogni azione della query (leggere una colonna, chiamare una funzione, cancellare, allegare un file) e che risponde “permesso” solo a SELECT, lettura e funzioni. In più la connessione è aperta in modalità mode=ro (sola lettura) e un contatore interrompe la query dopo 2 secondi:
{ "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"}}(un prodotto cartesiano di 305⁴ ≈ 8,6 miliardi di righe: la difesa serve anche contro gli errori, non solo contro i malintenzionati).
Python e TypeScript
Sezione intitolata “Python e TypeScript”Una funzione sql() riusabile che trasforma il formato colonnare in una lista di dizionari/oggetti, con parametri:
# 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))Cosa dovresti vedere:
{'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));Cosa dovresti vedere:
┌─────────┬────────┬─────────────┬───────────────────────────┬──────────┐│ (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=?)'…Prova subito
Sezione intitolata “Prova subito”Header della risposta
Header della risposta
Header della risposta
Header della risposta
Esercizio
Sezione intitolata “Esercizio”Provalo online, senza installare nulla:
Python su Google Colab (serve un account Google gratuito per eseguire le celle); TypeScript su StackBlitz (si apre e parte da solo nel browser). Se l’API di prova è spenta, accendila da /api-attiva/.
- Per ogni tag, quanti libri lo hanno? (tabelle
tags,book_tags,books; attenzione alla PK composta dibook_tags). - I 3 libri con più recensioni e il nome del loro autore (usa la vista
book_stats). - Gli autori che non hanno mai venduto una copia (serve un
LEFT JOINe unIS NULL… oppureNOT INcon una sottoquery). - Guarda con
explain: truela querySELECT * FROM books WHERE title LIKE '%Trieste%': usa un indice? Perché?
Soluzione
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)— nel dataset di partenza il risultato è vuoto: tutti hanno venduto almeno una copia.SCAN books: unLIKEche inizia con%non può usare un indice B-tree (dovrebbe cercare “in mezzo” alle stringhe). Per questo esiste la ricerca full-text del capitolo 9.
Errori comuni
Sezione intitolata “Errori comuni”- JOIN senza
ON(o con la condizione sbagliata): ottieni il prodotto cartesiano, righe moltiplicate e conteggi gonfiati. COUNT(*)dopo unLEFT JOIN: conta anche le righe conNULL. UsaCOUNT(colonna_della_tabella_destra).- Colonne ambigue:
SELECT id FROM books b JOIN authors a …→ errore “ambiguous column”: prefissa sempre con l’alias. - Dimenticare
LIMIT: il server tronca a 200 righe e ti avvisa contruncated: true, ma è meglio chiedere solo ciò che serve. - Aspettarsi di scrivere:
/sqlè di sola lettura per progetto, non per caso.
In sintesi
Sezione intitolata “In sintesi”Con /sql hai seguito le chiavi esterne a mano: JOIN a due, tre e quattro tabelle, aggregazioni per gruppo, CTE, viste, funzioni JSON e il piano di esecuzione. Hai anche visto come un server espone SQL senza esporre il database: authorizer, connessione read-only, limiti di righe e tempo. Nel prossimo capitolo torni a scrivere, e scopri cosa garantisce che i dati restino coerenti: transazioni e vincoli di integrità.