Self-Correcting Text-to-SQL: How Execution Feedback Loops Fix Broken Queries

#database #ai #sql #postgres

You wire up an LLM to your database, ask it "how much revenue did we make last month," and it hands back a beautiful SQL query. You run it. ERROR: column "total_amount" does not exist. The column is actually called amount_cents. The model guessed, and it guessed wrong.

This is the single biggest reason text-to-SQL demos look magical and text-to-SQL in production feels flaky. A model writing SQL from a natural language question is doing it blind — it never sees whether the query actually runs, returns rows, or returns nonsense. The fix that has quietly become the standard in 2025 research and production systems is deceptively simple: let the model run its own query, look at what happened, and try again. That loop is called execution feedback, and it's the difference between a party trick and a tool you can ship.

In this article you'll learn what an execution feedback loop actually looks like in code, why "self-correction" only works when it's grounded in real results, and the guardrails you need so a self-retrying AI doesn't melt your database or your latency budget.

Why single-shot text-to-SQL fails

A model generating SQL in one pass has three ways to be wrong, and only one of them is a syntax problem:

Failure type	Example	Does it error?
Syntax error	Missing comma, unbalanced parenthesis	Yes — loud
Schema error	Wrong column or table name	Yes — loud
Semantic error	Right syntax, wrong logic (e.g. summed `quantity` instead of `amount`)	No — silent

The loud errors are actually the good news. A database that rejects a query is handing you a precise, machine-readable description of what's wrong. The insight from systems like MAC-SQL, CHESS, and ReFoRCE is that this error message is far more useful as a correction signal than asking the model to "double-check your work" in the abstract. Research is consistent on this point: self-correction using execution results reliably improves accuracy, while self-correction without external feedback often doesn't help at all — and can even make the query worse as the model second-guesses a correct answer.

So the winning pattern isn't a smarter prompt. It's a feedback loop.

The core loop

Here's the whole idea in pseudocode. Generate, execute, and if execution fails, feed the error back and regenerate — up to a cap.

def text_to_sql(question, schema, max_attempts=3):
    error = None
    sql = None
    for attempt in range(max_attempts):
        sql = llm_generate_sql(
            question=question,
            schema=schema,
            previous_sql=sql,        # None on first pass
            previous_error=error,    # None on first pass
        )
        ok, result_or_error = safe_execute(sql)
        if ok:
            return sql, result_or_error
        error = result_or_error      # feed this back next iteration
    raise RuntimeError(f"Failed after {max_attempts} attempts: {error}")

The magic is entirely in what you put in the prompt on the second pass. Instead of the original question alone, the model now sees its own failed attempt and the exact database error:

The previous query failed. Fix it.

Question: How much revenue did we make last month?

Your previous SQL:
  SELECT SUM(total_amount) FROM orders
  WHERE created_at >= date_trunc('month', now() - interval '1 month');

Database error:
  column "total_amount" does not exist
  HINT: Perhaps you meant to reference the column "orders.amount_cents".

Rewrite the query using only columns that exist in the schema below.

Postgres literally suggests the right column name in its HINT. A model handed that hint fixes the query on the next pass almost every time:

SELECT SUM(amount_cents) / 100.0 AS revenue_dollars
FROM orders
WHERE created_at >= date_trunc('month', now() - interval '1 month')
  AND created_at <  date_trunc('month', now());

That's the entire mechanism. One retry loop turns a brittle guesser into something that converges on a working query.

Catching silent errors: empty results and sanity checks

Syntax and schema errors are self-announcing. Semantic errors are the dangerous ones — the query runs fine and returns a confident, wrong number. You can catch a useful subset of these by treating suspicious results as a form of error worth feeding back.

The most valuable signal is an empty result set. If a user asks "which customers churned last month" and the query returns zero rows, that's rarely correct — it usually means a bad join or an over-strict filter. Feed that back too:

ok, rows = safe_execute(sql)
if ok and len(rows) == 0:
    error = ("Query executed but returned 0 rows. "
             "This is likely a bad JOIN or an overly strict WHERE clause. "
             "Re-examine the filters and join conditions.")
    ok = False  # trigger another repair pass

