Unable to replace AppDbContext settings for production with in-memory database for integration test

Question

I have made a minimalized code to illustrate the problem at my github repo: https://github.com/suugbut/MiniTest/tree/main.

I am learning integration test to test my minimal api. I cannot replace AppDbContext settings for production with in-memory database for integration test. I get the following errors:

 Api.Test.TodoEndpoint_IntegrationTest.NumberOfTodos_MustBe_Two
   Source: TodoEndpoint_IntegrationTest.cs line 44
   Duration: 1.1 sec

  Message: 
Microsoft.Data.Sqlite.SqliteException : SQLite Error 1: 'no such table: Todos'.

  Stack Trace: 
SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
SqliteCommand.PrepareAndEnumerateStatements()+MoveNext()
SqliteCommand.GetStatements()+MoveNext()
SqliteDataReader.NextResult()
SqliteCommand.ExecuteReader(CommandBehavior behavior)
SqliteCommand.ExecuteDbDataReader(CommandBehavior behavior)
RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
Enumerator.InitializeReader(Enumerator enumerator)
<>c.<MoveNext>b__21_0(DbContext _, Enumerator enumerator)
NonRetryingExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
Enumerator.MoveNext()
Enumerable.TryGetSingle[TSource](IEnumerable`1 source, Boolean& found)
lambda_method157(Closure, QueryContext)
QueryCompiler.Execute[TResult](Expression query)
EntityQueryProvider.Execute[TResult](Expression expression)
TodoEndpoint_IntegrationTest.NumberOfTodos_MustBe_Two() line 50
RuntimeMethodHandle.InvokeMethod(Object target, Void** arguments, Signature sig, Boolean isConstructor)
MethodBaseInvoker.InvokeWithNoArgs(Object obj, BindingFlags invokeAttr)

`AppDbContext` for production

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddDbContext<AppDbContext>(options =>
{
    var constr = builder.Configuration.GetConnectionString("DefaultConnection");
    options.UseSqlite(constr);
});

builder.Services.AddScoped<TodoRepo>();

var app = builder.Build();
// Others are removed for the sake of simplicity.

"ConnectionStrings": {
  "DefaultConnection": "DataSource=Api.db"
}

If you want to inspect Program.cs, navigate to https://github.com/suugbut/MiniTest/blob/main/Api/Program.cs.

`AppDbContext` for integration test

public sealed class CustomWebApplicationFactory : WebApplicationFactory<Program>
{
    protected override void ConfigureWebHost(IWebHostBuilder builder)
    {
        base.ConfigureWebHost(builder);
        builder.ConfigureServices(isc =>
        {
            var descriptor = isc.SingleOrDefault(d => d.ServiceType == typeof(DbContextOptions<AppDbContext>));

            if (descriptor != null)
            {
                isc.Remove(descriptor);
            }

            isc.AddDbContext<AppDbContext>(options =>
            {
                var connection = new SqliteConnection("DataSource=:memory:");
                connection.Open();
                options.UseSqlite(connection);
            });

        });
    }

    // Others are removed for the sake of simplicity.
}

If you want to inspect CustomWebApplicationFactory.cs, navigate to https://github.com/suugbut/MiniTest/blob/main/Api.Test/CustomWebApplicationFactory.cs

Integration test

// Dependencies are removed for the sake of simplicity.

[Theory]
[InlineData(1)]
[InlineData(2)]
public async Task GetTodoById_Returns_OK(int id)
{
    // act
    var response = await _client.GetAsync($"/todos/{id}");

    // assert
    Assert.Equal(HttpStatusCode.OK, response.StatusCode);

    var content = await response.Content.ReadAsStringAsync();

    Assert.NotNull(content);
}

[Theory]
[InlineData(3)]
public async Task GetTodoById_Returns_NotFound(int id)
{
    // act
    var response = await _client.GetAsync($"/todos/{id}");

    // assert
    Assert.Equal(HttpStatusCode.NotFound, response.StatusCode);
}

[Fact]
public void NumberOfTodos_MustBe_Two()
{
    using (var scope = _factory.Services.CreateScope())
    {
        if (scope.ServiceProvider.GetRequiredService<AppDbContext>() is AppDbContext context)
        {
            var count = context.Todos.Count();
            Assert.Equal(2, count);
        }
    }
}

You can also inspect this test at https://github.com/suugbut/MiniTest/blob/main/Api.Test/TodoEndpoint_IntegrationTest.cs

In-memory databases are not suitable as like-for-like replacements for production databases and will have limits to what kinds of tests you can use them for, For tests that do not work with in-memory you should use a replica seeded database that is restored prior to the test run. This will obviously be more time consuming than unit tests, but run as part of continuous integration or prior to a release candidate build. — Steve Py
– Steve Py, Commented Jan 7, 2024 at 7:25
The error message indicates that actually you have successfully replaced connection with Sqlite in-memory. However the database is empty, so at some point you have to call EnsureCreated or Migrate in order to get tables created there. See official docs example for Testing with SQLite in-memory — Ivan Stoev
– Ivan Stoev, Commented Jan 7, 2024 at 8:36

Guru Stron · Accepted Answer · 2024-01-07 12:33:22Z

1

The following:

isc.AddDbContext<AppDbContext>(options =>
{
    var connection = new SqliteConnection("DataSource=:memory:");
    connection.Open();
    options.UseSqlite(connection);
});

Will result in new connection created multiple times and SQLite in-memory is transient, i.e. as the docs state:

The database ceases to exist as soon as the database connection is closed. Every :memory: database is distinct from every other.

So all the setup you have done (i.e. creating database and seeding) will not be persisted to the next opened connection.

You can fix this for example by simply moving connection creation outside the context setup lambda:

var connection = new SqliteConnection("DataSource=:memory:");
connection.Open();
isc.AddDbContext<AppDbContext>(options =>
{
                
    options.UseSqlite(connection);
});

edited Jan 7, 2024 at 12:33

answered Jan 7, 2024 at 12:15

Guru Stron

151k11 gold badges186 silver badges232 bronze badges

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

Collectives™ on Stack Overflow

Unable to replace AppDbContext settings for production with in-memory database for integration test

`AppDbContext` for production

`AppDbContext` for integration test

Integration test

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

AppDbContext for production

AppDbContext for integration test

Integration test

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related

`AppDbContext` for production

`AppDbContext` for integration test