Am I Unit Testing or Integration Testing my Stored Procedures?

Question

I have had many occasions recently where I have needed to maintain complex stored procedures and functions. These were broken already, usually in fairly subtle ways - there were very few occasions where you would call the SP with valid parameters and it just plain didn't work.

My solution was to evolve a framwork that executes the stored procedures inside a transaction, after initializing the database to the initial conditions I needed, then testing for the expected result, also within the same transaction. The transaction was rolled back at the end of the test.

This has worked very well. But some would call this "integration testing" since it involves integrating with the database. I call it unit testing, since I tested individual components and individual test cases for those components, and since I completely controlled the initial state of the database.

But where should the line be drawn? Is this integration testing or unit testing? Is there a practical reason why this sort of test is a bad idea? If this is "only" integration testing, does anyone have suggestions on how to do actual "unit tests" on these stored procedures?

---

Update, 3 1/2 years later. On my current project, I have begun using SSDT unit tests, with success, though they could be better. See Verifying Database Code by Using SQL Server Unit Tests. These typically deploy the database project to your instance of SQL Server LocalDB, so this removes any question about the database environment affecting the test. I populate the database with the required data during the Pre-Test, which removes questions about the database contents. In fact, I use MERGE statements to do this, ensuring that any data I don't need for the current test is removed, inserted or updated from the database before the test. They do have issues:

• They are not fast
• It is not possible to reuse test conditions
• It is not possible to reuse pre-tests (unless you make them common to all tests in a project)
• The user interface could be improved

One of the reasons for the above issues is that I have not yet complained about them. I recommend that anyone interested should try this feature, and then complain about it. That's how improvements are made.

Answer 1

The point of unit testing isn't to make a magic unit test fairy come and validate your opinion. It's to test the smallest, simplest building blocks of your system, so you're not testing some more extensive functionality and tripped up because something didn't work the way you thought it did. So, can you break this down further? I don't think you can, in which case:

(a) It sounds like a unit test to me, and (b) It doesn't matter whether it's a unit test or not, because it tests what you need testing, which is the point of using unit tests in the first place!

If you feel the procedures are too complex, you may well want to break them themselves into smaller parts (in database procedures or in code), but to do that you would obviously like to have these tests in place first!

Answer 2

Unit testing by definition revolves around testing a "unit" of code in it's running environment or platform. The platform your using is a database so you have to use it, it's not integrative.

A better question is why do you care. As long as it's automated and adds value do care that your test in a unit test, an acceptance test, a smoke test or a functional test?

At my work we currently leverage the unit testing framework to do Acceptance Test Driven Development (ATDD). Some tests are integrative, some are not. Almost none of them are unit tests, but we don't care as long as:

• the test adds value
• the test doesn't have false failures or successes (is mostly deterministic)
• the test is automated

Update

It's all a question of cost and benefit. The more moving parts you have the higher the risk of having a non-deterministic test. Classic unit tests have the least amount of moving parts, and therefore the least chance of being non-deterministic. The tradeoff is you aren't testing the integration points (code, database, fs, network, etc). All of these are useful to have covered in a test, as long as the risk of false failure or success is low enough.

Tests provide value in what they do not what they are. The key is how often you are going to have false failures and success. If I have false failures for 2 hours every month because that is maintenance window for the network, then the value of the tests are apparent. When they fail it's obvious there is something wrong with the networked resource, not a particular test. And now you have more test coverage precisely because you are testing those integration points.

Answer 3

I think it depends where the data comes from. If you're creating your test conditions in the test prefix I think it's reasonable to call it a unit test. Otherwise you're into nit-picky arguments about what counts as "pre-existing" for a unit test. Just as your database unit tests rely on the existance of database tables and so on, unit tests outside the database rely on things like class definitions and often instances of those clssses. That's not bad, it's realistic.

Answer 4

I would consider that to be integration testing:

1. It's much slower than a unit test should be.
2. It does not exercise a single, atomic unit of code -- it exercises your procedure, the database server, the network stack, etc.
3. It won't be easy to detect where the error occurred if the test fails. You'll have to look at each failure manually to see if it was an external system failure, or your code.

However, tests like you describe are invaluable to have. We recently started adding tests just like that, to test stored procedures that are impossible to test manually in certain environments. I don't know another way to have automated tests for stored procedures.

So, these tests are a great idea, but call them integration tests -- use a test category or name suffix to distinguish them from regular unit tests. This way, you can flag integration test failures differently from unit test failures in your build automation.

Answer 5

Is this integration testing or unit testing?

Where does the test run? On the database or via code-based test harness (JUnit)?

On the database then it is likely a unit test (as it is self contained), JUnit-like test harness then it connects to an external database and as such an integration test.

If this is "only" integration testing

Why the quotes? Integration tests are not some sort of beast you should never do.

does anyone have suggestions on how to do actual "unit tests" on these stored procedures?

I generally find the only reasonable unit for a stored procedure is the calling code¹ and the procedure. So I integration test the complete set in a set of tests which look just like unit tests.

A better question is why do you care [about the name].

As it allows you to split the tests out. Integration tests take longer to run, require additional set up and likely access to a resource shared by many tests (the database for example).

1) The calling code will have additional unit tests if required.

Answer 6

Microtest is a useful term that may help you sidestep the debate about terminology and whether your test is a unit-test or not.

A common definition of unit-test includes that the system under test is a small unit of functionality and you must be able to run all tests against it in memory, without help from file systems, networks, or database engines. If you need an actual database engine to run it, then it is not a unit test.

While this definition works very well for all application layers, it isn't very helpful to the bottomest database access layer. We have to recognize that it is special. If your tests test only small units of storage/retrieval/update/delete functionality, then your tests are clearly as valuable as "strict" unit tests written against the upper layers of your system.