Episode 86: tSQLt

Episode 86: tSQLt

Episode 86: tSQLt 560 420 Carlos L Chacon

Based on listener feedback we got from episode 72, a request was made to have a conversation about testing frameworks and in this episode we are going to focus on tSQLt.  We are pleased to have Sebastian Meine, the creator of the tSQLt unit testing framework, on the podcast with us.  We talk about the basic principles of tSQLt Framework when it comes to unit testing. Sebastian will also be discussing about the do’s and don’ts as well as the different aspects to be considered when using the framework. Sebastian makes the case for why you should take the time to create unit tests and we discuss the challenges culture might play into the use of the framework.

 Episode Quote

“Anybody who earns a living with writing SQL modules should test those modules…and tSQLt is the best way to do that”

“For a contribution to be accepted it’s important that it is well covered with tests”

Listen to Learn

  • What the tSQLt framework is
  • The case for using unit testing in database projects
  • How the framework has been integrated into third party projects
  • How you can participate in building the framework

Sebastian on Twitter
tSQLt framework
tSQLt project on GitHub
Sebastian’s Website

About Sebastian Meine

tSQLtSebastian Meine, Ph.D. is the SQL Stylist behind sqlity.net llc. In 2010 he founded sqlity.net to fully engage in his passion for SQL Server.

Armed with a Ph.D. in Computer Science from the University of Linz in Austria and years of experience in architecting DBs for a global Fortune 50 company, Sebastian now provides training and consulting services for his clients, specializing in SQL Server Security, Test Driven Development and Performance. Sebastian is an experienced public speaker, published author, president of the Philly PASS Chapter, PASS Regional Mentor and coauthor & lead developer of tSQLt.

Carlos: Well, Sebastian, welcome to the program.

Sebastian: Hello!

Carlos: Yes. I think we have some interesting things to talk about and not only technology but how you decided to go ahead and out this together. I guess, first our subject is tSQLt and so why you won’t give us the nickel tour here on how you explain to somebody what tSQLt Framework is.

Sebastian: The tSQLt Framework here that is a unit testing framework for SQL Server. And what I mean was that a unit testing really means you go down to the smallest unit of code so in SQL Server that would be stored procedures or functions of use and you’ve write test for them in isolation. tSQLt, of course, a lot of functionality around that that makes particular the testing in isolation easier.

Carlos: Ok, I guess can you give an example there of a way that someone would might create a unit test around a stored procedure for example.

Sebastian: How detailed do you want me to get it’s early on?

Steve: Maybe we could take a little bit different approach there of higher level of like around test driven development. One of the things that I’ve seen with that is the sort of strategy of you first create a failing unit test and then you write your code to pass, and then you write another test and you write the code pass that test. Is that what people are doing with tSQLt?

Sebastian: Test driven development and I have to prefix this, I love it. Test driven development has a better reputation of that. A lot of people think it’s scary and it takes too much of work and we can talk about that a little later. Yes, tSQLt allows you to do test driven development. In fact, tSQLt itself was developed a kind of bootstrap using itself completely test driven. Important in test driven development is that you take a really small piece of functionality. You write a test such as that functionality isn’t there. And you implement a functionality and then you run the test again and it hopefully shows you now that the functionality is there that really does a few things. First, writing the test for us making sure that it fails shows to you that the test actually is testing something. It’s really easy in unit testing in any language to write a test that doesn’t really test anything. A test that can fail, so by writing that test first have the assurance that you have written your test correctly. And then by implementing just the minimum piece, minimum amount of code required to make that test pass. You end up with very modular code which in turn is really easy to maintain and you also have to test that tells you everything was still working.

Carlos: Ok. And you’ll forgive me right, so being a little bit of knuckle dragger that I am, you know, I guess it makes sense to me that you could, you know, a piece of functionality is not there and you want to test for it so let’s just say I’m adding a parameter, right. Or adding another column that you would return or something like that, right. Ok, that seems fairly straight forward. Am I using this also to test the results or to interrogate what gets returned from a store procedure or from a view as well, or is this just limited to the object itself?

Sebastian: Let’s talk about the view example. So I have a view and I want that view to return some aggregated data so I might have a count column and a sum column let’s say.

Carlos: Ok, right.

