Is there anything wrong with testing SQL with JUnit tests over JDBC?

Question

I have Java application that makes some JDBC queries to PostgreSQL database, using prepared statements. The queries are not very complex, mostly one liners, but anyway I would like to have some Unit tests for them.

Initially I wrote a JUnit test cases that connect a separate testing database (not a production database of course), wipe it at startup, create simple test content and fire the queries. I have been criticized for this approach because the "database should not be used in tests".

The reason why the "database is used in tests" is that database engine is the interpreter for the code (SQL) I am testing. A requirement to test SQL without the database sounds like a requirement to test Java code without Java Virtual machine.

The claim that some code just must never be tested because of philosophical reasons sounds somewhat strange, same about suggestion that most of SQL features (like ranged or otherwise more complicated queries, or joins) should actually never be used. I also use a wrapper class around all database activities and have a "SQL service mock" that does not talk with the database, and use it in various other tests, but I also need to test the actual production implementation of this mock.

Is there alternative, better method to test my JDBC query set, or maybe I am just not doing anything wrong?

Answer 1

Martin Fowler has a nice post about external dependencies in unit tests:

"Even a classic tester like myself uses test doubles when there's an awkward collaboration. They are invaluable to remove non-determinism when talking to remote services. Indeed some classicist xunit testers also argue that any collaboration with external resources, such as a database or filesystem, should use doubles. Partly this is due to non-determinism risk, partly due to speed." http://martinfowler.com/bliki/UnitTest.html

Personally, I think it would be fine if your tests set up their context instead of relying on external systems that might not always be available.

Answer 2

From the mentioned @Aaron Digulla post (http://blog.pdark.de/2008/07/26/testing-with-databases/) looks like using database in tests directly is mostly problematic when the local, dedicated database instance on developer workstation is expensive:

• The database is licensed on "per machine" basis so money must be paid for these local installs.
• Developers actually cannot setup and maintain the database (they do not have access to schema dumps, for instance). A dedicated database administrator must be involved, this administrator has enough other tasks to do.
• Using shared database instance dedicated for testing only allows one developer at time as schema must be wiped before running tests.
• Using testing schema in production database may be a disaster. Agree.

Hence it is obvious the importance to find and encourage alternative testing approaches.

From this look like if open source or otherwise free database is used, and most of developers know how to install it and were where to edit a couple of lines in database configuration file to get it running (needs only be done once), this may become less an issue and eliminate the need to write the database mock. This mock also takes time to write. However, if once written, the mock is much faster than the database.

I also fully agree that lots of good tests, very likely majority of them, can be written without referring the database at all.

Answer 3

I've outlined my reasons for avoiding databases in unit tests in this blog post: http://blog.pdark.de/2008/07/26/testing-with-databases/

To summarize: What are you testing? PostgreSQL? Why? Don't they have thousands of unit tests already which make sure that the database works as expected?

Why are you testing PostgreSQL? You should be testing your code. The reason why you don't is that nagging feeling "I might miss something." The source of that feeling is that you don't know exactly what you're doing. The natural instinct is to drown the anxiety in lots and lots of code (as if executing more code in a unit test would make it better in any way).

Solution: Try to separate the code which creates the SQL queries from the code which actually executes it. That way, you can easily split unit and integration tests. The unit tests just check that your code generates the correct SQL (simply call the "create SQL query" part and assert that the string and the arguments are correct).

The integration tests then can actually execute the SQL queries to see if a test database would return the correct results.

When you move the integration tests to a CI server, you can run the fast unit tests with a lot of confidence since the CI server will let you know eventually when they break.