Automate database changes in .NET
Hello!
I would like to talk about the projects Migrator.Net and ECM7.Migrator .
Migrator.Net is a database version control mechanism similar to Migrations in Ruby on Rails. Migrator allows you to automate the execution of database modification operations and automatically keeps track of versions.
Migrator is written in C # and will be convenient, first of all, when used in projects under .NET.
')
First, I will do a small review of these projects, and then I will try to highlight their “pros” and “cons.”
To modify the database, you create an assembly containing “migrations”. "Migrations" are classes, each of which describes a small change in the database.
When writing “migrations” classes, it is necessary to inherit from the special base class Migration and block 2 methods:
The actions that must be performed on the database are set in the language in which you write your .NET project. There is a special framework containing classes and methods for performing basic operations on the database. It is also possible to execute an arbitrary SQL query.
Let's look at what is happening here:
Migrator provides several ways to launch your “migrations” classes.
Each of these methods receives as parameters the type of database used, the connection string and the version number of the database to be obtained.
Migrator.NET can work with various DBMS. When “migrations” are launched for execution, along with the connection parameters to the database, the migrator indicates the class “dialect” by which SQL queries are generated for a specific DBMS.
In the Migrator.NET project there are “dialects” for the following DBMS: MySQL, Oracle, SQLite, MS SQL Server (including CE), PostgreSQL.
At the beginning of this post, I mentioned another project with Migrator.Net : ECM7.Migrator . Now I will talk about it in more detail.
In the spring of this year, my colleagues and I learned about Migrator.NET. We tried to use it and we really liked it. Now I use the migrator on both of my jobs (a total of 3 fairly large projects already working in real conditions).
Unfortunately, it turned out that Migrator.NET has some inconveniences, and its provider for Oracle contains many errors (by the way, this is written on the main page of the project in google code, by the way).
During the period from May to September, a large number of changes were made to Migrator.NET: several classes of so-called rewritten. "Kernels", many errors in the provider are corrected, several "cosmetic" improvements that make using the migrator more convenient.
As a result, the code that we finalized was so divided with the code of the original project that it was decided to make a separate project. That he laid out in the google code called ECM7.Migrator . It was also partially translated (and corrected for a new project) documentation.
In addition, PostgreSQL support was removed as unnecessary.
During my work with the migrator, I felt two of his shortcomings:
Migrator does not save you from having to think about the structure of the database. It does not generate any changes to the database machine. It's just a more convenient way to manually set all actions on the database, as well as a more convenient way to perform them.
In general, this is all that I wanted to tell about the migrator. How successful was the story - you decide.
I will be very happy if someone is interested in a migrator. This will mean that the work of its developers (and therefore mine too) was not in vain. I will try in every way to help such people in the use of the migrator - to answer questions and quickly fix the bugs found.
Thank you all for your attention.
- Note
Some time ago, the octalforty Wizardby project was already discussed here . As I understand it, this is another migrator.
Unfortunately, very briefly acquainted with this project. The main difference that catches the eye is that there “migrations” are written in a special language.
If you used Wizardby in real conditions, write to me. It would be very interesting to learn about its merits / demerits and compare it with Migrator.NET.
I would like to talk about the projects Migrator.Net and ECM7.Migrator .
Migrator.Net is a database version control mechanism similar to Migrations in Ruby on Rails. Migrator allows you to automate the execution of database modification operations and automatically keeps track of versions.
Migrator is written in C # and will be convenient, first of all, when used in projects under .NET.
')
First, I will do a small review of these projects, and then I will try to highlight their “pros” and “cons.”
Basic principles
To modify the database, you create an assembly containing “migrations”. "Migrations" are classes, each of which describes a small change in the database.
When writing “migrations” classes, it is necessary to inherit from the special base class Migration and block 2 methods:
- The Up method sets operations on the database that are necessary for moving to the next version of the database (for example, creating a table);
- The Down method sets the operations necessary to undo changes made by the Up method (for example, if a new table is created in the Up method, then it should be deleted in the Down method).
The actions that must be performed on the database are set in the language in which you write your .NET project. There is a special framework containing classes and methods for performing basic operations on the database. It is also possible to execute an arbitrary SQL query.
Migration example
[Migration(7)]
public class AbstractTestMigration : Migration
{
override public void Up()
{
Database.AddTable( "CustomerAddress" ,
new Column( "customer_id" ,
DbType.Int32, ColumnProperty.PrimaryKey),
new Column( "address" ,
DbType.AnsiString, ColumnProperty.NotNull));
}
override public void Down()
{
Database.RemoveTable( "CustomerAddress" );
}
}
* This source code was highlighted with Source Code Highlighter .Let's look at what is happening here:
- We see that our class “migration” is marked with the attribute
[Migration (7)] . The attribute parameter is a value of the long type that specifies the version number of the database to which a transition will occur when making changes from the Up method. - As we have said, the class “migration” should be inherited from the base class Migration . Also, the “migration” class should override the Up and Down methods of the base class.
- The base class Migration has a Database property. This property contains an object that implements the ITransformationProvider interface. By calling the methods of the ITransformationProvider interface on the Database object, you can perform operations on the database. For example, in the Down method, Database.RemoveTable is used to delete a table.
The ITransformationProvider interface contains theint ExecuteNonQuery(string sql)method that allows you to execute an arbitrary SQL query.
Ways to make changes
Migrator provides several ways to launch your “migrations” classes.
- Console application
- Task for NAnt
- Task for MsBuild
- Program execution - performing “migrations” from your program through the API
Each of these methods receives as parameters the type of database used, the connection string and the version number of the database to be obtained.
Supported DBMS
Migrator.NET can work with various DBMS. When “migrations” are launched for execution, along with the connection parameters to the database, the migrator indicates the class “dialect” by which SQL queries are generated for a specific DBMS.
In the Migrator.NET project there are “dialects” for the following DBMS: MySQL, Oracle, SQLite, MS SQL Server (including CE), PostgreSQL.
Differences between Migrator.Net and ECM7.Migrator projects
At the beginning of this post, I mentioned another project with Migrator.Net : ECM7.Migrator . Now I will talk about it in more detail.
In the spring of this year, my colleagues and I learned about Migrator.NET. We tried to use it and we really liked it. Now I use the migrator on both of my jobs (a total of 3 fairly large projects already working in real conditions).
Unfortunately, it turned out that Migrator.NET has some inconveniences, and its provider for Oracle contains many errors (by the way, this is written on the main page of the project in google code, by the way).
During the period from May to September, a large number of changes were made to Migrator.NET: several classes of so-called rewritten. "Kernels", many errors in the provider are corrected, several "cosmetic" improvements that make using the migrator more convenient.
As a result, the code that we finalized was so divided with the code of the original project that it was decided to make a separate project. That he laid out in the google code called ECM7.Migrator . It was also partially translated (and corrected for a new project) documentation.
In addition, PostgreSQL support was removed as unnecessary.
How can a migrator help you?
- It is convenient to distribute database changes with the migrator. There is no folder with a bunch of scripts, instead it is the only dll. I think you will appreciate this if you need to update the windows-application for several hundred users.
- Migrations are easy to write: check at compile time, intellisense, etc. No one writes SQL manually. It is usually generated via the GUI. Often, this generates a bunch of "left" teams. With a migrator, you can fully control everything without spending a lot of time on it.
- Migrations are convenient to perform. As I already wrote, there are tasks for the migrator for NAnt and MSBuild. With them you can easily perform the migration during the automated assembly of your project. In addition, software migration tools make possible such things as updating the database structure when starting the application or creating a clean database for unit tests.
- Migrations do not depend on the DBMS. To be honest, I have never had to work on a project that should support several DBMS. But, perhaps, independence from the DBMS is also needed by someone.
- Migration is easy to add arbitrary logic. For example, you can create a bunch of tables in a loop or add some prefix to the name of each table, which is defined in the application settings.
disadvantages
During my work with the migrator, I felt two of his shortcomings:
- If a person does not like to work with programs via the command line (as I do), then the lack of a program with a GUI that allows to start migrations for execution will be a disadvantage for him. Fortunately, this problem was quickly resolved by writing a bat file that launches a migrator console application with the necessary parameters.
- The second inconvenience is that when I write migration, I do not remember the names of already created database objects. This problem has not been resolved. When writing migrations, I look at the names in the GUI or in the hbm files (we use NHibernate in many projects).
What can not migrator
Migrator does not save you from having to think about the structure of the database. It does not generate any changes to the database machine. It's just a more convenient way to manually set all actions on the database, as well as a more convenient way to perform them.
Conclusion
In general, this is all that I wanted to tell about the migrator. How successful was the story - you decide.
I will be very happy if someone is interested in a migrator. This will mean that the work of its developers (and therefore mine too) was not in vain. I will try in every way to help such people in the use of the migrator - to answer questions and quickly fix the bugs found.
Thank you all for your attention.
- Note
Some time ago, the octalforty Wizardby project was already discussed here . As I understand it, this is another migrator.
Unfortunately, very briefly acquainted with this project. The main difference that catches the eye is that there “migrations” are written in a special language.
If you used Wizardby in real conditions, write to me. It would be very interesting to learn about its merits / demerits and compare it with Migrator.NET.
Source: https://habr.com/ru/post/70884/
All Articles