I’ve recently been presented a concept of initializing the database (creating or re-creating it) with Unit Test method. Initially I thought it’s a non-sense, but after a while of taking a deeper look…



Code First approach

The method of initializing the database I mentioned was used with Entity Framework Core in ASP.NET Core project, where Code First database creation approach was used. As you know, this approach implies that we create models (classes) representing our database entities in the code first and then, using an ORM system (such as EF), database structures are created.

This is very convenient, especially in prototyping. I’ve developed few small or average-size ASP.NET apps and I always used Code First. However, I cannot say how it works on production as these apps were university or pet projects which I’ve never deployed on real customer’s environment. What I noticed is that creating entities using this approach is fast and quite easy.

Database initialization in development phase

As long as your project is in development phase, different developers are working on it and there is some database behind, but the data itself is not very important (you only need the database structure – there’s no production data in it yet), programmers often need to have the database (re)created. To make this process quick and easy, instead of using Migrations straightaway, you can define your models, DbContext and write a Unit Test method which initializes the database. Then, each developer working on the project only needs to re-run this Unit Test to have the database created.





What’s more, as soon as another programmer makes any change in any of the models, the others just need to re-run the Unit Test which re-creates the database and potentially fills it with sample data. There’s no need to keep any migration files/scripts in the development phase.





The following subsections present how to do that.





Examples are based on a simple ASP.NET Core MVC application called CarServiceMvc. I’ve used .NET Core 2.0 Preview 2 and Visual Studio 2017 15.3.0 Preview 3.0.

The whole source code is available on GitHub.

Models

First, let’s create a simple model in our application. The following class is created in Models folder:

DbContext

Next, let’s add CarServiceContext to our application. Let’s store it in ORM folder.

Constructor taking DbContextOptions has been created for Unit Tests purpose.

We also have a DbSet of Cars.





We also need to add a connection string to appsettings.json. For the development phase we can use mssqllocaldb included within Visual Studio:

In the end, Startup.cs should be modified to have our DbContext registered:

Now we can add a new controller for the Car model we created, so Visual Studio scaffolds all basic views. However, if we try to launch the application calling one of CarsController‘s methods, we get the following exception:

So, let’s see how we can initialize our database with Unit Test.

Database initialization Unit Test

Firstly, new project should be added to our solution. Let’s add a new one of type Unit Test Project (.NET Core) and call it CarsServiceMvc.Tests.





We would like to use DefaultConnection connection string defined in the main project. For that purpose, we right click on the Tests project, select Add -> Existing item …, go back to the main project’s folder and select appsettings.json file, adding it as a link:

This file should also be copied to the output directory of Tests project (it can be set in the Properties of the linked file).





Now we can add a new TestClass called CarServiceContextInitTests which will contain our db initialization Unit Test. In order to be able to access the same database as in the main project, we need to create few properties and configure necessary settings in the constructor of the test class:

As the appsettings.json file is copied to the Tests project’s output directory, we can read its contents with ConfigurationBuilder class. After, we just initialize _options property with the db connection settings.





Finally we can add the desired Unit Test method:

There are few important parts of this code:





Line 4: we used _options property to configure the CarServiceContext thanks to its second constructor



Lines 6 and 7: EnsureDeleted() method is called before EnsureCreated(), which makes the db being re-created every time Unit Test is run.





After, there’s just creation of two Car objects and inserting these into the database.





After this Unit Test is run, Cars table is created in the local database with two sample rows. When we launch the application there’s no more error:

From now on, every developer who starts working on the project only executes our unit test and his/her local database is created and filled with sample data.





Moreover, when there’s any change in the model class, there’s just one Unit Test to modify (e.g. if Required column is added to the model) and the other programmers just re-execute the Unit Test and database is up-to-date and ready to work with.

Database initialization on production

The Unit Test approach of database initialization could also be used on production environment, for instance when the application is to be installed for the first time on customer’s environment. We can create another Unit Test which creates the database structures, but doesn’t fill it with any data in a separate project. Then such Unit Tests project can be compiled to an executable and launched on production environment.





I know people who used it on production, but I don’t think I’ be brave enough to go that far 🙂

Summary

The database initialization with Unit Test is kind of a new approach I’ve never been thinking about. In my opinion it could be used with success during development phase, when the data in the database is not a production one. As soon as customer starts creating some business data in the application, it would probably make less sense to drop and create brand new database each time a developer starts working on the project (most of the bugs/issues we solve on production are dependent from the business data in the application).





Did you know this approach of creating the database with Unit Test? What are the techniques you use to initialize the database when working with other developers? Let me know, I’m deeply interested how it looks on your daily basis 🙂