DevOps: SQL Server and Unit Test Challenges
I had a previous life as a developer and ran a software-development house. Even then, I was very focussed on data. I don’t think I’ve ever had a BD (before data) period. I see almost everything I’ve ever worked on in data-related terms, so perhaps it’s the time before I focussed on data.
But what this does mean is that whenever I get together with other data-related people, I’m one of the people who is asking why things that are taken for granted in the developer community, aren’t present in SQL Server and its T-SQL language.
Testing is one of these challenges. I’ve had old wise colleagues in the past who see this simply:
It’s pointless to write code that you can’t test
And yet, T-SQL development is still one of the areas where that’s a challenge. Over the years, I’ve had many discussions with SQL Server product team members about this, and still haven’t been persuasive enough to convince them to do something about it.
I should note at this point though, that if you’re someone who sees the database as just a storage for your objects, that no code should exist in the database at all, and that the performance you’re currently seeing is good enough, then read no further.
But I currently live in a world where performance matters and many operations need to be done as close to the data as possible, with as little data movement as possible.
If you are developing in T-SQL today though, writing unit tests and test harnesses is just way more challenging than it should be. In many cases, you simply can’t do it in any practical way, at least not within T-SQL. There have been some great attempts like TSQLUnit (https://sourceforge.net/projects/tsqlunit/), and tSQLt (http://tsqlt.org/) but these aren’t really where I want to be. It’s not for lack of trying, but it’s for a lack of support within the product itself.
A simple example might help.
I can write code in T-SQL to trap and handle errors, including system errors (ie: those with error numbers less than 50,000). But if I want to test that error handling, I’m fresh out of luck. The “normal” way to do that in high-level languages is to write code to just throw that error and check what happens. But the product group have decided that we’re not allowed to raise system errors.
When I’ve discussed this with them, I get responses like “ah but then we’d get errors reported and we’d have no idea where they came from”. I get that they’re thinking from a conservative product support point of view, but that sort of thing can be handled. There would be a way to handle this if there was a desire to do so. And what I’ve never understood is the lack of interest in doing so. Product group members are, by and large, developers whose lives must take testing as part of their own routine.
Error handling is just one example issue though.
So we’re left with two options really:
- Spend our time asking the product group for changes to support better quality development in T-SQL
- Wrap all our unit tests in other languages, accept that we just can’t test some things, and deal with language limitations.
As we move into a DevOps-centric world, this is going to be more of an issue. I’d love to see us pushing for the first option and having the expectation that people developing with T-SQL should have access to similar developer-focused tooling to those building SQL Server itself.
2018-03-16