5. Quickly create empty database

5. Helpers for creating an empty database, and deleting SQL unit test databases

The standard way of creating an empty database with the latest schema to match EF Core's model of the database is to run the following code.

context.Database.EnsureDeleted();
context.Database.EnsureCreated();

This is totally foolproof, and you can use it if you want, but it takes a LONG time (10 seconds on my PC). That is a really painful amount of time to wait for a unit test to set itself up, especially when you need to run the test lots of times to debug things. I have therefore come up with another approach.

The CreateEmptyViaWipe extension method

The CreateEmptyViaWipe extension method will ensure the database is created and then it will use a 'safe' and quick way to wipe all the rows in all the tables. (How I do that is complicated, and I describe it in my book, but not here). Suffice to say it takes something like 100ms, which is 100 times faster than the 'foolproof' method, but does have some downside, which I will cover. First, here is an example of it being used.

[Fact]
public void TestExampleSqlDatabaseOk()
{
    //SETUP
    var options = this.CreateUniqueClassOptions<EfCoreContext>();
    using (var context = new EfCoreContext(options))
    {
        context.CreateEmptyViaWipe();

        //ATTEMPT
        context.SeedDatabaseFourBooks();

        //VERIFY
        context.Books.Count().ShouldEqual(4);
    }
}

The CreateEmptyViaWipe extension method needs a DbContext as it first parameter. It also has other parameters relating to the wiping of the database. I suggest you look at the WipeDbViaSql for more details.

The downsides of the CreateEmptyViaWipe method and how to get round them.

There are two downsides of the CreateEmptyViaWipe method. They are

1. My database wipe can't handle certain complex database schemas. If it has a problem it will throw an exception and tell you why it can't wipe your database. Then you are going to have to write some extra code to fix this.
2. To save time it only creates the database if there isn't one already. So, if you change your DbContext or entity classes then the schema of the test database is incorrect. The solution to this is the DeleteAllUnitTestDatabases method.

The DeleteAllUnitTestDatabases extension method

This method will delete ALL the databases that start with the database name in you appsettings.json file (see 3. Creating connection strings).

Here is a unit test, guarded by the attribute [RunnableInDebugOnly], that you should run after a change to your DbContext or entity classes.

//Run this method to wipe ALL the test databases using your appsetting.json connection string
//You need to run it in debug mode - that stops it being run when you "run all" unit tests
[RunnableInDebugOnly]
public void DeleteAllTestDatabasesOk() 
{
    var numDeleted = DatabaseTidyHelper 
        .DeleteAllUnitTestDatabases();
    _output.WriteLine( "This deleted {0} databases.", numDeleted);
}

Once DeleteAllUnitTestDatabases has finished then when you run your unit tests they will create a new database, which will have the new schema defined by the current DbContext or entity classes.