"Can't ChatGPT already write SQL?"
It can. Ask any modern model for a query and it hands you something that looks professional within seconds. Correct indentation, sensible joins, a neat little GROUP BY at the bottom. It looks like something a senior analyst would write.
So here is the question worth asking:
If the model is already this good, why does every serious text-to-SQL product ship thousands of lines of code wrapped around that single model call?
The answer is that generating SQL was never the hard part. Generating SQL that is safe to run against someone's real database, that actually answers the question they asked, and that fails in a way you can explain to them. That is the hard part.
All of that machinery around the model has a name. It is called a harness.
In this article we will look at what a text to SQL harness is, what each piece of it does, and why removing any one of them breaks something. Simple explanations, real SQL examples, and no dense research papers.
Before We Talk About the Harness
Try a small experiment.
Open ChatGPT and ask it this, without giving it any other information: "Who are my top 10 customers by lifetime value?"
It will answer. Confidently. You will get something like this :
SELECT c.customer_name,
SUM(`total_amount`) AS lifetime_value
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_name
ORDER BY lifetime_value DESC
LIMIT 10;Read that query again. It s clean. The join is right. The aggregation makes sense. If you saw it in a code review you would probably approve it. There is just one problem. The model has never seen your database.
It does not know if your table is called customers or Customer. It doesn't know whether total_amount exists, or whether your order totals are actually stored per line item and have to be calculated as unit_price * quantity. It does not know that you keep cancelled orders in the same table and everyone on your team knows to filter them out.
It guessed. It guessed in a very educated, very plausible way, and it wrote a query that will either crash or, much worse, quietly return a number that is wrong.
That gap between SQL that looks right and SQL that is right is the entire reason a harness exists
What is a Text-to-SQL Harness?
A harness is everything that surrounds the model.
The model does one job : it turns a question and some schema information into a SQL string. Thats it. The harness does everything else. It decides whether the question is even worth answering. It finds the right tables. It checks the SQL before anything runs. It runs the query safely. It retries intelligently when something fails, and it records what happened so you can go back and read it later.
Think of it like a kitchen.
The model is the chef. A very fast chef who has cooked millions of meals and can produce a dish in seconds. But a chef alone is not a restaurant. A restaurant also has someone taking the order and checking it makes sense, someone fetching the right ingredients from storage, someone tasting the dish before it goes out, and someone who sends it back when its wrong.
The harness is the restaurant. The model is just the chef. Most demos you see online are only the chef. That is why they are so impressive and so fragile at the same time.
Writing SQL is not the Same as Answering the Question
This is the part beginners find surprising.
A query can be perfectly valid SQL, run without a single error, return a tidy table of results, and still be completely wrong. Say someone asks:
"How much revenue did we make from each region last year?"
And the model writes this:
SELECT r.region_name, COUNT(o.order_id) AS total
FROM orders o
JOIN regions r ON r.region_id = o.region_id
GROUP BY r.region_name;It runs. It returns rows. It has a column helpfully labelled total. Everyone is happy.
Except it counted orders instead of summing revenue, and nobody filtered to last year.
The database has no opinion about this. Databases dont check whether your query matches your intent, they only check whether your query is legal. Legal and correct are two very different things, and the whole middle section of a harness exists to tell them apart.
Why Do We Need a Harness?
Imagine shipping text-to-SQL with no harness at all. A question comes in, the model writes SQL, you run it against production. Several things go wrong almost immediately.
Someone types "hi" and you spend money generating a query for it. Someone asks about a table that does not exist and they get a raw database error in the UI. The model invents a column name and the whole thing crashes. Someone asks a question whose answer genuinely is zero rows, and the system retries five times trying to "fix" a query that was correct all along.
And then the one that actually keeps people awake at night. Your application takes text typed by a user, sends it to a language model, and runs the output against a live database. If nothing sits between those two steps, you have handed a stranger a SQL console.
A harness turns each of those from an incident into a handled case.
Not Every Question Deserves a Query
The first thing a good harness does is decide whether to continue at all.
This is called routing, and its a cheaper and more important than it sounds. Before any SQL is written, the system looks at what the question actually contains. Does it name anything to look up? Does it name anything to measure? Are there any tables in this database that could plausibly answer it?
If someone types "hi, how's it going?", there is nothing to build a query from. The right response is to say so, politely and instantly, and stop.
There is a temptation to treat this as rude. Surely we should at least try? But trying costs money and produces nonsense. A system that answers "hi" with a SQL query is not being helpful, it is being confused in an expensive way.
Good routing also catches the boring cases. No tables loaded for this database yet. Schema never processed. These have clear, honest answers that dont require a model at all.
The Model Cannot Guess Your Schema
Once we have decided the question is worth answering, the model needs to know what its working with.
You might think the answer is simple. Give it the whole schema. Just paste every table and column into the prompt.
That works fine on a toy database with six tables. Real databases have hundreds, sometimes thousands. You cannot fit them in a prompt, and even if you could, burying the three relevant tables inside four hundred irrelevant ones makes the model worse, not better.
So instead the harness searches. It takes the question, finds the handful of tables most likely to be relevant, and shows the model only those, with their real column names, their types, and how they connect to each other.
This is the single highest leverage part of the whole system. Give the model the right three tables and it usually writes a good query on the first try. Give it the wrong three and no amount of clever prompting saves you.The model is not guessing anymore,but it can only ever be as right as the tables you handed it.
Three Checks Before Anything Runs
Now the model has written a draft. This is the moment where an unharnessed system would just run it.
A harness does not. It checks first, in a specific order, and the order is the whole point.
Does it parse, and is it allowed? The first check is pure code, no model involved. Is this a single read-only SELECT? Does it only touch the tables we actually gave it? This is the check that stops the nightmare scenario:
DROP TABLE customers;and the subtler one:
SELECT * FROM internal_user_credentials;Neither of those is in the tables we retrieved, so neither one gets anywhere near a database connection. This check is fast, free and deterministic, which is exactly why it goes first.
Does it answer the question? The second check asks a model to review the draft against the original question and the schema. This is what catches COUNT where you wanted SUM, or a missing date filter, or a join that silently drops rows. It is slower and it costs a call, which is why it only runs on SQL that already passed the free check.
Does the database accept it? The last check runs the query for real, read-only, with a row limit and a timeout. This is the only source of ground truth in the entire system. The parser knows grammar. The reviewer has an opinion. Only the actual database knows whether that column exists and whether that join resolves.
When a Query Fails, Ask Again Properly
Here's where a harness gets genuinely clever. When a check fails, you do not just try again and hope. Retrying the same prompt gets you the same answer. That is the same how these models work. Instead you tell the model exactly what went wrong.
The database says column "customername" does not exist. That failure goes back to the model as context, here is the SQL you wrote, here is the exact error, try again. The second attempt is a different prompt with more information in it, so it has a real chance of being different and better. That is a repair loop, and it is not the same thing as a retry. A retry hopes the network behaves. A repair loop learns from a specific, named failure.
But it has to know when to stop, and knowing when to stop is most of the skill.
Stop after a few attempts. Each one costs real money. The ceiling should be low.
Stop when nothing changed. If the rewrite comes back byte for byte identical to the last one, the model has locked onto an answer. A third attempt produces the same thing.
Stop when it isn't fixable. A refused connection or a missing permission is not something rewriting SQL can solve. Feeding "connection refused" to a query generator just burns tokens.
Stop when time runs out. One slow database should not hold a request open forever. And one thing it must never do. Never repair a query that returned zero rows. Zero rows is very often the true answer.
Letting the Database Have the Last Word
Running the query is the one step where a bug stops being embarrassing and starts being dangerous. So it gets its own rules. Run it inside a read only transaction that is rolled back no matter what happens. Even if something slipped past every earlier check, it cannot write.
Set two timeouts, not one. A server side timeout makes the database stop working. A client side timeout only makes your app stop waiting, which leaves a runaway query quietly burning someone else's CPU.
Cap the rows. Nobody needs four million rows rendered in a browser tab. And use a read only database user. Every check above is a wall you built. A read only credential is the wall the database builds for you, and it's the only one still standing if you make a mistake in the other ones.
How Do You Know the Harness Is Working?
This is the question people skip, and it is the one that separates a demo from a product.
You cannot improve a system you can not measure. So you write down a set of questions you already know the answers to, run them through the harness on a schedule, and score the results. The obvious approach is to compare the generated SQL against a "correct" query, string for string. Don't. Two perfectly correct queries can differ in alias names, join order, whitespace, or whether they used a CTE or a subquery. String matching measures the models stylistic consistency rather than its correctness, and it turns your test suite red every time you tweak a prompt. A suite that cries wolf gets ignored within a week.
That last one is the interesting one, and it is the one people forget. Counting attempts measures the harness, not the model. A system that gets the right answer on the first try and a system that gets there on the third are both "correct", but they cost very different amounts and they feel very different to use.
Your first score is not a grade, it is a baseline. It gives you a number to beat, and more usefully it tells you where the points are going, whether you are losing them on retrieval, on the checks, or on attempts. Then you change one thing, run it again, and watch the number move. Without that loop you are not improving a system. You are editing prompts and hoping.
Final Thoughts
The model is the smallest part of a text2SQL system.
That sounds wrong the first time you hear it, because the model is the part doing the impressive looking thing. But go back to the query at the top of this article, the clean, professional, completely made-up one. The model produced that in under a second. Everything that turns it into an answer you would actually show your finance team happens somewhere else. It happens in the routing that decides the question is real. In the retrieval that finds the right three tables out of four hundred. In the checks that run before anything touches a database.
In the repair loop that knows the difference between a fixable mistake and a hopeless one. All of it is the difference between a demo and something people trust with their data.
So the next time you see a text-to-SQL demo write a beautiful query in two seconds, the interesting question isn not "how did it write that?"
It's "what would have happened if it were wrong?"