Using InMemoryDb with Npgsql

[sstillen]

I have an application that uses Npgsql to connect to my database. I wanted to write unit tests for my application’s data layer using Microsoft’s InMemory Database Provider. However, I am running into issues because some of the columns in my database are arrays, which EF Core doesn’t support. Is there any way to use Npgsql purely in memory?

[roji]

Is there any way to use Npgsql purely in memory?

No, Npgsql can only be used to connect to PostgreSQL, which does not have a memory-only mode. Using the InMemory database as a test platform for relational databases is not necessarily a good idea, as the EF Core docs state, especially since it's a non-relational provider.

Nevertheless, you can definitely open an issue in the EF Core repo asking for array support. In the meantime (and even in general), it's recommended to use Npgsql and PostgreSQL for your tests to make sure the behavior being test is identical with what you'll encounter in production.

[weitzhandler]

Thanks for your answer @roji,
What would be a good way to unit test services depending on a DbContext<T> that operates against PG?

[roji]

@weitzhandler this page has some general info on testing EF Core applications: https://docs.microsoft.com/en-us/ef/core/miscellaneous/testing/

As a general rule, I'd default to simply testing against your really database - with PostgreSQL it's very easy to set up test databases and write efficient tests against them. This gives you 100% confidence that everything is behaving as it would in production.

If you want true unit tests (no database access), you can use the repository pattern and wrap your DbContext; this allows you to easily mock your repository.

Finally, there are ways, e.g. to use Dbcontext with the InMemory provider, but these are strongly discouraged as a general rule.

[ilya-chumakov]

@roji let me strongly disagree with you. Our tests for WebApi applications consist of 3 main layers:

1. DAL tests (which use a PostgreSQL db)
2. BL tests (in-memory)
3. REST API tests (in-memory)

Having DAL entirely covered, there is no good reason to use a real db for BL and API tests. No matter how it is set up, a PostgreSQL instance makes tests much slower than an in-memory db, especially for enterprise-grade apps containing thousands of tests.

Then, low speed makes impossible the effective usage of the Continuous Testing technique, when tests are re-runned in the background on developers' workstations.

In fact, InMemory Provider works fine 99% of time now, but the lack of support for advanced Npgsql types, such as arrays and ranges leads to nasty and cumbersome workarounds. For example, now we are discussing complete tsrange / NpgsqlRange ban. In BL tests, it just brings more complexity than takes away.

Hiding EF details behind custom Repository classes is widely considered as an anti-pattern, because EF itself offers enough isolation from db details.

[roji]

Having DAL entirely covered, there is no good reason to use a real db for BL and API tests.

That's true until you need to use raw SQL for some purpose, or some other thing that cannot work in-memory.

No matter how it is set up, a PostgreSQL instance makes tests much slower than an in-memory db, especially for enterprise-grade apps containing thousands of tests.

The Npgsql EF Core provider has about 8000 tests that run against actual PostgreSQL; EF Core itself has tens of thousands. All that runs in a few minutes, no more (thanks to parallelization). In a lot of cases I've seen, tests against real databases are simply architected or executed in a problematic way, which makes them slow, making people think that testing against database is inherently slow...

For example, now we are discussing complete tsrange / NpgsqlRange ban. In BL tests, it just brings more complexity than takes away.

If you're considering avoiding a PostgreSQL feature entirely just because of difficulties it presents for in-memory testing, that's very sad indeed (and somewhat backwards reasoning). I'd really consider looking again at database testing, at least for those specific features.

Hiding EF details behind custom Repository classes is widely considered as an anti-pattern, because EF itself offers enough isolation from db details.

The repository pattern isn't only for DB details isolation; it can encapsulate higher-level operations (which may span multiple EF queries and/or updates), and help avoid repetition of the same LINQ query in multiple queries.

But having said all that, if InMemory is working great for you, then I definitely am not trying to convince you to drop it. I just hope you don't rule out testing whatever makes sense against PostgreSQL (e.g. things which for some reason can't be tested in-memory), and also explore possibilities of efficiently testing against your real database. Many are doing that successfully for large numbers of tests.

[weitzhandler]

I wish IDbSet<T> wasn't cancelled. It was easier to write business-logic tests concentrating on the SUT.

BTW @ilya-chumakov, here's a related discussion

[losbaltica]

You can add your own convention to support in-memory arrays.

