How to test functions which interacts with databases?

Question

I have a function getEmployeeData() which internally calls generateSQL() to get the SQL and execute it and returns the final result.

Now, how to test this functionality? Having JUnit test to validate the generated SQL (i.e validate the sql returned by generateSQL()) is enough or should write some integration tests which will validate the data returned by SQL?

Answer 1

You should test on more levels and ask more questions about this testing problem, some ideas are:

• How can you tell that getEmployeeData() returns all data someone wants? Is something missing? Or perhaps you expose too much in the result set? How do you know your requirement is correct in the first place?
• How does the execution plan look like? Is it effective? Is it effective when you have 10x more employees? How long will I, as a customer, wait to get the data I want? Will it drive me crazy, or will it be done it split second?
• How does getEmployeeData() handle (edge) cases like when no employee data are returned because none exist? You can't tell by just looking at generated SQL code provided by generateSQL().
• You say that generateSQL() generates SQL code. Perhaps it accepts some parameters. Are the params safely handled when you pass intentionally malformed data? When this happens, how does it look like? How does the exception handling look like? What does the end user sees in such an example? Do you leak internal information such as stack traces etc.?

Integration tests have their place. Other test areas just as performance have their place as well. Questions like the few I shared help you think about more of such areas that are likely important for the people who will eventually consume the result of the function(s).

Some of these areas might already be covered by some other checks and testing, it's hard to say from your question. But generally speaking, you want to have integration tests as well.