Sebastian: And so, tSQLt allows you to compare result sets. It actually compares tables not result sets so the content of tables. So what you do is insert your result set into one table and then you insert the expected result into another table and then you use set equals table to compare the two tables and it will tell if there is a difference or if they are equal.

Steve: Ok, so then in that case you would know based of the contents of the table that would be for instance being inserted from a view if the view is producing the right result or the wrong results.

Sebastian: Correct.

Steve: Based off of comparing that to a known expected outcome.

Sebastian: Correct.

Steve: Yup, ok.

Sebastian: And when starting out in general I recommend that you follow test case heuristics and you write your test cases. And for testing results sets, for sets in general you want to follow the zero one sum heuristic. So you start out with zero rows and the table that the view is accessing. Then make sure that the view is returning the right thing. So say you’re starting from scratch you haven’t written a view yet. The first thing you would do is you write a view that contains a select column say the sum column from the view and put that into, I tend to use temporary tables into a Temp table, and now the view isn’t there. The table that the view is accessing to be assumed that they’re ready, the view isn’t there yet so when you compile a store procedure you get a warning. And then if you run the test case you get an error since the view isn’t there. Let’s you first call it call to action to now implement the view. Now, what the test at this point is asking for is you have one column called sum, probably a little more expressive than sum. And the test cases are accessing the view as a name so the view has a name and the two things that are required at this point, so you implement the view. You don’t have any logic at his point because it has not ask for anything yet so you might just return a hard coded empty result set.

Steve: Ok, so then once you’ve done that you’ve confirmed that the zero case that if there are zero rows return that you’re getting back the empty set that you’re expecting.

Sebastian: At this particular example I guess you shouldn’t make up examples on the fly and sum as an aggregate so you will get one row back. In this case we would expect zero in the sum or the count column input is empty.

Steve: Right.

Sebastian: That is by the way another thing that test driven development really good at. It makes you think for you requirements so you catch something like this early on whereas often this particular case you want no row to be returned or you want a zero to be returned goes unthought-of and then the result is whatever it is but that might not match what the stakeholder actually requested.

Steve: Ok, so then once you’ve got the zero rows situation figured out I assume you move into the one row.

Sebastian: Correct. Zero one sum so the next test is the one row so you write a new test case. You insert one row into the base table then you expect the sum column to return the value that is in that row in the column that we are looking at.

Steve: Ok, and then, so then you would have your, what was the set method you mentioned that compares the tables?

Sebastian: Set equals table.

Steve: Ok, so a set equals table would make sure that your two tables are the same for your one row and then I assume you would just continue on with that for the more than one rows, the sum.

Sebastian: The sum rows, yes. And sum really means stick with the few. I usually recommend three or so. I rarely run into a case where you really need more than ten rows. And important to note here is unit testing is not performance testing. So you want to set your test data as minimal as possible. You want your test cases to run as fast as possible. In fact, you want to run all your test cases every time you make a change. The only way that is going to happen because we all are impatient and lazy. If executing a test case is easy and fast.

Steve: Right. We had the same recommendation in Episode 72 with Lin. We were talking about test automation for data warehouses. Now is kind of here thing is like find a representative sample, right. Don’t need the whole database, find your sample, couple of customers that you’ve worked with and start there. It sounds like you’re saying the same thing.

Sebastian: Absolutely.

Carlos: Ok, so then one of the next questions that we had was around who was using this? We’re not really looking for specific company names, what we are looking for is like what type of people, what type of roles are the people? Are they software developers, or are they DBAs, testers? Who is really using the product and getting the most value out of it?

Sebastian: Anybody who is writing SQL modules should be using it. And I don’t have company names so I couldn’t even answer that. Anybody who earns a living with writing SQL modules should test those modules. While I’m extremely biased I would say SQLT is the best way to do that.

Steve: You know, a good example is that I was working on a store procedure for a client recently where after the 1st of January it behaved differently than it did in the months that we’re near the end of the year, and after seeing the problems that we ran into there because of somebody doing month addition or subtraction. It would have been really nice to have a tSQLt test case for that so that we could have run through and done extensive testing there beyond what it originally done with. That’s something that I could think would be great use for it.

Sebastian: Yeah, that’s a boundary condition that crossing from one month into another one. There is a heuristic for that too. You want to make sure that code is behaving correctly at those boundaries and following test driven development and heuristics would have caught that problem most likely before it actually became a problem.

