I've been writing SQL code for years now and the QA loop is always done by a human, in every job I've had.
Any time I propose automated testing or some forms, people dismiss it because every data set is different blah blah blah.
What I want to do is build something that scans a SQL script and writes to a text file everything the QA person should check based on the SQL script.
I know i could do it using powershell, but that's hardly high tech. I looked into building a VS Code extension to do it, but I would be learning that all from scratch.
Does this sort of thing already exist before I go and re-invent the wheel? Where It can parse text and based on pre-determined rules output something specific?
Many thanks in advance.
Top comments (3)
SQL testing and analysis tools are (so far as I'm aware) limited to linters like sqlint and unit tests in the form of pgTAP. I don't know of anything that does what you're envisioning, but it involves static analysis similar to a linter -- just with a different final objective.
I would definitely target the most general platform possible. A standalone binary which can be glued with little effort to any plugin-supporting editor is the best way to go; VSCode is big, but it's not everything. Powershell is likely not a great idea unless you only care about SQL Server since other SQL-writing audiences trend Linuxy.
Something which reads an input stream (the SQL script), does some transformation, and produces an output stream (the human-readable test script) is a compiler. Compilers aren't just for implementing programming languages.
SAP's Chevrotain is a pretty easy-to-learn JS library which you could use to make a compiler.
It's unclear what you're looking to do though. Testing that a program is doing what's in the SQL script doesn't seem valuable. If there was a bug in the script, there would be a bug in the test plan too, wouldn't there? How would you catch the underlying problem where the SQL works (it runs without error) but isn't returning what the humans intended?
People are afraid of new tools and don't understand how they benefit from the tools. They see tools as toys. In general, of course.
Personally, I'd do static analysis on SQL and some kind of behavior driven testing because I have no idea how to apply TDD to SQL.
On larger scale, run automated tests in your CI/CD pipeline (like Jenkins with JMeter). Explain to your team that large scale security tests are done with automated tools, I mean finding sql injection bugs and, in general, fuzzying.
On fuzzying, you can find quite many tools and research papers. Google did nice progress on Chrome thanks to fuzzying.