We defined what we want to do in Part 1: Intro. Now, let’s dive into it and start with creating the database.

The Why

If you ask why don’t we use a mock or an in-memory database, then I will admit right at the start that I just don’t believe in using:

something that is not a database or
something that it’s a different kind of database

for testing the code that makes calls to a database. It just doesn’t seem right to me. Honestly, we are actually going to break the second point (but only a bit!) as we will use LocalDB instead of a full SQL Server database.

If we are going to test not just the happy paths, but also the not-so-happy ones, then we should also cover the behaviour of the application in cases such as when user attempts to insert invalid foreign key (ruling out EF Core In-Memory Database).

Another option could be using SQLite. Official EF Core documentation says it’s an option but still recommends to test agains a production database as well. I can imagine more complex project to find it useful to run some tests on SQLite and some on LocalDB, but for me, it was simpler to run tests straight against the LocalDB.

The How

I used Database Project (it comes as a part of SQL Server Data Tools for Visual Studio) to create and store database definition. This project type provides a GUI for definition of database objects and produces SQL scripts that can be executed against the database.

Build of a database project produces a .dacpac file that is essential for our setup. If you use different approach for storing database definition (that doesn’t produce the .dacpac file), this solution unfortunately won’t work for you. But you may still make use of part 3, part 4 and part 5 of this series.

We use Microsoft.SqlServer.DacFx.x64 package to deploy the database from the .dacpac file.

We do the deployment in a DeployDatabase() method which calls Load() method from the DacFx package. It can be almost infinitely parametrised with DacDeployOptions (see the full list of options).

We are interested in four of them:

AllowIncompatiblePlatform – when set to true, it allows you to deploy database project to a different database system, in our case, we want to deploy SQL Server DB to LocalDB.
GenerateSmartDefaults – when set to true, it allows you to deploy a new version of the database with some non-nullable columns added. We don’t care what those values are because we are going to seed them just after the database is deployed.
DropObjectsNotInSource – when set to true, it drops all the objects that are not in the database anymore (e.g. when you change the structure and remove a table).
BlockOnPossibleDataLoss – when set to false, it allows you to drop columns and therefore in the tables (otherwise the deploy would fail).

In our scenario, we assume that we want to either update an existing database or create a new one in case it doesn’t exist. However, the solution below doesn’t clean the existing data, and although the list of deploy options is long, I haven’t been able to find any that would allow me to wipe out the database. Therefore we need to do this ourselves – and we do so in the third part of the series.

public void DeployDatabase()
{
    // load the .dacpac file
    using(var dacPackage = DacPackage.Load(dacpacPath))
    {
        var dacDeployOptions = new DacDeployOptions
        {
            // true so that we can deploy to LocalDB
            AllowIncompatiblePlatform = true, 
            // true so that the deploy works even if we add a new non-nullable column
            GenerateSmartDefaults = true,
            // true so that the old objects are dropped
            DropObjectsNotInSource = true,
            // false so that the deploy succeeds even if some columns are dropped
            BlockOnPossibleDataLoss = false
        };
        
        var instance = new DacServices(connectionString);
        // notice that we set value of upgradeExisting to true
        instance.Deploy(dacPackage, databaseName, upgradeExisting: true, dacDeployOptions);
    }
}

This method of course expects an existing and running LocalDB instance (just run sqllocaldb create [instance name] and sqllocaldb start [instance name] from the command line).

Both GenerateSmartDefaults and DropObjectsNotInSource are needed only if you don’t re-create the database with every test run. (In my case, it was a requirement to use the same database.)

Then you can just remove these options because their default value is false . Also set upgradeExisting parameter of Deploy() method to false. However, bear in mind that if you call DeployDatabase() multiple times during your test run, it will slow it down.

Source Code

You can view the complete source code on Github.

What’s next

Read the next posts in the series:

The previous one was:

Part 1: Intro

Last modified on 2020-10-16