TDD with SQL and data manipulation functions

Question

While I'm a professional programmer, I've never been formally trained in software engineering. As I'm frequently visiting here and SO, I've noticed a trend for writing unit tests whenever possible and, as my software gets more complex and sophisticated, I see automated testing as a good idea in aiding debugging.

However, most of my work involves writing complex SQL and then processing the output in some way. How would you write a test to ensure your SQL was returning the correct data, for example? Then, say if the data wasn't under your control (e.g., that of a 3rd party system), how can you efficiently test your processing routines without having to hand write reams of dummy data?

The best solution I can think of is making views of the data that, together, cover most cases. I can then join those views with my SQL to see if it's returning the correct records and manually process the views to see if my functions, etc. are doing what they're supposed to. Still, it seems excessive and flakey; particularly finding data to test against...

Answer 1

An important rule to test everything that is database related is to completely isolate it from the rest of your application.

The ports and adapters architecture is a really good example. The database is regarded as an external plugin through an adapter to your application. The same goes with all the 3rd party subsystems. For testing how your app would behave and interpret the responses of 3rd party subsystems the only way I know how to test that is to stub the responses of this individual subsystem. I does not necessarily mean that you would have to manually write all the data objects. You can easily take the approach of using data driven testing.

In regard to testing how your application interacts with your database you can fake out the database adapters to use an in-memory database for example.

Now testing your database queries. First of all all the complex queries should be decomposed in more easy, simple and predictable queries. The same you would do for a fat class or for a fat function. There are tools that can help you testing your database like Dbunit. A simple approach I sometimes take is to use the concept of characterization tests. So I would put the database in a know state, run all the queries I have to write save the output in a place(file, memory) and consider this output to be the correct one. The next runs would compare their output with this one, so that would definitely offer me the regression testing i need. Indeed the first output is not guaranteed to be correct but the regression problem can be solved this way. If you have your queries well decomposed you can test them individually towards the database that is in a known state.

Answer 2

That is an interesting question because the database is usually the part that is faked during application unit testing. Hopefully the logic of the database engine itself is well tested by the provider but of course the queries, schema, and stored procedures are code that needs to be tested and protected against regression. This is often left to integration testing which is not TDD.

Views would likely be a difficult way to do it because they don't really lend to the test first red-light, green-light automatic testing of one aspect per test that is preferred in TDD. Also with views you can't write the test first before the code. A better approach would be to write stored procedures where you can add "assert" logic in the procedure (e.g. using "if" statements) to test the output for failure. You need to test just one thing in each unit test to isolate the unit, and the SP method would be better suited for that. Also, with SP's you could run the whole suite of them as scripts as you develop the initial code and later on when testing for regressions when refactoring.

Also, be mindful that the tests must be repeatable and you will need some scripts to initialize and tear-down the database state to ensure that the state is the same for each unit test.

For your question about data that is not under your control, that is a difficult area. I think you are better off mocking it with fake data and testing the exception and edge conditions as much as possible for unit tests. Otherwise it will fall more into the category of integration testing (which is also a good thing to do). For integration testing you can run your tests against the 3rd-party data and let it generate an initial output and for subsequent tests (e.g. after refactoring) make sure those outputs repeat the initial known output.

Answer 3

At some point you're going to need test data. If you're using a 3rd party system, the schema has already been created, but you'll need to address future changes. Hopefully, you you can get these changes from upgrade documentation, but you may be forced to compare database versions yourself.

The expected result sets can be saved in database tables or external files/spreadsheets. I've even seen CHECKSUM used or comparison. When you test a view/sproc, you'll get a failure since they don't exist. Then you create the object with enough code to at least execute (SELECT -1 as [wrong_data];) and you'll get a failure because it doesn't match the result set. Once they match, you have your greenlight.

I've started to work with project owners and ask them to mock reports in a spreadsheet and try to come up with partial data for me (You could put the result data in a test table.). There was some pushback at first, but they realized I'm going to create a report and they're going to have to verify it anyway. This has saved time in the long run. If they want to make a change request, they get to redo the spreadsheet. Now they can answer the question, "How hard would it be to add...?"

Answer 4

If your database platform is SQL Server, there is a very nice free tool: tSQLt.

tSQLt is a database unit testing framework for Microsoft SQL Server. tSQLt is compatible with SQL Server 2005 (service pack 2 required) and above on all editions.

I have successfully used to implement testing at the database level.

Some of the key elements that make it so useful include:

• Ability to work with fake tables and views which reduces the setup normal involved
• Tests automatically run in transactions (so easily re-runable)
• Your asserts can do comparisons on tables (both real and fake) so you can see if you have changed any data easily