Displaying database unit test package names in CI

I've been using tSQLt tests for a while now with Continuous Integration, and one of the things I've noticed is that test results from nUnit or JUnit are presented with a nice package name, but that the tSQLt tests either have no package name, or list as (root) - depending on your CI system.

TeamCity

Jenkins

I have recently had the occasion to realise quite how annoying this can be, particularly if you're using Jenkins and running tests with the same names in multiple databases (TeamCity does list the database if you dig into the tests tab, but oddly not everywhere). This presents the problem that you can't easily see the name of the database in which a failure has occurred, to aid prompt troubleshooting.

So - I've done a little digging, and found this post on why this might be the case for Jenkins. I then modified the way which tSQLt tests are output to achieve the test labeling which Jenkins needs, i.e. "classname.testname". I felt that database name was the best class to suit my needs, but you can modify to suit your requirements.

The modification is to [tSQLt].[XmlResultFormatter]. I changed the selects in the union, for example in the snippet below:

I've changed the 'Class' lines to read:

Do this for all the 'Class' lines in the procedure. There is 1 change in the second union, and 2 in each of the third and fourth unions.

Making this change gives us the following result when we re-run the tests from Continuous Integration:

TeamCity

Jenkins

As you can see this small modification helps to aid visibility to the test failure display.

Once you've modified the framework, you can either script the change as part of your build process, or if you have a source controlled database, then you can simply change the source controlled version of the [tSQLt].[XmlResultFormatter] stored procedure.

By the way - you can also output test execution times to your CI system by modifying this same procedure - see this mailing list post for details.

I think these changes really help to integrate database unit tests within the CI process, and make them easier to use in a complex development environment.

10 things I wish I'd known when I started with tSQLt and SQLTest

Simple Talk have published my article which details some tips and tricks I've learned about unit testing using the tSQLt framework. More details here.

Agile Database Practice Training

As you will probably know from reading previous entries on this blog, I've become a convert to using Agile approaches to database development. When coupled with Continuous Integration and Test Driven Development, it can really help you to turn around database development quickly.

And it's not just developers who need to be part of this loop. Continuous Deployment and automated best practice testing can help allow DBAs to be responsive to deploying new developments, without huge amounts of extra work. The key advantage of this? You can turn around software developments more quickly - and that means you are more valuable, and your employer can do better in the market. At a the recent SQL in the City conference in London, Red Gate Software's Joint CEO Simon Galbraith gave a very impressive overview of how Agile approaches had helped Red Gate to achieve better market adoption and a product better tuned to market need by adopting Agile approaches.

The authors of the tSQLt database unit testing framework (which is what I use for database unit testing and test driven development) are running a course on how to get started. This covers everything from how to use tSQLt unit testing to write tests to how to adopt this within a CI environment and fit Agile development into the development cycle. The course follows the US tour of SQL in the City and you can find more details (and book your place!) on this website. It's not free, but should provide great business value to anyone looking to adopt Agile practices within database development, or as part of a wider development team. The course also includes a license for Red Gate's Source Control software, as well as an automation license and a discount on SQL Test - together worth $1235 - which gives you the tools necessary to get started.

It looks like a great course and one I wish had been held after the London SQL in the City event - the session that Dennis and Sebastian presented was a great introduction to tSQLt as a test framework.