Unit Test for a sql query c#

Question

I have a sql statement i.e select x, y, z from T and this query gives me list of results.

My goal is to write a unit test if this query results list or fails to retrieve from database if change in environments. I'm new to unit test and unaware if I can open and close connection.

Let me know if someone can help me on this.

Answer 1

When writing unit tests, you should not try to test querying the a real database. Unit testing is about testing individual units, this mean you should mock out external dependencies from the unit you are testing.

What you probably are trying to do is integration testing.

Answer 2

TL;DR – check out DbSample on GitHub, a sample EF Core based project with fully automated tests against MS SQL Server and a GitHub Actions pipeline.

A detailed analysis of orchestrating tests is covered in "Pain & Gain of automated tests against SQL (MS SQL, PostgreSQL)" article. The key steps are:

1. Launch SQL engine in Docker (use official images for SQL Server or PostgreSQL) and create a test database, like

docker run --name sql-server -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=Secret_Passw0rd" -e "MSSQL_PID=Express" -p 1433:1433 -d mcr.microsoft.com/mssql/server 
# Creating a new DB
docker exec -i sql-server /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P Secret_Passw0rd -d master -Q "CREATE DATABASE TestDb"

2. Populate the schema, logic, lookup dictionaries, etc (by running an SQL script prepared earlier):

# Copy the SQL script
docker cp CreateOrMigrateDatabase.sql sql-server:/home/script.sql
# Executing the script
docker exec -i sql-server /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P Secret_Passw0rd -d TestDb -i /home/script.sql

3. Connect xUnit/NUnit tests to the database and for each test (rinse and repeat):
   1. Seed test data
   2. Perform the testable activity and checks
   3. Revert the database to the pristine state (via Respawn):

Checkpoint checkPoint = new();
await checkPoint.Reset(_sqlConnection);

4. Tear down the SQL engine along with the database and other artefacts.

Don't cry "It's not a unit test!"

The OP's request is legit for asserting a certain behaviour (e.g. applying BDD), where SQL queries/commands is essential part, isolating which might hurt reliability of the project. And also using in-memory DB providers would mask potential issues and discouraged by Microsoft.

Why so complicated?

Testing against a real SQL engine has to work:

• locally on dev machines (that run Windows, MacOS or Linux);
• build pipelines in the cloud (e.g. Azure DevOps pipelines, GitHub Actions, etc.).

So Docker indeed 😀