This post has already been read 995 times!

This week at Redgate HQ we’ve been lucky enough to have a visit from Dave Farley, Continuous Delivery evangelist and co-author of the book Continuous Delivery.

In one of our discussions we started talking about database unit testing. Pretty soon we realized we both meant something fundamentally different when we called something a database unit test.  Have I been doing it wrong all these years?

I’ve historically used database unit tests to test small units of business logic in my database. Every time the database is changed I run this whole suite of unit tests, and am confident that my business logic works as expected. This approach is analogous to the type of unit test I write when developing application code. For example I might have a test that checks that a ‘get_user’ stored procedure returns the data I expect from the Users table.

Typically I write these tests using the tSQLt framework, which makes it easy to write self-contained tests that include their own data.

Dave Farley has taken a different unit testing approach, which focuses on testing the change you’re making rather than testing the state you end up in. This type of ‘migration test’, as we started to refer to them as, can complement the ‘business logic test’ approach by testing the changes you make to objects that contain persisted data, e.g. tables. This is a pretty powerful approach, as it lets us test the data migration logic as well as the schema change.

The aim of both types of test is the same though. We’re trying to prove with our tests that the code we have written has the effect we expect it to. If it doesn’t our tests will fail and we can re-think our approach.

Here’s an example scenario. Say I’m splitting a column in a table, in this example I’ll split a ‘name’ column in my Users table into new ‘first_name’ and ‘second_name’ columns. To keep my data migration example simple let’s assume that splitting on the first space is sufficient to separate out the names.

Let’s follow the Test Driven Development (TDD) philosophy and write the test first.

NB – I’m borrowing the tSQLt syntax here, but this example is pseudo code. This isn’t a valid stored procedure as I’m referring to columns that don’t yet exist in my database. More on how to make this actually run in tSQLt later.

Leave a Reply

Post Navigation