Last week I wrote about why you can't unit test a stored procedure — or any SQL, for that matter. The database is hostile to unit testing. Test friction makes integration tests expensive to write and maintain. Testing cultures die quietly when the friction gets too high. And stored procedures are architecturally incompatible with continuous deployment.
That post was all diagnosis. This one's the prescription.
Best Way to Avoid a Fight? Don't Be There.
You can't unit test the database. Fine. Accept that fact. Don't fight it. The best way to avoid a fight is to not be there.
That's my answer. That's the whole prescription. I call it the Mr. Miyagi School of Database Development. Yes, it's software development based on a karate movie from the 1980's. The quote from the master teacher is something like "Remember, best block, no be there". Which I've consistently misremembered as "the best way to avoid a fight? Don't be there."
So if it's difficult, bordering on impossible to test the database, well then stop trying to test the database. Instead, minimize how much of your application requires the database to test.
I can already hear the screams and agitation: "that's irresponsible! you have to test the database!"
No. You really don't. If you think strategically about your software architecture -- especially about managing dependencies -- it ends up not adding a whole lot of value.
Fuzzy Thinking About Single Responsibility Principle (SRP)
The very common reason why teams think that they have to test the database comes down to fuzzy thinking about responsibilities in the code. You might have heard of the SOLID Principles in software engineering. The 'S' in SOLID is "Single Responsibility Principle" also known as SRP.
SRP says that a class should do one thing and one thing only. Another way of stating it is that a class should have only one reason to change. When you combine these -- do one thing only and only one reason to change -- you get a really elegant double-check on yourself that keeps your code clean and organized.
Does this class do only one thing? Does this class have hidden reasons to change that imply it does more than one thing?
The Essential Split: Data Access vs. Data Structure Conversions
Here's specifically where teams go wrong. They write a class called something like PersonDataAccess and it's got methods like GetById() and Save(). Looking at the interface (the method contract) for this class, it seems like it's doing a good job on single responsibility principle. It looks like it does that final hop to the database -- the data storage and retrieval "concern".
But then when you actually get into this class, you start to realize that this class owns the object-to-relational conversion PLUS the calls to the database. That's two (probably 3) different "concerns" and WHOOMP! there's your SRP violation.
Responsibility #1: convert object data into the structures needed for saving to the database
Responsibility #2: convert query results into objects
Responsibility #3: issuing calls to the database
You could probably distill that down to just two concerns: 1) adapting data to/from the database and 2) making calls to the database. And that's how I structure it in my apps: Adapters and Repositories. Adapter classes know how to do all the conversion logic. Repositories make the calls to the database and coordinate with the adapters.
The Secret Sauce for Testability
So why does this help with testability? It lets me make a whole bunch of important and VERY bug-prone logic into unit tests and then keep some other not-particularly-bug-prone stuff separated away.
The buggy stuff: all that data conversion that addresses the object-relational impedance mismatch. The not buggy stuff: making database calls.
So what you get is two classes instead of one class. PersonDataAccess becomes PersonRepository and PersonAdapter.
So what does this have to do with Mr. Miyagi?
When you try to unit test PersonDataAccess, you find that it needs a running database and probably a whole bunch of test data. Well, that's an integration test (not a unit test) and it's got a bunch of annoying test friction things like spinning up the database engine, deploying the schema, and ensuring your test data is right BEFORE you run the test. That's choosing to be there for the fight. You're fighting the database in order to verify that that logic is working.
But when you split those into PersonRepository and PersonAdapter, you suddenly have something that's easy to unit test. PersonAdapter does all that data conversion and THAT'S where the bugs always are. Getting stuff shaped correctly so that it can be sent to the database. Getting those query results shaped correctly so that they can be returned as objects and used by the rest of your application code.
In order to test PersonAdapter, there's zero reason to have a database involved. Create an in-memory structure that represents the business object (Person) that needs to get saved and convert it into whatever you're going to fire off to the database -- that's probably something like PersonEntity or DataRow (https://learn.microsoft.com/en-us/dotnet/api/system.data.datarow?view=net-10.0). In your unit test, verify the heck outta the conversion result. Did you get what you wanted? Yes? Awesome. No? Well, you got that answer from a unit test that took 1 second to run. Cheap. Fast. Easy to write.
And then PersonRepository just needs to know how to call PersonAdapter, get the result, and then push that result to the database.
Keep it Simple
That PersonRepository becomes almost boring. No business logic. No data shaping. No validation. No workflow decisions. Just plumbing — get this, save that, query by these criteria.
Keep the database simple, too. The database stores data and enforces data integrity constraints (NOT NULL, UNIQUE, foreign keys). That's the database's job. Keep the code that talks to it just as simple.
When your repository is this simple, testing that integration with the database ALSO becomes simple: it either works or it doesn't. It doesn't fail in some vague way with some random piece of data out of place. The call succeeds or the call bombs.
It becomes self-evidently correct or self-evidently broken. A repository method that calls SaveChanges() and returns an entity doesn't fail in subtle, hard-to-diagnose ways. It either works or it throws a SqlException in your face. You'll know immediately.
That's the Mr. Miyagi payoff. You avoided the fight. The important, bug-prone logic (the adapter) is fully unit testable. The simple, not-particularly-bug-prone logic (the repository) barely needs testing at all. You didn't skip testing — you architectured so that the stuff worth testing doesn't require a database and the stuff that requires a database isn't worth agonizing over.
Layered Defense and Calculated Risk
There's a classic military strategy book called The Defense of Duffer's Drift that I think about a lot when it comes to testing strategy. The core idea is layered defense — you don't try to defend everything equally. You figure out where the real threats are, you put your strongest defenses there, and you accept calculated risks on the parts that are less likely to get hit. You don't leave them undefended -- you just defend them differently with a different focus.
Testing works the same way.
You don't test everything equally. You can't test everything equally — there aren't enough hours in the day and the test friction would bury you. So you make choices. You put your heaviest testing where the highest-value logic and/or where your bug-prone logic lives. You put lighter testing (or no testing) on the stuff that's simple, obvious, and unlikely to fail in ways that surprise you.
That's exactly what the adapter/repository split gives you. The SRP discipline we talked about earlier isn't just about clean code — it's what makes layered defense possible. Clean boundaries let you put heavy testing focus on the adapter (where the bugs are) and light-to-no testing on the repository (where the bugs aren't).
The Part That'll Drive the Internet Crazy
Ok. Here's where I get honest. This is the part where the stored procedure defenders are going to lose their minds.
I quite often just don't integration test my calls to the database.
I know. I KNOW. The guy who just spent 2,000 words talking about how important testing is just told you he doesn't test his database calls. Sounds hypocritical. Hear me out.
I mock the absolute hell out of those integration points in my unit tests. This is where dependency injection is so IMPORTANT. The repository interface? Mocked. The data it returns? Controlled. The behavior when it throws? Tested. Every business rule that depends on data from the database is tested thoroughly — just without an actual database. I'm doing behavior-level testing on those boundaries. I know my code does the right thing with the data it gets.
(Shameless plug: I built a utility class called MockUtility that automates this mock setup. It uses reflection to inspect your constructor, creates Moq mocks for all the dependencies, and hands you both the instance and the mocks. It's in my Benday.Common.Testing NuGet package. It makes writing these tests almost trivially easy.)
What I'm not testing is the hop across the boundary. The actual call to SQL Server. The actual EF Core query. The actual SaveChanges().
Why? Because I've kept that boundary stupid simple. The repository implementation is almost entirely plumbing — get this, save that, query by these criteria. There's no business logic in there. There's minimal conditional branching. There's no "if the order total is over $500, apply the discount in the WHERE clause." It's just data in, data out.
And here's the thing about trivially simple code that talks to a database: when it's wrong, it doesn't fail subtly. It explodes. You get a SqlException. You get a null reference. You get a column name mismatch. You get an entity mapping error. These things don't sneak past you during development. They throw an exception the first time you run the code. They're loud, obvious, and immediate.
So the calculated risk I'm taking is this: I'm betting that trivially simple data access code will either work correctly or fail so obviously that I'll catch it immediately during development. I'm betting that the cost of writing and maintaining integration tests for that code — the test friction — is not worth the marginal safety I'd get from it.
But What If I Really Want an Integration Test?
So what if I really want to have an integration test? What do those look like? This is where WebApplicationFactory
My integration tests often are just really ham-handed calls to Controller operations. Or if I'm feeling really fancy, I'll use WAF to new up an instance of a repository and fire off some commands. Did that integration test explode? No? It's probably fine. Exploded? Well, let's take a look at that exception message.
BTW, one of the handiest integration tests is checking for dependency injection misconfigurations. Create an instance of the app in a WAF and then ask the WAF for an instance of any class that extends from Controller or ControllerBase. I catch SO MANY bugs right there. Typically those bugs are "we added a new dependency but we forgot to register it with the DI".
Risk Tolerance
My risk tolerance for not integration testing a dependency boundary goes UP as my unit test coverage goes UP. Let me put that a different way: if I've got a TON of unit tests that test the things that participate around that boundary, the less worried I am about the actual boundary. The boundary becomes (usually) a C# interface contract that gets checked at compile time.
The more thoroughly I've tested the business logic around the database, the less I need to worry about the database call itself. If I know the code handles every edge case correctly with mock data, the only remaining question is "does the real query return the same shape of data as my mock?" And for simple CRUD operations, the answer is almost always yes.
Is this a universal rule? No. If I've got a genuinely complex query — something with multiple joins, window functions, conditional aggregation — I might write an integration test for that. Some queries earn that investment. But those should be the exception, not the rule. And if most of your queries are that complex, I'd argue that's a design problem, not a testing problem.
The point is: I'm not skipping integration tests out of laziness. I'm making a deliberate architectural choice to keep the risky stuff testable and the database stuff simple enough that the risk is acceptable. Layered defense. Strongest coverage where the threats are highest. Calculated risk where the code is simple enough to be self-evidently correct (or self-evidently broken).
Zooming Out: The Bigger Picture
So far I've been talking about the data access layer — the adapter/repository split. But this same principle applies at every layer of your application. I wrote a couple weeks ago about architecting for being wrong, and this is what that looks like in practice.
Here's a quick example at the service layer. You've got an IOrderRepository interface. Your service class depends on that interface. In production, the implementation talks to SQL Server through EF Core. In your tests, you hand it a fake that returns predictable data.
// Your business logic doesn't know or care about the database
public class OrderService
{
private readonly IOrderRepository _repository;
public OrderService(IOrderRepository repository)
{
_repository = repository;
}
public OrderResult PlaceOrder(OrderRequest request)
{
// Business rules here — all testable without a database
if (request.Items.Count == 0)
return OrderResult.Failed("Order must have at least one item.");
var order = new Order(request.CustomerId, request.Items);
order.CalculateTotals();
_repository.Save(order);
return OrderResult.Success(order);
}
}
That PlaceOrder method is trivially unit testable. Hand it a mock repository. Verify the business rules. Check the calculations. Confirm it saves. Done. No database. No setup. No teardown. Runs in milliseconds.
Compare that to the same logic in a stored procedure. To test it, you need a running database, customer data, product data, address data, category data, and a prayer that nobody else's test corrupted your test state. The test takes 2 seconds instead of 2 milliseconds. Multiply by 500 tests and the difference between a test suite that runs in 1 second and one that takes 15 minutes.
And the kicker? The stored procedure version tests less because the test friction means people write fewer tests. So you're slower AND less covered.
The Real Tradeoff
Like everything in this series, this comes down to tradeoffs. And the tradeoff for testability is stark.
Business logic in stored procedures gives you: the data is "right there," maybe a performance edge in specific scenarios, and the comfort of a familiar pattern. It also gives you: integration-test-only testing, slow feedback loops, fragile test infrastructure, eroding test coverage, scary deployments, and maybe something like a two-week regression cycle before every release.
Business logic in C# behind interfaces gives you: fast unit tests, reliable CI/CD, confident refactoring, a testing pyramid that actually makes sense, and boring deployments.
I like boring deployments.
-Ben
