Clone this wiki locally
Portability, Validation and Knowledge Pool
One of Mig# main concerns is to ensure portability of the migrations which is crucial for applications supporting multiple database platforms. Developers should have not have to write provider specific code in their migrations nor in their DAL as this ends up in duplicated and hard-to-maintain code.
Thus, Mig# checks migrations for compatibility issues against all database platforms that you define should be supported by your application. This way, problems can be detected without first having to run the migration on a specific platform.
Also, validation messages do not only point out a problem, but also try to give helpful tips on how to resolve the issue. For example, if you are using the GUID data type in one of your migrations, you will get the following validation warning for Oracle:
Migration '...' uses the data type 'Guid' which is not fully supported by 'Oracle': Requires custom ADO.NET code to convert to/from a byte array (call Guid.ToByteArray(), Guid(byte)) and the DbParameter.DbType must be set to DbType.Binary.
In this way, Mig# also thrives to become a source of insights for cross-db programming.
The downside of this feature is that Mig# in general only supports features which are common between today’s RDBMS. As an example, only data types that are exposed through System.Data.DbType are supported.
Support for Industry Standard Relational Databases
Currently, Mig# offers support for SQL Server, Oracle, Teradata, MySQL and SQLite. Most of these also come with explicit ODBC support (if you think ADO.NET behaves the same if you have an underlying ODBC connection, you’re wrong). Contributors for more are welcome :)
The support for SQLite has been added for light-weight integration testing.
Fluent Interface for Schema Modifications
All schema modifications can be specified in C# or any .NET language and run on all supported database platforms. Mig# offers an intuitive fluent interface for these operations. Thus, developers can concentrate on what they do best: programming in the language of their choice instead of having to learn x SQL dialects.
It should be noted that the original idea of handling database migrations in such a way comes from the Ruby on Rails camp.
Mig# keeps track of which migrations have been executed on a specific database and only applies missing ones.
Support for Aggregate Migrations
Multiple migrations can be aggregated into aggregate migrations so that individual migrations do not have to be executed individually. This allows for something like base-lines where an empty database can be initialized in a fast-forward manner. The command-line tool
Generate.exe allows for an automatic reverse-engineering of an existing database schema into an aggregate migration.
Sometimes modules of an application need to co-exist in the same database and have their own versioning. Mig# migrations can be annotated with a module name to allow for this scenario.
For the cases where software is not allowed to change the database schema on its own, Mig# comes with the option to script the migrations into SQL files which can then be inspected and run manually by DBAs.
Mig# is designed to be adoptable by legacy applications that have their own versioning system in place. There are two possible levels of integration:
- The legacy application wants to completely replace its custom versioning mechanism. It can do so by specifying a method that tells Mig# what the current version of a given database is. Mig# then initializes its versioning table under the assumption that all previous migrations have already been executed and only executes the current pending ones.
- The legacy application wants to keep its versioning mechanism but wants to specify new migrations using Mig#. It can do so by implementing an interface that abstracts the versioning.
Also, the name of Mig#’s versioning table if freely configurable should there be a collision.
Finally, Mig# targets .NET 3.5 and .NET 4.0.
Mig# encourages its users by convention to use non-colliding versioning “timestamps” and filenames. The timestamps are encoded in the filename as post-fixed numbers. E.g.
Migration20100708113520. Adopting this convention, two teams can introduce new migrations without the need of synchronization. Merging their branches into the trunk is collision free.
If a migration with a slightly older timestamp is introduced later, Mig# executes it “a posteriori” as it keeps a list of all executed migrations.
Each migration runs in a transaction of its own and therefore is guaranteed to succeed or fail as a whole.
All of the executing SQL is generated solely from the logic contained within the migrations: there are no queries performed against a database that modify any of the generated SQL. This property allows for a static inspection of the generated SQL by a DB administrator if needed. If needed, all migrations could be exported into a file and ran manually without Mig# (note that Mig# currently does not have this option, but it could easily be extended to support this scenario).
Migrations can also specify hand-coded SQL if needed. Example:
if (db.Context.ProviderMetadata.Name == ProviderNames.Oracle)
db.Execute("some custom SQL...");
The connection and transaction objects used during the execution of a migration are exposed through a context object. They can be used if the need arises to load data to the client (who is executing the migration), transform it, and send it back to the database.
Schema changing library
Since 2.2, Mig# supports executing schema changing operations directly without involving any versioning. The `DbSchema` class offers a dedicated API for this purpose. Thus, Mig# can act as a library to change database schemas on-the-fly in a platform agnostic way.
Migrations classes only need to implement an interface without the need of inheriting a base class.