Category: Blog

I think learning to better test our software, including the database objects, is one of the ways in which we’ll build better software applications in the future. Testing is a complex subject, but this is part of a series that looks at ways in which you can use tSQLt.

Checking Table Metadata

One of the easy tests you can write is to compare the meta data of an object to a known quantity. The easy way to do that in tSQLt is to use the AssertResultSetsHaveSameMetaData function. This function compares the structure of two result sets, covering names, ordering, and data types, to determine if they are the same.

Here’s a quick example of how that’s done.

Let’s assume I have this table:

 create table Articles  (
    [ArticlesID] [int] identity(1,1) not null,
    [AuthorID] [int] null,
    [Title] [char](142) null,
    [Description] [varchar](max) null,
    [Article] [varchar](max) null,
    [PublishDate] [datetime] null,
    [ModifiedDate] [datetime] null,
    [URL] [char](200) null,
    [Comments] [int] null
  );

If I wanted to write a test in tSQLt to check this table for changes or alterations, here’s what I’d do in code:

create procedure Articles.[test Articles_Check_metadata]

as

begin

  –Assemble

create table Articles.Expected

  (

    [ArticlesID] [int] identity(1,1) not null,

    [AuthorID] [int] null,

    [Title] [char](142) null,

    [Description] [varchar](max) null,

    [Article] [varchar](max) null,

    [PublishDate] [datetime] null,

    [ModifiedDate] [datetime] null,

    [URL] [char](200) null,

    [Comments] [int] null

  );

 
  –Act

 
  –Assert

exec tsqlt.AssertResultSetsHaveSameMetaData

  @expectedCommand = N’select * from Articles.Expected’,

  @actualCommand = N’select * from articles’

  ;

end

;

go

Note that I don’t have an ACT section in this test.

The Assemble section is easy. I record the size and shape of my table. This will be what I compare the actual table to in the database.

The Assert section is a call to AssertResultSetsHaveSameMetaData, with a SELECT * from the real table being compared to the same SELECT from the expected result table I created. If these match, I pass the test. If they don’t, the test fails.

Why?

This seems silly, I know. What does it matter if the table changes, and it certainly will need to change. I definitely questioned the value of a test like this when I first saw the example. However when I thought about it, and thought about the places in which I’ve developed databases, this makes some sense.

Imagine that I have 3 or 4 (or more) developers. As we get new requirements, we’ll change the schema over time. Imagine that I actually have views built on this table, and other procedures and functions, all of which have some tests on them. If I change this schema, and run a test suite, I could see multiple failures. If I did that, one would hope I realized that the addition of a column here (or a rename) would cause those issues. However if I changed a couple things before running a test, which is something I might do at times, having this test fail tells me quickly that the schema was altered. If someone else changed the schema, I also quickly see that this change was to the schema.

It’s not a big change, but it does allow me to determine that I need to refactor all the objects (potentially) that depend on this table. I can go do that work now, or add it to the list of tasks for this particular development task, and also fix the tests, which should go quickly.

If the work doesn’t go quickly because I have a lot of objects, then I’m really glad that I learned now this is an issue.

This becomes even more valuable with views and procedures returning result sets. If I add a column, then I may or may not want views to change, but certainly a check of view meta data will tell me if they do.