How to do database unit testing?

Question

I have heard that when developing application which uses a database you should do database unit testing. What are the best practices in database unit testing? What are the primary concerns when doing db unit testing and how to do it "right"?

Answer 1

Take a look at this link. It goes over some of the basics for creating unit testing stored procs in SQL Server as well as the different types of unit tests and when you should use them. I'm not sure what DBMS you are using but obviously this article is geared towards SQL Server.

Stolen from the article:

Feature Tests

The first and likely most prevalent class of database unit test is a feature test. In my mind, feature tests test the core features—or APIs, if you will—of your database from the database consumer's perspective. Testing a database's programmability objects is the mainline scenario here. So, testing all the stored procedures, functions, and triggers inside your database constitute feature tests in my mind. To test a stored procedure, you would execute the stored procedure and verify that either the expected results were returned or the appropriate behavior occurred. However, you can test more than just these types of objects. You can imagine wanting to ensure that a view, for example, return the appropriate calculation from a computed column. As you can see, the possibilities in this realm are large.

Schema Tests

One of the most critical aspects of a database is its schema, and testing to ensure that it behaves as expected is another important class of database unit tests. Here, you will often want to ensure that a view returns the expected set of columns of the appropriate data type in the appropriate order. You might want to ensure that your database does, in fact, contain the 1,000 tables that you expect.

Security Tests

In today's day and age, the security of the data that is stored within the database is critical. Thus, another important class of database unit tests are those that test the database security. Here, you will want to ensure that particular users exist in your database and that they are assigned the appropriate permissions. You will often want to create negative tests that attempt to retrieve data from restricted tables or views and ensure that the access is appropriately denied.

Stock-Data Tests

Many databases contain stock data, or seed data. This data changes infrequently and is often used as lookup data for applications or end users. ZIP codes and their associated cities and states are great examples of this kind of data. Therefore, it is useful to create tests to ensure that your stock data does, in fact, exist in your database.

Answer 2

I use junit/nunit/etc and code up database unit tests with java or c#. These can then run on an integration server perhaps using a separate schema to the test database.

The latest oracle sql developer comes with a built in unit testing framework. I had a look into this but would NOT use it. It uses a GUI to create and run tests and stores all the tests in the database so not so easy to put test cases under version control. There are probably other testing frameworks out there I imagine they might be specific to your database.

Good practices are similar to regular unit tests:

• put the tests under source control
• make tests that run fast - don't test too much at once
• make your tests reproducible

Answer 3

I do database unit-testing in the sense that I want to exercise my data access objects to make sure that they're doing what I want them to. I use DBUnit to set up the data for the tests, so that each test has a well-defined set of data it starts with, and each test has a set of data for the ending state that it can compare against the actual content of the database.

The biggest problem I see is that people try to run their tests in an environment that is being used for other things, so that they don't feel free to manipulate the data to support their tests, the data there is always changing, and the tests break too often. The best practice is to have a database reserved entirely for running tests, if you can manage it (meaning you have something like Hibernate generating your SQL so you can target different databases) using an in-memory database like H2 is ideal.

Answer 4

What are the best practices in database unit testing?

The DbUnit framework (a testing framework allowing to put a database in a know state and to perform assertion against its content) has a page listing database testing best practices that, to my experience, are true.

What are the primary concerns when doing db unit testing

• Creating an up to date schema, managing schema changes
• Setting up data (reference data, test data) and maintaining test data
• Keeping tests independent
• Allowing developers to work concurrently
• Speed (tests involving database are typically slower and will make your whole build take more time)

and how to do it "right"?

As hinted, follow known good practices and use dedicated tools/frameworks:

• Prefer in memory database if possible (for speed)
• Use one schema per developer is a must (to allow concurrent work)
• Use a "database migration" tool (a la ruby) to manage schema changes and update a schema to the ultimate version
• Build or use a test harness allowing to put the database in a known state before each test and to perform asserts against the data after the execution (or to run tests inside a transaction that you rollback at the end of the test).