Unit Testing Stored Procedures


SQLUnit is a regression and unit testing harness for testing database stored procedures. An SQLUnit test suite would be written as an XML file. The SQLUnit harness, which is written in Java, uses the JUnit unit testing framework to convert the XML test specifications to JDBC calls and compare the results generated from the calls with the specified results.

Stored procedures are often used to provide an object abstraction to the underlying relational data store. The object abstractions are consumed by Java middleware programs. In shops where this is the case, Java programmers often use the popular JUnit testing framework to unit test their code. Using SQLUnit to unit test the database stored procedures thus becomes a natural extension of the unit testing process, and provides unit testing coverage for a layer that is often neglected in this respect.

Based on the realization that the stored procedure author need not be the same person who writes the Java code, and may not even know Java or JUnit, SQLUnit provides an XML language to write test suites. Even for those stored procedure authors who can write JUnit tests, the repetitive nature of JDBC programming would, over time, encourage the author to refactor the common parts of the tests into utility classes. SQLUnit already provides this functionality, except it goes one step further and abstracts the test specification out of Java into XML.

The XML itself is fairly small and easy to learn for anybody and should be instantly intuitive to a person who is already familiar with JUnit.

Further, since SQLUnit is written in Java and uses JDBC to communicate with the database, it should be possible, at least in theory, to write unit tests for stored procedures for any database that provides a JDBC driver. In reality, some porting may be needed for each new database, based on proprietary database vendor-specific extensions made over the SQL standard and the subset of the JDBC API implemented by the supplied JDBC driver. However, the porting activity should be relatively painless.

News/Release Notes