Is Unit Testing your SQL taking TDD Too far?

Question

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?

Answer 1

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.