Gist
Writing and validating SQL functions becomes increasingly time-consuming when complex business logic requires extensive test coverage. We explored whether AI could generate reliable SQL test cases without simply shifting the manual effort to engineers.Our initial experiments produced only 40–60% coverage, inconsistent outputs, syntax errors, and significant manual correction. We then evaluated different prompt frameworks — TREE, TCR, RACE, and CRISPE — across multiple OpenAI models.The results showed that both model selection and prompt structure significantly influence the quality of generated SQL tests. Strong model-and-prompt combinations achieved 85–90% coverage and accuracy, with GPT-5-nano standing out for its balance of speed, cost, and performance.
Writing and validating SQL functions is not new for data engineering teams. But when the SQL function contains complex business logic, creating enough test cases to cover different conditions can become a time-consuming task.
That was the problem we wanted to explore at HashAgile.
We wanted to know whether AI could help us generate SQL test cases without creating another problem for the engineering team. Generating a few test cases is easy. The real question is whether the generated tests are complete, executable, consistent and useful enough that an engineer does not have to spend hours fixing them.
Our first attempt was simple.
We gave the SQL function to an LLM and asked it to create comprehensive test cases. It worked. But not well enough.
Some test cases were missing. Some SQL had syntax errors. Negative and edge cases were often overlooked. The format was different from one run to another, and in many cases we still had to spend significant time reviewing and correcting what the model produced.
That was the point where we stopped asking, Can AI generate SQL test cases?
Instead, we started asking a more useful question:
What does it take to make AI-generated SQL test cases reliable enough to actually use?
That question led us to evaluate different prompt structures, different OpenAI models and different combinations of the two.
The First Experiment Was Simpler Than We Expected
We started with an unstructured prompt.
Something along the lines of:
Write comprehensive test cases for this SQL function: [function code]
There was nothing particularly wrong with the prompt. It was clear enough for a person to understand what we wanted.
But an LLM doesn't necessarily interpret a broad request in the same way every time.
After running more than 10 iterations across different models, we started seeing a pattern.
GPT-5, GPT-5-mini, GPT-5-nano and o4-mini averaged around 60% coverage. GPT-4.1-mini and GPT-4o-mini were around 40%. The bigger problem was not just coverage.
Around 50% of the generated outputs contained syntax errors or incomplete SQL logic. The structure of the test cases also changed between runs, which made it difficult to use them as a consistent test suite.
Negative scenarios and edge cases were another weak point.
The models were generally good at identifying the obvious happy-path scenarios. But when we looked for things such as NULL values, boundary conditions, invalid inputs and error scenarios, the coverage was not where we wanted it to be.
And then came the part that matters most when we talk about automation.
Nearly 80% of the outputs required significant human revision.
At that point, we had to ask ourselves whether we were actually automating SQL testing or simply moving some of the manual work to a different place.
The answer was obvious.
We needed to change the way we were asking the models to do the job.
We Changed the Prompt Before Changing the Model
This was probably one of the most important parts of the experiment.
Instead of immediately looking for a better model, we looked at the prompt itself.
We evaluated four structured prompt engineering frameworks:
- TREE: Task → Requirement → Example → Expectation
- TCR: Task → Context → Reference
- RACE: Role → Action → Context → Example
- CRISPE: Capacity → Role → Insights → Statements → Personality → Experiment
The idea was simple.
Instead of giving the model one broad instruction, we wanted to give it enough structure to understand what it was expected to do, what information it had, what the final output should look like and how it should behave when something went wrong.
This changed the experiment quite a bit.
We were no longer testing only the models.
We were testing the combination of model and prompt framework.
And that turned out to matter a lot.
What Did We Consider a Successful Test Suite?
Before comparing the results, we needed to decide what success actually meant. For us, generating a large SQL file wasn't enough.
A successful test suite needed to:
- Execute without SQL syntax errors
- Cover more than 85% of the identified test cases
- Include meaningful edge cases
- Handle error scenarios
- Require little or no manual correction
- Remain reasonably efficient in terms of time and cost
We also looked at six areas when comparing the models:
Coverage and Completeness
How many relevant test cases were actually generated?
Accuracy and Validity
Could the generated SQL execute correctly?
Cost
How much did it cost to generate the test suite?
Generation Time
How long did the model take?
Consistency
Could we get similar quality across repeated runs?
Manual Effort
How much work did an engineer still need to do?
This was important because a model that gives 90% coverage but takes a long time and requires substantial manual correction isn't necessarily better than a model that gives 88% coverage in a fraction of the time and cost.
Then We Started Comparing the Models
We evaluated GPT-5, GPT-5-mini, GPT-5-nano, GPT-4.1, GPT-4.1-mini, GPT-4o-mini and o4-mini.
The results were quite different.
GPT-5 was the strongest when it came to coverage, code quality and accuracy. It was particularly useful for complex SQL workloads, but that performance came with higher cost and longer execution times.
GPT-5-mini was much more balanced. It delivered strong coverage and accuracy while keeping cost and generation time at a more reasonable level.
GPT-5-nano was probably the most interesting result from a practical point of view. It performed strongly on coverage and accuracy while also being the fastest and least expensive option in our evaluation.
GPT-4.1-mini and GPT-4o-mini were where we saw more limitations. Their coverage and accuracy were lower, and the amount of manual effort required made them difficult to recommend for production-grade SQL test generation.
o4-mini was also interesting because it delivered a strong combination of cost, speed and accuracy.
But model selection was only half of the story.
The prompt framework made a significant difference.
The Prompt Framework Changed the Results
We ran the structured frameworks against the different models and started seeing some clear patterns.
With GPT-5, CRISPE produced 90% coverage and 90% accuracy. RACE reached 85% coverage but took around 12 minutes. TCR reached 80% coverage and 85% accuracy in around 5.3 minutes, while TREE was at 70% coverage.
GPT-5-mini also performed particularly well with CRISPE and TCR.
CRISPE reached 88% coverage and 87% accuracy, while TCR reached 85% coverage and 86% accuracy.
GPT-5-nano was where things became particularly interesting.
CRISPE produced 90% coverage and 90% accuracy in around 1.2 minutes, while TCR produced 88% coverage and 88% accuracy.
And the cost was as low as $0.01 in our evaluation.
That combination changed the conversation for us.
We weren't looking only at the most powerful model anymore.
We were looking at what model and prompt combination could actually be used repeatedly.
Why CRISPE Worked Better for Us
After seeing the results, we wanted to understand why CRISPE was performing better.
The difference was not simply that the prompt was longer.
It gave the model a much clearer definition of the job.
We told the model what it was capable of doing, what role it was playing, what information it would receive, what the generated SQL needed to look like and how it should behave when the generated script failed.
For example, we defined the model's capacity around parsing PostgreSQL functions, table DDLs and statistical information.
We gave it the role of a PostgreSQL testing expert rather than simply asking it to write SQL.
We also defined the information it would receive.
That included:
- function.txt containing the PostgreSQL function
- table_schema.txt containing the required table definitions
- pg_stats.txt containing information such as row counts, data ranges and data types
- sample_data.txt containing example data
This gave the model more information to work with when creating the test data.
Then we became very specific about the expected output.
The model had to generate a standalone .sql file.
It had to create missing schemas and tables where required.
It had to define the function.
It had to generate the tests.
It had to report PASS or FAIL.
And the entire script had to run inside a transaction.
That level of instruction made the task much clearer.
We Also Told the AI What to Do When Things Went Wrong
This was another important change.
Instead of expecting the first generated SQL script to be perfect, we gave the model a way to deal with failure.
The generated script could be executed using:
psql -U <username> -f <function_name.sql>
If the script failed because of a syntax error, assertion failure or missing object, the model was expected to diagnose the problem, regenerate the script and try again.
That retry loop was important.
In a traditional prompt, the model generates the SQL and stops.
In our structured approach, the model had a defined responsibility beyond generation.
Generate. Execute. Find the problem. Fix it. Try again.
That is much closer to how an engineer would actually approach the task.
The Test Cases Were Not Just Happy-Path Tests
We also made the expected test coverage explicit.
The generated suite needed to consider:
- Valid inputs
- Invalid inputs
- NULL values
- Minimum and maximum values
- Empty values
- Boundary conditions
- Error-producing scenarios
- State-dependent scenarios
Each test also had a defined structure.
The test needed to insert the minimum required data, execute the function, compare the actual result with the expected result, report PASS or FAIL and clean up the test data.
The entire script was wrapped in a transaction and rolled back at the end.
This was important because we weren't trying to generate a document that looked like a test suite.
We wanted something that could actually be executed.
What Did the Generated SQL Look Like?
One of our generated test suites created the required schemas and tables, defined the PostgreSQL function and then created individual test cases.
The generated tests included a simple matching scenario, a case where the related table had no matching record, a non-existent input and a NULL input.
For example, one test inserted the required data, executed the function and compared the returned JSON with the expected JSON.
If the result matched, the script reported:
Test 1: PASS - Simple match
If it didn't, the test raised an exception showing the expected and actual values.
The generated script then cleaned up the inserted data before moving to the next test.
At the end, it produced a summary showing how many tests had been executed.
When we ran the generated script, all four test cases passed.
The output showed:
- Test 1: PASS - Simple match
- Test 2: PASS - NULL join fields
- Test 3: PASS - No rows returns NULL
- Test 4: PASS - NULL input returns NULL
SUMMARY: 4 test cases executed.
This was the point where the experiment started feeling less like a demonstration and more like something that could become part of an engineering workflow.
The Model Comparison Became More Interesting After Structured Prompting
Once we moved away from the initial unstructured approach, the differences between the models became much clearer.
GPT-5 with CRISPE reached 90% coverage and 90% accuracy.
GPT-5-mini with CRISPE reached 88% coverage and 87% accuracy.
GPT-5-nano with CRISPE also reached 90% coverage and 90% accuracy, but completed the generation in around 1.2 minutes and at a much lower cost.
o4-mini produced similar results with CRISPE, reaching 90% coverage and 90% accuracy, while also keeping runtime and cost low.
The weaker models didn't suddenly become strong simply because we changed the prompt.
GPT-4o-mini, for example, remained weak across the frameworks we tested, with coverage ranging from 55% to 70%.
GPT-4.1-mini was inconsistent as well. CRISPE reached 85% coverage, but TREE, TCR and RACE were considerably lower.
That told us something important.
Prompt engineering matters, but model capability still matters.
A well-structured prompt can help a model perform better.
It cannot completely remove the limitations of the underlying model.
So, Which Model Would We Use?
Based on our evaluation, GPT-5-nano came out as the most practical choice when we considered coverage, accuracy, cost and generation time together.
Our ranking was:
1. GPT-5-nano
Best overall balance of practicality, speed and cost.
2. o4-mini
A strong cost-performance option with good efficiency.
3. GPT-5-mini
A good balance between accuracy, cost and generation time.
GPT-5 remained the strongest choice when the complexity of the SQL workload justified the additional cost and runtime.
So there isn't really one model that wins every situation.
If the workload is complex and accuracy is the priority, GPT-5 makes sense.
If the workload is routine and we need to generate many test suites quickly, GPT-5-nano becomes much more interesting.
That distinction is important if the goal is to build something that can actually scale.
What We Learned From the Experiment
The first lesson was probably the most obvious one after seeing the results.
A simple prompt is not enough for complex SQL test generation.
Our initial approach produced roughly 40–60% coverage, inconsistent output and a high level of manual correction.
Once we introduced structured prompting, the results moved into the 85–90% range for the stronger model and framework combinations.
The second lesson was that CRISPE consistently performed well in our evaluation.
It gave the models more context around the task, the role, the available information and the expected behavior. That helped particularly with complex SQL functions and edge cases.
The third lesson was that the model and the prompt cannot be evaluated separately.
The same framework behaved differently across models.
CRISPE with GPT-5-nano gave us a very different result from CRISPE with GPT-4o-mini.
So asking which model is best isn't enough.
We need to ask:
Which model and prompt combination works best for this particular task?
The fourth lesson was about cost.
GPT-5-nano stood out because it combined near-90% coverage and accuracy with much lower cost and shorter generation time. In our evaluation, it reduced cost by more than 60% compared with GPT-4.1 while maintaining enterprise-level coverage and accuracy.
That makes the economics of automated SQL testing much more interesting.
From Experiment to Something We Can Actually Use
When we started this work, the goal was simply to understand whether LLMs could generate SQL test cases.
The first experiment gave us a quick answer.
Yes, they can.
But that wasn't enough.
The more useful answer came after we changed the way we approached the problem.
We structured the prompts.
We defined what success meant.
We tested different models.
We measured coverage, accuracy, cost, time, consistency and manual effort.
We made the generated SQL executable.
We also gave the model a way to diagnose and retry when the generated script failed.
That changed the outcome.
Based on our internal evaluation, structured prompt engineering moved automated SQL test generation from something that required significant manual correction toward something that can be repeated and used as part of an engineering workflow.
And that, for me, is the bigger lesson.
AI doesn't remove the need for engineering judgment.
It makes that judgment even more important.
The engineer still has to decide what should be tested, what good coverage looks like, which model is appropriate, what the prompt needs to tell the model and whether the generated test suite is actually trustworthy.
The model can generate the SQL.
The engineering team decides whether that SQL is good enough to use.
One Final Observation
Our evaluation was conducted in August 2025 using the OpenAI API and our internal datasets and controlled test setup.
So I wouldn't treat these numbers as universal benchmarks.
A different SQL function, dataset, prompt or model version can produce different results. AI models are also changing quickly, so the model that performs best today may not produce the same result in a future evaluation.
What I do think the experiment shows is something more useful than a model leaderboard.
If we want AI to generate reliable SQL test cases, the quality of the prompt and the engineering process around the model matter just as much as the model itself.
We started with:
Give the AI a SQL function and ask it to create tests.
We ended with something much more structured:
Give the AI the function, its dependencies, the data context, the role it needs to play, the test rules, the expected output, a way to execute the result and a way to fix its own failures.
That difference is what made the experiment work.
And perhaps that is the real opportunity with AI in software testing.
Not replacing the engineer.
Giving the engineer a better starting point and letting the machine take care of more of the repetitive work.
Note: The results and observations in this article come from the internal datasets and controlled experimental setups described in the source material. They are directional findings, not universal benchmarks. Actual results can vary based on the use case, dataset, prompt design, input complexity and model version.
