Tag Archives: visual studio

A rigorous management of Entity Framework migrations adapted to multiple app deployments

This post follows a question that I asked on stackoverflow several months ago. I did not receive a satisfactory answer at that time. I will expose the solution that we found at Keluro for this problem.

At Keluro, our client app products (for example the VSTO KMailAssistantKBilling and the SPA web apps) communicate with a REST web api. This web api uses EntityFramework 6.0  on top of SQL Server for the persistence. However, some of our web api deployments are not necessarily multitenant. Indeed, we do have multiple clients who do not want to share their infrastructure, they demand an isolated deployment mainly for security and confidentiality reasons. For us, in order to keep things simple, it was important to be sure that all of our client deployments share the same database schema, even if they got into production at a different step during the development of the products. Consequently, we have N deployments of our web api with as many database catalogs. We also want to have all our web api deployments to be up-to-date compared to a stable revision of the source code. To this aim, a continuous build is in charge to update all these web api. Necessarily, the associated databases also need to  be updated automatically. The Entity Framework is able, when the web app starts, to handle the migration process (if needed), the topic of this post is to propose a rigorous methodology to manage the migrations.

Entity Framework Code First supports migrations (see documentation here). To answer the problem explained above, when reading the documentation it is not clear how we should use the migrations. Indeed, we are told to call Update-Database from Visual Studio or to use Enable-Automatic migrations. Let us explain how to use the set of features proposed by Entity Framework to handle multiple deployment in a clean and rigorous way. Note also that this approach works well for local databases that are deployed with your ‘rich client‘  application, for example with SQL Server CE which we use for our VSTO addins KMailAssistant and KBilling.

TLDR; Determine a “stable production schema”:  which is the database schema corresponding to the web app code for a stable branch/tag in your source control. Avoid the so-called AutomaticMigration and always create new code based migration using Add-Migration with respect to an empty database that have been updated to the “stable production schema” after applying all existing migrations. Do not use Update-Database command to update your database in production, let the framework do it for you at startup using the MigrateDatabaseToLatestVersion initializer.  Then, when you release a new environment starts the application with an empty database. You will also have to take care of version control when working with feature branches.

Generating clean code based migrations

For the following, I will assume that you have read the Entity Framework documentation. It is very important that you decide what is the “stable database schema”. It corresponds to the schema determined by the source code (remind that we use EF Code First) for the selected revision in your stable branch or tag. We advise you to avoid the AutomaticMigrations. Actually, AutomaticMigration does not mean that the migrations created will be applied automatically (we will discuss how to do that latter). It means that the migration needed, which is the piece of SQL needed to change the database between its actual stated and what it should be, will be generated and applied on the fly. This is dangerous in our situation, think of our multiple deployments, they have not been started at the same time. Indeed, with automatic migrations some migrations could have been generated and applied for some older client environments while you have to push a new environment right now. Consequently, the history of automatically generated migrations could be different even for the same revision of the source code.

The best solution to avoid this situation is that all deployment share the same series of code based migrations. On a stable source code revision, the succession of existing code migrations applied to an empty database produce a database with the so-called “stable database schema” introduced above. Then, if a new client is deployed, an empty database (no tables, no data) is created and then all existing migrations will be applied automatically by Entity Framework when the web app starts the first time. For example, suppose that we have the following list of migrations: 201401050000000_MigrationA (January 5st 2014), 20150300000000_1MigrationB, 201504120000000_MigrationC, 201511150000000_MigrationD. This means that, if a client web app and its database is put in production on March 2015, all migrations will be applied (including MigrationA and MigrationB).

When using code base migrations, it is important to keep in mind that the migrations are presented as csharp files that represent the SQL instructions to be applied (e.g. drop a table, adding a column etc.). In addition, a given database has also a table _MigrationHistory which keeps all migrations that have been applied to it. Then, if all your web apps are up-to-date with respect to the same web app source code, for all your databases, you will get exactly the same rows in the table _MigrationHistory.

When an application starts, to automatically migrate the associated database to the latest migration, you have to run this code at startup (e.g. Globalasax.cs for an asp.net web app).

Database.SetInitializer(new MigrateDatabaseToLatestVersion<MyDbContext, Configuration>());
Code based list of migrations in Visual Studio

