The Need for Database DevTest

The first and overriding thought of the experienced developer given any new development task is, or should be, ‘How the heck am I going to test this?’. I wonder, though, how often developers neglect to ask this question. Nowadays, with Visual Studio, the debugging facilities are so good you can muddle along happily for a long time, fixing problems on the fly. However, eventually the skies will darken around your development project, or your team members ostracize you, as they struggle to understand and test the code you’ve produced.

In fact, the real question to ask is subtly different: ‘How the heck am I going to test this in such a way that it will guide me to find out what is wrong’? In other words, we need not only ‘testability’, but also ease of diagnosis. There are no shortcuts; you must design the application, from the ground-up, for ease of testing and diagnosis, and this is an especially urgent requirement, in a dynamic multiuser system such as a database.

For a database to be inherently testable, it must be possible to control the state of the database under test, easily observe the results, test parts of the database in isolation, and automate all the necessary tests. The database must be clearly partitioned into logical components, and must not share functionality. It must be intelligible and well-structured. It must be possible to script out a whole variety of processes that are ‘idempotent’ in the sense that, each time they are run they should give the same result, so you can check they do so.

We can’t stop there though; we must then make it easy to diagnose. This can vastly reduce the total time and effort of debugging. A debugging framework is like scaffolding around a house. You might think it was pointless putting up scaffolding when you eventually won’t need it and will take it down. However, any experienced builder will just laugh if you try to build a house without it.

So, what is this ‘scaffolding’ when applied to database development? Although there are some startlingly good system functions for investigating the running SQL Server system, in general, they are most effective when used as part of an intelligent instrumentation, logging and recording system that will allow you to easily reproduce any failure or stress condition.

I’ll log all calls to the interface in as much detail as possible (stored procedures are great for this). I’ll build in a lot of reports that will tell me what went on in a batch or process. I’ll create scripts from the logs that can repeat a state that caused problems. I’ll implement whatever I can to log changes to the data.

If development and test are rather separate concerns in your organization, then you’ll find that by working in this way the relationship between developers and testers becomes far more intertwined and cooperative. Maybe I shall call this approach ‘Database DevTest’. After all, having hurdled the database testing fence, with Database DevTest, surely leaping the great deployment barrier, using Database DevOps, should much easier?

Commentary Competition

Enjoyed the topic? Have a relevant anecdote? Disagree with the author? Leave your two cents on this post in the comments below, and our favourite response will win a $50 Amazon gift card. The competition closes two weeks from the date of publication, and the winner will be announced in the next Simple Talk newsletter.


  • Rate
    [Total: 6    Average: 4.7/5]
  • Keith Rowley

    I love this in theory. In practice I don’t know where to start since I am inheriting an existing database in a rather sprawling ongoing software development project.

  • Parker

    Keith –

    I suggest that you look for any good practices to be found in your inheritance, and use them as the basis for standards you want to follow going forward. If you don’t find any good practices, then you get to (have to) define your own.

    Express the good practices as template files where you can, so that copying and using the templates is easier for developers than not doing so. Also, bring one of each kind of object up to standard as you can, and refer developers to these as examples of what is expected.

    Finally, review and correct before implementation – set the expectation that new work is done to standard, major revisions are brought up to standard, and minor revisions have the new material done to standard (with praise and encouragement to those who bring the rest of the code/component up to speed).

    Easy to say, harder to do…

    • Gina Taylor

      Hi @simpletalk-sso-253da7c9464cdfe6368cad7e9b552ca6:disqus, congratulations on winning this week’s commentary competition! Drop an email to and we’ll arrange your gift card prize.

  • willliebago

    I don’t get it?

    Do DB developers not have use cases, test cases, or user stories (with As a ___, I want ___, so that ___) that can be used to generate testing scenarios?

    If DB developers don’t have anything like that, then there are probably bigger problems to deal with than figuring out how to test the DB code 🙂

    • Peter Schott

      Sometimes our only guideline is “As a system, I want a place to store this data, so that it can be stored/retrieved.” The app itself does all of the read/write ops and not using stored procs. Testing against that from a purely DB perspective is a bit challenging. 🙂

  • Peter Schott

    How much you can do also depends on the nature of the dev work. I’ve found adding a “Debug” parameter to my stored procs can really help later to show different values and times along the way or to log extra data along the way. However, if the dev team is heavily into ORMs and doesn’t use stored procs a lot, that practice is a bit more limited. That’s been really useful for more complex procs to log all sorts of information – number of executions, length, stats, parameters, seeing output along the way, and more. Yes, it has to be coded, but it can be as simple or complex as needed if that @Debug parameter is set.

    I’m still working out how best to do that in SSIS, but following good naming practices and patterns is a good start. I’ve been working through the SSIS Design Patterns book from APress and have mostly adopted Jamie Thomson’s naming guidelines.

    I never quite adopted the whole unit test idea of “test for existence first”. We try to use tools that build up the database automatically such as Migrations or SSDT to get to a given version so those sorts of tests haven’t ever made as much sense.

    The biggest problem overall is coming up with realistic test data. We can’t just scrub the data from production due to the laws that constrain us. Generating something realistic that you can populate into an empty database is always challenging, especially as the system evolves. We struggled with this even with systems that weren’t constrained by legal requirements. I’ve worked with some great QA teams that automated this sort of thing to do data quality testing, though. They programmed their tests to enter data for expected values, test various things against those, then tear down the data after the tests passed. It took quite a bit of time to get to that point, but it tied in with testing the interface so saved everyone time in the long run.