Voice of the DBA

Tag: tsqlt

  • Testing

    It seems that software always contains bugs. No matter how much time and effort is spent building an application, there will be issues. Sometimes this is because of a lack of testing, and sometimes this is because of poor testing, but in any case, the expectation that we will test our code is becoming more prevalent as we depend more and more on computer software. Users expect our software to work.

    It seems there is never enough time to properly test software after it is complete. Perhaps your deadlines are too tight, perhaps there aren’t enough resources to devote to comprehensive testing processes, but it really doesn’t matter. We will never have enough resources in our QA and testing teams to do as much testing as we would like. We need to expect this and find ways to raise the quality of our work, given those constraints.

    One solution to increasing code coverage and ensuring more testing takes place is to move some of the testing burden into the development process. While this sounds like a bad idea, overburdening developers that already struggle to meet deadlines, I’d note that part of the burden of development is fixing the mistakes they make. Perhaps a bit more testing in the development process will help us release fewer mistakes.

    The idea of regular, repeatable, automated unit testing has become quite commonplace in many software development tools and environments, but it hasn’t caught on in database development. There are some frameworks for testing T-SQL, and I’d encourage you to look at our Stairway for TDD as a way to get started or download tSQLt and give it a try.

    We can fix mistakes after our software is released, or we can fix them before the software is completed, but we’ll be fixing them either way.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.1MB) podcast or subscribe to the feed at iTunes and LibSyn.

  • Expected Variables–T-SQL Testing

    After I wrote about creating tables that contain the results I expected, I wondered if I could do the same thing with variables. I was thinking that I’d like to better self-document my tests, and I’d like to be able to do this:

    declare @TimeElapsed.Expected int = 25;

    It turns out that I can’t. This gives me a syntax error and forces me to do this:

    declare @TimeElapsed_Expected int = 25;

    I can’t even do this:

    declare @[TimeElapsed.Expected] int

    I’m slightly surprised, but not overly so. More sad that I can’t easily set variables that follow the same format.

  • Setting Expectations–T-SQL Testing

    I don’t know who started this pattern, as I’ve seen it in a few places. It could have been Sebastian Meine or Dennis Lloyd. Maybe it was Dave Green, who did the Pluralsight course on tSQLt. Not sure, but I like it.

    If I’m looking to set up a test against a table called Customers in my database, in the Assemble section of the test, I’ll create this table:

    create table Customers.Expected
( CustomerID int
, CustomerName varchar(200)
...
, Status int
)

    This would match the exact structure of the Customers table, or at least have the structure I’m testing. If I’m only testing a part of the table, I might not use all the fields.

    However this lets me easily determine what this table is. It’s my expected result set.

  • Anatomy of a tSQLt test

    I’ve been doing some work on testing database code, and working with the tSQLt framework. I really like the framework more and more, and want to continue to use it. The whole idea of testing is a bit immature in database code, and perhaps software in general, but at least the software people are trying to move forward. At least in places.

    As I’ve been learning, and presenting on this, I’ve started to adopt and follow the simple pattern espoused by the tSQLt framework. It makes sense, and it provides what I think is a good format for unit test. The structure is:

    • Assemble
    • Act
    • Assert

    It’s simple, and it makes it easy to see how a test works.

    Assemble

    Declare variables and create tables you need. Insert data, initialize values and ensure that you have an expected environment on which to base your tests. I think this is especially important for database code tests.

    Act

    This is the place you call your code, or perform some action. Usually easy to do, but there are some thing that might be tough to set up.

    Assert

    This is where you assert, or declare something to be true. A value is equal to another. A result set looks like another. Something that you declare to be true, which could be some condition, like an error has occurred. If it’s true, the test passes. If not, it doesn’t.

    More to Learn

    There is lots more to learn, and I’m trying to do more. However I also need others to not only try to write tests, but evaluate how well they work and also publish some code. Explain to people what has worked, AND what hasn’t. Having that information out there, for a large variety of cases, will help others better test their code.