See the solution provided by @ajcvickers in following issue

And then you can add for example flag for running "InMemory" or not.
eg.

    private readonly bool _isInMemory;
    public DbContext()
    {
    }

    public DbContext(DbContextOptions<UvsProcessingDbContext> options, 
        bool isInMemory = false)
        : base(options)
    {
        _isInMemory = isInMemory;
    }

    private struct ArrayWrapper
    {
        public ArrayWrapper(string[] values)
            => Values = values;

        public string[] Values { get; }
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
            if (_isInMemory)
            {
                modelBuilder
                .Entity<Blog>().Property(e => e.TileId)
               .HasConversion(
                  v => new ArrayWrapper(v),
                  v => v.Values);
            }
    }

[ilya-chumakov]

@losbaltica unfortunately it doesn't work for NpgsqlRange<DateTime>. A query like this still throws a NotSupportedException if is runned against in-memory db:

ctx.Set<Foo>().FirstAsync(x => x.FooDtRangeProperty.Contains(DateTime.UtcNow))

the exception message:

Contains is only intended for use via SQL translation as part of an EF Core LINQ query.

Sadly, it even breaks OData queries:

"42703: column $it.Foo.FooDtRangeProperty does not exist",

But your timing is perfect :) I've just returned here today looking for a workaround for NpgsqlRange<T> (no idea yet)

Moreover, @roji, could you tell if support for Contains and similar operators can be added on npgsql side? If yes, would you accept a PR?

[roji]

@ilya-chumakov are you asking about adding a C# implementation to NpgsqlRange.Contains, so that it can be used outside of translating to PostgreSQL? If so, that's generally not a direction we want to go in - see this breaking change note in EF.

tl;dr we don't want to attempt to replicate PostgreSQL operations in C# - doing so is a lot of work and it's hard to be sure we do it exactly right. It's recommended you consider testing against PostgreSQL rather than with InMemory.

BTW am not sure if your OData query failure is related to the above - if possible, try isolating the query expression tree produced by OData and open a new issue for that.

[ilya-chumakov]

@roji yes, I asked about adding a C# implementation to NpgsqlRange.Contains, making this abstraction not so leaked.
What are the benefits of using a special range datatype in business logic, if it still forces statements like x.LowerBound <= time && time <= x.UpperBound for entity evaluation instead of encapsulating it in a simple Contains?

Unfortunately, the direction you want to go in will probably make InMemory provider unusable for unit testing. If DAL is isolated (which would require a complete architecture rework in my case), then things can be done without InMemory at all.

Btw, in addition to speed (arguable, I see), InMemory provider has another advantage - isolation. Using it, business logic tests can run anywhere, anytime. On a Windows home station, on a Linux build server, inside a Docker container etc.
Adding a postgresql instance to all of that makes things more fragile with no benefits for business logic... again.

[roji]

There was no intention in making NpgsqlRange.Contains an abstraction (leaky or otherwise) - it's simply a method that's there for translation when executing queries against PostgreSQL. This is no different from any other PostgreSQL-specific EF.Functions method (see this page and the others). For NpgsqlRange specifically the decision was made to add extension methods rather than EF.Functions, since it's already an Npgsql-specific type; but that doesn't really matter much.

Put another way, why is the lack of NpgsqlRange.Contains on InMemory any worse than InMemory not supporting transactions, or raw SQL queries? InMemory simply isn't supposed to be a full relational database or support PostgreSQL.

If we start going down the path of providing C# implementations for every bit of PostgreSQL functionality people may need, we're pretty quickly going to find ourselves implementing full-text search, thinking about discrete and non-discrete ranges (crucial for Contains), or accounting for the differences between the PG spatial implementation and a .NET implementation. That's simply not tenable, and it would only be for testing/mocking purposes.

Btw, in addition to speed (arguable, I see), InMemory provider has another advantage - isolation. Using it, business logic tests can run anywhere, anytime. On a Windows home station, on a Linux build server, inside a Docker container etc.

If you're looking to mock your data layer, you should probably look into the repository pattern. Put actual database operations behind .NET methods, which themselves can do anything they want against PostgreSQL. The mock those methods (i.e. the repository).

Once again, even if we were to provide a .NET implementation specifically for NpgsqlRange.Contains, your data logic would fail in various ways because InMemory is neither PostgreSQL, nor a relational database.