Want Better Unit Tests with In-Memory Databases?

Developing .NET applications that interact with relational databases requires robust unit tests. While mocking is a common approach, it may not always accurately represent real-world database behavior. Using in-memory databases like SQLite for these tests offers several advantages:

• Realism: By using an in-memory relational database, the test environment more closely resembles a production environment, allowing for more realistic testing of database interactions.
• Isolation: Each test has its own isolated database, eliminating dependencies on external environments.
• Cost: Eliminates the need for commercial database licenses.
• Simplicity: Easier to set up compared to a full-fledged database.
• Concurrency: Multiple developers can execute tests simultaneously without conflicts due to the isolation of each test.

It’s important to note that unit tests with in-memory databases do not replace integration tests but complement your testing strategy.

In this article, we’ll explore:

• SQLite Challenges: Let’s use in-memory SQLite and highlight its limitations compared to a full relational DBMS.
• Best practices: How to write effective unit tests with in-memory databases and how to combine them with mocking to simulate external dependencies.
• Practical example: A .NET API with adaptable configuration, using SQLite for testing and a relational database (SQL Server) in a production environment.

SQLite Challenges:

SQLite is a lightweight and fast database, but it has its limitations.

• Data Types: It doesn’t support all data types found in larger databases. For example, JSON can be problematic.
• Advanced Features: It lacks features like partitioning, complex indexes, and distributed transactions, which are essential for more complex applications.
• Scalability: It may struggle with large volumes of data and complex queries.

Best Practices

• Tools and Frameworks: Use tools like Entity Framework Core or Dapper to simplify database configuration and interaction. In our example, we’re using Entity Framework Core, and when instantiating the context, we pass an option specifying where it should point (to the in-memory database or an external database). See the context class for an example of SQLite in-memory and SQL Server:

Context class:
(used in all examples and in the API that will be made available in this post)

using System;
using System.Collections.Generic;
using Microsoft.EntityFrameworkCore;

namespace DBRepository.Model;

public partial class EliasdcDevContext : DbContext
{
    public EliasdcDevContext(DbContextOptions<EliasdcDevContext> options)
        : base(options)
    {
    }

    public virtual DbSet<Car> Car { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        OnModelCreatingPartial(modelBuilder);
    }

    partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
}

Sql Server Example:

public EliasdcDevContext Context { get; private set; }

 public Method()
 {
     DbContextOptions<EliasdcDevContext> options = new DbContextOptionsBuilder<EliasdcDevContext>()         
         .UseSqlServer("Server=xxx;initial catalog=xxx;persist security info=True;user id=xxx;password=xxx;MultipleActiveResultSets=True;Encrypt=False")
         .Options;

     Context = new EliasdcDevContext(options);            
 } 

SQLite In-memory:

public EliasdcDevContext Context { get; private set; }

 public Method()
 {
     DbContextOptions<EliasdcDevContext> options = new DbContextOptionsBuilder<EliasdcDevContext>()         
         .UseSqlite("DataSource=:memory:")
         .Options;

     Context = new EliasdcDevContext(options);            
 } 

In this way, it becomes easy to configure unit tests to point to an in-memory database, while your API, when running, points to another external database. Both use the same context class and its models.

• Initialization and Destruction: Use a testing framework like xUnit or NUnit to create a fixture that initializes the database before each test and destroys it afterward. This ensures a clean environment for each test.
• Specific Configurations: Define the cache size, enable or disable indexes, adjust page size, and other specific configurations for your database and tests.

Example with xUnit:

public class DatabaseFixture : IDisposable
{
    public EliasdcDevContext Context { get; private set; }

    public DatabaseFixture()
    {
        DbContextOptions<EliasdcDevContext> options = new DbContextOptionsBuilder<EliasdcDevContext>()
            /*here you can put other definitions. 
             *Examples:
             *  Cache=Shared: Allows the in-memory database to be shared across multiple connections.
             *  Cache=Private: Creates a private in-memory database for each connection (default).
             *  Foreign Keys=True: Enables foreign key constraint enforcement in SQLite.
            */
            .UseSqlite("DataSource=:memory:")
            .Options;

        Context = new EliasdcDevContext(options);            

        Context.Database.OpenConnection();
        Context.Database.EnsureCreated();
    } 

    public void Dispose()
    {
        Context?.Dispose();
    }
}

• Realistic Data: Generate data that represents real-world scenarios to ensure your tests are comprehensive.
• Test Isolation: In-memory databases simplify test isolation because changes are discarded upon database restart. Nevertheless, in your testing framework, the database might not be restarted after each test. Therefore, it’s crucial to clean up the database either before or after each test to prevent test interference. The API we’ll make available will showcase both approaches.
• Combination of in-memory database with mocks: For unit tests involving interactions with external systems or database features not available in SQLite, the combination of in-memory databases and mocks is essential to simulate these behaviors and ensure full code coverage.

Let’s Get Practical

A sample API was created in .NET 8 using Entity Framework Core with SQL Server and xUnit with SQLite for unit tests. Along with the API on GitHub, I will include a script to create the table in the database and instructions on where to update the connection string (address, username, and password).

GitHub repository: https://github.com/eliasedc/MemoryDBToTest

The API includes Swagger and has just one exposed method called GetCarByName, which essentially returns a list of cars stored in the database, with an option to filter by name.

API Structure

The API is divided into five parts:

