asp.net core Integration test for database

Question

I am trying to setup integration testing of the database in an asp.netcore project. I am using code first approach to create the database.
For the testing iam using the nudget packages XUnit, FluentAssertions and NUnitestApadter3. When i run the test for the first-time the test passes.

[Collection("Integration test collection")]
public class BookServiceTest : IntegrationTestBase
{
    [Fact]
    public void CanCreateUser()
    {
        using (var context = GivenBPDContext())
        {
            var Book = new BookService(context);

            Data.Database.Entities.Book book = Book.AddNewBook("test");
            context.SaveChanges();

            book.Id.Should().NotBe(0);
            book.Name.Should().Be("test");
        }
    }
}

public class IntegrationTestBase
{
    protected static BPDContext GivenBPDContext()
    {

        var context = new BPDContext(new DbContextOptionsBuilder().Options);

        return context;
    }
    // i tried dropping the database here and it do not work
}

A very basic logic test

public class BookService
{
    private BPDContext _context;

    public BookService(BPDContext context)
    {
        _context = context;
    }

    public Book AddNewBook(string name)
    {
        var book = _context.Books
            .FirstOrDefault(x => x.Name == name);

        if (book == null)
        {
            book = _context.Books.Add(new Data.Database.Entities.Book
            {
                Name = name,
            }).Entity;
        }

        return book;
    }
}

The second time i run the test and change a value being tested it fails. I need a way of dropping the database after each test and then running the migrations to get the database up to the correct version.

Below is how i setup up the database. startup.cs

public void ConfigureServices(IServiceCollection services)
{
    services.AddMvc();
    services.AddTransient<IBPDRepository, BPDRepository>();
    services.AddDbContext<BPDContext>();
}

public class BPDContext:DbContext
{
    public DbSet<Entities.Book> Books { get; set; }
    public DbSet<Entities.User> User { get; set; }
    public DbSet<Entities.Reviewer> Reviewer { get; set; }

    public BPDContext(DbContextOptions options):base(options)
    {

    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        //maybe put something in if its in testing mode
        optionsBuilder.UseSqlServer("Server = (localdb)\\mssqllocaldb;Database = BookProjectDatabase;Trusted_Connection = True; ", options => options.MaxBatchSize(30));
        optionsBuilder.EnableSensitiveDataLogging();
    }

}

In summary I need to delete the database before each test run, then updated the database with the migrations and finally perform the units .

Answer 1

There are not that many good options to isolate your integration tests from each other in regard to the shared state.

Here are a few:

1. Make tests responsible for restoring the data they change;

   I.e each test changing data, should have some additional cleanup logic implemented, to revert the data it changed to its previous state. Might involve loads of noise in every test.

2. Prepare data needed in the tests themselves and assert in some smart way on this data only;

   This implies that tests are both creating data they need and are then asserting on this data only. I.e none of the tests should be able to change the other tests data. Might be not that easy to implement and could require adjustments to the app itself.

3. Create and initialize fresh database from scratch for every test;

   Will surely work, but could be slow.

4. Use database backups to restore database to the point you need;

   Still most likely slow.

5. Use database snapshots for the restore;

   This one is sth to consider as it's both easy to implement and fast enough.

6. Wrap each test in transaction and revert it afterwards;

   Won't work in general as transactions couldn't be nested, but might be used as an optimization for such parts of you app logic, which don't create transactions on their own. TransactionScope support or some way to pass DbTransaction instance created in the tests to the app data layer are needed.

7. Execute scripts to delete all the data and insert it again;

   Also a good option. Libraries like Respawn or Reseed could help you to generate the scripts easily.

And then to optimize the tests performance you might use a pool of databases instead of the only, this way you'll be able to run your tests in parallel. You could either manually prepare a pool of dedicated test databases or dynamically create them on-demand with sth like Docker and TestContainers.

Another optimization approach is spliting tests in two groups: one that does read-only access and another one that mutates data. You might skip data restore phase and moreover safely use parallelization for the read-only tests if you use a database per group.

Answer 2

You can use InMemoryDbContext for test operation. Via InMemoryDbContext, you does not have to create physical db; furthermore, you can dispose it easily.

[Collection("Integration test collection")]
public class BookServiceTest : IDisposible
{
    private BDPContext _context;
    public BookServiceTest()
    {
        DbContextOptions<BPDContext> options = new DbContextOptionsBuilder<BPDContext>()
            .UseInMemoryDatabase(GetType().Name)
            .Options;

        _context = new BPDContext(options);
    }

    [Fact]
    public void CanCreateUser()
    {
        var Book = new BookService(context);

        Data.Database.Entities.Book book = Book.AddNewBook("test");
        context.SaveChanges();

        book.Id.Should().NotBe(0);
        book.Name.Should().Be("test");
    }

    public void Dispose()
    {
        _context.Dispose();
    }
}

You want to test with real db (integration test), modifing your test may be easy solution.

[Fact]
public void CanCreateUser()
{
    string bookName = DateTime.Now + "test book";
    var Book = new BookService(context);


    Data.Database.Entities.Book book = Book.AddNewBook(bookName);
    context.SaveChanges();

    book.Id.Should().NotBe(0);
    book.Name.Should().Be(bookName);
}

Answer 3

Take a look at Respawn. Another option to avoid migrations is to do a database snapshot/restore. Finally, you could, prior to each test, start a new TransactionScope and then call its Dispose() method after the transaction without calling its Complete() method. This will abort the transaction and roll the database back to the state it was in prior to running the test.

Dropping the database is a bit heavy-handed and will likely increase the time it takes to run your tests.