Text-to-SQL Explained: How AI Turns Plain English Into SQL Queries

You've probably seen the demo by now. Someone types "show me the top 5 customers by revenue last month" into a chat box, and a fully-formed SQL query appears, runs, and returns a table. It feels like magic — or like the death of the WHERE clause you spent years mastering.

It's neither. Text-to-SQL is a real, useful capability, but it's also a leaky abstraction. If you're building a product that lets users ask questions of a database — or you're just tired of writing the same reporting queries — it pays to understand what actually happens between the English and the SQL. Because when it goes wrong (and it will), knowing the pipeline is the difference between a five-minute fix and a support ticket that says "the numbers are wrong" with no other detail.

This post walks through how a text-to-SQL system actually works, from the moment a question arrives to the moment a query runs, and where each stage tends to fail.

The four stages of a text-to-SQL system

Under the hood, almost every text-to-SQL system — whether it's a homegrown wrapper around an LLM or a polished analytics product — moves through the same four stages:

1. Schema context injection — the model is told what tables and columns exist.
2. Natural language parsing — the model interprets what the user is actually asking.
3. SQL generation — the model writes a query.
4. Execution and result delivery — the query runs and results come back.

The interesting part is that the LLM only owns stages two and three. Stages one and four are plumbing you control, and that plumbing is usually what determines whether the whole thing works.

Stage 1: The model doesn't know your database

Here's the thing people miss: an LLM has never seen your database. It doesn't know you have an orders table, that status can be 'refunded', or that revenue lives in subscriptions.mrr_cents and not orders.total. Out of the box, it knows SQL syntax — not your schema.

So before the model writes a single line, the system injects schema context into the prompt: table names, column names, data types, and the foreign-key relationships that connect them. A simplified version of what the model receives looks like this:

Table: orders
  id (int, primary key)
  user_id (int, foreign key -> users.id)
  amount_cents (int)
  status (text: 'paid', 'refunded', 'pending')
  created_at (timestamp)

Table: users
  id (int, primary key)
  email (text)
  plan (text: 'free', 'pro', 'enterprise')
  signup_date (date)

Given that context and the question "How much revenue did we make from pro users last month?", the model can now produce:

SELECT SUM(o.amount_cents) / 100.0 AS revenue_dollars
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE u.plan = 'pro'
  AND o.status = 'paid'
  AND o.created_at >= date_trunc('month', current_date - interval '1 month')
  AND o.created_at <  date_trunc('month', current_date);

Notice how much of that correctness depends on the schema hints. The model knew to filter status = 'paid' (and exclude refunds) and to divide by 100 only because the schema told it amounts are stored in cents and status is an enum. Strip that context out and you get a plausible-looking query that quietly double-counts refunds.

This is also why schema linking — matching words in the question to the right tables and columns — is the single biggest source of failures. Research on text-to-SQL systems consistently finds schema-linking errors driving well over half of all mistakes. When a database has hundreds of columns, dumping the entire schema into the prompt actually hurts: the irrelevant tables become noise that pulls the model toward the wrong join. The better systems retrieve only the handful of tables likely relevant to the question before generating anything.

Stage 2 & 3: From ambiguous English to precise SQL

This is where the fundamental tension lives. Human language is ambiguous and context-dependent. SQL is precise and unforgiving. "Last month" could mean the previous calendar month or the trailing 30 days. "Active users" could mean anyone who logged in, anyone with a paid plan, or anyone who wasn't soft-deleted. The model has to collapse that ambiguity into one exact interpretation — and it won't tell you which one it picked unless you make it.

Consider "Show me our best customers." There's no single correct query. The model might reach for:

SELECT u.email, SUM(o.amount_cents) / 100.0 AS lifetime_value
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.status = 'paid'
GROUP BY u.email
ORDER BY lifetime_value DESC
LIMIT 10;

That's a reasonable guess — "best" mapped to lifetime spend. But maybe your business defines "best" as most frequent, or most recent, or highest retention. The SQL is valid. The logic might be wrong. This is the failure mode that hurts most, because nothing errors out — you just get confidently incorrect numbers.

