 Hi there, and welcome to another episode of Visual Studio Toolbox. I'm excited to be back once again with my friend Eric Kang. Eric, welcome to the show. Thank you, Dimitri. Thank you for coming back. Well, we've been doing a three-part series on SQL Server Data Tools, a topic that a lot of our viewers are interested in, certainly a great way to do database development. Today, you're back to talk about a third part in our series. This is something we were hoping to get to and we finally got to it. So what are we going to talk about today? Okay. In the first episode, we talked about the key concept, the core concept, and the key features of SSTP. What is this thing and how can it help our community? Exactly. In that episode two, we talked about the database CI and CD. Today, we are going to talk about database unit testing. Awesome. Well, I think that brings us to every bullet you wanted to hit. So that's really, really awesome. And if folks say, if you missed the episode that we did, the other two that we just talked about, they're up on channel line, of course, go to Visual Studio Toolbox or go to the Visual Studio YouTube channel, you'll find those shows there. But hey, we've got a great topic, so we're going to jump right into it. So Eric, why don't you take it over? Okay. Thank you very much, Dimitri. Okay. So today, we are going to talk about unit testing in DevOps pipeline. So let's set a goal for the demo. I will just jump right into the demo and instead of talking too much before the demo. Our audience wants more slides actually. Really? I'm getting tweets already. I only prepare always. I want two slides. You only have only two slides? All right. We're going to have to deal with it. Okay. Let's set up a goal for today's episode. As a developer in DevOps practice, I want to unit test my database codes like C-Shop, a Java program to achieve high code coverage. So if you're an application developer, you already practiced unit testing, integration testing, and functional testing, right? So why don't we bring that good practice, test the practice into database as well? I'm going to talk about that one. Sure. Let me ask you a question to that point. As a developer, I'll be honest, I never had unit tests on my data layer. That was just not where we went. I mean, we barely had unit tests on our code side. But I guess the way that I always thought about it, and again, I'm just speaking honestly for myself, is that we're testing the code that ran against the SQL, I was testing the SQL, right? So that was sort of an indirect way. What would be some of the benefits of testing SQL at the data layer? I see. So it's kind of a fundamental way of changing in terms of a fundamental way of thinking. So before, we traded database as more like a runtime. That has data and as your application to run, then you need to have a database as runtime, and then you focus on application testing, right? Yeah. The data was just a structure, and the data was the data, right? And the app is really what mattered was a lot of the mindset. But in reality, your database like a store procedures and functions have the business logic for your application as well. So if you want to have the good practice of testing, validating your outcome end-to-end, then why you miss out database, right? That is basically a very fundamental thought. And nowadays, instead of thinking the database as runtime, SQL Server instance as the database in the runtime, you think more holistically application, most applications have a database layer, and in a holistic view, application and database is an integral part of application as a whole solution, right? So that is a way of thinking. And with a CI CD, you want to put the application and database into the CI CD pipeline. Then for application case, you always have a gated build using tests as a part of the CI process. Makes sense, yeah. Then now it makes sense to have a database test in your CI build so that you can always have a gated build in your CI pipeline. Right, so you put validation this way all across your application, and for sure we've all had those monster store procedures with tons of logic and sometimes hard to test from the app code because of by the time you get to it, right? Or which paths of code won't get to my SQL, right? There's always that question that you're struggling with is building the unit test in the code side. So I guess we're building against SQL directly, you're making sure that you really do test every input parameter you care about and every other scenario there. So the common practice of unit test for application is that you test the program's business logic at the smallest unit like functions, right? So if you apply the concept to database, your store procedures and functions and the triggers, those are like functions in your application and those are the subject of the unit testing. If you test your application with other modules and then other components, including database, then it is a stage of integration test. So you already graduated the unit testing. So if you just focus on database testing and the integration testing or functional testing, then you end up having no unit tests for database, right? So today we are going to kind of show you how you can easily enable unit testing on your database. If it takes like a days and days to learn and enable, then it's too hard, right? Yeah, it gets harder. Right. So the techniques and technology solution that I'm going to show you today, probably you can enable it within an hour. Awesome. All right. Let's jump in. Okay. Okay. Before we go, I will briefly introduce you the open-source DB unit test frameworks. It's new. It's my pleasure to introduce Slacker and SlackerRunner. T-SQL T is another open-source unit test, database unit test framework, which is more popular. I will first tell you what is kind of the principle for me when I look for a database unit test and the framework. One thing is that, as I mentioned, it should be very easy to set up and run, right? And because it has, it's dealing with the data, I always look at if it is flexible to mock or generate test data, okay? And of course, it has to be easy to develop, like assert logics and test logics in the test framework, and easy to debug test when there is an issue or code that the test reports adder. So we can see the detail adders coming from the test cases, and then we can go and fix our code easily. Those are kind of debugging matter. And lastly, we are talking about, in terms of DevOps and CI-CD world, right? So we should be seamlessly integrated to the CI-CD pipeline that you're using. It can be Visual Studio Team Services, it can be other open-source like Octopus Deploy, JetBrains Team CD and those kind of stuff. So those are my category. And these two framework that I listed here, Slacker and SlackerRunner, there are others as well, but the key thing that I can tell you is that these two frameworks meet these criteria, right? And Slacker is developed by a very large software development consulting firm, software development, you know, a firm, and the developers actually adopted SSDT database project-based DevOps pipeline in their core software development business for the company. And thankfully, they made it as an open-source. That's awesome, I remember you mentioned it and it's awesome to see it as an open-source. So I guess one of the big takeaways here, these two frameworks are not Microsoft frameworks, right? They are not, yeah. We are showing two things that are built by the community and they're not the only two things in there, they're just two that met your criteria, the USETS sort of baseline of being very productive for the kind of tests you want to provide. Right, right. Cool. Okay, so, oh, sorry, I meant to, I told you that I only have two slides, so I went to the last slide. We'll get to the resources. All right, so, okay, so let's start our demo. Ideally, whenever you have schema changes in your database, then in ideal perspective, you should have a unit test or more unit tests associated with the change. It's a really tough task. Yeah, yeah, schema change is a lot, but then especially at the beginning of the project, right? Right. But if you use a SSDT database project, then you can save a ton of time because SSDT compiles database project and reports errors for all reference and integrity, like your storage area is referencing a table, a table has a foreign key relationship, all that. Yeah, your build is already a very rich validator for the foundational things. Yeah, those dependence issues are all detected by SSDT at the compile time and reports it. So, if there's any error, then you cannot actually deploy anything to your database, right? So, it's a compile time validation is one of the key feature of SSDT. Then you can rely on SSDT to handle that part of the changes on your database and then now you can focus on business logic in your store procedures, functions, triggers. That is basically the main, the subject of a unit testing. So, you can kind of make your job more efficient in that case. Yeah, it's funny, like when you're just talking about, let's say, C-sharp code, you don't even think about it anymore. The build just did what the build did for the longest time for database developers. We've all had many years of just not being there. So, it's good to point out, SSDT gives it a baseline and these frameworks lets you go off to the logic. Exactly. So, let's quickly show you what happens. So, for the first part of the SSDT validation, I just opened up the person.sql, which is the table source code for person-to-person table. One day I feel like I want to rename that table to person-to-persons, the s in it, right? Yeah. So, if I do that... Famous rename challenge we have as programmers. Right. Then, as soon as I change, if you notice at the bottom, it's already done. So, SSDT, analyze your code immediately and then reports error. Oh, there's more than 102 errors and all that. Right, because you didn't use any refactoring tool here, you just made one change to one SQL file. Right. That just doesn't work when you have stuff referencing that. So, I'm just demonstrating one sample, but any changes like that violate those reference integrity or dependency relationship within your schema, that will be detected and then tell you so that you can always make sure that your code change is valid. Of course, the correct way of changing it is instead of changing it directly, you can use a refactor rename functionality. Then, SSDT handles it. But it was just a demonstration purpose, so I just directly modified it under code. Yeah. Okay, good. So, let's jump into today's demo. So, in starting episode two, I started using my demo application, which is a bunch of words directory services that has like employee phone books and the org chart, the functionality, that just renders the chart. Yeah, we did some masking of the data. That is right. So, it's kind of the version two of that scenario. So, I will just click the band miller and we show all the detail of the band miller. But this one is a public application. And we are showing all the detail of employee to the public. So, what we want to do is that we want to mask this information. Now, it is the version two part if it is used by public application user. If internal user or internal app is using the same data, then we don't want to mask it. We want to show email phone number, all that information without masking. So, that's our goal, right? So, let me go back to our solution. What I did is that I added these two users. So, internal apps and public apps, as I mentioned, it has different permission sets. It has a different usage in terms of how we show the masked data without with mapping, even though the database has the masking definition enabled. So, let's do the same thing that we did in the episode two. That means go to email address the SQL, which is an email address, the table. And I will, okay. I'm running my demo on the virtual machine, so it can be a little bit slow. It takes a few seconds. All right. So, I changed my code to enable masked with close in my code. And let's build. Okay, it will come out all successful in a second. Okay, that's great. And let's publish it to our local development environment. Let's do run the unit test. Let's do it. So, I already made the publish profile. What it does is that basically defines the connection string to my local machine and deploy my database project to the target unit test database and to be able to run the unit test. One thing that I added in the publish profile is SQL command variables. So, what it does is that it can control the conditional deployment. If I define deployment configuration variable value as unit test, then it deploys unit test related logins and users to be able to run the unit test from the test module. We need to have a connection to SQL server. But the login and the user for the unit test, we don't want that in production or test environment. It should just stay in the unit test environment. So, that's why we can have the conditional deployment and create user, login and user only for that unit test database. This is what makes it all work, right? Giving you the flexibility to deploy when you need this parameter in a particular environment. That is correct. So, I'm clicking the publish button. Within a second, it'll be done. Okay, it's creating, analyzing the project and building the back pack output. It's done. So, I'm gonna go through how we enable the test. Before that, let's run the test first and then talk about that. Sure. Okay. So, we go to test menu, run all tests. It opens up the test explorer. It runs. Okay, there is a failure. This is expected failure. Let's click it and see what has failed in our test. So, make it a little bit bigger. Okay. So, we had the two unit tests and both failed. One is that when we are running our code, the code which is basically coring the email address as a public user, public apps user, then we were expecting master value. The result, as you see here, result was correct, but with the code change, we didn't update our test case. That was the issue. So, it's a test of the code issue. And the second issue is about running the same test as an internal app user. We defined that for internal app user, we don't want to mask our data. We want to show the data without masking. Yeah, we show everything to internal and public people, we want the masking. So, the unit tests need to reflect that. Right. But what you see here is that, so we were expecting the data without masking, but the returned data was masked. So, the test is correct. We just detected the code issue. For our change, we didn't update necessary change to make that happen. So, let me close it. Little bit. So, let's fix those. First one is that, let's first fix the app issue. So, we defined internal apps user here. And one thing that we didn't do was that, let me copy and paste. We didn't give internal apps user unmask permission. Unmask permission basically tells SQL server not to mask, even though the column has a mask with definition. So, it's an override at the user level. So, this user will just not see mouse data in the matter where it's defined. So, now we gave the correct permission to internal app user. One of the impact that we can think of is that, without unit testing, this type of issue could have slipped through all our DevOps pipeline and it was possible that customer detects this issue. Now, let's talk about that impact. So, this AdventureWorks database is serving my demo application, but it is also serving other applications like CRMs and then your communication application within your company. So, sales team as a customer, they rely on email address and the phone number. People would use it in many reasons. Right, they make a sales activities, do the sales activities. Now, it's all stopped because all the email and phone number are masked and as a sales person, now you cannot make any communication. Your phone starts ringing at that point as the developer. It can be a huge loss in your potential sales, right? Yeah. Well, that is a real monetary value impact for this type of change. And let's look at then the sales team contact the support line, right? And support line, support team needs to investigate where is the root cause of this issue. If they can get developers email and phone number, they can contact your development team, right? Yeah. And then when the DAV team gets this fixed request, now time's ticking, potential loss in business and lots of pressure, now you have to fix it. Let's assume that you fixed it like what we did. Yeah. How can you guarantee that your fix is not causing any more additional bug? Yeah. Anytime you change anything related to security, you're asking, you get a little bit nervous as a developer so you won't test on it if you can. Exactly. So, will you have a unit test on your database or not? Right. Right? So, I could have a unit test on my database so I could detect this one all the way. So, it matters when you detect the same issue in your pipeline. If you detect it all the way during your development time, then it is a much less priority than the customer detects the issue. The cause goes up exponentially. The closer you get to your customer, the more expensive it gets to fix the issue. Right. So, we fixed the first one. The second one we're gonna fix is that it's a test issue. The test is expecting mass to value and now I'm going to show you how the test is structured. And which test framework are we using in this particular case? It's a slacker. The slacker. Yes. So, if you take a look at my solution, there are two projects. One is a database unit test and the test specs. I will look at the test spec project first. This one is a slacker project. Is it just a regular C-Sharp assembly project? What kind of project type is it that's, what is it trying to build here? Because there's no code in it, right? There's no C-Sharp code, just SQL code, but VS is rendering it as a C-Sharp. That's a good question. So, slacker is written in Ruby in the R spec. To be able to put it in our solution, we just need to have a placeholder. So, I used the C-Sharp empty project as a placeholder and I marked it as a do not build and it's just they will hold that in a structure. That's basically what it is. Okay. Okay, and then I added slacker project structure in that the C-Sharp empty project. That is basically what I did. So, you're basically impacting the file system more than the C-Sharp. Yeah, that is right. That is right. In that case, I can check in all this as part of my solution and then it travels along with the rest of my database and application code and then they can go through a CIC pipeline altogether. That is basically the region that I did it. Right. Okay. So, as I mentioned, the slacker is written in the Ruby. In terms of a learning curve, I mentioned that it should be very easy to start and use it, right? And I never used the Ruby language before I tested the slacker and the building this demo and stuff. But I could learn Ruby for slacker usage within a day and I could start producing my own test specs in there. In the Ruby R-spec term, the things that we call as test class and test case is called the tested spec, okay? And then in the spec folder, each of those R-spec Ruby files contains that. So, in my application, I use get a direct manager and get direct reports, store procedures. I created a unit test for each of those. It is very simple. So, each spec can contain multiple test methods in the Ruby term, R-spec term, it's examples, okay? And I can have a single or multiple. It's very simple, I will highlight it. So, each method or example runs when you run the spec during the run all test type of action, we'll go through each spec and then they run methods. So, what I did- Is it just running kind of vertically the first one, second one? That is what the sequential is just run those. So, the key thing is that as I mentioned, I need to be able to mark or generate the unit test data. You're not gonna have to test unit test cases against your production size of database four terabytes of database. So, usually what you need is that for the unit testing, you can have a lookup tables and reference data and for user data, you can just start with empty. And for your logic testing, you should be able to quickly generate mock data for your unit testing, right? So, that is one. So, I have created a function because I'm going to mock, add the mock data repeatedly across my test cases. I just made it as a function. I will go to that one. Yeah, it makes sense. You may make a generic across all the tests and then the test input in the expect below is just the individual test you're actually doing. Right. So, I insert those mock data and then I call expect to function. What it does is it calls this file, ccall.adventureworksexec, human resources get direct report. It's not something special. It's just a SQL statements. It's a file that has your SQL statements that is basically calling your supercedure with variable ID. And then once it is executed, then it matches with your expected value in CSV file. If it matches, then your test fails. If it doesn't match, then the test fails. So, all these utility functions like a matching, being able to load the values, the CSV values into your table and then being able to generate even those CSV data during the run time so that you can provide the key values but the rest of the value that you don't really care about your testing, you can, the slacker can generate those data automatically for you. So, it's very powerful framework in my perspective. Yeah. It gives you all the foundational things. You need to be able to do these sort of tests. Right. So, let's quickly look at the SQL file. So, it's a simple, it's a SQL statement. Only difference is this passing the parameter value. That's it. Rest of them is the simple. So, this is my main logic for application to generate org chart structure, hierarchical structure and it runs. Then the last piece that I want to show you is the data part. So, I have a seed data and the expected result data. I will make it bigger. You can separate out the mock data part from your test logic. So, you can manage your mock data separately from the test logic development. So, what I have is all the CSV files. These are, this contains like one or two rows that I really need to unit test my store procedure and functions. Yes. The minimal viable amount of data. Right. And it's very flexible to define it in the CSV and then they use it. And the expected result is, it is a values that you want to compare, match after you run your statistical statements and the result should match with this one. So, you can define all those in CSV file and you can also source control this together. So, that's a value of having this one. Okay. So, let's get back to our main business of fixing the code. So, it was defined in XKit as user spec. And as we mentioned, what we are expecting is that instead of expecting the email in clear data format, we want to have it masked. Right. So, this is correct. When I just added it for demo purpose. So, I can fix my test. So, this is correct. The broken test is now the expected test. Right. So, for public user, it should expect the masked value. For internal apps, it shouldn't be masked. Yeah. It should be the real value. Right. So, that's the change. And let me run the test one more time. And run all this. This is, you know, the kind of like, the user is a great example because the user could just be right in your machine with the configuration. The granted permissions could just be right in your local dev box. Right. It's something that I can easily see myself overlooking when you move environment. So, having this in multiple environments, especially when you get to the cleaner like UAT where you have hopefully a lot less sort of chaos going on. Right. And you can run this, make sure things are still good. Oh, we forgot the one step. So, we made the code fix in internal apps, right? Mm-hmm. And... Oh, redeploy, of course. Yeah. We need to build it. I was thinking we should do that. And then... Why didn't you tell me? You get me too excited about the other things we're looking at. Okay. It should be done by now. Okay. It's done. Yeah. So, let's publish one more time. Oh, let's use it. But our unit tests all proved useful, right? That's right. It is still giving us. Yeah. It is testing our testing. All right. Okay. Great. So, in a few seconds, it will be done. Okay. It's done. So, let me run the unit test one more time. All right. It's passed. Awesome. So, now we are ready to check in. All right. Let's check in. And let me name it as a channel nine demo. Changes. And I'll commit. Okay. It's committed. And here down below, I'll show you. Oh, we'll push it to our repository. Here. Push. Okay. It'll be done. Great. So, it's checked in. So, let's go and check our builds. So, now we see two builds kicked off because our change that we just made. The first one is what we already had in the episode two. The second one is called Tools Unit Test CI Dev, which is the new build definition that I added. So, let's go and check it out. If you see, there is a database unit test step added in our build definition to have a gated build. While it's running, let's take a look at the history of our builds. Okay. So, if you take a look at the, out of the history there, it was a failed one. Okay. If I click then. The good thing is that Visual Studio Team Services with the Slacker Runner integration, it shows all the test results in the same build dashboard. So, whenever there's a build or failure, you can always go and check what has failed. Clicking the reports will show you exactly what has failed. So, you have a full history of your CI build. If any test fails, then your entire build instance will fail. That means you're not going to have a build out. Then it doesn't, the CI-CD pipeline stops at that level so we can gate it. Yeah, and it's the same amount of information you get in Visual Studio in the Test Explorer, right? So, you have the full fidelity of the info being available. That is correct. Yeah, very cool. Okay, let's go back to our build. It's all done. So, let me quickly show you what I have changed in the build definition. So, it's all the same except the database unit test. Yeah, and when we say the same, we mean like to the previous one. Oh, from the episode two. So, if you want to kind of correctly follow up, you know, I suggest watch the episode one, two, and the three. If you have already done that, then it'll be easy to just follow up. I can't help but think of Star Wars every time we talk about episodes one and two. The marketing has worked, I'm converted, but yes, we're not talking about anything made by Disney here. Okay. Okay, thank you for the clarification. Yes. So, I made a little bit of a change in the build task. Instead of just the building it, I also added publish. Okay, I will make it big. What I want to do is that as a part of CI build process, after build is successful, then I want to publish it to a unit test environment, which right now is the same environment as a build agent. Sure. So, you're publishing your T-SQL for all the changes that they've built successfully. So, the first part is the build, right? That's the basic validation we're talking about. And now, that's the second part. In order to run the test, we need to have something to run against with some data, with some structure to it. Yes. Yes. And then one more parameter that, property that I added is a SQL published profile path. In the Visual Studio, we added the published profile for unit test use. And we just defined it here, because we are defining the server instance as local host, where it goes, it runs on the same box. So, in this case, we have installed the SQL server on the build agent, and it runs there. Even though it is an enterprise, my production is running on Azure or enterprise edition of a SQL server instance. On the build agent, you can use Express or developer. As a part of SQL Server 2016 SP1, the great feature expansion was we expanded all the program mobility surface area. So, all those, most of those enterprise edition level of feature is also available in the Express and all the way up. Yeah, that's the change that folks might have missed potentially, right? By the way, we're excited. It was one of my fun projects, so. Right. So, on the build agent, you don't have to run the enterprise edition to test enterprise version of a SQL instance. Okay, so you have flexibility. On the test side, I didn't change anything. What I did was just a click add test, and there's a test category. And if you go all the way down, there's a Visual Studio test. Because our solution has a slacker test, and then the slacker runner, I'm going to show you in a second. Visual Studio test automatically picks up our test project and run it as a part of this task definition. Okay, so you just add and you didn't customize it besides the name? No. Okay. So, that's basically what happened. Okay. And then, for that matter, I will quickly show you our project one more time, and then database unit test project. It is the same as Visual Studio C-Sharp test project. And after you create, let me show you, you go to add new project, and it's the same as this one. Okay? Unit test project. So, just the default unit test project. And then I see over there in the references, over in solution explorer, I mean. Right. You added a slacker runner. Right. So, it is a NuGet package. You can easily install this NuGet package from NuGet installer. Slacker runner and X unit. These are what you can get from NuGet package. I have a setup guide that I published in my GitHub public project. So, I have a reference to do that. And you can just follow the step by step, and you can have the same thing. But there is no additional coding that you need. You just add a NuGet package. And the test case, which is a Visual Studio test framework, test the case. What you can do is this is like a template. So, you can copy paste the same code, and then I put it in the test-spec.cs. And it will pick up all the specs that you have defined in Slacker project as a specs, and then just run it in sequential. So, you're making it very generic, and this just enables the magic glue to get everything working together. For people who have been using the Visual Studio test project, test the framework a long time, you can do all other magics, right? Instead of running all, you can run it individually, and then you can do, but you can apply the same technique here, just for the demo, I just made it really simple, and the template level to pick up all the specs and run it. All right, so let's check our application. So, now our change is again deployed to our unit test environment, the usual acceptance test environment, so that now we can do the functional testing on that one and the usual acceptance test. Cool, that's very cool. Okay, so that was the demo that I wanted to show you. So, the key thing is that using the unit test, you could detect test the code and your real code issue in the database, so that before it slips through the, to the all the way to the production, and then they're getting the customer reported issue with the heavy pressure to fix it and validate it. So, there is a value that you can apply the database unit test in the perspective. I will go to the references. I have listed all the references about the technologies that we used and then the tutorials that we went through. So, for Slacker and Slacker Runner, you can go to GitHub Project, both are open source, and then you can learn from here. And for, if you're interested in T-Tecility, T-Tecility has its own homepage, and it's open source, but it also have a lot of community contributions. So, it's been a lot longer from what I can think of. It's a longer than the Slacker. And for episode one, our SSDD DevOps episode one and episode two, there is a link. And if you want to try the same project, there are a bunch of works of directory service and they run the same Slacker test on your own environment. And I made it two projects in my GitHub account, so you can go to these two GitHub projects and clone it and simply run it and see what's in there. Awesome. So, if you have any question, then you can send me an email to EricKangatMicrosoft.com or you can follow us, it's called Data Tools on Twitter. So, that's basically what we offer. Awesome. Well, I hope folks out there enjoyed this episode. I think we covered a lot. I think the test frameworks are really cool to see the community investing into the SQL space for us and us having such great integration. I mean, I'm sure it would have taken me some time to learn, but it doesn't seem like it's way more than something you can learn. It's a days of work and then hopefully your project benefits for years to come. Right. So, if you're a developer, as a developer, as a PM, I could learn it in one day, right? As a developer. A developer could be maybe 10 minutes. Yeah, maybe 10 minutes. All right, Eric, well, thank you so much and I'm sure we'll have you on again for other topics, but thank you for being on. It was great to have you. Thank you, Dimitri. And thank you, folks, for watching. We'll be back next time on Digital City Toolbox. Have a good one.