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 fit the model, but this procedure will lead to the loss of existing data. Thus, the function of the migration system is to synchronize it with the data model in the application without losing the 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 a similar choice.

Task

Our company is now actively developing the next generation of the product - Docs Security Suite (DSS). The server part is written in .Net Core, and the Entity Framework Core is used as the DBMS respectively. When designing an 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 his 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, there are some complaints to it, the main of which was that EF lacks a sane approach to resolving version conflicts. This fact still saddens us when bugfixing as part of the 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 DBMS. Required MS SQL Server, PostgreSQL, Oracle, but potentially use other
2. Work with ORM. Initially, it was supposed to use EF Core, but at the design stage other ORMs were ready to consider
3. Autogeneration of migrations. Taking into account the development of Code First, the need to “paint with pens” migration would be desirable to avoid
4. Version conflicts. Under conditions of distributed development, when merging, EF Core can fall into conflicts. This becomes a significant problem, since different parts of the application are created by different developers, so you have to spend a lot of time on each
5. Developed documentation and support. Here, it seems to us, explanations are not needed.
6. Free The criterion is conditional, since the systems are not very expensive 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 choice. 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 made against the classic EF are also relevant to the EF Core.

Thus, pluses for EF Core are highlighted:

• Microsoft support, documentation, including in Russian, huge community
• Auto-generated migrations based on CodeFirst
• Compared to EF 6, a snapshot of the database is no longer stored in EF Core. When working with EF Core in Code First, it is no longer necessary to deploy the database
• As far as Code First is concerned, it is possible to conduct one migration to all required data access providers.
• Regarding providers, it supports PostgreSQL, Oracle, etc., etc., and even - MS SQL Server

And also cons:

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

Dbup


dbup.imtqy.com

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

What are the advantages:

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

And cons:

• With all the variety of supported DBMS, Oracle is not among them.
• Does not interact with ORM
• Writing scripts in T-SQL "pens" is not what we wanted
• Documentation and the community are so-so, although they may not be necessary in the conditions of writing SQL scripts.

RoundhousE


github.com/chucknorris/roundhouse

This migration management tool, distributed under the Apache 2.0 license, as well as the previous one, works on the T-SQL migration engine. Apparently, the developers focused on solving technical problems in terms of supporting the DBMS, and not on creating a comfortable development process.

Pros:

• Supports the necessary DBMS (including Oracle)

Minuses:

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

ThinkingHome.Migrator



A tool for versioning migration of a database schema for a .NET Core platform, distributed under the MIT license. The developer himself wrote about his latest version almost a year ago .

Pros:

• Sharpened under .NET Core
• Implemented a branching migration sequence
• Implemented migration logging

Minuses:

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

In general, the project looks promising, especially if it would have developed, but we had 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, is a migration platform for .NET, similar to Ruby on Rails Migrations. Database schema changes are described in C # classes.

There are pluses:

• Support for the required DBMS
• .NET Core support
• Large developed community
• Conflicts of migrations are solved sequentially - the order of execution is indicated for migrations. In addition, if a conflict arises around a single entity, when merging a code, its solution is made 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 minuses, here:

• No auto generation of migrations
• No communication with EF models
• No database snapshots

What was our choice?

The most heated debates turned around two parameters: auto-generation of migrations and sane conflict resolution. Other factors frightened 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 longer term will bring a much greater number of advantages.

findings

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