Skip to content
AI360XpertBeta

Text-to-SQL

Type a plain question and get a working SQL query back. The model reads your words plus the database schema, then writes the SELECT for you.

A plain question plus the schema becomes one read-only SELECT that returns 115000 as the average of 120000 and 110000
A plain question plus the schema becomes one read-only SELECT that returns 115000 as the average of 120000 and 110000

Why Does This Exist?

You have felt this wait. Someone asks what the average engineering salary is, and the answer sits in a Postgres table you cannot query. So you file a ticket, an analyst finds an hour, writes the filter and the aggregate, and three days later a CSV lands in your inbox. The data was there all along. SQL was the gate.

text-to-SQL removes that gate for read-only questions. You type the question, the model reads the database schema and writes one SELECT statement, your backend runs it with read-only rights and hands you the number. Seconds, not days. Teams use it for dashboards, one-off headcount and revenue questions, and every query an analyst is tired of rewriting.

This page covers the single-shot version: one question in, one query out, one execution. When that query misses and the system should read the error and try again, that loop is a structured data agent. When you are torn between querying tables and searching documents, the decision is Text-to-SQL vs Document RAG.

Quick grounding so nothing later feels like magic. A relational table stores rows with named columns. SELECT picks columns, FROM names the table, WHERE keeps only matching rows, and AVG of a column averages it over the survivors. That is all the SQL this page needs.

Think of It Like This

Ordering lunch through an interpreter at a strict counter

You are hungry in a kitchen where the cook only accepts written order slips. You do not know the slip format. An interpreter stands beside you. You say something hot with chicken, no peanuts. The interpreter checks the menu board, writes the slip in the kitchen code, and slides it through the hatch. The cook makes exactly what the slip says and slides a tray back.

Point for point: your sentence is the question, the menu board is the schema, the slip is the SQL, the hatch rule that food only comes out is read-only access, and the tray is the executed result.

Where it stops: the interpreter cannot taste the food. If hot meant spicy to you and high temperature to the cook, the slip is valid and the meal is wrong. Models fail the same way. A query can run cleanly and still answer the wrong question, which is why a successful execution is not proof of a correct answer.

How It Actually Works

Here is the deal. The model never sees your database. It sees two strings. QQ (your question) is the plain sentence you typed. SS (the schema) is the contract describing tables, columns, types, and keys. YY (the SQL string) is what the model writes. The job is Y=f(Q,S)Y = f(Q, S): choose tables, columns, filters, and aggregates so that running YY returns the answer to QQ.

Production setups do this in three passes, and skipping any of them is where most demos break.

Schema linking decides everything

schema linking means grounding each phrase of QQ in a real piece of SS: a table, a column, or a stored value. Average salary points at AVG of the salary column. Engineering points at a value inside the dept column. Unlinked words are guesses, and guesses become wrong columns. The fuller treatment of this step is schema linking.

Give the model the smallest schema that answers the question: table names, column names and types, keys, plus a few distinct values per filtered column so it writes Engineering versus ENG correctly. Huge schemas get truncated or confuse the model, so retrieve the relevant tables first on big databases.

Generation, then execution with checks

Generation writes exactly one statement. Serious setups add constrained decoding so the output obeys SQL grammar by construction, plus guardrails that reject anything that is not a single read-only SELECT.

Then your backend, never the model, runs YY with a read-only role, a statement timeout, and a row cap. Check the result before showing it: did it run, did it return rows, does the row count make sense. Empty results and absurd counts go back for a rewrite instead of reaching the user, and that rewrite loop is the ReAct pattern applied to databases.

The trade-off is blunt. You buy speed and access for everyone. You pay in correctness risk, because the model can always link a phrase to the wrong column and sound confident doing it. The assumption underneath is that the schema fits in context and names mean what they say. When names lie, when the question needs business judgment no column holds, or when the request writes or deletes, do not use single-shot text-to-SQL. Hand it to an analyst or an agent with checks.

Worked example

Take this tiny employees table:

  • 1, Alice, Engineering, 120000
  • 2, Bob, Sales, 95000
  • 3, Carol, Engineering, 110000

QQ is: What is the average salary in Engineering. SS is: employees with id as INTEGER, name as TEXT, dept as TEXT, salary as INTEGER.

Linking: average salary becomes AVG of salary, Engineering becomes dept equal to Engineering. Generation produces one statement:

Y=SELECT AVG(salary) FROM employees WHERE dept = ’Engineering’Y = \texttt{SELECT AVG(salary) FROM employees WHERE dept = 'Engineering'}

Execution filters first. Alice and Carol survive, Bob drops out, so 2 rows remain. Sum their salaries: 120000 plus 110000 equals 230000. Divide by the count: 230000 divided by 2 equals 115000. The database returns 115000.0 and your backend formats it as a sentence. Same numbers sit in the diagram above and the snippet below, so all three check each other.

Code

The point of this snippet is the execution half, shown without any model call. The SQL string stands in for what the model should produce, and the database decides whether it was right.

import sqlite3

def build_demo_db() -> sqlite3.Connection:    conn = sqlite3.connect(":memory:")    conn.execute(        "CREATE TABLE employees (id INTEGER, name TEXT, dept TEXT, salary INTEGER)"    )    conn.executemany(        "INSERT INTO employees VALUES (?, ?, ?, ?)",        [            (1, "Alice", "Engineering", 120000),            (2, "Bob", "Sales", 95000),            (3, "Carol", "Engineering", 110000),        ],    )    return conn

def ask_average(conn: sqlite3.Connection) -> list[tuple[float]]:    model_sql = "SELECT AVG(salary) FROM employees WHERE dept = 'Engineering'"    return conn.execute(model_sql).fetchall()

conn = build_demo_db()print(ask_average(conn))  # -> [(115000.0,)]

The printed row is 115000.0, which matches the hand computation: 230000 divided by 2. Change Engineering to a value the table never stores and you get [(None,)], the empty-answer shape the retry logic must catch.

Watch Out For

Valid SQL that answers the wrong question

The symptom is quiet success. The query runs, no error appears, and the number is wrong. Engineering versus ENG, California versus CA, gross versus net revenue: the model linked your word to a real column but the wrong value or the wrong column, so the database faithfully averaged the wrong rows. Empty results are the loud version of the same bug.

Fix it before generation, not after. Pass distinct column values into the prompt so the model sees the exact stored strings, surface the generated SQL plus the row count next to every answer, and treat zero-row or one-row surprises as rewrite triggers. If misses keep coming, move to a structured data agent that reads the miss and tries again.

The model gets write access

The symptom is a query you never wanted to see: a DROP TABLE, a DELETE, or a SELECT STAR dump of a sensitive table, generated from a hostile or careless prompt and executed by your backend. Never connect the generator to a credential that can write.

Fix it at the database, because prompt rules alone do not hold. Use a read-only role, allow only one SELECT per request, deny DDL keywords, and enforce a statement timeout plus a row cap. Log the SQL string for every request so a bad query is evidence, not a mystery.

The Quick Version

  • Text-to-SQL turns one plain question plus the database schema into one read-only SELECT statement.
  • Schema linking grounds each phrase in a real table, column, or stored value, and wrong links cause wrong answers.
  • Generation should be grammar-checked and guardrailed before anything touches the database.
  • Execution runs under a read-only role with a timeout and a row cap, and empty results trigger a rewrite.
  • Single-shot text-to-SQL fits read-only fact questions and fails on writes, hidden business logic, and misleading names.
  • The worked fixture averages 120000 and 110000 to 115000, and the diagram, prose, and code all carry those numbers.