Code based list of migrations in Visual Studio

Keep a clean list of code based migrations

Let us explain how to keep a clean list of code migrations. I suggest to create a clean code based migration anytime a database schema change is required. To do so, you will need to use the Add-Migration command in your Package Manager Console in Visual Studio. Remind that if you do not specify a database connection string in the Powershell command, the connection string used will be the first found in your app.config or web.config file. This selected database may not have the proper “production schema”, it is error prone. My advice is to create only for the generation of this new code migration a database with no data but with the actual “production schema”. This is extremely simple and is also a sanity check of your existing migrations: create a new empty database, in one click in VisualStudio SQL Server Express (see picture below).

Create empty database from Visual Studio

Create empty database from Visual Studio

To update this database schema, take the connection string (right click on your database > Properties) then update the database by targeting the last migration, e.g.201511150000000_MigrationD, with the following command:

Update-Database MigrationD -ConnectionString "<yourConnectionString>" -ConnectionProviderName "System.Data.SqlClient" -Verbose

Then now this local database is “up-to-date” and you can generate your new migration named MigrationE (choose something more meaningful in your case) with the command:

Add-Migration MigrationE -ConnectionString "<yourConnectionString>" -ConnectionProviderName "System.Data.SqlClient" -Verbose

Then the migration files are generated, it is recommended to read them and make sure they correspond to the changes you intended to introduce. Now they are ready to be committed in a single and clean commit. As we have seen the migration is prefixed with number which corresponds to its generation date (e.g. 20150501000000_MigrationE). This number is effectively used by when using the MigrateDatabaseToLatestVersion database initialize and it can be a problem when not carefully used with version control.

Migrations and version control

There may be troubles when branching, to see this let us explain how the Entity Framework applies the code base migrations. Have a look at the table _MigrationHistory, the rows are the migrations, the date when the migration was generated is also there, because it is included in the name of the migration. Entity framework takes the date of most recent migration applied in the _MigrationHistory table and applies all migrations in the web app code that have been generated latter.

The _MigrationHistory table generated by Entity Framework

The _MigrationHistory table generated by Entity Framework

You see the potential problem? Say that you have created two feature branches: X and Y. Suppose that you have generated a migration for each of these branches, for X then for Y. But for some reason, you merged Y into your stable branch before X, the migration of X will not be applied!

To avoid this as much as possible, I suggest that you generate a minimum of migrations and , for each newly generated migration, put it in a dedicated commit with nothing but the code of the migration and with a clean indicator in the commit log (e.g. put a “[MIGRATION]” tag). Remark that the git rebase interactive command of git can be useful (take care when rebasing pushed commits!). For example you can remove all intermediate [MIGRATION] commits and regenerate a single one. or if you decide that a migration (not deployed!) is no longer needed you can drop the commit etc. I think it is wise thing to name a “database” master in your dev team. This person should be the one responsible for merging branches involving database migrations. He will be aware of the potential problem with migrations date generation and will know how to fix it.

Tools for javascript TDD with Visual Studio

In this post I describe the tools that I have selected for efficient development of javascript tests within Visual Studio. Indeed, if you are developing sites with ASP.NET or Apps for Office then you are more or less committed to use Visual Studio. Therefore, you probably do not want to use another editor for the javascript development.

What were my requirements when I chose the tools that I will describe to you in this post? Actually, I needed tools which can provide me the same comfort that I have while I am developing in TDD with .NET. Precisely, the test should be run individually (or at least individually for a given set). They should be run on the continuous integration build, which is teamcity in my case. The test runner may use any browser for executing the tests even if flexibility for using all kind of browsers would be appreciated. In addition, the test should be easily debugged and this is a very important aspect for me.

Let me detail a little this latter requirement. Indeed, I have read recently this blog post and its written that

Debugging the tests
Wiseman said: “If you need to debug unit test then something is wrong with it’s unitness.”
PS: If it’s really needed you can debug it in browser as you normally debug JavaScript code.

Even if the rest of the blog is brilliant, I do not agree at all with this sentence.  If you practice regularly TDD then you’ll see that you have to debug, that’s part of the game. If you never have to debug maybe it’s because you are asserting too much trivialities and not enough your own complex logic. In addition, if you have to create the webpage on your own, for debugging, that is not acceptable to me either. You should be able to put your breakpoint anywhere in your code and hit it in less than five seconds. We will see that the tool chosen permits such quick debugging and that’s the main reason why I am using it.

