Is there something wrong with SQL testing with JUnit tests over JDBC?
I have a Java application that makes some JDBC queries against a PostgreSQL database using prepared statements. The queries are not very complex, basically one liner, but I would like to have some unit tests for them anyway.
I first wrote JUnit test cases that connect a separate testing database (not a production database, of course), wipe it out on startup, create simple test content, and run queries. I have been criticized for this approach because "the database should not be used in tests."
The reason the "database is used in tests" is because the database engine is an interpreter for the (SQL) code that I am testing. The requirement to test SQL without a database sounds like a requirement to test Java code without the Java Virtual Machine.
The assertion that some code simply does not need to be checked for philosophical reasons sounds somewhat odd, as does the assumption that most SQL functions (such as ranked or otherwise more complex queries or joins) will never actually be used. I also use a wrapper class in all database activities and have a "SQL service mock" that doesn't talk to the database or use it in other tests, but I also need to test the actual production implementation of that mock.
Is there an alternative, better way to test my JDBC queryset, or maybe I'm just doing nothing wrong?
source to share
Martin Fowler has some good posts about external dependencies in unit tests:
“Even a classic tester, like me, uses double tests with awkward collaboration. They are invaluable for removing ambiguity when talking to remote services. Indeed, some classic xunit testers also claim that any collaboration with external resources such as a database or file system should use doubles. This is partly due to the risk of indeterminism, partly due to speed. " http://martinfowler.com/bliki/UnitTest.html
Personally, I think it would be nice if your tests set up their context instead of relying on external systems that may not always be available.
source to share
I have 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? What for? Don't they already have thousands of unit tests to ensure that the database will perform as expected?
Why are you testing PostgreSQL? You should be testing your code. The reason you don't do this is because I feel, "I might be missing something." The source of this feeling is that you don't know exactly what you are doing. The natural instinct is to drown the anxiety in a lot of code (as if running more code in a unit test would make it better anyway).
Decision. Try to separate the code that generates SQL queries from the code that actually executes it. This way you can easily separate unit tests and integration tests. The module verifies that your code is generating correct SQL (just call the "create SQL query" part and validate that the string and arguments are correct).
Integration tests can then actually execute SQL queries to see if the testbase returns the correct results.
When you move integration tests to a CI server, you can run fast unit tests with great confidence as the CI server will tell you when they break.
source to share
From @Aaron Digulla's mentioned post ( http://blog.pdark.de/2008/07/26/testing-with-databases/ ) it looks like using the database in tests directly is mostly problematic when the local dedicated database instance is on a developer workstation is expensive:
- The database is licensed on a per machine basis, so money must be paid for these local installations.
- Developers cannot actually configure and maintain the database (for example, they don't have access to schema dumps). You must use a dedicated DBA, that DBA has enough other tasks.
- Using a shared database instance intended for testing allows only one developer at a time when the schema must be wiped before running the tests.
- Using a test schema on a production database can be a disaster. I agree.
Therefore, it is clearly important to find and encourage alternative testing methods.
It follows that if an open source or free database is used and most developers know how to set it up and where a couple of lines in the database config file could have been edited to get it started (this only needs to be done once) this might become less problematic and eliminate the need to write a database. This layout also takes time to write. However, once written, a layout is much faster than a database.
I also completely agree that many good tests are likely to be written without a database reference at all.
source to share