How do you (Unit) Test the database schema?

Question

When there are a number of people working on a project, all of who could alter the database schema, what's the simplest way to unit test / test / verify it? The main suggestion we've had so far is to write tests for each table to verify column names, constraints, etc.

Has anyone else done anything similar / simpler? We're using C# with SQL Server, if that makes any real difference.

Updates:

• The segment of the project we're working on is using SSIS packages to do the bulk of the work so there is very little C# code to write unit tests agains.
• The code for creating tables / stored procedures is spread across SQL files. Because of the build system, we could maintain a separate VS DB project file as well, but I'm not sure how that would help us verify the schema either.

Weathering

Answer 1

One possibly answer is to use Visual Studio for Database developers and keep your schema in source control with the rest of your code. This allows you to see differences and you get a history of who changed what.

Alternatively you could use a tool like SQLCompare to see what has been modified in one database compared to another.

Singlebreasted

Answer 2

Atlas, a language-agnostic database schema as code tool has an extensive framework for testing schemas as well as migrations.

Using this framework you can easily test triggers, stored procedures, views and even data migrations.

(FD: I'm one of the authors of the tool).

Eggshaped

Answer 3

Your (relational) database does two things as far as I'm concerned: 1) Hold data and 2) Hold relations between data.

Holding data is not a behavior so you would not test it

And for ensuring relations just use constraints. Lots of constraints. All over the place.

Endsley

Uhhh, cant help myself from commenting on this one: "Holding data is not a behavior so you would not test it" <= If you find value in testing it, you should test it no matter what theory says. "And for ensuring relations just use constraints. Lots of constraints. All over the place." <= For big tables/databases, the real world often prohibits an extreme use of constraints as performance may take a hit. Again - having code testing this may be the only real option in the real world. Just my 5 cents. – Elevenses

10 years later eh? So I absolutely agree that if there is value in testing things you should test them. But the "Unit" in "unit test" is defined in terms of behavior - namely a single piece of business logic that if it were to change would change atomically. Tests of non-behavior can be useful, they're just not unit tests. In fact the question seems to be not about unit testing at all but about having approvals over any schema changes. In retrospect, something like outputting the DDL to text and running ApprovalTests on it might have been a good idea. – Endsley

I acknowledge your comment is from 09. But I've been researching evolutionary database design and testing the schema is actually common. In my case changing the schema is a behavior of the migration script and I want to have a check that makes sure the migration completed. Allowing us to rapidly transform the schema and test it at the same time. – Hostility

Answer 4

That is an interesting question! There are lots of tools out there for testing stored procedures but not for testing the database schema.

Don't you find that the unit tests written for code generally find any problems with the database schema?

One approach I have used is to write stored procedures to copy test data from the developer's schema to a test schema. This is pretty rough and ready as the stored procedures generally crash when they come across any differences between the schemas but it does alert you to any changes you haven't been told about.

And nominate someone to be the DBA who monitors changes to the schema?

Barozzi

Answer 5

I've had to do this type of thing before, although not in C#. To begin with, I built a schema migration tool, based on the discussion at Ode to Code (page 1 of 5) (there are also existing tools to do similar things). Importantly, the migration tool I built allowed you to specify the database you were applying the changes to and what version you wanted to apply. Then, following a test first methodology, whenever I needed to make a schema change I would write a test script which would create a test database, apply version changes to the one before my target change script, add some data, apply the change script under test, and confirm that the data was in an expected state.

My main goal with this was to confirm that no data was lost or corrupted during schema migrations, not to check specifically that the schema was in a particular state. A good awareness of your production data set is required, so you can write representative sample data for the tests.

It's debatable if this should be considered unit testing or integration testing. I would tend to consider it integration testing, based on the fact that I don't want to run old tests every time I iterate my code. Whatever you want to call it, I found it to be a useful tool for that situation.

Hypercorrection

Answer 6

This is an old question but it appears that people are still landing here. So the best tool I have found so far is "SQL Test" by Red Gate. It allows you to create scripts that run as transactions. Allowing you to run "sandboxed" queries for checking the state of the database.

Hostility

Answer 7

This does not really fit the unit test paradigm. I would suggest version controlling the schema and limiting write access to a single qualified team member such as the DBA or team lead, who can validate any requested changes against the entire application. Schema changes should not be done haphazardly.

Downstroke

Answer 8

Don't you find that the unit tests written for code generally find any problems with the database schema?

This assumes, of course, that your tests test everything.

Pintsize