Efcore: Testing migrations using SQLite in-memory database

[VictorIranzo]

Problem to solve

In my organization, loads of migrations using EF Core are generated over the same database. Thanks to the use of Git branches, when branches are merged to master sometimes occurs that one migration cannot be applied. The reason is most of the time a programmer fault. For example, when a field of an entity has been added in master but no migration was done, and then from different branches a new migration is generated, they generate a similar migration where the new column is added. When this occurs, the second time that the ‘Add column’ instruction is executed will fail.

Our decision was to add some tests in order to guarantee: first, that a migration is created when the model differs from the model snapshot and second, that the existing migrations can be executed in order from the first one.

For the second mentioned test, a database is created and the migrations are applied in order. The code used for this is similar to the one that can be found in this repo.

In the majority of our tests we use an SQLite in-memory database. However, at this test we can not use it because an error appears (the provided repo reproduces the error when the Program class is executed):

Microsoft.Data.Sqlite.SqliteException: 'SQLite Error 1: 'table "__EFMigrationsHistory" already exists'.'
   at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
   at Microsoft.Data.Sqlite.SqliteCommand.<PrepareAndEnumerateStatements>d__64.MoveNext()
   at Microsoft.Data.Sqlite.SqliteCommand.<GetStatements>d__54.MoveNext()
   at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at MigrationsSqlite.Program.Main(String[] args) in MigrationsSqlite\MigrationsSqlite\Program.cs:line 39

Our solution relies on using a LocalDB, generating the MDF file in a controlled location and remove the generated files after the test execution, but the performance is not as good as desired.

We have checked similar issues related with testing migrations using the InMemory provider, like this. However, our case is different because we would like to use SQLite, despite all the limitations related to the supported migrations.

Desired solution

After this background, our question is:

¿Are there plans to support testing migrations using a SQLite in-memory database?
¿Are there any other possible approaches for testing migrations?

[ErikEJ]

What performance do you desire with LoclDB? (I am using it for integration testing with good results)

[ajcvickers]

@VictorIranzo This should work as long as management of the SQLite connection is done correctly. Specifically, the in-memory database is deleted by SQLite when there are no open connections. This means that you'll have to maintain an open connection for the time that the database is being used. Issue #16103 is tracking making this easier from EF.

[VictorIranzo]

@ErikEJ, in the EF Core Context of the project we have many entities. Of course, the performance depends on many aspects. For example, you can use a unique LocalDB and reuse it along all your tests and this will reduce some time penalties because methods like EnsureCreated() or EnsureDeleted() are invoked only once. However, our criteria is creating a different in-memory database at every test.

I have checked informally (using 2 tests that only invoke EnsureCreated() and EnsureDeleted() for our context) the time of using LocalDB and in-memory SQLite. The first one lasts 12 seconds while the second needs 9 seconds. Maybe, if more operations are done the difference could be greater. I think that this justifies the existance of the InMemory provider.

[VictorIranzo]

@ajcvickers, I think that at my repo I'm managing well the connection. The reason to argue this is that the termination of the Main method here depends on using one SQLite connection or other:
1) If I use the following new SqliteConnection("Data Source=Database;Mode=Memory;Cache=Shared"), the error that appears is the mentioned when the second migration is applied: 'SQLite Error 1: 'table "__EFMigrationsHistory" already exists'.'
2) If I use an SQLite database into a DB file (new SqliteConnection("Data Source=MyDatabase.db;Cache=Shared")), the program terminates.

The code in both cases is the same. ¿Should I manage the SqliteConnection in a different way if it is in-memory?

[ajcvickers]

@VictorIranzo Thanks--I agree this isn't working.
@bricelam Can you take a look?

[klausenbusk]

Our decision was to add some tests in order to guarantee: first, that a migration is created when the model differs from the model snapshot and second

I'm wondering if there should be a builtin way to check if the model and snapshot match. What was your solution?

Edit: See https://github.com/aspnet/EntityFrameworkCore/issues/577 + https://stackoverflow.com/questions/47091587/check-if-applied-migrations-match-the-dbcontext

[bricelam]

a builtin way to check if the model and snapshot match

https://github.com/aspnet/AspNetCore/blob/d34dc80e02161369908d8560f550e6e2822807b3/src/Middleware/Diagnostics.EntityFrameworkCore/src/DatabaseErrorPageMiddleware.cs#L138-L147

[bricelam]

Investigated; this is a bug. Mode=Memory isn't being handled here:
https://github.com/aspnet/EntityFrameworkCore/blob/0f8d0e92f8784b839fe4991bf1274267cb75626d/src/EFCore.Sqlite.Core/Storage/Internal/SqliteRelationalConnection.cs#L100
It works if you use connection string Data Source=:memory:

[klausenbusk]

https://github.com/aspnet/AspNetCore/blob/d34dc80e02161369908d8560f550e6e2822807b3/src/Middleware/Diagnostics.EntityFrameworkCore/src/DatabaseErrorPageMiddleware.cs#L138-L147

Thanks! I look forward to https://github.com/aspnet/EntityFrameworkCore/pull/14122, it is a bit easier just to run a few commands as part of our CI.

Investigated; this is a bug. Mode=Memory isn't being handled here:

Will the fix be backported to 2.2?

[bricelam]

Will the fix be backported to 2.2?

Unlikely. But 3.1 is scheduled to release in December (the same month 2.2 goes out of support)