1- Shared: Contains classes that can be used across all projects. In this case, it includes a DTO that reflects the Car table.

2- Api: Contains the API configurations and the exposed methods in the CarController.cs class.

3- Domain: Contains the implementation of the exposed methods and is where business rules can be added.

4- Repository: Contains the models and the communication with the database.

5- Test: Contains the unit tests.

When you run the API, Swagger will open automatically, and you can make requests by passing the name of a car to search for. You can use a single letter or a part of the car name. If you used my script, here is the list of cars available in the SQL Server database:

Example request using Swagger:

……………………………………

Initializing the Connection

The context class was shown earlier in this post. Below is the location of the class in the project:

The same context class, EliasdcDevContext.cs, and the model Car.cs are used for both SQL Server and SQLite in-memory connections.

In the 2-Api folder, within the Program.cs file, you can see how the context is configured for SQL Server:

In the 5-Test folder, you can find the DatabaseFixture.cs class, which sets up the unit tests to use SQLite in-memory:

This demonstrates how simple it is to switch your code between different databases using the same context class.

Note: This example uses Entity Framework, but the same concept applies to projects that don’t use this structure and directly query the database. SQLite also allows running raw SQL queries (e.g., INSERT, UPDATE, SELECT, etc.).

Observation: SQLite does not support schemas, like in larger databases. Therefore, queries like “SELECT * FROM schema.Cars” are not allowed. One workaround is to use an underscore to separate the schema and table names: “SELECT * FROM schema_Cars”.

Unit Tests

There are two unit test classes included in the API, with a subtle but important difference between them.

Class UnitTest_IClassFixture

using DBRepository.Model;
using Microsoft.EntityFrameworkCore;
using Shared;
using System.Collections.Generic;
using System.Threading.Tasks;
using Xunit;

namespace Cars.Test
{
    public class UnitTest_IClassFixture : IClassFixture<DatabaseFixture>
    {
        private readonly DatabaseFixture _fixtureDB;
        private Domain.Domain _domain;

        public UnitTest_IClassFixture(DatabaseFixture fixture)
        {
            _fixtureDB = fixture;
            _domain = new Domain.Domain(new DBRepository.DBRepository(_fixtureDB.Context));
        }

        [Theory]
        [InlineData("Accord", 1)]
        [InlineData("n", 2)]
        public async Task ToMustReturnCarByName(string name, int count)
        {
            // Arrange
            // This delete is necessary due to the IClassFixture implementation
            _fixtureDB.Context.Database.ExecuteSqlRaw("DELETE FROM Car");

            List<Car> cars = new List<Car>
            {
                new Car { Name = "Honda Accord", CreatedDate = DateTime.Now },
                new Car { Name = "Subaru Outback", CreatedDate = DateTime.Now.AddDays(-1) },
                new Car { Name = "Ford Mustang", CreatedDate = DateTime.Now.AddDays(-2) }
            };

            _fixtureDB.Context.Car.AddRange(cars);
            await _fixtureDB.Context.SaveChangesAsync();

            // Act
            List<CarDTO> result = await _domain.GetCarByName(name);

            // Assert
            Assert.NotNull(result);
            Assert.Equal(count, result.Count);
        }
    }
}

In this class, the IClassFixture implementation ensures that the DatabaseFixture constructor is called only once, regardless of the number of tests. At the end, the Dispose method is automatically called to release resources.

Class UnitTest_IDisposable

using DBRepository.Model;
using Microsoft.EntityFrameworkCore;
using Shared;
using System.Collections.Generic;
using System.Threading.Tasks;
using Xunit;

namespace Cars.Test
{
    public class UnitTest_IDisposable : IDisposable
    {
        private readonly DatabaseFixture _fixtureDB;
        private Domain.Domain _domain;

        public UnitTest_IDisposable()
        {
            _fixtureDB = new DatabaseFixture();
            _domain = new Domain.Domain(new DBRepository.DBRepository(_fixtureDB.Context));
        }

        [Theory]
        [InlineData("Accord", 1)]
        [InlineData("n", 2)]
        public async Task ToMustReturnCarByName(string name, int count)
        {
            // Arrange
            List<Car> cars = new List<Car>
            {
                new Car { Name = "Honda Accord", CreatedDate = DateTime.Now },
                new Car { Name = "Subaru Outback", CreatedDate = DateTime.Now.AddDays(-1) },
                new Car { Name = "Ford Mustang", CreatedDate = DateTime.Now.AddDays(-2) }
            };

            _fixtureDB.Context.Car.AddRange(cars);
            await _fixtureDB.Context.SaveChangesAsync();

            // Act
            List<CarDTO> result = await _domain.GetCarByName(name);

            // Assert
            Assert.NotNull(result);
            Assert.Equal(count, result.Count);
        }

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

In this class, the IDisposable interface is implemented to ensure the Dispose method is called after each test, cleaning up the in-memory database.

Conclusion

In summary, this API demonstrates how to flexibly integrate different databases using Entity Framework to manage both SQL Server and in-memory SQLite for testing. Throughout this post, we covered the pros and cons of using an in-memory database for unit tests, emphasizing its simplicity and limitations. Additionally, we highlighted the importance of unit testing and how different approaches, such as using IClassFixture and IDisposable, affect the test lifecycle.

That’s it

I hope this example has been helpful and serves as a foundation for your implementations.

See you next time!