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…

Table of Contents

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:

	public class Car
	{
	public int Id { get; set; }
	public string Producer { get; set; }
	public string Model { get; set; }
	public DateTime ProductionDate { get; set; }
	public bool IsOnWarranty { get; set; }

	}

view raw

Car.cs

hosted with ❤ by GitHub

DbContext

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

	public class CarServiceContext : DbContext
	{
	// DB connection properties injected automatically
	public CarServiceContext() {}

	// DB connection properties given explicitly (for Unit Tests)
	public CarServiceContext(DbContextOptions options) : base(options) { }

	public DbSet<Car> Cars { get; set; }

	}

view raw

CarServiceContext.cs

hosted with ❤ by GitHub

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:

	{
	"Logging": {
	"IncludeScopes": false,
	"Debug": {
	"LogLevel": {
	"Default": "Warning"
	}
	},
	"Console": {
	"LogLevel": {
	"Default": "Warning"
	}
	}
	},
	"ConnectionStrings": {
	"DefaultConnection": "Server=(localdb)\\mssqllocaldb;Database=aspnet-Demo;Trusted_Connection=True;MultipleActiveResultSets=true"
	}
	}

view raw

appsettings.json

hosted with ❤ by GitHub

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

	public void ConfigureServices(IServiceCollection services)
	{
	services.AddDbContext<CarServiceContext>(o =>
	o.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));
	services.AddMvc();
	}

view raw

Startup_dbContext.cs

hosted with ❤ by GitHub

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:

	[TestClass]
	public class CarServiceContextInitTests
	{
	// to have the same Configuration object as in Startup
	private IConfigurationRoot _configuration;

	// represents database's configuration
	private DbContextOptions<CarServiceContext> _options;

	public CarServiceContextInitTests()
	{
	var builder = new ConfigurationBuilder()
	.SetBasePath(Directory.GetCurrentDirectory())
	.AddJsonFile("appsettings.json");

	_configuration = builder.Build();
	_options = new DbContextOptionsBuilder<CarServiceContext>()
	.UseSqlServer(_configuration.GetConnectionString("DefaultConnection"))
	.Options;
	}
	}

view raw

CarServiceContextInitTests_ctor.cs

hosted with ❤ by GitHub

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:

	[TestMethod]
	public void InitializeDatabaseWithDataTest()
	{
	using (var context = new CarServiceContext(_options))
	{
	context.Database.EnsureDeleted();
	context.Database.EnsureCreated();

	var car1 = new Car()
	{
	Producer = "Volkswagen",
	Model = "Golf IV",
	ProductionDate = new DateTime(2009, 01, 01),
	IsOnWarranty = false
	};

	var car2 = new Car()
	{
	Producer = "Peugeot",
	Model = "206",
	ProductionDate = new DateTime(2000, 01, 01),
	IsOnWarranty = false
	};

	context.Cars.AddRange(car1, car2);
	context.SaveChanges();
	}
	}

view raw

InitializeDatabaseWithDataTest_method.cs

hosted with ❤ by GitHub

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 🙂

0 0 votes

Article Rating

2 Comments

Oldest

Newest Most Voted

Inline Feedbacks

View all comments

RedDeath

7 years ago

Good article, thanks for the insight.
The only thing I would mention that, from what I’ve read, the:

context.Database.EnsureCreated();

is NOT compatible with Migrations, so I would be very careful of using this on production.
First deploy will go fine, but after that…

Author

Dawid Sibiński

Reply to RedDeath

@RedDeath,
thanks for your comment.
I didn’t really mean to use this approach on production. That’s why I wrote, that the only usage on production I would consider (as I know some people use it) is to compile such Unit Tests project into an executable and run it when your application is installed for the first time – to have database structures initialized. However, I don’t think I’d personally use that on production 🙂

This approach is great during development phase – as least I found it useful for that purpose.

Search Site

Entity Framework Core – database initialization with Unit Test

Code First approach