Databases are not very different from code:

• They should always be under version control. This rule is essential for any team of one or more developers.

• Databases are versioned similarly to code.

• Databases require architecture, design and implementation.

• More time is spent maintaining databases than creating them.

• Databases should be tested.

If you have written unit tests to test your application, imagine database tests as the same thing, but for databases: instead of testing the logic of your code, they focus on the database itself and how it handles the data.

Similarly to the application itself which can hold complex business logic with multiple, complex rules, database rules can become particularly complex in large applications. A database may contain:

• Constraints, which have to be tested just like any other code. Check constraints, for example, may become quite difficult over time, but even the simplest constraints such as the maximum length of a field or an unique constraint should be tested as well if the database is holding business-critical data.

• Queries, for example in a form of a view. When views start to do inner joins, groupings, etc., it’s time to test them as well.

• Code, for example in stored procedures. Projects which use stored procedures a lot and have large ones are not unusual. One way is to verify that those stored procedures contain code which is purely related to database, and doesn’t contain business logic: often, they contain business logic which then can be moved outside the database. Sometimes, this is not possible; in that case, this code should at least be thoughtfully tested.

• Features, such as Unicode support. Complex features are tricky, and you may expect the database to behave in one way, while it does the opposite. A basic example from the list below: if one of your applications handles Unicode normalization forms, another one doesn’t, and the database doesn’t either, you may find yourself loading two products with an exact same name, while you expect the names to be unique.

Let’s take an example of a table which stores products. Here are some database tests which may exist:

• Ensure that the name of the product can contain a maximum of 100 characters.

  Tested feature: length constraints.

• Ensure that Unicode names are inserted and selected correctly.

  Tested feature: Unicode support.

• Ensure that the name of the product can contain a maximum of 100 Unicode characters.

  Tested feature: length constraints coupled with Unicode support.

• Ensure that it is impossible to insert two products with the same name.

  Tested feature: unique constraint.

• Ensure that it is impossible to insert two products with the same Unicode name written using different Unicode normalization forms.

  Tested feature: complex case mixing unique constraints and Unicode support.

• Ensure it is impossible to insert a product without a name.

  Tested feature: non-NULL constraint (and eventually a check constraint).

• Ensure that the product cannot be created without being assigned to at least one category.

  Tested feature: check constraint.

• Ensure that the price in RebateProductsView is determined as expected based on a sample product with a specified price and rebate.

  Tested feature: view.

• Ensure that the price in RebateProductsView is still determined correctly when the product price is equal to zero.

  Tested feature: view.

• Ensure that it is possible to store 5.000.000 products.

  Tested feature: scalability.

• Ensure that a given select takes less than 4 ms. in 95% of tested cases on a given machine when the table contains 1.000.000 products.

  Tested feature: performance.

• Ensure that when inserting a product with the price 13.95 while the database default culture is fr-FR where the correct way to write it would be 13,95 produces the expected result.

  Tested feature: encoding.