How to test a text to SQL feature
An evaluation set for text to SQL is a list of questions whose right answers you know, run every time the prompt or model changes. It tells you whether a change made things better or worse.
Modules 68 and 85 score replies in words. This lesson scores SQL by the rows it returns.
By the end of this lesson
Note
I can score candidate queries against a set of questions with known answers.
Key words
| Word | What it means |
|---|---|
| Evaluation set | questions with known answers, run on every change |
| Gold query | a query checked by hand that gives the right rows |
| Execution match | same rows as the gold query |
| Regression | a question that passed before and fails now |
| Gate | a rule that blocks a change |
The cells students fill in are left blank in this preview.
Every case has a question and gold query
A case holds a question, a gold query and whether order matters. Write the gold query yourself and check its rows by hand on the fixture.
Run the gold query to get the expected rows. Then the set keeps working when the data grows, as long as the gold queries stay right.
Worked example: five cases for the library
| Case | Question | Gold rows |
|---|---|---|
| 1 | Loans made in month 3 | 4 |
| 2 | Different members in month 3 | 3 |
| 3 | Books still out | 5 |
| 4 | Titles member 2 borrowed | A Map of Rain, Quiet Engines, Seven Kettles |
| 5 | Books never borrowed | North of Noon |
The cells students fill in are left blank in this preview.
Score by result, not by text
COUNT(id) and COUNT(*) give the same number here, and a join can be written as a subquery. Two different texts can both be right.
So run each candidate and compare its rows with the gold rows. Exact text match marks right answers wrong, as the Run shows.
Worked example: text and result disagree
| Candidate | Same text as gold | Same rows as gold |
|---|---|---|
| SELECT COUNT(id) FROM loans WHERE month = 3 | No | Yes |
| A join written as IN (SELECT book_id ...) | No | Yes |
| SELECT COUNT(*) FROM loans | No | No |
The cells students fill in are left blank in this preview.
A regression hides inside the same score
A new prompt fixes question 2 and breaks question 3. Both versions pass four of five, so the score did not move.
Compare case by case. A question that passed before and fails now is a regression, and the gate blocks the change until it is fixed.
A common mistake
Warning
Many people think an equal pass rate means nothing got worse. One question can be fixed while another breaks. Compare each case with the last run, and block any change that loses a pass.
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 evaluation script, part 1
docker exec -w /home/node/project agent sh -c "printf '%s\n' 'const { ask, sqlOf } = require(\"./model\");' 'const { open } = require(\"./library\");' 'const db = open();' 'const cases = [' ' { gold: \"SELECT COUNT(*) FROM loans WHERE month = 3\" },' ' { gold: \"SELECT COUNT(DISTINCT member_id) FROM loans WHERE month = 3\" },' ' { gold: \"SELECT COUNT(*) FROM loans WHERE returned = 0\" },' ' { gold: \"SELECT b.title FROM loans l JOIN books b ON l.book_id = b.id WHERE l.member_id = 2\" },' ' { gold: \"SELECT title FROM books WHERE id NOT IN (SELECT book_id FROM loans)\" },' '];' > eval.js"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Write the evaluation script, part 2
docker exec -w /home/node/project agent sh -c "printf '%s\n' 'const A = [\"SELECT COUNT(id) FROM loans WHERE month = 3\",' ' \"SELECT COUNT(member_id) FROM loans WHERE month = 3\", cases[2].gold,' ' \"SELECT b.title FROM books b JOIN loans l ON l.book_id = b.id WHERE l.member_id = 2\",' ' \"SELECT b.title FROM books b LEFT JOIN loans l ON l.book_id = b.id WHERE l.id IS NULL\"];' 'const B = [cases[0].gold, cases[1].gold, \"SELECT COUNT(*) FROM loans\",' ' \"SELECT title FROM books WHERE id IN (SELECT book_id FROM loans WHERE member_id = 2)\", cases[4].gold];' 'function rows(sql) {' ' try { return db.prepare(sql).all().map(function (r) { return JSON.stringify(Object.values(r)); }).sort().join(\"|\"); }' ' catch (error) { return null; }' '}' >> eval.js"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Write the evaluation script, part 3
docker exec -w /home/node/project agent sh -c "printf '%s\n' 'function score(sql, i) {' ' const got = rows(sql);' ' return got === null ? \"error\" : got === rows(cases[i].gold) ? \"pass\" : \"fail\";' '}' 'function rate(list, test) {' ' return Math.floor(100 * list.filter(test).length / list.length) + \" percent\";' '}' 'const a = A.map(score);' 'const b = B.map(score);' 'const same = function (sql, i) { return sql === cases[i].gold; };' '// A regression: passed in A, does not pass in B.' 'const lost = [];' 'a.forEach(function (v, i) { if (v === \"pass\" && b[i] === \"fail\" || v === \"pass\" && b[i] === \"error\") lost.push(i + 1); });' >> eval.js"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Write the evaluation script, part 4
docker exec -w /home/node/project agent sh -c "printf '%s\n' 'async function main() {' ' a.forEach(function (v, i) { console.log(\"question \" + (i + 1) + \": A \" + v + \", B \" + b[i]); });' ' const pass = function (v) { return v === \"pass\"; };' ' console.log(\"by result: A \" + rate(a, pass) + \", B \" + rate(b, pass));' ' console.log(\"by exact text: A \" + rate(A, same) + \", B \" + rate(B, same));' ' console.log(\"regressions in B: \" + (lost.length ? lost.join(\", \") : \"none\"));' ' console.log(\"gate: \" + (lost.length ? \"block, a question that passed now fails\" : \"ship\"));' ' await live();' '}' >> eval.js"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Write the evaluation script, part 5
docker exec -w /home/node/project agent sh -c "printf '%s\n' 'async function live() {' ' const q = \"loans(id, book_id, member_id, month, returned)\nQuestion: How many different members borrowed a book in month 3?\";' ' const sample = \"SELECT COUNT(DISTINCT member_id) FROM loans WHERE month = 3;\";' ' const reply = await ask([{ role: \"system\", content: \"Answer with one SQLite SELECT query and nothing else.\" },' ' { role: \"user\", content: q }], sample, 120);' ' const sql = sqlOf(reply.text);' ' console.log(\"model wrote: \" + sql);' ' console.log(\" scored apart from the totals: \" + score(sql, 1));' '}' 'main();' >> eval.js"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Score two versions on five questions
docker exec -w /home/node/project agent node --experimental-sqlite --no-warnings eval.jsShown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
What the Run prints
| Line | What it shows |
|---|---|
| question 2: A fail, B pass | The new prompt fixed question 2 |
| question 3: A pass, B fail | The new prompt broke question 3 |
| by result: A 80 percent, B 80 percent | The score did not move |
| by exact text: A 20 percent, B 60 percent | Text match marks right answers wrong |
| regressions in B: 3 | The question that was lost |
| gate: block, a question that passed now fails | The decision |
The cells students fill in are left blank in this preview.