I understand there are many questions in this site revolving around the same concept, but I could not get a precise answer for my case.


I am handling an ERP System, with code base in both:

  • VB.NET/ASP Forms for the user interface portion of the system
  • TSQL Stored Procedures on SQL Server, handling business logic.

So both database tables and database stored procedures are tightly coupling data and business logic

We have been trying to figure out a way to automate unit tests to help us detect regression and other potential bugs. But the problem with our setup is the presence of database as a major component of our system's business logic; Databases retains states, i.e. we cannot run something like xUnit with independent classes. because each test will alter that state of our system, by changing one of the records of data tables, lookup tables or control tables.


We are not expected to shift our logic to the VB portion of our code (For political reasons)

Note that mocking database will not solve our problem, because business logic runs on stored procedures, that is by design relying on state of data


What are our options?

  • is there a way to implement automated unit/integration testing?
  • Are we stuck in manual testing?
  • Should we place our environment in a farm of virtual machines with the same baseline and run independent tests on different machines?

Unfortunately, I could not find any literature related to best practices for TDD and Stored Procedures based systems.

  • 1
    You can't use a test database? We solved a similar issue changing the connection string to point to a test database, running the procedures and then querying the database to see if they did what they are supposed to do. You can make your first test database using a backup from the production data if you don't need to anonymize anything. Also - keep in mind that TDD doesn't lend itself that well towards managing legacy code. Unit Testing is good, but by itself it doesn't make TDD.
    – T. Sar
    Aug 22, 2017 at 13:03
  • 1
    @A.Rashad - You can certainly mock and fake database tables to do this easily... Look at frameworks such as tSQLt: tsqlt.org
    – Milney
    Aug 22, 2017 at 14:26
  • 1
    I would suggest that you abandon the concept of Unit Tests in the pure form and focus on testing the most important pieces of the system: the stored procedures. Focus on having a single script clear our the database and prepare it for a full test run of your app in a testing location, pointed to a test version of the database, with some known set of data that is the state of the table if the tests pass.
    – GHP
    Aug 25, 2017 at 18:58
  • 1
    It won't be as fast as code-only unit tests, but it means you'll be testing both the database stuff (sprocs, triggers, etc) AND the web app behavior (button clicks, links, etc).
    – GHP
    Aug 25, 2017 at 19:05

4 Answers 4


I've implemented automated testing including the database in a system with some of the same problems. The approach I took was roughly:

  • Before all unit tests, bring up a DB from scratch with schema and no data
  • Before each test case, truncate all the relevant db tables

This works, though it does produce very slow tests.

Once you have regression/characterization tests in place, you should begin decoupling the application logic from the database. Hide the business logic behind an interface and use inversion of control so that you can pass in the implementation. You'll start with the db-backed implementation, and in tests you can use an in-memory version that's lightweight and faster.

For example, if you have:

def frob_widgets(foo, bar):
    if db.some_query(params=blah):
    return db.explode(bar, foo)

Change the signature to def frob_widgets(db, foo, bar)

You may find a tool like ephemeral_pg useful, which helps to run a postgres database as lean as possible by running in memory with fewer safety/reliability checks enabled

  • If you can get the DB to run in memory, or switch to one that does, it may help. Aug 22, 2017 at 23:05
  • @FrankHileman:any idea how one easily switch to a MSSQL compatible, lightweigt in-memory database which supports stored procedures in TSQL? I have none. For me it looks you are suggesting the OP to throw his system away and start from scratch - not very constructive.
    – Doc Brown
    Aug 24, 2017 at 5:45
  • @DocBrown Virtual machines is a start. I don't know of the latest techniques, but there may be a lighter way to do it. Aug 24, 2017 at 19:52
  • @FrankHileman: VMs will help to install a test database quicker, especially if that has to be repeated. But they will not make the tests run "in memory" or faster, quite the opposite.
    – Doc Brown
    Aug 25, 2017 at 8:25
  • You need to run the same database in test that you use in production for these tests to be properly valuable. Tune it to not use journaling, not sync to disk if possible, maybe make the DB volume mounted on a ramdisk.
    – Daenyth
    Aug 25, 2017 at 10:50

Assuming all logic is within your DB, in stored procedures, and you simply cannot change that in your project, I'd do the following:

  1. (optional) Refactor your stored procedures in order that each one has one clear responsibility, and that you can map that for an input A, there will be an output (or some specific data in a table) B;
  2. have a test database, where you will clear all data, and insert specific data on-the-fly during the unit tests execution;
  3. With that, create your unit tests as stored procedures, and also stored procedures to insert specific input data (input A from step 1), and stored procedures to clear all current data;
  4. Each test procedure (unit test) tests One of your logical stored procedures, and inserts data as input A and expects the procedure to generate output data as output B, if that is done then the test PASSES, otherwise if FAILS;
  5. (optional) the test procedures exist only in the test database; you could also have a separate UnitTests table, which contains the name of the stored procedure and its last status of execution; with this, you could create a UnitTestManager stored procedure, that will lookup in the UnitTests table, and dynamically calls the tests procedures, and updates the UnitTests data. Later, after execution, you can check for detailed information of failures and successes within you UnitTests table.

You can try to build mocks, stubs and fakes around your units as replacement for the dependencies.

I haven't got much experience with VB.NET but I'm sure there are unit testing frameworks out there for you to find.

A colleague recently pointed me at an article from Martin Fowler. I hope that can help you find your way as well.


I just thought of a theoretical solution to this problem as follows:

  1. Create a transaction
  2. Do your testing
  3. Rollback the transaction

    Begin Transaction test_case
       -- Test Preparation Steps
       -- Assertion Steps 
    Rollback transaction test_case

This will guarantee that tests will not alter the state of the tested system, unless of course one of the tested steps involves DML.

There is a drawback in this approach, that we can't save test results in this database. instead, we can place results in some report or a spool file

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.