Comparison and selection of data migration systems

Comparison and selection of data migration systems

The data model in the development process has the property to change, and at some point it ceases to correspond to the database. Of course, the database can be deleted, and then ORM will create a new version that will correspond to the model, but such a procedure will lead to the loss of existing data. Thus, the function of the migration system is to ensure that, as a result of changing the scheme, synchronize it with the data model in the application without losing existing data.

In this article, we would like to consider various tools for managing database migrations. We hope this review will be useful for developers who are faced with this choice.

Task

Our company is currently actively developing the next generation of the product - Docs Security Suite (DSS). The server part is written on .Net Core, and Entity Framework Core is respectively used as a DBMS. When designing the application, we use the Code First approach.

The domain model of the application is created by several developers at the same time - each is responsible for its own logical part of the system.

In the previous generation of DSS, the classic Entity Framework Migrations (EF 6) was used as a migration management system. However, some claims have accumulated against it, the main of which was that EF lacks a sane approach to resolving version conflicts. This fact still upsets us when bug fixing within the framework of support, so it was decided to consider alternative options.

As a result of the discussion, the following requirements for the migration management system were formed:

1. Support for various DBMSs. Mandatory MS SQL Server, PostgreSQL, Oracle, but you can potentially use other
2. Work with ORM. Initially, the use of EF Core was supposed, but at the design stage, other ORMs were ready to consider
3. Autogeneration of migrations. Given the development of Code First, I would like to avoid the need to “paint with pens” migrations
4. Version conflicts. In a distributed development environment with merging, EF Core can crash in conflicts. This becomes a significant problem, because different parts of the application are created by different developers, so you have to spend a lot of time for each
5. Advanced documentation and support. Here, it seems to us, no explanation is needed
6. Free of charge. The conditional criterion, since not very expensive systems or expensive, but ideal in convenience, we were also ready to consider

As a result of a small study, the following options were found and considered desirable for consideration:

1. Ef core migrations
2. Dbup
3. RoundhousE
4. ThinkingHome.Migrator
5. Fluent migrator

And now a little more

EntityFramework Core Migrations

Naturally, this was the first and main option for selection. A native tool that works out of the box without any dancing with a tambourine. A large amount of documentation, official and not very, simplicity, etc. However, the claims presented to the classic EF are quite relevant for the EF Core.

Thus, the advantages for EF Core are highlighted:

• Microsoft support, documentation, including in Russian, a huge community
• CodeFirst-based Auto Migration
• Compared to EF 6, the database snapshot is no longer stored in EF Core. When working with EF Core in Code First, you no longer have to deploy a database
• Since we are dancing from Code First - it is possible to conduct one migration to all the required data access providers
• Regarding providers - PostgreSQL, Oracle, etc., etc., etc., and even - MS SQL Server are supported поддерживается

As well as cons:

• Conflict resolution remained at the same level. It is necessary to build a sequence of migrations and update database images
• Dependence on models on the basis of which migrations are generated

DbUp


dbup.github.io

DbUp is a .NET library that is installed by NuGet and helps to roll changes to SQL Server. It keeps track of which change scripts have already been executed, and launches those that are needed to update the database. The library grew out of the ASP.NET open source blog engine project and exists under the MIT license, and the code is on GitHub. Migrations are described using T-SQL.

What are the advantages:

• Support for a large number of DBMSs (MS SQL Server, PstgreSQL, MySQL)
• Since scripts are written in T-SQL, they look pretty simple
• Conflicts are also resolved using SQL

A cons:

• With all the variety of supported DBMSs, Oracle is not among them.
• Does not interact with ORM
• Writing T-SQL scripts with pens is not what we were aiming for
• Documentation and the community are so-so, although they may not be needed in the context of writing SQL scripts.

RoundhousE


github.com/chucknorris/roundhouse

This migration management tool, distributed under the Apache 2.0 license, like the previous one, runs on the T-SQL migration engine. Apparently, the developers focused on solving technical problems regarding DBMS support, rather than creating a comfortable development process.

Pros:

• Supports the necessary DBMS (including Oracle)

Minuses:

• Oracle (as well as Access irrelevant for us) is not supported on .NET Core, only on .NET Full Framework
• Doesn't work with ORM
• There is even less documentation than the previous tool
• Again - migrations are written in scripts

ThinkingHome.Migrator



Tool for versioned migration of the database schema to the .NET Core platform, distributed under the MIT license. The developer himself wrote about the latest version almost a year ago .

Pros:

• Sharpened under .NET Core
• Implemented branching sequence of migrations
• Implemented migration logging

Minuses:

• Last update - a year ago. Apparently, the project is not supported.
• Not supported by Oracle (the article states that this is due to the lack of a stable implementation for .NET Core - but this is a year ago)
• Missing auto-generation of migrations

In general, the project looks promising, especially if it would develop, but we needed to make a decision here and now.

Fluent Migrator


github.com/fluentmigrator/fluentmigrator

The most popular migration tool with a large army of fans. Distributed under the Apache 2.0 license. As stated in the description, it is a migration platform for .NET, similar to Ruby on Rails Migrations. Changes to the database schema are described in classes in C #.

There are pluses:

• Support for the necessary DBMS
• .NET Core Support
• Large developed community
• Conflicts of migrations are solved sequentially - the order of execution is indicated for migrations. In addition, if there is a conflict around one entity, when merging code, its solution is performed in the same way as in the rest of the code
• There are profiles that run after a successful migration. And they can carry service functions.The last update was a month ago, that is, the project lives

As for the cons, here:

• Missing auto-generation of migrations
• No connection with EF models
• No database snapshots

What was our choice?

The most heated debate revolved around two parameters - auto-generation of migrations and sane conflict resolution. Other factors scared much less. As a result, as a result of the discussion, the team decided to use Fluent Migrator in the new project. For the resolution of conflicts in the future will bring a lot more advantages.

conclusions

Of course, there are no perfect tools. So we had to prioritize our “Wishlist” for a choice. However, other factors may be decisive for other teams and other tasks. We hope this article helps you make a choice.