0

I am working on a large-scale .NET test automation project (over 2500 test cases) that interacts with an application API. My goal is to keep the database clean and isolated between test runs.

Here's my problem:

  • When I run integration tests that directly execute SQL commands (using ADO.NET), I can successfully roll back changes at the end of each test by wrapping everything in a TransactionScope. This keeps my database clean.
  • However, most of my tests interact with the application via its public API (for example, sending HTTP requests to the API endpoints). In these cases, the API code opens its own database connection/process and commits changes independently.
  • Even though I create a TransactionScope in the test code (before calling the API), any database changes performed by the application/API are not rolled back. Only changes made directly by the test code’s connection are rolled back.

The SQL Server database is quite large, with a high number of tables and a significant amount of data. As a result, approaches like truncating all tables or dropping & recreating them after each test are impractical because they add significant overhead and make test runs much slower. I am specifically looking for efficient strategies that work well with large and complex databases.

Is there a way to ensure all database changes (including those made by the application/API in response to test requests) are rolled back after each test without db restore?

I tried wrapping my test execution in a TransactionScope (in C#) so that all database changes made during a test would be rolled back at the end. This approach works perfectly if I execute SQL commands directly from the test code: those changes are rolled back when the transaction is disposed.

However, most of my tests interact with the application via its API endpoints (for example, by sending HTTP requests). In these cases, the API code runs in a different process, opens its own database connection, and commits changes outside of the test's TransactionScope. As a result, any database modifications made by the API are not rolled back, and the database state is not isolated between tests.

I was expecting that using a TransactionScope in my test code would also roll back any database changes made by the application in response to my API calls, but that is not happening. I am looking for a way to ensure all changes (including those from the API) can be rolled back or reset efficiently between tests.

Improve this question
7
  • Do you have control over the API code? Is there a "test" version of the API? Commented Jun 9 at 12:27
  • If you are using Entity framework core you might consider the in-memory database provider Commented Jun 9 at 12:51
  • You probably want to use a separate test version of a database, otherwise this is just doomed to fail rather badly. Do you have such a version? And can you easily control if transactions are test or not? Commented Jun 9 at 12:51
  • "most of my tests interact with the application via its API endpoints" this is where your problem is. Most of your tests should be unit tests. You are describing integration tests and they should be very much the minority. You say that you can run direct sql from your tests, so for the very small number of integration tests that cause data changes, your test code can insert test data and clean up afterwards. Commented Jun 9 at 13:27
  • To expand on that. You have an API endpoint A. A does nothing apart from pass the parameters directly to class B. B converts your parameters into the internal data model and calls C. C contains the business logic and needs to save or retrieve data. It calls class D which contains only the database CRUD (or equivalent). You test D by writing tests that instantiate D and interact with the dev database, in a transaction. This proves D works. You test C by injecting a mock or stub D which returns test data. You know your mock D behaves the same as real D because you have tests of D by itself... Commented Jun 9 at 13:39

1 Answer 1

Reset to default
5

I was expecting that using a TransactionScope in my test code would also roll back any database changes made by the application in response to my API calls

That's never going to work. That scope only exists for the client app on the client machine, whereas the commands are all executed on the webserver (which may or may not be the same machine as the SQL Server machine).

Your best bet is probably Database Snapshots. I have used this successfully in exactly these circumstances.

First, ensure your database is exactly how you want it to be at the start of every test run.

Then create a snapshot. Note that the snapshot itself is read-only. We simply revert the current database to the snapshot.

CREATE DATABASE AdventureWorks_dbss1800 ON ( NAME = AdventureWorks, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Data\AdventureWorks_data_1800.ss' ) AS SNAPSHOT OF AdventureWorks;

Then you need to revert the snapshot in between tests. You can do this from a separate endpoint you call at the end of the test.

If you are restarting the webserver on each test then you can put this code in a shutdown hook.

app.Lifetime.ApplicationStopping.Register(() => { const string query = """ RESTORE DATABASE [AdventureWorks] FROM DATABASE_SNAPSHOT = [AdventureWorks_dbss1800]; """; using var conn = new SqlConnection(_connectionString); using var comm = new SqlCommand(query, conn); conn.Open(); comm.ExecuteNonQuery(); });

Note that the above won't run if you stop the webserver via the debugger.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Wouldn't the last code restore the production database? sounds a bit dangerous
Point is to use a test database, snapshotted to how you want it to look at the start of each test. Then you revert to the test DB's snapshot. No you wouldn't use prod data for this.
What, no, then you will have the data after the test ran. I'm not sure you're quite aware of how this is working. Get the DB into a known state. Create snapshot. Run test on current DB, not the snapshot. Revert current DB to the snapshot. Now it's back to the known state.
Wish I’d known about this a couple of years ago to clean out my local DB :) - I had code to drop the indexes, truncate the tables and recreate the indexes…
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.