Share via

SQL Unit Testing

  • Article

Thought it was time to combine my old favorite subject (SQL) with a new interest (TDD). So how do you test drive your SQL? I think the first answer is you don't. The database is typically something you want to fake anyway since setting up and accessing the database is to slow when you want rapid feedback. Nowadays when things like NHibernate are popular I think most people don't write much SQL anyway so there is no need to test drive the creation of SQL queries or stored procedures. But there was a time when many applications kept a lot of logic in the stored procedures and in those systems it might make some sense. Also we have the fact that the database needs to be created some way. Why not test drive the database schema?

So even though this might not be the hottest topic on the block, what alternatives do we have? First of all we have SQLUnit. This project does not seem to have been updated for several years. Guess that is because we don't put all application logic in stored procedures any more. But it is at least an attempt to create a unit test framework for SQL. But why use the same language for the tests as the implementation? Some people think this is OK but I think there is also a danger in doing so. Switching languages typically makes you do small stupid mistakes you don't do if you stick to one language. But sometimes it might still be worth it. For example I'd much rather use any .Net based unit test framework over SQLUnit and an example of how that looks can be found here.

Then there is of course the option of not using any framework at all. Creating your own framework may be the most efficient way of doing things and an example of database creation in a test driven manner without any fancy frameworks can be found here.

Comments