At the time of the writing, the popular Visual Studio Addin Resharper with its version 8 supports javascript tests with the frameworks Jasmine or QUnit. Unfortunately, the debugger cannot be properly attached while debugging the scripts. Sadly, I had to reject Resharper that I appreciate so much for .NET development.

Finally, the best tool that I found is Chutzpah (which means Audacity in Yeddish). Firstly, there is the Chutzpah runner which is a javascript test runner that uses the headless browser phantomJs, we will invoke it from the command line in the continuous build. Sedondly, there is the Chutzpah Visual Studio extension which enables quick runs and debugging sessions from Visual Studio. Both runner and Visual Studio extension is fully compatible with JS test framework Jasmine 2.0. that I have chosen. Note that I also use the mocking library sinonJs but I won’t discuss it there.

You may retrieve the code samples below on my github repository.

To illustrate these tools we will use a very simple mockup project and we start its description with the following VisualStudio solution.

The Visual Studio solution describing the example of this post.

The Visual Studio solution describing the example of this post.

As usual there are two projects: WebApplication1, the core project, containing the app folder with our javascript logic, especially the one that we would like to test: sut.js (for SystemUnderTest). There is also the test project, WebApplication1.Tests. The file testing the logic contained in sut.js is simply sutTests.js. The testing framework Jasmine is added to the solution by referencing the folder lib/jasmine-2.0.0..

We will take a very simple example, where the object app.sut has a function for computing the factorial of an integer. The code of sut.js can be found in the following snippet.

(Note that in app we have implemented a basic namespacing function such as this one)

Let us now focus on the test suite in the sutTests.js code.

This piece of code is very basic Jasmine syntax for a test suite. We assert the values of the factorial function for the two corner cases where the input is 0 and 1 and we also test the situation with n=5. Remark also that we have a test which asserts that an exception is thrown when a non positive value is passed to the function. We have not covered this situation with the snippet above, consequently, we expect this test to fail… Remark that all the needed references to js files are handled with ///<reference of Visual Studio. By the way, we benefit from Visual Studio Intellisense here.

The Javascript intellisense with Visual Studio

The javascript Intellisense with Visual Studio

In the next screen shot, we run the tests with the Visual Studio Test Explorer that is compatible with Jasmine thanks to Chutzpah.

Run all our test with the Visual Studio Test Explorer. As expected we encounter a failure.

Run all our test with the Visual Studio test explorer. As expected we encounter a failure.

If we were in the situation where we do not know what is going wrong (which is often the case) we would need to debug the test. Unfortunately, like Resharper, you cannot debug the test directly in VisualStudio, the debugger does not attach well. Even Chutzpah creator does not know how we would do that, so I believe we will have to wait to debug javascript test as easily as we debug .NET within visual studio. Then, we will have to debug with a web browser. However, Chutzpah creates the webpage for bootstrapping your failing test. So just by clicking in Open in browser you will have the web page loaded and a link to run the failing test within the browser (see screenshots below).

Chutzpah Visual Studio extension creates web page for running/debugging tests in the web browser.

Chutzpah Visual Studio extension creates web page for running/debugging tests in the web browser.

Then the debugging sessions happens in your browser. Reclicking the links reexecutes the tests.

Then the debugging sessions happens in your browser (here Chrome). Clicking again the links reexecute the tests.

Now, we do have all the material for editing efficiently tests in Visual Studio. Let us have a few words for running the test using the command line on the server. Personally, I embed the Chutzaph runner in a /build directory within my sources and executes the following Powershell script.

Here is what it looks when ran it an Powershell console with teamcity options.

The execution of all javascript tests of the solution with Chutzpah runner using Powershell

The execution of all javascript tests of the solution with Chutzpah runner using Powershell

To conclude, I would say that Chutzpah is a great project and if you need a simple and ready to use test runner I would recommend it. The only limitation for now  would be that the runner only supports phantomJS. However, you may use another another test runner for executing the tests with different browser and you can keep the Chutzpah Visual Studio extension for the development. One last important thing to note is the fact that Chutzpah 3.0 supports RequireJs.