Automated Tests for Database Procedures

Why not future-proof your database procedures just as the middle tier and front-end developers have been future proofing theirs? Not only can automated unit tests be built for each procedure but Test Driven Development (the practice of writing a simple unit test that fails and forces you to implement some simple procedure to satisfy the failing test, and once the test passes you enhance the test or add a new unit test to grow the procedure further) can be done as well.

Why Unit Test Procedures?

Every time there's a change in a procedure or schema, unexpected errors can happen. To mitigate disaster, you'll have been doing some kind of testing: manual testing, automated testing, ... 

An early adopter's mistake is to test every possible case you can imagine. For unit testing, this isn't your test target. You're goal is to have every line of procedure be necessary to pass your test procedures. Although this means that some lines of code are tested as a side affect, and that's OK.  When constructing your unit test, focus on testing only the one procedure you're targeting.

Confidence

Without unit tests whose job description is to test each stored procedure, then you're gambling that the testing in other tiers will uncover problems. Why live with this uncertainty? If you have a test suite that confirms every line of procedure works as expected, you'll have supreme confidence that your next release is truly an improvement (rather than new features with new bugs). 

Fast feedback

Since we're writing unit tests that test each procedure in isolation from other procedures (or at least as isolated as possible), then the tests will execute quickly. Likely you can test 100 procedures in a minute which scales to thousands of procedures in thirty minutes. In fact, if you run these tests continuously (triggered by code checkins) or whenever the you feel some uncertainty, you can execute the test suite to ask the question, "do I have a regression?" and get back the answer in less than a minute.

The tests "tell you" the regression's location

With individual unit test (test procedures) designed to test a stored procedure in isolation, you'll have signaling that shows what procedures are fine and which are in failure. This means once there is a regression it the tests will indicate approximately what procedure or procedures to examine.

Tools for Unit Testing Database Procedures

Here are a few that either I've personally used or I read through the documentation and meets my minimum feature list (asserts, pre test execution routines, post test execution routines, outputs a report, allows easy execution of entire test suite).

Oracle

PLUto

utPLSQL

(PLUnit looked promising but isn't maintained any longer, which makes it unusable since it's closed source.)

Oracle SQL Developer has unit testing tools built in (view->unit tests).  It's a bit complicated.  The learning curve to use this tool is higher than PLUnit.  Here are some links about this tool:

http://www.thatjeffsmith.com/archive/2014/04/unit-testing-your-plsql-with-oracle-sql-developer/

http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/sqldev/r30/sqldev_unit_test/sqldev_unit_test_otn.htm

PLUTo is a minimalist setup.  utPLSQL has a rich API.  The Oracle one is hard to get into because Oracle's tech writing is so dull and uninspiring.  And the last time I tried to use it, the tool was disabled for some reason (license issues?).  My favorite is utPLSQL.  It's made popular mention on Steven Feuerstein's (of Oracle) blog as well.  You can install it in minutes and start with the examples.

T-SQL

T.S.T. Sql is a nice simple tool with some getting started videos and an InfoQ article.

Sql Server Database Test (SSDT).

Common Test Environment Configurations

To execute automated tests for a DB means you need a DB instance to put the procedures inside of and data for the procedures to execute.  As for data/schema, this should be created (inserted) as part of the automated test.  To answer the question of how many DB instances, there is a range to this answer:  You need enough. :-)

Usually you need:

• 1 instance for continuous integration, since you're going to want to execute your automated tests continuously.
• At least one instance for someone to develop automated tests.  Typically, every developer creates an instance on their own system whenever they want to execute, debug, or create automated tests.  If for some number of reasons you can't have an instance for each developer, then you'll need to manage them as a pool.

Test Design

The above tools add some supporting procedures so you can focus on creating automated tests in the form of test procedures.  The job of unit tests is to confirm that the code under tests operates as the developer intended it.
Good automated unit tests have the following characteristics:

• each test executes in microseconds
• independent of execution of other tests (said another way, you should be able to execute the tests in any order you wish)
• prefer having many simple test procedures to test a DB procedure (which are easy to understand and maintain) rather than a few test procedures that check many thing.
• use test data which is as simple as possible--just enough to make the procedure under test happy.
• test data should be created either by the test itself
• test procedure names should express: what procedure they are testing and the test scenario
• each of DB procedure under test should be tested in isolation (without dependency on other procedures)

System level tests are supposed to answer the question, is feature XYZ working as the user expects it?  These tests will work with large data sets and developing system level tests:

• each test executes seconds, minutes, or longer
• independent of other tests
• destructive tests should undo their "inserts" so as not to affect other tests (could be implemented by either refreshing the data afterwards or not committing the transaction).
• prefer creating tons of non-destructive tests and few destructive tests so you don't need to often refresh the data which is slow
• keep destructive tests which require data refresh in a separate test suit so you have a "fast" suite for continuous builds and your "slow" suite for hourly or nightly builds.

Test Data Creation

Ways that test data is created are: 

• restoring to the database a set of test data.
• sql script that inserts the data.
• sql code that creates test data that is used by your test procedures

Your DB instance creation along with schema should be automated as well so that any developer on the team can easily create a DB test environment and so your continuous integration system can recreate the test environment each time it executes.

Other Links

Best practices of testing stored procedures.