VereskLearn

Module 4 · Retrieval

Lesson 4.9

Text to SQL

Text to SQL asks a model to write a database query, which the system checks before it runs.

Lesson 28 of 622 min read

The problem

Why it matters

Without it

Many answers sit in tables, such as prices and order dates. Searching free text over those rows cannot compare numbers or sort them.

With it

A model writes a precise query, such as the plans under 40. The database then does the comparison and returns exact rows.

How it works

  • The table layout goes into the prompt

    The model needs the table and column names to write a valid query. Send them with the question, or the model guesses names and the query often fails. The example table has three rows and two columns, name and price.

    nameprice
    Basic plan12
    Pro plan30
    Team plan80
    The products table that the question is about.
    // The schema text sent to the model with the question.const schema = "products(name TEXT, price INTEGER)";const question = "Plans under 40?";console.log("Schema:", schema);console.log("Question:", question);

    Output

    Schema: products(name TEXT, price INTEGER)
Question: Plans under 40?

  • The model writes one SQL string

    For "Plans under 40?" the fake model returns one SQL string, SELECT name FROM products WHERE price < 40. SQL is the language databases use to read rows. The model returns text, not rows, so a program must still run the query.

    1. Question
    2. SQL text
    3. Check
    4. Database rows
    The path from the question to the rows, left to right.
    // A fake model. It returns one SQL string for the question.const fakeModel = (question: string) => {  if (question.includes("under")) {    return "SELECT name FROM products WHERE price < 40";  }  return "DROP TABLE products";};console.log(fakeModel("Plans under 40?"));

    Output

    SELECT name FROM products WHERE price < 40

  • The check runs before the query

    The check accepts one plain SELECT and refuses any write word, such as DROP or DELETE. The SELECT passes, and "DROP TABLE products" is refused before it reaches the database. A real check parses the SQL, which is stricter than a word search.

    Accepted

    • SELECT name FROM products WHERE price < 40

    Refused

    • DROP TABLE products
    Two model outputs and the result of the check for each.
    // Accept only one SELECT with no write words.function isSafe(sql: string): boolean {  const upper = sql.toUpperCase();  const banned = ["DROP", "DELETE"].some((w) => upper.includes(w));  return upper.startsWith("SELECT ") && !banned;}console.log(isSafe("SELECT name FROM products WHERE price < 40"));console.log(isSafe("DROP TABLE products"));

    Output

    true
false

  • Only matching rows come back

    The filter price < 40 runs on each row, and it keeps the rows that pass. Basic plan at 12 and Pro plan at 30 pass, while Team plan at 80 does not. The snippet applies the same filter to an array, so the example runs with no database.

    NamePriceReturned
    Basic plan12yes
    Pro plan30yes
    Team plan80no
    Each row checked against price < 40.
    // The same filter as the SQL, applied to an array of rows.const products = [  { name: "Basic plan", price: 12 },  { name: "Pro plan", price: 30 },  { name: "Team plan", price: 80 },];console.log(products.filter((r) => r.price < 40));

    Output

    [ { name: 'Basic plan', price: 12 }, { name: 'Pro plan', price: 30 } ]

  • A read-only login is the last guard

    A checker can miss a clever query, so it is not the only guard. A read-only database login can read rows but cannot change them, so a bad query cannot alter the data. Show the query next to the rows, so a person can see which filter was used.

    • Check on the SQL textstops most bad text
    • Read-only loginstops any write
    Two guards, before any row can change.

Full code

Read the program

See the full code

Common mistakes

What goes wrong

  • Running the model output without a check. A model can write a DELETE as easily as a SELECT.
  • Giving the model a login with write access. Use a read-only database account.
  • Trusting the first answer. Show the query so a wrong filter is visible.

Use it when and skip it when

Is it the right tool?

Use it when

  • The answer is a count, sum, sort, or filter over rows in a table.
  • The data changes often, and copying it into documents would go stale.

Skip it when

  • The answer is a passage of text, such as a help article.
  • Users may query tables they should not see, and no access rules are in place.

Words to know

Terms in this lesson

SQL
The language that databases use to read and change rows.
Table
A set of rows that share the same named columns.
Read-only account
A database login that can read rows but not change them.
Parse
Read a text and break it into parts that a program can check.
Full code