What a text to SQL answer should show
Explaining a text to SQL answer means showing the user the query and its rows, not only a sentence. A sentence alone sounds certain even when the query was wrong.
Module 84 designs AI features for people. This lesson writes the reply a text to SQL feature gives: what it shows, when it admits doubt and when it asks first.
By the end of this lesson
Note
I can write a reply that shows the query, admits doubt and asks when a question is vague.
Key words
| Word | What it means | Example |
|---|---|---|
| Reply | what the user is shown after asking | |
| Reading | one way of turning a question into a query | |
| Vague word | a word with more than one meaning here | popular |
| Clarifying question | a question back that removes a guess | |
| Empty result | a query that returns no rows |
The cells students fill in are left blank in this preview.
Show the query and the rows
Put the query and its rows beside the sentence. A user who knows the data can spot a wrong join at once.
Keep the rows short: show the first few and say how many more there are. The sentence comes from the rows, never from the model's memory.
Worked example: two replies to one question
| Reply | What the user can check |
|---|---|
| Five books are out. | Nothing |
| Answer: 5. Query: SELECT COUNT(*) FROM loans WHERE returned = 0 | The filter, the table and the number |
The cells students fill in are left blank in this preview.
Say when you are not sure
"Who borrowed the most books?" has two readings. Most loans gives Asha, with a tie broken by name. Most different books gives Ben.
Run both readings. When they agree, answer. When they disagree, say you are not sure and show both. An empty result is an answer too: say no rows matched.
Ask before guessing a vague word
Words such as popular, recent, best and active have no single meaning in the data. Popular could be borrowed twice, or borrowed this month.
When a question holds one, ask a short clarifying question first. One question back costs less than a confident wrong answer.
A common mistake
Warning
Many people think a confident sentence is the most helpful reply. A confident wrong answer is worse than a question back. Answer firmly only when the readings agree and no word is vague.
Write the model helper, part 1
docker exec -w /home/node/project agent sh -c "printf '%s\n' 'const base = process.env.AI_URL || \"http://ai:4000\";' 'function count(data, name) {' ' return data.usage && typeof data.usage[name] === \"number\" ? data.usage[name] : null;' '}' '// A reply may wrap the query in a fenced block: keep what is inside it.' 'function sqlOf(text) {' ' const parts = text.split(String.fromCharCode(96).repeat(3));' ' const inner = (parts.length > 2 ? parts[1] : text).trim();' ' return inner.toLowerCase().startsWith(\"sql\") ? inner.slice(3).trim() : inner;' '}' > model.js"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Write the model helper, part 2
docker exec -w /home/node/project agent sh -c "printf '%s\n' 'async function ask(messages, sample, max) {' ' try {' ' const res = await fetch(base + \"/v1/chat/completions\", {' ' method: \"POST\",' ' headers: { \"content-type\": \"application/json\", authorization: \"Bearer lab\" },' ' body: JSON.stringify({ model: \"lab\", messages: messages, max_tokens: max || 100 }),' ' signal: AbortSignal.timeout(30000),' ' });' ' if (res.ok === false) throw new Error(\"status \" + res.status);' ' const data = await res.json();' ' const text = data.choices[0].message.content;' ' return { text, inTokens: count(data, \"prompt_tokens\"), outTokens: count(data, \"completion_tokens\"), recorded: false };' ' } catch (error) {' ' console.log(\"Recorded sample: no model answered (\" + error.message + \").\");' ' return { text: sample, inTokens: null, outTokens: null, recorded: true };' ' }' '}' 'module.exports = { ask, sqlOf };' >> model.js"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Write the small library database, part 1
docker exec -w /home/node/project agent sh -c "printf '%s\n' 'const { DatabaseSync } = require(\"node:sqlite\");' '// An invented library: six books, four members, nine loans.' 'const BOOKS = [[1, \"The Salt Road\", 2019], [2, \"Glass Harbour\", 2021], [3, \"A Map of Rain\", 2015],' ' [4, \"Quiet Engines\", 2023], [5, \"Seven Kettles\", 2018], [6, \"North of Noon\", 2022]];' 'const MEMBERS = [[1, \"Asha\"], [2, \"Ben\"], [3, \"Chen\"], [4, \"Dara\"]];' '// id, book_id, member_id, month, returned (1 back, 0 still out)' 'const LOANS = [[1, 1, 1, 3, 1], [2, 2, 1, 3, 1], [3, 3, 2, 3, 0], [4, 1, 3, 3, 1], [5, 1, 1, 4, 0],' ' [6, 4, 2, 4, 0], [7, 2, 3, 4, 1], [8, 5, 2, 5, 0], [9, 2, 4, 5, 0]];' > library.js"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Write the small library database, part 2
docker exec -w /home/node/project agent sh -c "printf '%s\n' 'function put(db, table, rows) {' ' const marks = rows[0].map(function () { return \"?\"; }).join(\", \");' ' const insert = db.prepare(\"INSERT INTO \" + table + \" VALUES (\" + marks + \")\");' ' rows.forEach(function (row) { insert.run(...row); });' '}' 'function open(file) {' ' const db = new DatabaseSync(file || \":memory:\");' ' db.exec(\"CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, year INTEGER)\");' ' db.exec(\"CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT)\");' ' db.exec(\"CREATE TABLE loans (id INTEGER PRIMARY KEY, book_id INTEGER REFERENCES books(id), \"' ' + \"member_id INTEGER REFERENCES members(id), month INTEGER, returned INTEGER)\");' ' put(db, \"books\", BOOKS);' ' put(db, \"members\", MEMBERS);' ' put(db, \"loans\", LOANS);' ' return db;' '}' 'module.exports = { open };' >> library.js"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Write the reply script, part 1
docker exec -w /home/node/project agent sh -c "printf '%s\n' 'const { ask } = require(\"./model\");' 'const { open } = require(\"./library\");' 'const db = open();' 'const VAGUE = [\"popular\", \"recent\", \"best\", \"active\"];' 'const M = \"SELECT m.name, COUNT(\";' 'const J = \") AS n FROM loans l JOIN members m ON m.id = l.member_id GROUP BY m.name ORDER BY n DESC, m.name LIMIT 1\";' > explain.js"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Write the reply script, part 2
docker exec -w /home/node/project agent sh -c "printf '%s\n' 'const asks = [' ' { q: \"How many books are still out?\", a: \"SELECT COUNT(*) FROM loans WHERE returned = 0\" },' ' { q: \"Which titles has member 2 borrowed?\",' ' a: \"SELECT b.title FROM loans l JOIN books b ON l.book_id = b.id WHERE l.member_id = 2\" },' ' { q: \"Which books are popular?\", a: \"SELECT book_id FROM loans GROUP BY book_id HAVING COUNT(*) > 1\" },' ' { q: \"Who borrowed the most books?\", a: M + \"*\" + J, b: M + \"DISTINCT l.book_id\" + J },' ' { q: \"Which books were borrowed in month 9?\",' ' a: \"SELECT b.title FROM loans l JOIN books b ON l.book_id = b.id WHERE l.month = 9\" },' '];' >> explain.js"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Write the reply script, part 3
docker exec -w /home/node/project agent sh -c "printf '%s\n' 'function rows(sql) {' ' return db.prepare(sql).all().map(function (r) { return Object.values(r).join(\" \"); });' '}' 'function reply(x) {' ' const vague = VAGUE.filter(function (w) { return x.q.toLowerCase().indexOf(w) >= 0; });' ' if (vague.length) return \"Before I answer: what do you mean by \" + vague[0] + \"?\";' ' const a = rows(x.a);' ' const b = x.b ? rows(x.b) : a;' ' if (a.join(\"|\") === b.join(\"|\")) {' ' if (a.length === 0) return \"No rows matched. Query: \" + x.a;' ' return \"Answer: \" + a.join(\"; \") + \". Query: \" + x.a;' ' }' ' return \"I am not sure. Two readings disagree: \" + a.join(\"; \") + \" or \" + b.join(\"; \");' '}' >> explain.js"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Write the reply script, part 4
docker exec -w /home/node/project agent sh -c "printf '%s\n' 'async function main() {' ' asks.forEach(function (x, i) { console.log((i + 1) + \" \" + x.q + \"\n \" + reply(x)); });' ' const facts = asks[0].q + \" Rows: \" + JSON.stringify(rows(asks[0].a));' ' const sample = \"Five books are still out on loan.\";' ' const res = await ask([{ role: \"system\", content: \"Say the answer in one short sentence. Use only the rows given.\" },' ' { role: \"user\", content: facts }], sample, 60);' ' console.log(\"model said: \" + res.text);' ' console.log(\" shown with its query and rows, so the user can check it\");' '}' 'main();' >> explain.js"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Reply to five questions
docker exec -w /home/node/project agent node --experimental-sqlite --no-warnings explain.jsShown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
What the Run prints
| Question | Reply |
|---|---|
| 1 How many books are still out? | Answer: 5. Query: ... |
| 2 Which titles has member 2 borrowed? | Answer: A Map of Rain; Quiet Engines; Seven Kettles. Query: ... |
| 3 Which books are popular? | Before I answer: what do you mean by popular? |
| 4 Who borrowed the most books? | I am not sure. Two readings disagree: Asha 3 or Ben 3 |
| 5 Which books were borrowed in month 9? | No rows matched. Query: ... |
The cells students fill in are left blank in this preview.