What database tests should I write so that refactoring database is easy.

Question

Are there any guidelines on writing database tests so that you can refactor database "without fear" while doing evolutionary database design?

What aspects of database should be put to test while developing it? Any example would be great..

Answer 1

The concept of having tests, according to TDD, is that you test the way things are meant to work now. When you need to change something, you change the tests that address that, (ensure the current codebase fails), and then make the code changes until the tests pass. The tests that didn't change give you confidence that other aspects of your software is still doing what it used to and that your refactoring hasn't broken anything.

So you don't write tests with refactoring in mind; rather, you test your requirements, and then when your requirements change you update the tests accordingly, and refactor the code so it passes the new tests.

Answer 2

Always fear every change you make to a database. This will compel you to double check every thing you change and narrow down the possible errors.

Keep in mind that databases are usually the backbone of one or more application, so every change you make must be planned carefully and properly tested.

Answer 3

Scott Ambler's "Refactoring Databases" is a very good book, analogous to Fowler's "Refactoring". It talks about testing.

Answer 4

I write tests that call my dal code and after I check if the inserts/updates/deletes actually occurred, so called state tests. These are pr definition not Units test but rather integration tests, but they have actually helped me many times doing database changes. Over the time I have more and better tests and even bigger database changes go rather seamlessly.

Answer 5

When writing stored procedures, I liberally add Debug / Print statements. With @Debug as a input parameter of type bit :

IF @Debug = 1 PRINT @MyDynamicVariable