I’m a big fan of unit tests. But not everything can and should be unit tested. Also I do love CQRS architecture – it provides awesome separation of read and writes and isolates each read from the next read via query classes. Usually my queries are reading data from a database, though they can use any persistence i.e. files. About 90% of my queries are run against database with Entity Framework or with some micro ORM (currently I’m a fan of PetaPoco, though Dapper is also grand).

And no amount of stubs/mocks or isolating frameworks will help you with testing your database-access layer without actual database. To test your database-related code you need to have a database. Full stop, don’t even argue about this. If you say you can do unit-tests for your db-layer, I say your tests are worth nothing.

A while ago I’ve blogged about integration tests and that article seems to be quite popular – in top 10 by visitors in the last 2 years. So I decided to write an update.

VSTS and SQL Server

These days my primary build server is provided by Visual Studio Team Service. I used to have TeamCity, but can’t be bothered maintaining a server and install updates. Problem with hosted service – you can’t really log in into a build agent and install a SQL Server. Luckily VSTS build agents already come with SQL Server already installed and this is awesome news.

In 2016 I prefer to keep my tests-database in LocalDB – which is a lightweight version of SQL Server. The benefit of it – your connection string can be the same locally and in your build server – you are in control of the server instance.

VSTS Build Process

To use LocalDB in VSTS you need to first start it. Execute this:

"C:\Program Files\Microsoft SQL Server\120\Tools\Binn\SqlLocalDB.exe" create "v12.0" 12.0 -s

This will create and start LocalDB server instance named “v12.0”. Second “12.0” refers to the version of LocalDB – 2014. Name for this instance is not random – this instance is created for you by Visual Studio (or SQL Server?) on your development machine – chances that you already have it locally are high, so let’s keep it.

You can execute arbitrary commands in VS with “Command Line” build step provided by VSTS. Bear in mind that we have only created and started a database server and named it “v12.0”. We have not created any databases yet.

So your connection string now will look like this: data source=(LocalDb)\v12.0; – this will connect you to master database and you’ll be able to Create Database MyDatabase and then use MyDatabase; GO and then do whatever you need to do with the DB for testing – see above for the link to the previous article.

Cake Build

Also in 2016 I fall in love with Cake Build. This is a cross-platform build automation system. And it has a lot of power to offer. The best thing about it – it is C# and I already know C#. And you should totally check it out an use for your next project!

Now I’m slowly migrating all my projects into cake build scripts instead of VSTS build steps. This allows me to run the same build locally and on other build systems.

Also I’ve build a plugin for Cake to work with SQL Server: check it out on GitHub. Plugin does not do much just now, but already allows to start LocalDB instance – just the same as we’ve done above. And you can create databases and run SQL scripts. So my default action before running tests would be to start an instance of LocalDB, create a database there, create schema from files, then pass the control over to NUnit/xUnit.net. So my build.cake scripts look like this:

#addin "nuget:?package=Cake.SqlServer"

// other steps


Task("Create-DB-And-Schema")
    .Description("Creates database and installs schema")
    .Does(() => 
    {
        LocalDbCreateInstance("v12.0", LocalDbVersion.V12);

        var masterConnectionString = @"data source=(LocalDb)\v12.0;";
        var dbConnectionString = @"data source=(LocalDb)\v12.0;Database=MyProject-Testing";

        DropAndCreateDatabase(masterConnectionString, "MyProject-Testing");
        ExecuteSqlFile(dbConnectionString, "./src/SqlFiles/Install.sql");
    });

Task("Run-Unit-Tests")
    .IsDependentOn("Build")
    .IsDependentOn("Create-DB-And-Schema")
    .Does(() =>
    {
        XUnit2("./src/**/bin/" + configuration + "/Tests.dll");
    });

Here we have Task “Create-DB-And-Schema” that depends on successful execution of step “Build” (not shown here). This step creates LocalDB instance, then creates database “MyProject-Testing” and then executes sql commands from Install.sql file. You can also create database inside of Install.sql file, but that file is usually what I use to set up production environment and database usually exists.

This listing above is adapted version of what I run for my new project NSaga – you can see the whole build script on GitHub. I’m building this project on AppVeyor, but the same will be executed if I’m running on VSTS.

Part-Migrate

Adding another moving part (Cake Build) into your build pipeline can be too involved. But you don’t have to move your entire build process into Cake – you can do step by step. One of my recent small ad-hoc projects uses mostly VSTS steps (because reasons), but for bringing up my database I use Cake. The whole of the cake script is like this (I only change the db-names, nothing else):

Then this script is called by VSTS before I run tests. My tests are provided by hard-coded connection string: data source=(localdb)\v12.0;Database=MyProject-Testing.

Conclusion

I’m doing (more) integration tests after the initial post in 2014. I do this now mostly without Entity Framework because my projects are smaller and DB is not under my control. And I’m using Cake Build because it is awesome and allows to automate your development chores.