Carlos: Yup, ok, so if somebody is listening and they think, “Oh, I want to give this a try.” Where would you recommend the good place to get started or steps to follow or where to learn about using this? How do you jump in using tSQLt?

Sebastian: It’s called tSQLt and the website for it is tSQLt.org. It is open source so you can just go there download it and start playing with it. There is a small introduction manual if you want to call it, that too also a complete functional manual almost complete. There are few functions I haven’t gotten around documenting yet.

Carlos: Yup, ok. So even more than I think just to jump in a little further there is, you talked about, so the case, you made the case that gosh if I had unit tested this I could have avoid the problems down the line. But it’s almost a mentality shift because, you know, just use that example. I have a store procedure that needs a view. Well, I’m going to be thinking, “Ok, let’s see what my view needs to have so that I can feed this procedure.” And it sounds like I need to reassess what it is that I’m asking or what it is my requirements are. And so how do you begin that mind shift to say, “Ok”, instead of just jumping in and creating the view let me think about how I could setup this use cases or what questions I need to ask myself to get them setup. Does that make sense?

Sebastian: Yeah, that make sense. So unit testing, automated unit testing is a complete mind shift. It is hard for people to get use but once you have done that a little bit regularly. It’s not actually hard. It’s scary at first but you get use to it very quickly.

Carlos: Yes, I guess you can tell where I fall into my automated unit testing practice.

Sebastian: Yeah, just think about what does this view need to do for me then pick the thing that seems easiest for you right now to write a test for that functionality. And then write the test and then implement the functionality and then pick the next thing.

Steve: Ok. Now, I know I’ve seen at a couple of SQL Saturdays. I’ve seen Steve Jones with Red Gate presents on tSQLt and the Red Gate tools that integrate with it. Can you maybe share with us what part of that are the Red Gate products versus what part of it is the tSQLt framework?

Sebastian: Red Gate provides that easy button. tSQLt is a bunch of SQL scripts and to write your test cases you write store procedures that follow a naming convention. Actually, you need to create a what I call a test class. The reason I call it a test class is because that’s a naming convention that JUnit Framework, the first unit testing framework put in place and tSQLt is following the JUnit convention. So a test class is really a schema that has been marked by the framework as a schema that contains test cases and then to mark that test case store procedure in that schema you just need to start their name with let us test. That allows you to put other help a store procedure in that same schema. That makes it very flexible even though naming convention seems rigid at first. Now, to execute your test cases you have to run the store procedure tSQLt run all. That runs all the test cases in the database. A run takes parameters what you specify what you want to run. And that can be at times a little cumbersome that you have to actually call that store procedure. What Red Gate did is they provided a log in for Management Studio that makes that easy to execute test cases. You just have to select the test case you want to run and click the run button, and there’s a nice GUI on top of tSQLt. And I have to say Red Gate isn’t alone anymore. There are now I think three other vendors that offer similar functionality, but Red Gate was the first.

Carlos: Interesting. Now, so you’ve mentioned the documentation. Is there an example, I mean, so I think again, a knuckle dragger that I am It would be helpful for me to see some examples. Like, “Ok, here’s a view, here’s a procedure.” Let’s walk through that. I know you mentioned you had the procedures there to kind of get going. Are there good examples out there that people could reference to kind of get a handle on this?

Sebastian: Yeah, so there’s the user guide which is really the manual of the procedures and the processes. There’s also a quick start guide on tSQLt.org which is a very basic introduction into how to use the framework and the unit testing in general.

Carlos: Very good.

Steve: So for the unit testing to work, is this something that the people use and need to have, like if it’s developer sharing a database that they have to have their own individual instance or database they’re working with. Or can multiple developers work against a shared database using tSQLt.

Sebastian: As a general best practice, you want to have your own development environment. And SQL Server Developer Edition now being free there’s really no reason anymore to not have your own development environment.

Carlos: Very good point. Yeah, ok.

Sebastian: And the database code has to be, it’s not even an option anymore, has to be on source control and it’s easy to run, install your own copy of the database.

Carlos: So when you say it has to be in source control does the tSQLt do something to integrate to pull things from source control?

