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

I'm glad you asked about Unit Testing, and not testing in general.

Databases have many features that need to be tested. Some examples:

• Data Types/Size/Character sets (try inserting a swedish name, or long urls or numbers from the real worlds, and see if your column definitions are ok)
• Triggers
• Contraints (foreign keys, uniqueness...)
• Views (check that data is correctly included/excluded/transformed)
• Stored Procedures
• UDFs
• Permissions
• ...

This is useful not only when you change something in your database, but also when you upgrade your dbms, or change something in your settings.

Generally, Integration Testing is done. This means that a Test Suite in a programming language like PHP or Java is created, and the tests issue some queries. But if something fails, or there are some exceptions, it's harder to understand the problem, for 2 reasons:

• The problem could be in your PHP code, or in PHP configuration, or in the network, or...
• The SQL statements are harder to read and modify, if they are embedded in another programming language.

So, in my opinion, for complex databases you need to use a Unit Testing framework which is written in SQL (using stored procedures and tables). You have to choose it carefully, because that kind of tools is not widely used (and thus not widely tested). For example, if you use MySQL I know these tools:

• STK/Unit http://stk.wikidot.com/stk-unit
• utMySQL http://utmysql.sourceforge.net/

Answer 2

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 3

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 (à la RoR) 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).

Answer 4

Take a look on DBTestDriven framework. It works great for us. Download it from GitHub or their website.

Answer 5

As for JVM development, unit tests can benefit from JDBC abstraction: as soon as you know which JDBC data are raised by DB access, these JDBC data can be 'replayed'.

Thus DB access case can be 'reproduced' for testing, without the target DB: no test/data isolation complexity, ease continuous integration.

My framework Acolyte is an helpful framework in this way (including studio GUI tool to 'record' DB result): https://github.com/cchantep/acolyte

Answer 6

A list of items that should be reviewed and considered when staring with database unit testing

• Each tester needs a separate database, in order to avoid interfering with activities of other tester/developer
• To have an easy way of creating a database to be tested (this is related to having a SQL Server database under version control). This is specifically useful when trying to find what went wrong if some tests fail
• Focus on specific areas and creating tests for a single module instead of covering all at once. Adding tests granularly is a good way to be efficient
• Make sure to provide as many details as possible when a test fails, to allow easier debugging
• Use one and the same test data for all tests

If test are implemented using tSQLt framework, the unit testing process could be complicated when dealing with a lot of databases from multiple SQL Server instances. In order to maintain, execute and manage unit tests directly from SQL Server Management Studio, ApexSQL Unit Test can be used as a solution