Hello! Today I have released a new version of ThinkingHome.Migrator , a tool for versioning migration of a database schema for a .NET Core platform.
Packages are published in NuGet , written detailed documentation . You can already use the new migrator, and I will tell you how it appeared, why it has a version number 3.0.0 (although this is the first release) and why you need it when you have EF Migrations and FluentMigrator .
How it all began
9 years ago, in 2009, I was working as an ASP.NET developer. When we released our project, a special person stayed at work until late and, simultaneously with updating the files on the server, they handled SQL scripts that updated the database while it was being sold. We searched for a tool that would do this automatically, and found the Migrator.NET project.
Migrator proposed a new idea for the time - to set changes in the database in the form of migrations. Each migration contains a small portion of changes and has a version number into which the database will go after its execution. Migrator himself kept track of versions and performed the necessary migrations in the right order. Especially cool was that the migrator allowed for each migration to set backward changes. When starting the migrator, it was possible to specify a version lower than the current one, and it would automatically roll back the database to this version, performing the necessary migrations in the reverse order.
[Migration(1)] public class AddAddressTable : Migration { override public void Up() { Database.AddTable("Address", new Column("id", DbType.Int32, ColumnProperty.PrimaryKey), new Column("street", DbType.String, 50), new Column("city", DbType.String, 50) ); } override public void Down() { Database.RemoveTable("Address"); } }
There were a lot of mistakes in that migrator. He did not know how to work with database schemas other than the default one. In some cases, generated incorrect SQL queries, and if you specify a non-existent version number for it, it fell into an infinite loop. As a result, my colleagues and I forked the project and fixed bugs there.
GitHub.com with its fork and pull requests was not there yet (the migrator code was on code.google.com ). Therefore, we didn’t particularly bother so that our changes got back to the original project - we just sawed our copy and used it ourselves. Over time, we rewrote most of the project, and I became its main maintainer. Then I posted the code of our migrator on google code and wrote an article on Habr . This is how ECM7.Migrator appeared.
During the work on the migrator, we almost completely rewrote it. At the same time, they slightly simplified the API and covered all with autotests. Personally, I really liked to use what happened. Unlike the original migrator, there was a feeling of reliability and there was no feeling that incomprehensible magic was happening.
As it turned out, our migrator liked not only me. As far as I know, it was used in quite large companies. I know about ABBYY, BARS Group and concert.ru. If you type in the search query "ecm7 migrator", then you can find in the results of an article about him, references to resumes, descriptions of use in student work. Sometimes I received letters from strangers with questions or words of gratitude.
After 2012, the project almost did not develop. His current capabilities covered all the tasks that arose in me and I did not see the need to complete something.
ThinkingHome.Migrator
Last year, I started working on a project on .NET Core. There it was necessary to make the ability to connect plug-ins, and plug-ins should be able to create the necessary database structure for themselves to store their data there. This is exactly the task for which the migrator is well suited.
EF Migrations
I used the Entity Framework Core to work with the database, so the first thing I tried was EF Migrations. Unfortunately, I almost immediately had to abandon the idea of using them.
Migrations of the Entity Framework drag a bunch of dependencies into the project, and when they start up, they do some special magic. Step to the left / step to the right - you rest on the restrictions. For example, migrations of the Entity Framework for some reason must be in the same assembly with DbContext . This means that it will not be possible to store migrations inside plugins.
FluentMigrator
When it became clear that EF Migrations are not suitable, I looked for a solution in Google and found several open source migrators. The most advanced of them, judging by the number of downloads to NuGet and asterisks on GitHub, was FluentMigrator . FM is very good! He knows a lot and he has a very convenient API. At first I decided that this was what I needed, but later several problems were discovered.
The main problem is that FluentMigrator is not able to simultaneously take into account several versions of sequences within one database. As I wrote above, I needed to use a migrator in a modular application. It is necessary that modules (plugins) can be installed and updated independently of each other. FluentMigrator has continuous version numbering. Because of this, it is impossible to execute / roll back from the migration database of one plug-in without affecting the database structure of the other plug-ins.
I tried to organize the desired behavior with the help of tags , but this is also not exactly what is needed. FluentMigrator does not store tag information about completed migrations. In addition, tags are tied to migrations, not to assemblies. This is very strange, considering that the entry point for the search for migrations is an assembly. In principle, it was probably possible to make a parallel account of versions in this way, but you need to write a rather complicated wrapper over the migrator.
Port ECM7.Migrator to .NET Core
In the beginning, this option is not even considered. At that time, the current version of .NET Core was 1.1 and its API was poorly compatible with the .NET Framework in which ECM7.Migrator worked. I was sure that porting it to .NET Core would be difficult and long. When there are no "take ready" options, I decided to try. The task turned out to be easier than I expected. Surprisingly, it all worked almost immediately. It took only minor changes. Since the logic of the migrator was covered with tests, all the places that broke were immediately visible and I quickly repaired them.
Now I have ported adapters for only four DBMS: MS SQL Server, PostgreSQL, MySQL, SQLite. I didn’t port adapters for Oracle (because there is still no stable client for .NET Core), MS SQL Server CE (because it works only under Windows and I’m stupid to run it) and Firebird (because it not very popular, porting later). In principle, if you need to make providers for these or other DBMS - it's pretty simple.
The source code for the new migrator is on GitHub . Run tests for each DBMS in Travis CI is configured. A command line utility ( .NET Core Global Tool ) is written, which can be easily installed from NuGet . Documentation is written - I tried very hard to write in detail and clearly, and it seems that this is how it turned out. You can take and use!
A little about the name ...
The new migrator does not have backward compatibility with the old one. They work on different platforms and their API is different. Therefore, the project was published under a different name.
The name was selected by the project ThinkingHome , for which I ported the migrator. Actually, ECM7.Migrator is also named after the project I was working on at that moment.
Perhaps it was better to choose some kind of neutral name, but it did not occur to me to make good choices. If you know this - write in the comments. It's not too late to rename everything.
Version number indicated 3.0.0, because new migrator - a logical continuation of the old.
Fast start
So let's try using a migrator.
All changes to the database are recorded in the migration code - classes written in a programming language (for example, in C #). Migration classes are inherited from the Migration base class from the ThinkingHome.Migrator.Framework package. They need to override the base class methods: Apply (apply changes) and Revert (roll back changes). Inside these methods, the developer using a special API describes the actions that need to be performed on the database.
Also, the migration class should be marked with the [Migration] attribute and indicate the version to which the database will go after these changes are made.
Migration example
using ThinkingHome.Migrator.Framework; [Migration(12)] public class MyTestMigration : Migration { public override void Apply() { // : Database.AddTable("CustomerAddress", new Column("customerId", DbType.Int32, ColumnProperty.PrimaryKey), new Column("addressId", DbType.Int32, ColumnProperty.PrimaryKey)); } public override void Revert() { // : Database.RemoveTable("CustomerAddress"); // , // Revert } }
How to start
Migrations are compiled into a .dll file. After that, you can make database changes using the console migrate-database utility. First, install it from NuGet .
dotnet tool install -g thinkinghome.migrator.cli
Run the migrate-database , specifying the desired database type, connection string and path to the .dll file with the migrations.
migrate-database postgres "host=localhost;port=5432;database=migrations;" /path/to/migrations.dll
Run through API
You can perform API migrations from your own application. For example, you can write an application that, when launched, creates for itself the necessary database structure.
To do this, plug into your project the ThinkingHome.Migrator package from NuGet and the package with the transformation provider for the desired DBMS . After that, create an instance of the ThinkingHome.Migrator.Migrator class and call its Migrate method, passing as a parameter the desired version of the database.
var version = -1; // -1 var provider = "postgres"; var connectionString = "host=localhost;port=5432;database=migrations;"; var assembly = Assembly.LoadFrom("/path/to/migrations.dll"); using (var migrator = new Migrator(provider, connectionString, assembly)) { migrator.Migrate(version); }
By the way, you can compare it with the FluentMigrator launch example.
Conclusion
I tried to make a simple tool without dependencies and complex magic. It seems to be good. The project is not raw for a long time, everything is covered with tests, there is detailed documentation in Russian. If you are using .NET Core 2.1, try a new migrator . Most likely you will like it too.