There is an article out on www.sqlservercentral.com about unit testing your SQL.
The TDD Guy in me said good, we can test the database stuff.
The System Architect in me said, what logic are we testing? There shouldn't be any logic in the database, the only thing you should be doing in the data base is selecting, updating, or inserting.
So if you feel the need to unit test your SQL, are you just being really thorough, being overly pragmatic, or is it a sign of design smell?
I agree with the System Architect, too much business logic is making its way into databases these days.
I think it's a bit of overkill. I suppose you could do it and put the tests in a special category so that they aren't running on every build, maybe just when checked in and run on the server. Typically with all unit tests you want no external dependencies.
Your SQL contains logic. For example, the boolean condition checks in the "WHERE" clause. Can you think of any ways in which the SQL could be wrong? If so, would it make sense to test the SQL, to ensure that these errors are not present?
(For example, some silly programmer, like me, could accidently type "WHILE" instead of "WHERE" in my comment above! ...like I did. But I later corrected it. So where are my stackoverflow tests?!? ;-)
This issue is hottly debated. If you ask a DBA they will think its the best thing in the world to have all your apps use predefined stored procedures. Those days though are coming to an end, with Hibernate and LINQ gaining popularity, you really CAN use the database as a repository of information and have your data access layer process all requests. I think LINQ can do everything for you in MS SQL except full text searches. As for performance difference between SPROCs and LINQ, it is negligable. My vote is no code in the database, all code in yoru data access layer, and have testing for it.
It depends on you database architecture. If you have only tables and views, I think the unit tests are not necesary, because every (or most) bugs will be caught on unit testing in application.
But if you have complex functions, stored procedures, triggers etc..., then you have a lot of places where can be bug and the application unit test does not cover them.
I don't do TDD directly on my database, but there are plenty of opportunities where it is valid to put "logic" into the database. Constraints, default values (yeah, I know it's a constraint, too), triggers, etc. Often these are the best way to implement some business logic AND ensure database consistency. Most of the time I'm able to convince myself of the correctness with some manual testing and leave it at that, but I could see where someone might want to do TDD with this.
EDIT:
For example, I will use a default value on insert and a trigger on update to set the "last updated time" fields on insert/update. In LINQ I'll set the column up as an autogenerated value and make it read-only. This is simpler, to me, than adding a PropertyChanged event handler to make sure that whenever a field on the entity is changed, the last updated time is changed as well. Do I test it? Sure, but manually and after the fact even though I strongly prefer TDD for most things.
As long as the WHERE clause is not empty, it should be tested.
Here we make use of NHibernate Criteria API to query database. Still, we put simple unit tests to safeguard the data access layer. Consider this:
public IList<Book> GetBorrowedBooks(User user);
It may look silly in the first place. But for such a simple situation, we are dealing with at least 3 model objects: Book, User, Borrow, and perhaps Return. Any attempt to modify any of the 3 (or more) classes may break the code.
What's the cost? Writing the tests in this example take less than 20 minutes, I guess. With the help of Category in NUnit, data access unit tests can be configured to run at night while other tests to run on every commit. Slow data access unit tests does not harm, and they are life saver.
I don't know why when we hit the db layer all good practice should go the window. If there is logic in this layer its doubly important. There is a great tool built on top of Fitnesse called dbfit which seems to take all the pain out of unit testing the dblayer. If you are interested you should take a look.
Distinguish between unit tests / specs and integration tests / specs.
If you're classes have both then you a violating a sound principle: Separation of Concerns.
Your tests should be clearly defined between unit tests for testing persistent ignorant POCO / POJO units such as entities and services and integration tests. Which are for testing where your application hits the metal.
Integration tests should test persistence such as repositories and unit of work implementation for your persistence mechanism (RBDMS), Active Directory, Exchange, File System and email etc.
If your use-case necessitates the thorough testing an integration point, which uses a trigger then test the behaviour not the trigger explicitly. In the future you may choose not to use a trigger and use an ORM or AoP interceptor instead.
In most living projects, the database is in some amount of flux between project milestones. Tables and columns are created, dropped, or changed. Lookup tables are updated. And you could be testing against multiple instances of the database, so it's good to have some validation of the state of metadata and data in the database, as part of your regression testing.
There are several cases where I would suggest testing a database:
Tables & views: Verify the tables and views you expect to exist. Verify that these tables and views contain the columns you expect. You can also verify that tables, views, or columns you dropped in this milestone are in fact absent.
Constraints: Try to execute data changes that should be rejected. The constraints should prevent these changes. You can avoid later bugs if you catch cases where the constraints aren't working.
Triggers: Same as for constraints, and also triggers can be used for cascading effects, or to transform values, etc. Test these logic paths.
Stored Procedures: I support the caution against putting too much logic into the database, when the logic is more easily developed, debugged, and maintained in the application layer. But there are cases when there are compelling reasons to use stored procs. Often you see a performance bottleneck solved by putting complex logic into the database. So stored procs are not going away completely, and testing them is a good idea.
Bootstrap data: Lookup tables are an example of data that needs to be present even in an "empty" database. There can be other examples. Test that the database contains the required data.
Queries: You application code is laced with SQL queries. Test them for proper functionality and also for performance. Especially performance -- because the same query can perform well one day and become a bottleneck the next day, as the volume of data changes, indexes grow imbalanced, etc.
ORM classes: Like triggers, ORM classes in your application can contain logic to validate, transform, or monitor database operations. These should be tested.
These tests might not accurately be called "unit testing." Unit testing is a specific type of testing where each test is independent from other tests, and you try to test small units of code in isolation. I'd say testing the database the ways outlined above is an example of functionality testing.
The system architect is correct. There should not be inserting business logic into your database and thus you aren't really unit testing anything.