Comparison and selection of data migration systems
The data model tends to change during the development process, and at some point it ceases to correspond to the database. Of course, the database can be deleted, and then the ORM will create a new version that will match the model, but this procedure will lead to the loss of existing data. Thus, the function of the migration system is to ensure that, as a result of a schema change, it is synchronized 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 faced with a similar choice.
Task
Our company is currently 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 application domain model is created by several developers at the same time - each is responsible for his own logical part of the system.
The previous generation of DSS used the classic Entity Framework Migrations (EF 6) as the migration management system. However, some complaints have accumulated against him, the main of which was that EF lacks a sane approach to resolving version conflicts. This fact still upsets us when bugfixing as part 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:
- Support for various DBMS. Mandatory MS SQL Server, PostgreSQL, Oracle, but it is potentially possible to use others
- Working with ORM Initially, EF Core was supposed to be used, but at the design stage, other ORMs were ready to be considered
- Auto generation of migrations. Given the development of Code First, I would like to avoid the need to βpaint with pensβ migrations
- Version conflicts. In a distributed development environment, when merging, EF Core can fall on 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
- Developed documentation and support. Here, it seems to us, no explanation is needed.
- Free. The criterion is conditional, 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:
- EF Core Migrations
- DBup
- RoundhouseE
- ThinkingHome.Migrator
- Fluent Migrator
And now a little more
Naturally, this was the first and main option for choice. A native instrument that works out of the box without any tambourine dancing. A large amount of documentation, official and not so, simplicity, etc. However, the claims made against the classic EF are quite relevant for EF Core as well.
Thus, the pros are highlighted for EF Core:
- Microsoft support, documentation, including in Russian, a huge community
- Auto-generation of migrations based on CodeFirst
- Compared to EF 6, EF Core no longer stores a snapshot of the database. Deploying the database is no longer required when working with EF Core in Code First
- Since we are dancing from Code First, it is possible to conduct one migration to all required data access providers
- As for providers, it supports PostgreSQL, Oracle, etc., etc., and even MS SQL Server ο
And also cons:
- Conflict resolution remains the same. It is necessary to build a sequence of migrations and update database snapshots
- Dependence on models based on which migrations are generated
DbUp
DbUp is a .NET library that is installed by NuGet and helps push changes to SQL Server. It keeps track of which change scripts have already been executed and runs those needed to update the database. The library grew out of an open source blogging engine project on ASP.NET and exists under the MIT license, and the code is on GitHub. Migrations are described using T-SQL.
What are the advantages here:
- Support for a large number of DBMS (MS SQL Server, PstgreSQL, MySQL)
- Since the scripts are written in T-SQL, they look quite simple.
- Conflicts are also resolved with SQL
And the cons:
- With all the variety of supported DBMS, Oracle is not one of them
- Doesn't interact with ORM
- Writing scripts in T-SQL with βhandlesβ is not what we were striving for
- Documentation and community is so-so, although in terms of writing SQL scripts, they may not be needed.
RoundhouseE
This migration management tool, distributed under the Apache 2.0 license, like the previous one, runs on the T-SQL migrations engine. Apparently, the developers focused on solving technical problems in terms of supporting the DBMS, rather than creating a comfortable development process.
Pros:
- Supports required DBMS (including Oracle)
Cons:
- Oracle (as well as Access, which is irrelevant for us) is not supported on .NET Core, only on .NET Full Framework
- Doesn't work with ORM
- Even less documentation than the previous tool
- Again - migrations are written by scripts
ThinkingHome.Migrator
Tool for versioned migration of the database schema to the .NET Core platform, distributed under the MIT license. .
Pros:
- Tailored for .NET Core
- Implemented a branching sequence of migrations
- Implemented migration logging
Cons:
- The last update was 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 developed, but we needed to make a decision here and now.
Fluent Migrator
The most popular migration tool with a large fan base. Distributed under the Apache 2.0 license. As stated in the description, is a migration framework for .NET, similar to Ruby on Rails Migrations. Database schema changes are described in C# classes.
There are pluses here:
- Support for the required DBMS
- .NET Core support
- Large developed community
- Migration conflicts are resolved sequentially - the migration order is specified. In addition, if a conflict arises around one entity, when merging the code, it is resolved 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, then here:
- Missing auto-generation of migrations
- Missing communication with EF models
- No db snapshots
What was our choice?
The hottest debate revolved around two parameters - auto-generation of migrations and sane conflict resolution. Other factors frightened much less. As a result, based on the results 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 much greater number of pluses.
Conclusions
Of course, there are no perfect tools. So we had to prioritize our "Wishlist" to make a choice. However, for other teams and other tasks, other factors may be decisive. We hope this article will help you make your choice.
Source: habr.com