Comparison and selection of data migration systems
The data model in the development process is subject to change, and at some point, it may no longer align with the database. Of course, the database can be deleted, and then ORM will create a new version that corresponds to the model, but this procedure will lead to the loss of existing data. Therefore, the migration system's function is to synchronize the schema with the data model in the application due to changes without losing existing data.
In this article, we would like to examine various tools for managing database migrations. We hope this overview will be useful for developers facing such choices.
Task
Our company is currently actively developing the next generation product – Docs Security Suite (DSS). The server side is being developed in .Net Core, and Entity Framework Core is used as the DBMS accordingly. We are applying the Code First approach in the application design.
The domain model of the application is created by several developers simultaneously – each responsible for their logical part of the system.
In the previous generation of DSS, the classic Entity Framework Migrations (EF 6) was used as the migration management system. However, some complaints have accumulated regarding it, the main one being that EF lacks a reasonable approach to resolving version conflicts. This fact still bothers us during bug fixing in the framework of support, so it was decided to consider alternative options.
As a result of discussions, the following requirements for the migration management system were formed:
- Support for various DBMSs. MS SQL Server, PostgreSQL, and Oracle are mandatory, but potentially other systems may also be used.
- Integration with ORM. Initially, the use of EF Core was assumed; however, at the design stage, we were ready to consider other ORMs as well.
- Auto-generation of migrations. Given the Code First development, we would like to avoid the need to manually write migrations.
- Version conflicts. In a distributed development environment, EF Core may fail due to conflicts during merging. This becomes a significant problem since different parts of the application are created by different developers, requiring extensive time for each.
- Comprehensive documentation and support. Here, we believe, further explanations are unnecessary.
- Free of charge. This criterion is conditional, as we are also ready to consider not very expensive systems or expensive ones that excel in convenience.
As a result of a brief investigation, the following options were identified and deemed desirable for consideration:
- EF Core Migrations
- DBup
- RoundhousE
- ThinkingHome.Migrator
- Fluent Migrator
Now, a bit more detail
Naturally, this was the first and primary option to choose. A native tool that works out of the box without any extra hassle. A large amount of documentation, both official and unofficial, simplicity, etc. However, the issues raised regarding traditional EF are just as relevant for EF Core.
Thus, the advantages of EF Core include:
- Microsoft support, documentation, including in Russian, a huge community
- Automatic migration generation based on CodeFirst
- Compared to EF 6, EF Core no longer stores a snapshot of the database. When working with EF Core in Code First, it is no longer necessary to deploy the database.
- Since we are starting from Code First, it is possible to maintain one migration for all required data access providers.
- Regarding providers—both PostgreSQL and Oracle are supported, etc., and even—MS SQL Server :)
And here are the disadvantages:
- Conflict resolution remains at the same level. It is necessary to establish the sequence of migrations and update database snapshots.
- Dependence on the models based on which migrations are generated.
DbUp
DbUp is a .NET library that is installed via NuGet and helps apply changes to SQL Server. It tracks which change scripts have already been executed and runs those necessary for updating the database. The library grew out of an open-source blogging engine project based on ASP.NET and is available under the MIT license, with the code hosted on GitHub. Migrations are described using T-SQL.
Here are some advantages:
- Support for a wide range of DBMS (MS SQL Server, PostgreSQL, MySQL)
- Since the scripts are written in T-SQL, they appear quite straightforward.
- Conflicts are also resolved using SQL.
And the disadvantages:
- Despite the variety of supported DBMS, Oracle is not among them.
- Does not interact with ORM.
- Writing T-SQL scripts manually is not what we aimed for.
- The documentation and community are lackluster, though they may not be necessary for writing SQL scripts.
RoundhousE
This migration management tool, distributed under the Apache 2.0 license, like its predecessor, operates on the T-SQL migration engine. It seems that the developers prioritized solving technical problems related to database support rather than creating a comfortable development process.
Pros:
- Supports necessary databases (including Oracle)
Cons:
- Oracle (as well as the outdated Access) is not supported on .NET Core, only on the .NET Full Framework.
- Does not work with ORM
- There is even less documentation than for the previous tool.
- Again, migrations are written with scripts.
ThinkingHome.Migrator
A tool for versioned database schema migration for the .NET Core platform, distributed under the MIT license. .
Pros:
- Tailored for .NET Core
- Branching migration sequences are implemented.
- Migration logging is implemented.
Cons:
- The last update was a year ago. It appears the project is not maintained.
- Oracle is not supported (the article states this is due to the lack of a stable implementation for .NET Core – but that was a year ago).
- Auto-generation of migrations is absent.
Overall, the project looks promising, especially if it were to evolve, but we needed to make a decision here and now.
Fluent Migrator
The most popular migration tool, with a large following. Distributed under the Apache 2.0 license. As stated in the description, it is a migration platform for .NET, analogous to Ruby on Rails Migrations. Database schema changes are described in classes in C#.
There are advantages here:
- Support for necessary databases
- Support for .NET Core
- A large, developed community
- Migration conflicts are resolved sequentially – migrations indicate the execution order. Moreover, if a conflict arises around a single entity, conflict resolution during merging is handled just like the rest of the code.
- There are profiles that execute after successful migration. They can carry service functions. The last update was a month ago, which means the project is active.
As for the downsides, they include:
- Auto-generation of migrations is absent.
- No connection to EF models
- No database snapshots
What was our choice?
The most heated debates revolved around two parameters – auto-generation of migrations and a reasonable conflict resolution solution. Other factors were far less intimidating. Ultimately, after discussions, the team decided to use Fluent Migrator for the new project. This is because conflict resolution in the longer term will bring a lot more benefits.
Conclusions
Of course, there are no perfect tools. We had to prioritize our 'wish list' for our selection. However, other factors might be crucial for different teams and tasks. We hope this article will help you make your choice.
Source: habr.com
