What text to SQL needs to know
Text to SQL means a language model turns a question in plain words into an SQL query. The model has never seen your database, so it can only guess at tables you do not describe.
Module 4 teaches SQL and module 63 asks a model for a fixed shape. This lesson decides what the model must be told about your tables.
By the end of this lesson
Note
I can describe a database schema briefly enough for a model to write SQL from it.
Key words
| Word | What it means | Example |
|---|---|---|
| Text to SQL | a model turning a question into a query | |
| Schema | the tables and columns of a database | |
| Foreign key | a column that points at a row in another table | loans.book_id |
| Join | matching rows of two tables on a column | |
| Note | a line saying what a column's values mean |
The cells students fill in are left blank in this preview.
A model needs tables, columns and joins
To write a query the model needs three things. It needs the table names, the columns in each table, and how the tables join.
Without the joins it guesses. A guess such as joining loans.id to books.id runs without an error and gives the wrong books.
Worked example: the library's schema
| Table | Columns | Joins to |
|---|---|---|
| books | id, title, year | Nothing |
| members | id, name | Nothing |
| loans | id, book_id, member_id, month, returned | book_id to books.id, member_id to members.id |
The cells students fill in are left blank in this preview.
Some columns need a note
A column's name does not always say what its values mean. In the library, returned is 1 when a book is back and 0 while it is out.
A model reading only the name might test returned = "no" or returned IS NULL. One short note per unclear column stops that guess.
Worked example: names that need a note
| Column | A guess without a note | The note |
|---|---|---|
| loans.returned | returned = "no" | 1 when back, 0 while out |
| loans.month | A date such as 2026-03-01 | 1 to 12, this year |
| books.year | Year it was bought | Year it was published |
The cells students fill in are left blank in this preview.
A common mistake
Warning
Many people think a model already knows their database. It has never seen it. Send the tables, joins and notes with every question, because each request starts from nothing.
A brief description beats the whole CREATE text
You could paste every CREATE TABLE statement. A brief line per table, then the joins and the notes, says the same in a form a model reads easily.
In a database of forty tables, send only the tables the question needs. Fewer words cost less and leave less to misread.
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 schema description 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 tables = db.prepare(\"SELECT name, sql FROM sqlite_master WHERE type = ? ORDER BY name\").all(\"table\");' 'function brief(t) {' ' const cols = db.prepare(\"SELECT name, type FROM pragma_table_info(?)\").all(t.name)' ' .map(function (c) { return c.name + \" \" + c.type; });' ' return t.name + \"(\" + cols.join(\", \") + \")\";' '}' 'const links = [];' 'tables.forEach(function (t) {' ' db.prepare(\"SELECT * FROM pragma_foreign_key_list(?)\").all(t.name).forEach(function (k) {' ' links.push(t.name + \".\" + k.from + \" -> \" + k.table + \".\" + k.to);' ' });' '});' > schema.js"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Write the schema description script, part 2
docker exec -w /home/node/project agent sh -c "printf '%s\n' 'const notes = [\"loans.returned is 1 when the book is back, 0 while it is out\", \"loans.month is 1 to 12, this year\"];' 'const schema = tables.map(brief).concat(links, notes).join(\"\n\");' 'const whole = tables.map(function (t) { return t.sql; }).join(\"\n\");' 'console.log(schema);' 'console.log(\"whole CREATE text: \" + whole.length + \" characters, brief with notes: \" + schema.length);' 'async function main() {' ' const question = \"How many loans were made in month 3?\";' ' const system = \"Answer with one SQLite SELECT query and nothing else.\";' ' const sample = \"SELECT COUNT(*) FROM loans WHERE month = 3;\";' ' const reply = await ask([{ role: \"system\", content: system },' ' { role: \"user\", content: schema + \"\nQuestion: \" + question }], sample, 120);' ' console.log(\"model wrote: \" + sqlOf(reply.text));' ' console.log(\"not checked yet: lessons 2 to 5 check it\");' '}' 'main();' >> schema.js"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Describe the schema and ask
docker exec -w /home/node/project agent node --experimental-sqlite --no-warnings schema.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 |
|---|---|
| books(id INTEGER, title TEXT, year INTEGER) | One brief line per table |
| loans.book_id -> books.id | One line per join, read from the foreign keys |
| loans.returned is 1 when the book is back, 0 while it is out | A note you wrote by hand |
| whole CREATE text: 283 characters, brief with notes: 312 | The sizes of the two forms |
| model wrote: SELECT COUNT(*) FROM loans WHERE month = 3; | The query, or a recorded sample if no model answers |
The cells students fill in are left blank in this preview.