You can layer on cheap sanity checks as additional feedback signals:

A revenue query that returns a negative total
A count that exceeds the known row count of the table
A per-user aggregate where one user_id appears twice (a fan-out join bug)

None of these are proof of a wrong answer, but as feedback prompts they nudge the model to reconsider before a human ever sees the result.

Guardrails: making a retrying AI safe

The moment you let a model execute SQL — and re-execute it several times — you've built something that needs seatbelts. Four are non-negotiable.

1. Read-only, always. Run every generated query through a connection whose database role has SELECT-only privileges. Don't rely on the LLM to avoid DROP; enforce it at the database layer where it can't be prompted away.

-- One-time setup: a role the AI connects as
CREATE ROLE ai_readonly LOGIN PASSWORD '...';
GRANT CONNECT ON DATABASE app TO ai_readonly;
GRANT USAGE ON SCHEMA public TO ai_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO ai_readonly;
-- No INSERT, UPDATE, DELETE, DROP — ever.

2. A keyword denylist as a second layer. Before executing, reject any query containing INSERT, UPDATE, DELETE, DROP, ALTER, TRUNCATE, or GRANT. Belt and suspenders — the read-only role is the real defense, but a static check catches problems earlier and cheaper.

3. Statement timeouts and row limits. A retry loop can generate an accidental cross join that scans billions of rows. Cap it:

SET statement_timeout = '5s';
-- and wrap the model's query:
SELECT * FROM ( /* model SQL */ ) sub LIMIT 1000;

4. A hard cap on retries. This is the one people forget, and it bites twice. First, latency: each retry adds roughly 1.5–3 seconds of model plus execution time, so an uncapped loop can leave a user staring at a spinner for 15 seconds. Second, over-correction — models sometimes make a correct query worse on later passes, second-guessing themselves into a mangled answer. Three attempts is a sane default. If it hasn't worked by then, fail loudly and log the transcript.

Common mistakes

Feeding back the wrong thing. The database error string is gold — pass it through verbatim, including hints. Teams that summarize or truncate the error ("the query didn't work") throw away the exact signal that makes the loop work.

Retrying forever. No cap means unbounded latency and cost. Always set max_attempts.

Trusting self-critique alone. Asking the model "are you sure this SQL is correct?" without executing it is theater. The improvement comes from real execution results, not introspection.

Logging nothing. You cannot debug or improve a loop you can't see. Log every attempt: the question, each generated query, each error, and the final outcome. Those transcripts are also the best training data you'll ever get for few-shot examples.

Skipping the human confirmation early on. In the first weeks of production, show the generated SQL to the user before running it. It builds trust and surfaces semantic errors that no automated check will catch.

Key takeaways

Single-shot text-to-SQL fails because the model writes queries blind. An execution feedback loop — generate, run, feed the error back, retry — grounds the model in reality and is the single highest-leverage upgrade you can make. Treat empty result sets and failed sanity checks as errors worth feeding back, not just syntax failures. And wrap the whole thing in guardrails: a read-only role, a keyword denylist, statement timeouts, row limits, and a hard retry cap. Do that, and text-to-SQL stops being a demo and starts being infrastructure.

The good news is you don't have to build all of this by hand. Tools like Draxlr handle AI-powered SQL generation, safe read-only execution, and turning the results into shareable dashboards — so you get the feedback loop and the guardrails without wiring them up yourself.

How are you handling AI-generated SQL in your stack today — full auto-execute, human-in-the-loop, or something in between? What's the worst query an LLM has handed you? Drop it in the comments; I collect these.

Sources: ReFoRCE: A Text-to-SQL Agent with Self-Refinement, Consensus Enforcement, and Column Exploration, RetrySQL: text-to-SQL training with retry data for self-correcting query generation, Bridging Natural Language and Databases: Best Practices for LLM-Generated SQL, LLM Guardrails: Best Practices for Deploying LLM Apps Securely (Datadog).