Sebastian: No, tSQLt is only for testing. But Red Gate has a nice plug-in, Red Gate Source Control, that even manages things like migration scripts.

Carlos: Yup, ok. So with tSQLt being an open source project. Is that something that you have other people contributing with or is it currently just you or where’s that at?

Sebastian: It started out with Dennis and me. And Dennis got lost somewhere on the way, alright, now it’s only me and every once in a while I get somebody asking if they can help out but they lend us up here too for whatever reason. So yes, I would love to have help on the project and the project is on GitHub. You can find it there if you search for tSQLt. I have been stretched a little been over the last few months so there has not been a lot of progress on tSQLt. I always doing a little bit but I haven’t actually put out a new version in a while.

Steve: Yeah, I’m seeing this, there are some issues and it looks like people are contributing some issues. 

Sebastian: for additional functionality for problems to be fixed.

Carlos: With that, part of the beauty of GitHub and being open source is that when people asked for things to be fixed they can also contribute.

Sebastian: Yes. And for a contribution to be accepted is it’s important that it is well covered with tests.

Carlos: That’s a great point since it is the SQL testing tool. It would be silly for someone to submit a change or a feature that isn’t well tested, right.

Sebastian: Correct and you will be surprised what I sometimes get.

Steve: So one thing that you have said is part of the story there was and I think you mentioned earlier, was that tSQLt or unit testing is not intended for performance work but it seems like it’s a great way when you’re doing the performance work to now if you broke anything or if you’re given the right results.

Sebastian: Correct. Anytime you have to change your code you want to be sure that you haven’t broken anything. And have you ever been in that situation where you change a little bit of code and of a store procedure because of a requirement change and other feature that you thought was totally unrelated suddenly broke?

Steve: Oh no, that never happens.

Sebastian: Unit testing is really good at showing you those scenarios while you’re still are in the code. So instead of hearing about it months later, “What did happen there? I don’t remember.” You get that feedback within a few minutes so you still know what you did just change and then you can easily understand hopefully easily as a connection figure out how to actually make it work. And that’s really one of the things behind Agile Development that we haven’t talked about yet. Unit testing is part of that Agile Development mantra. Agile development is all about reducing the lengths of your feedback loops. If something breaks I want to hear about it as soon as possible. The longer it takes, the more time passes the more the cost, call it exploit, it is actually exponential.

Carlos: The risk increases of your ability to quickly resolve that.

Sebastian: Yeah.

Steve: And it’s far cheaper to fix the bug that you find when you run your unit test than it is to test the bug that a customer reports after you released the code.

Sebastian: Correct and so far has like three 0s.

Carlos: So a lot of people like to pay lip service to that and you know particularly organizations, you know. I mean a lot of people say they’re running Agile and they really just kind of have a lot of meetings, right. What kind of numbers are you seeing from the tSQLt side, I mean, just from downloads. You’ve been working at this for a couple of years now at least from the GitHub persepective.      

Sebastian: It is still very low. I would like to add a zero to the number of people who downloaded it.

Carlos: So you need a few more people to get in there. And again just for me I think there’s still a pretty tall barrier to entry.

Sebastian: That’s the thing with unit testing in general. Test driven development in particular but even if you just do unit testing makes it a lot easier to maintain your code in the long run. You spent about 80% of your time maintaining existing code and only 20% of that writing your code. That makes sense to figure out a way to reduce the cost of maintenance. And if you have a good set of test cases it makes maintenance so much easier. It actually makes you more likely to implement a change too because you’ve learned now that it didn’t break anything. It reduces a fraction too good thing there. But anyway and to write that code of unit test that’s a lot of work so the actual writing of the code becomes more expensive. The problem there is that that cost us upfront. Now it’s hard to get into developer’s minds, it is really worst investing the time now because it will save you later. But it’s also hard to get that into the management mind because a few showed that in the old way would have been, let’s call it done, tomorrow is now going to take three or four days to be done.

Sebastian: Yes, so management needs to buy and it’s also hard to get them agree to it because it’s not going to take one day to finish the code but it’s now going to take four days. But the thing is you have the code done in one day and it breaks in a month from now and it is installed in a customer. You cannot really call it done in the first place. From my perspective code is done if it is proven to be correct. And that’s when you achieve this automated unit testing.