Two techniques meaningfully improve this stage. The first is few-shot examples: showing the model a handful of question/query pairs from your domain. Studies report execution-accuracy gains in the 9–12% range just from good examples, because they teach the model your conventions ("revenue always excludes refunds," "we filter deleted_at IS NULL"). The second is giving the model room to reason before it writes SQL — letting it decompose a complex question into steps rather than emitting a query in one shot. Non-reasoning models are notorious for producing valid SQL that's logically wrong: missing a GROUP BY column, selecting from the wrong table, or dropping a filter.

Stage 4: Running it (carefully)

Once SQL exists, it has to run. The naive approach — execute whatever the model produced — is how you end up with a DELETE in a reporting tool. Production systems wrap this stage in guardrails:

• Run everything as a read-only database role so INSERT, UPDATE, and DELETE simply can't execute.
• Validate before executing — parse the SQL and reject anything that isn't a SELECT, or that touches tables the user shouldn't see.
• Enforce a LIMIT and a statement timeout so an accidental cross join doesn't take down your database.

The better systems also add a self-correction loop: if the query throws an error, feed the error message back to the model and let it fix its own SQL. A query that references a nonexistent column revenue gets a column does not exist error, and the model retries — often landing on amount_cents the second time.

Here's a minimal version of that loop in pseudocode:

def text_to_sql(question, schema, max_retries=2):
    prompt = build_prompt(schema, question, few_shot_examples)
    for attempt in range(max_retries + 1):
        sql = llm.generate(prompt)
        if not is_read_only(sql):        # guardrail
            raise UnsafeQueryError(sql)
        try:
            return db.execute(sql, timeout=10)
        except DatabaseError as e:
            prompt += f"\nThat query failed: {e}\nFix it."
    raise CouldNotGenerateValidSQL(question)

Common gotchas developers run into

A few patterns show up again and again once you ship one of these:

Gotcha	What happens	Mitigation
Schema overload	Dumping 300 columns into the prompt confuses the model and inflates cost	Retrieve only relevant tables per question
Silent logic errors	Valid SQL, wrong definition of "active" or "revenue"	Few-shot examples encoding your business rules
Ambiguous time ranges	"Last month" interpreted differently than the user meant	Show the generated SQL to the user before trusting results
Hallucinated columns	Model invents a plausible column name that doesn't exist	Self-correction loop on execution errors
No guardrails	A destructive or runaway query executes	Read-only role, statement timeout, forced LIMIT

The meta-lesson: data and schema quality matter more than clever prompting. Clean, well-named tables with sensible constraints produce better SQL than a table full of col1, col2, flag_3 no matter how good your prompt is. If your schema is confusing to a new engineer, it's confusing to the model too.

Key takeaways

Text-to-SQL isn't magic and it isn't a black box. It's a four-stage pipeline: inject schema context, parse the question, generate SQL, execute with guardrails. The LLM only owns the middle two stages — the schema you feed it and the safety net you wrap around execution are the parts you control, and the parts that decide whether it works.

The two biggest failure modes are schema linking (the model joins the wrong tables) and silent logic errors (valid SQL, wrong business definition). You fight the first with focused schema retrieval and the second with domain-specific examples. And you always, always run the generated SQL as a read-only role with a timeout — because the model will eventually surprise you.

Used this way, text-to-SQL doesn't replace people who know SQL. It gives non-technical teammates a safe on-ramp to the data, and gives the rest of us a faster path from question to answer. This is exactly the pattern tools like Draxlr lean on — pairing AI-generated SQL with a schema-aware layer and read-only guardrails so the query that runs is one you can actually trust.

Your turn

Have you shipped a natural-language query feature? What broke first — schema linking, ambiguous questions, or something you didn't see coming? And if you've found a prompting trick that reliably nails your business definitions, drop it in the comments — I'd love to compare notes.

---

Sources: AWS: Build a robust text-to-SQL solution, Oracle: Natural Language to SQL Generation, RSL-SQL: Robust Schema Linking in Text-to-SQL, SQL-of-Thought: Multi-agentic Text-to-SQL.