NUnit Integration Tests on Web API - how to create/destroy integration test db

Question

I'm implementing NUnit Integration Tests of our controller REST endpoints in a .NET Web API 2 project. We use an Entity Framework code-first from database approach to create our controllers and models.

I've got the myProjectIntegrationTests project set up, with NUnit installed and a reference to myProject.

From my research, the next step is to create a TestSetup script which, on each test, creates an Integration Tests Database in a LocalDb. This allows us to integration test our API calls without affecting the master dev database.

This TestSetup script should do several things each time a test runs:
- check if a connection is currently open in Integration Test Db - if so, close it.
- check if an existing Integration Test db exists - if so, tear it down.
- run a migration from my master dev database to my Integration Test Db to load it with real data.
- create a new instance of Integration Test Db
- integration tests run...
- close Integration Test Db connections
- teardown Integration Test Db

Creating this TestSetup class is what's giving me trouble. I've found tutorials on how to do this for .NET MVC, .NET Core and also Entity Framework - but none of these seem to be utilizing just .Net Web API, so some of the libraries and code being referenced isn't working for me. Can someone provide an example script or tutorial link that might work in .NET Web API 2?

Here's an example of someone doing this for EntityFramework, using I believe .Net Core. This is part of a great PluralSight tutorial on integration testing in Entity Framework by Michael Perry, found here:

using Globalmantics.DAL;
using Globalmantics.DAL.Migrations;
using NUnit.Framework;
using System;
using System.Collections.Generic;
using System.Data.E  ntity;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Reflection;

namespace Globalmantics.IntegrationTests
{
    [SetUpFixture]
    public class TestSetup
    {
        [OneTimeSetUp]
        public void SetUpDatabase()
        {
            DestroyDatabase();
            CreateDatabase();
        }

        [OneTimeTearDown]
        public void TearDownDatabase()
        {
            DestroyDatabase();
        }

        private static void CreateDatabase()
        {
            ExecuteSqlCommand(Master, $@"
                CREATE DATABASE [Globalmantics]
                ON (NAME = 'Globalmantics',
                FILENAME = '{Filename}')");

            var migration = new MigrateDatabaseToLatestVersion<
                GlobalmanticsContext, GlobalmanticsConfiguration>();
            migration.InitializeDatabase(new GlobalmanticsContext());
        }

        private static void DestroyDatabase()
        {
            var fileNames = ExecuteSqlQuery(Master, @"
                SELECT [physical_name] FROM [sys].[master_files]
                WHERE [database_id] = DB_ID('Globalmantics')",
                row => (string)row["physical_name"]);

            if (fileNames.Any())
            {
                ExecuteSqlCommand(Master, @"
                    ALTER DATABASE [Globalmantics] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
                    EXEC sp_detach_db 'Globalmantics'");

                fileNames.ForEach(File.Delete);
            }
        }

        private static void ExecuteSqlCommand(
            SqlConnectionStringBuilder connectionStringBuilder,
            string commandText)
        {
            using (var connection = new SqlConnection(connectionStringBuilder.ConnectionString))
            {
                connection.Open();
                using (var command = connection.CreateCommand())
                {
                    command.CommandText = commandText;
                    command.ExecuteNonQuery();
                }
            }
        }

        private static List<T> ExecuteSqlQuery<T>(
            SqlConnectionStringBuilder connectionStringBuilder,
            string queryText,
            Func<SqlDataReader, T> read)
        {
            var result = new List<T>();
            using (var connection = new SqlConnection(connectionStringBuilder.ConnectionString))
            {
                connection.Open();
                using (var command = connection.CreateCommand())
                {
                    command.CommandText = queryText;
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            result.Add(read(reader));
                        }
                    }
                }
            }
            return result;
        }

        private static SqlConnectionStringBuilder Master =>
            new SqlConnectionStringBuilder
            {
                DataSource = @"(LocalDB)\MSSQLLocalDB",
                InitialCatalog = "master",
                IntegratedSecurity = true
            };

        private static string Filename => Path.Combine(
            Path.GetDirectoryName(
                Assembly.GetExecutingAssembly().Location),
            "Globalmantics.mdf");
    }
}

And here's an older example of someone doing this for .Net MVC:

using System;
using System.Data.Entity;
using NUnit.Framework;

namespace BankingSite.IntegrationTests
{
    [SetUpFixture]
    public class TestFixtureLifecycle
    {
        public TestFixtureLifecycle()
        {
            EnsureDataDirectoryConnectionStringPlaceholderIsSet();

            EnsureNoExistingDatabaseFiles();
        }

        private static void EnsureDataDirectoryConnectionStringPlaceholderIsSet()
        {
            // When not running inside MVC application the |DataDirectory| placeholder 
            // is null in a connection string, e.g AttachDBFilename=|DataDirectory|\TestBankingSiteDb.mdf

            AppDomain.CurrentDomain.SetData("DataDirectory", NUnit.Framework.TestContext.CurrentContext.TestDirectory);
        }

        private void EnsureNoExistingDatabaseFiles()
        {
            const string connectionString = "name=DefaultConnection";

            if (Database.Exists(connectionString))
            {                
                Database.Delete(connectionString);    
            }           
        }
    }
}

Answer 1

Going line-by-line through the type of sql commands you'll need for these operations is just going to be painful. You would benefit much better to just develop a stored procedure that does the tear-down/build-up steps. You appear to already have a start in that as I see you writing code around the statements. Then your integration test code would just need to call this procedure and wait for the setup to complete. Remember, You don't have to do everything in code.

Answer 2

Probably not the answer you are looking for but I have had success recently using the sql server docker image with docker compose.

You can fire up a database instance and delete the data volumes when the the image shuts down. Using the —rm switch on the docker run command will do that automatically for you.

If you are using dot net core you can setup another container to run your entity framework migrations and tests.

If you are using dotnet framework you maybe be able to run windows docker images however they tend to be a bit slower to startup.

This approach would work best if you launched everything from a powershell script. Launching the infrastructure from the code as you are looking to do could be tricky and perhaps more complex than it needs to be.