Carlos: Sure, and often times though when management buys in and say they want to be an Agile company or business unit. They don’t always buy in to the extended time that it may take to make some of those things happen and that’s where the challenges come up.

Sebastian: That is correct, yeah. Management has always more than one first priority.

Carlos: That is so true.

Steve: That’s right.

Carlos: So shall we go ahead and do the SQL Family?

Sebastian: Sounds good.

Steve: Alright, so Sebastian can you share with us how you first got started with SQL Server?

Sebastian: Yeah, that was the same company so when I was done with my Ph.D. in Germany. The job market had just tanked so I decided to try my luck in the US and ended up getting a job with a single phone call. That was luck. But the one thing that I told the guy in the interview was that I really like programming but I don’t want to have anything to do with databases.

Steve: Yeah, look at you now.

Sebastian: So he said, “Ok, that’s great.” And he ended up putting me in the database team.

Carlos: It must have been an accident, right, the accent, you know, it didn’t quite get across.

Sebastian: And to be fair my first assignment was C++ code there. I was working for the driver of those 128 store procedures. But then at some point, as our team member said, maybe we should ask the Ph.D. to help us with this performance issues and that’s how I got pulled into actually working on SQL Server. Somehow I learned liking it really quickly.

Steve: Very cool. Now, if you could change one thing about SQL Server, what would it be?

Sebastian: Yeah, a lot. What we really didn’t talk about today yet is testing an isolation. And to test and isolation in other languages you use things like test doubles, mark objects, so replacements of the actual object that behave in an expected way so that you can write your text around that. tSQLt provides that functionality by providing fake tables and spy procedures and fake functions and to make that work you have to go really deep into the internals of SQL Server and two things SQL was not written for. I would really love if that could be easier. By example the feature that’s most requested right now that I haven’t been able to implement completely yet is to deal with schema bound object.

Carlos: Alright, yeah, I noticed that on the request list.

Sebastian: To get a schema bound object replaced was a test double I need to first replace a schema bound object with a non-schema bound object. So after that I need I need to script out the object which we know SQL Server has that functionality built in but it doesn’t make that accessible to see for our code. I have to hand code that script thing again while there are really not a lot of mistakes or bugs in tSQLt. The ones that keep sneaking in are things like, I forgot to account for, it was a scale in the new date at DATE_TIME data type because I hadn’t use it.

Carlos: Got you.

Sebastian: And honestly I shouldn’t have to think about it because SQL Server should provide that functionality. And another thing that would be great is there would be a way to measure code coverage. Which there is one framework out there now that is doing something like code coverage based on extended events but It would like to get that a lot more fine grains in that solution.

Carlos: If you would forgive me, what would you mean by code coverage?

Sebastian: Code coverage means if I have my code and I have a bunch of test cases. I want to be able to pin point the areas in the code that have not been covered by a test.

Steve: So for instance a chunk of dead code that’s part of an IF statement that never been hit or something.

Sebastian: Part of an IF statement but likely will be hit in real life but I’ve forgotten to write a test for it. If you follow test driven development very closely that will not happen. Most people don’t and then the code coverage really becomes handy.

Steve: What is the best piece of career advice that you’ve received?

Sebastian: Keep learning.

Carlos: Yup, never stops.

Sebastian: Don’t stop learning, you know.

Carlos: Sebastian, our last question for you today, if you could have one superhero power, what would it be and why would you want it?

Sebastian: Yeah, so there was this movie ones very long time ago. It was about this Robot #5.

Steve: Short Circuit.

Sebastian: That goes back to keep learning. I love to read and I suck at it. I would really like to be able to read that fast.

Carlos: Ok, speed reader, very good. Well, Sebastian thanks so much for being on the program today.

  • Nicky van Vroenhoven March 23, 2017 at 12:45 am

    Hi guys! I’ve been following your podcasts for several months now. I specially liked the ones about Indexes, Data visualizations, BI automation, SSIS and this one. You may have guessed I’m a BI Consultant by now :-).

    I have downloaded and looked at the TsqlT-framework before but like you said, never got the time to really start with it. With that said, I do believe there is value in using it so I will commit to testing it in one of my next projects.
    Thanks for the great podcasts you’ve been delivering and keep up the good work!


Leave a Reply

Back to top