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.