One of the things I seem to struggle with frequently is the need to deploy a software application to test the database interaction. More often than I’d like, I have a syntax error or typo in my sql statement that triggers a runtime error when the statement is invoked.
In some of these projects, there is a very large number of sql queries (>100), which makes it very time consuming if I need check each one. Also, deployment and runtime steps can be so time consuming that it slows me down when checking sql I modified.
Most of the time after checking out an unfamiliar codebase or one I haven’t touched in some time, it is taken on faith that all of the sql works correctly.
What are standard practices around verifying database interaction in applications in a non-manualmore automatic fashion?
You should write test cases for all the database interactions. Given that syntax checking is a concern, you should run them against a test database running the same database software your application is using. If you can get a good representative data sample, then this also provides an opportunity to check your queries for performance.
You could refactor your applications to use Hibernate, or some other framework which generates queries for you automatically, but it sounds like this is legacy code that was not written with such frameworks in mind.
I found this on SO: https://stackoverflow.com/questions/5015613/online-sql-syntax-checker-conforming-to-multiple-databases …also this: https://stackoverflow.com/questions/7753081/are-there-any-sql-validators-that-can-check-syntax-against-multiple-database-ser
One link seems to be a formatter that also checkes syntax: http://www.wangz.net/cgi-bin/pp/gsqlparser/sqlpp/sqlformat.tpl and an online version: http://www.dpriver.com/pp/sqlformat.htm
Unfortunately, it looks like it’s a desktop application or VS add-in so it might only do MS SQL. For what you’re suggesting, it would be better to have some tool that can be run from the command line as a part of build process. The closest thing I can think of to that would be attempting to get an execution plan for each query because that would let you know if there’s any syntax errors, but your build process would need to make a connection to the database to actually get the query plan.
From the links above, I’ve found an online tool here: http://developer.mimer.com/validator/
It validates against SQL ANSI standards.
Also found an online tool for MySQL: http://www.piliapp.com/mysql-syntax-check/
For MS SQL there’s this one: http://www.ubitsoft.com/products/t-sql-analyzer/
And there’s also http://sqlfiddle.com/ which is a lot more than a validator.
And when you verify that the syntax is ok, how you plan to verify that your queries are ok and return the expected data?. Is perfectly posible, in fact the most usual, that your queries are syntactically correct but logically incorrect.
Test your application, if test manually its time consuming and impractical (and of course it is in every non trivial system) do automatic testing.
To expand on TMN’s answer, it’s very important to extract database logic into it’s own layer. You should have classes that wrap every call to the database. This makes unit testing your database classes much easier. Once you have verified that all your Data Access Layer classes are working correctly with THEIR unit tests, you can mock them out in other classes unit tests because you are no longer testing data access in that particular test.