fluentmigrator/fluentmigrator

Fluent migrations framework for .NET

FluentMigrator

Fluent Migrator is a migration framework for .NET much like Ruby on Rails Migrations. Migrations are a structured way to alter your database schema and are an alternative to creating lots of sql scripts that have to be run manually by every developer involved. Migrations solve the problem of evolving a database schema for multiple databases (for example, the developer's local database, the test database and the production database). Database schema changes are described in classes written in C# that can be checked into a version control system.

News

5.0.0 is released and supports .NET 6, .NET 7, and .NET 8. Thanks to @eloekset, our documentation website now has 5.0.0 API links!

Please read the changelog or the upgrade guide for further information: 2.x to 3.0.

There should not be a whole lot to do to upgrade from 3.0 to 5.0. As questions arise, we will consider adding a specific guide.

Packages

Package Source	Status	Source Code Tree
NuGet (Releases)		main
Azure Artifacts (Prerelease)		develop

The releases are stored on nuget.org while the CI builds are stored on Azure Artifacts.

⚠️ The badge for the Azure Artifacts feed won't display prereleases. We're looking into this.

Project Info

Documentation	On our GitHub pages
Discussions
Bug/Feature Tracking
Build server (new)

Prerequisites

Tool	Consequences when not installed
Multilingual App Toolkit Editor	You're unable to create translations.
Multilingual App Toolkit Extension (VS2017+)	You get a compilation warning and the changed translation doesn't get compiled.

Powered by

Contributors

A long list of everyone that has contributed to FluentMigrator. Thanks for all the Pull Requests!

Contributing

Please see our guide on how to contribute

Third Party Contributions / FluentMigrator Ecosystem

FluentMigrator has an actively developed and maintained ecosystem thanks to third party contributions. The following table summarizes some contributions (but are not endorsed):

GitHub/BitBucket	NuGet Package	Description
EasyMigrator	EasyMigrator.FluentMigrator	EasyMigrator allows you to specify database schema using simple POCOs with minimally attributed fields to represent columns. EasyMigrator's core can be adapted to sit on top of various migration libraries.
FluentMigrator-Generator	FluentMigrator.Generator	Adds a command to the package manager console to generate migrations for FluentMigrator.
AspNetBoilerplate	Abp.FluentMigrator	Adds fluent extensions specific to the entity model used by the ASP.NET Boilerplate architecture
Alt.FluentMigrator.VStudio	Alt.FluentMigrator.VStudio	Adds set of commands for Package Manager console: - Add-FluentMigration - Update-FluentDatabase - Rollback-FluentDatabase
FAKE.FluentMigrator	FAKE.FluentMigrator	FluentMigrator is a .NET library which helps to version database schema using incremental migrations which are described in C#. The basic idea of the FAKE helper is to run FluentMigrator over the existing database using compiled assembly with migrations.

fluentmigrator's Issues

Table rename generates incorrect Sql Server syntax.

The following code: Rename.Table('OldName').To('NewName')
generates the following t-sql:

sp_rename [dbo].[OldName], [NewName]

which is incorrect, arguments should be in singe quotes:

sp_rename '[dbo].[OldName]', '[NewName]'

Besides that, thanks for a great lib!

rollback:all doesn't drop VersionInfo table

rollback:all should leave the database in the same state before it was
touched by FluentMigrator. At the moment, it doesn't rollback the
creation of the VersionInfo table.

Add help syntax to commandline

Also, can we add a /? or /Help to the runner so that you can see the aggregate of parameters without having to have the source.
I would like to have a company wide build of the source and I don't want people to loose trust in the system because they have to ask me or someone in the know for the possible / params in our version.

Rollback fails for Access databases

Rolling back migrations in an Access database fails with an Exception in VersionLoader.DeleteVersion().

DeleteVersion executes the query:

Processor.Execute("DELETE FROM {0} WHERE {1}='{2}'", VersionTableMetaData.TableName, VersionTableMetaData.ColumnName, version.ToString());

The problem is that the version value is put into single quotes and thus treated as a string literal by Access. The version column though contains a numeric type, so Access throws an exception cause we are trying to compare incompatible types.

1. What's the reason for putting the value in single quotes? Will removing the single quotes break any other database?
2. Would it not be better to generate the SQL statement using the specific generator for the database?

Problem with 'tools' directory in NuGet package.

'Migrate.exe' and 'FluentMigrator.*.dll' from 'tools' directory are referencing to 'FluentMigrator.dll', which is resides in 'lib\35' directory.

So running 'tools\Migrate.exe' leads to:

"System.IO.FileNotFoundException: Could not load file or assembly 'FluentMigrator, >Version=1.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. The system cannot find the file specified."

Probably, 'FluentMigrator.dll' should be in 'tools' directory as well?

Delete.ForeignKey() not auto-generating the FK name

I'm submitting a fix for this shortly.

MigrationVersionMigrator doesnt support interleaved migrations

I've added a test that demonstrates an issue with the version runner. If there are a team of developers working on a project and migrations are being added at the same time, migrations previous to the current version are not being applied.

.Indexed() and .Unique() do nothing

Working against SqlServer, adding .Indexed() or .Unique() when creating a table's columns don't do anything.

I'm surprised this bug got this far. Surely an index is a pretty common requirement?

Rename column throws exception in mysql

Hey,

mysql documentation ->

You can rename a column using a CHANGE old_col_name new_col_name column_definition clause

The mysql generator is missing the "column_definition" variable which needs to be something like "varchar(255)" meaning something like .AsString(255) needs to be required if the migration is being applied to mysql ( unless the migrator can somehow inspect the column_definition and insert this if none is defined in the migration ). However not knowing whether other sql dialects also allow a type change at the same time as a rename, and given that migrations are suppose to be dialect independent requiring a type may not be suitable.

It seems whoever wrote the mysqlgenerator was afraid of something like this

public override string Generate(RenameColumnExpression expression)
{
    // may need to add definition to end. blerg
    return FormatExpression("ALTER TABLE `{0}` CHANGE COLUMN {1} {2}", 
         expression.TableName, expression.OldName, expression.NewName);
}

Thanks for a great framework.

XML columns with SQL Server

AsXml() gives me KeyNotFoundException in GeneratorBase.GetTypeMap on line 61

        if (size == 0)
            return ReplacePlaceholders(_templates[type][0], size, precision);

AsXml() generates column expression with size == 0.
Whereas SqlServer200Generator sets the following type map:

    public const int XmlCapacity = 1073741823;

        SetTypeMap(DbType.Xml, "XML", XmlCapacity);

That is why _templates[type] contains the only item with key == 1073741823, not 0.

Primary key name ignored for single column primary key on SqlServer

If you specify a primary key name for a primary key on a single column, the name is ignored and not used. For example:

Create.Table("InterestBasis")
     .WithColumn("InterestBasisID").AsInt32().NotNullable().PrimaryKey("PK_InterestBasis")
    .WithColumn("Name").AsString(200).NotNullable();

Delete.Index not working...

I can create an index like so:

Create.Index("IX_UserId").OnTable("User") 
    .OnColumn("id").Ascending().WithOptions().Unique();

But I can't delete an index like so:

Delete.Index("IX_UserId").OnTable("User");

With my build of fm this fails to compile. I do have the following intellisense options for Delete:

Column
ForeignKey
FromTable
Schema
Table

But not Index! If I attempt to do this, VS says:

'FluentMigrator.Builders.Delete.IdeleteExpressionRoot' does not contain a definition for 'Index' and no extension method 'Index' accepting a first argument of type 'FluentMigrator.Builders.Delete.IDeleteExpressionRoot' could be found (are you missing a using directive or an assembly reference?)

I'm poking around to try to understand this but I haven't figured out how to fix it yet -- according to the discussion thread it should work:

http://groups.google.com/group/fluentmigrator-google-group/browse_thread/thread/f33d9964cbcc236c#

Thanks!
Cymen

Linux/Mono support

using Migrate.exe with mono 2.8.2

mono Libs/FluentMigrator/Migrate.exe -a Migrations/bin/Release/Migrations.dll -db SqlServer2008 -conn 'Data Source=my.server.com;Initial Catalog=UKFS;User Id=usr;Password=secret;' -t migrate

yields:

!! An error has occurred.  The error is:
System.TypeLoadException: Could not load type 'FluentMigrator.Runner.Processors.Sqlite.SqliteProcessor' from assembly 'FluentMigrator.Runner, Version=0.9.0.42, Culture=neutral, PublicKeyToken=null'.
  at (wrapper managed-to-native) System.Type:type_is_assignable_from (System.Type,System.Type)
  at System.Type.IsAssignableFrom (System.Type c) [0x00000] in <filename unknown>:0 
  at FluentMigrator.Runner.Processors.ProcessorFactory.<ListAvailableProcessorTypes>b__0 (System.Type t) [0x00000] in <filename unknown>:0 
  at System.Linq.Enumerable+<CreateWhereIterator>c__Iterator1D`1[System.Type].MoveNext () [0x00000] in <filename unknown>:0 
  at System.Collections.Generic.List`1[System.Type].AddEnumerable (IEnumerable`1 enumerable) [0x00000] in <filename unknown>:0 
  at System.Collections.Generic.List`1[System.Type]..ctor (IEnumerable`1 collection) [0x00000] in <filename unknown>:0 
  at System.Linq.Enumerable.ToArray[Type] (IEnumerable`1 source) [0x00000] in <filename unknown>:0 
  at System.Linq.QuickSort`1[System.Type]..ctor (IEnumerable`1 source, System.Linq.SortContext`1 context) [0x00000] in <filename unknown>:0 
  at System.Linq.QuickSort`1+<Sort>c__Iterator21[System.Type].MoveNext () [0x00000] in <filename unknown>:0 
  at FluentMigrator.Runner.Processors.ProcessorFactory.ListAvailableProcessorTypes () [0x00000] in <filename unknown>:0 
  at FluentMigrator.Console.MigratorConsole..ctor (System.IO.TextWriter announcerOutput, System.String[] args) [0x00000] in <filename unknown>:0 

Oracle support is not compatible with 10g odp.net driver

The 11g client was used to develop the oracle support. You will get exceptions if you try to use FM on Oracle 10g.

Query for dropping Sqlite columns is not supported by Sqlite

In SqliteGenerator.cs Generate(DeleteColumnExpression expression) method the sql query generated is "ALTER TABLE {table} DROP COLUMN {column}.

This alter functionality is not supported in Sqlite. Instead the existing table should be renamed to {table}-tmp, a new table should be created named {table} that contains all the original columns minus the column to be dropped. Then all data from {table}-tmp should be inserted into {table} and {table}-tmp should be removed.

Tests throwing exceptions

The tests under FluentMigrator.Tests.Unit.Runners.MigratorConsoleTests is throwing exceptions because of incorrect console arguments that seem to have fallen out of sync with the api. Resharper runner shows these tests as passing even though they are throwing exceptions

ICreateForiegnKeyCascadeSyntax typo in foreign

ICreateForiegnKeyCascadeSyntax should be ICreateForeignKeyCascadeSyntax
all other interfaces are with ICreateForeignKey

run query out of transaction

We need option to run Execute.Sql("....") out of transaction.
For example creating Full-Text indexes in SQL server is not permitted under open user transaction.

Supporting unicode characters

When inserting e.g. Chinese characters I seem to be missing a unicode prefix (i.e. N'something') to ensure that text is correctly inserted.

I don't know the reach of the problem, have only tested against SQL2008 (which I was able to fix by adding a N-prefix to the ConstantFormatter string handling)

Foreign key with UPDATE and DELETE behaviour

As far as I can see there is no way to specify
ON DELETE CASCADE
ON UPDATE CASCADE

for the foreign key?

support for smalldatetime

Would love to see support for SmallDateTime columns in SQL Server!

.NET 4 download

It would be nice to have a .NET 4 framework download.

Thanks,
Kevin

Db connections are never closed

Running against Sql Server I've noticed that the connection passed to any of the SqlServerProcessorFactories is opened immediately and then never closed. This is a problem because it is eating up all the connections available in the connection pool.

I see a couple ways to resolve this issue...

1. Place all connections into using statements. Open a connection as needed and let the using close it for you. However, this would considerably change what I assume to be a major intent of the design... which is, if one migration fails, roll back everything. So, on to the next idea...
2. Add a Connection.close() to the end of both IMigrationProcessor.CommitTransaction() and IMigrationProcessor.RollbackTransaction().

Thoughts?

Alter column

originally reported by shawng:

It would be nice if there was a way to alter an existing column without deleting and recreating it, for example when you want to change a data type or default value but retain all the data. Maybe something like:

Alter.Column("foo").OnTable("bar").AsInt16().WithDefaultValue(0);

WithDefaultValue NEWID()

Create.Column("Id").OnTable("TableName").AsGuid().PrimaryKey().WithDefaultValue("NEWID()");

gives me the following error:

[Exception: An error occured executing the following sql:
ALTER TABLE [dbo].[TableName] ADD Id UNIQUEIDENTIFIER NOT NULL DEFAULT 'NEWID()' PRIMARY KEY CLUSTERED

Why does FluentMigrator enclose NEWID() within apostrophes?

FileNotFoundException: Could not load file or assembly

While using FluentMigrator 0.9, I get an error when Migrate.exe attempts to load a few assemblies, despite the fact that the migrations actually run. Using the Assembly Binding Log Viewer, it appears as though it's trying to find the assemblies inside the lib\fluentmigrator directory instead of the app's bin directory. I tried adding the --workingdirectory command line option but the results were still the same and the viewer showed the bin directory wasn't being probed.

If I put Migrate.exe inside the bin directory the rest of the FM assemblies are not found because the Appbase is now set to the bin directory. This is essentially the same issue I'm having with my app's assemblies when I call Migrate.exe from the FM directory.

The only thing that seems to work for me right now is if I move Migrate.exe and Machine.exe.config to the solution root, then specify the FM and bin paths in the assemblyBinding section of Migrate.exe.config.

<runtime> 
  <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1"> 
    <probing privatePath="lib\fluentmigrator;MvcMigrations.Web\bin" /> 
  </assemblyBinding> 
</runtime>

(See discussion for complete thread)

MigrateUp does not commit transaction

It would seem (unless I'm doing it wrong...) that performing a migrate:up operation (in C#) to reach a specific database migration version does not commit the transaction, and hence never completes. A full migrate operation performs correctly. Target in this case is Sql Server 2008.

Deleting a column with a default value throws an error in SQL Server

originally created by shawng

Deleting a column with a default value throws an error in SQL Server, since SQL Server creates a default constraint to enforce the default value. If you are dropping a column, you don't need the default value constrain anymore either, so it should be safe to delete. Here's some code to do it (replace line 71 of SqlServerGenerator.cs with this, if it isn't horribly mangled by the editor):

public override string Generate(DeleteColumnExpression expression)
{

// before we drop a column, we have to drop any default value constraints in SQL Server
string sql = @"
DECLARE @default sysname, @Sql nvarchar(max);

-- get name of default constraint
SELECT @default = name 
FROM sys.default_constraints 
WHERE parent_object_id = object_id('{0}')
AND type = 'D'
AND parent_column_id = (
    SELECT column_id 
    FROM sys.columns 
    WHERE object_id = object_id('{0}')
    AND name = '{1}'
);

-- create alter table command as string and run it
SET @sql = N'ALTER TABLE [{0}] DROP CONSTRAINT ' + @default;
EXEC sp_executesql @sql;

-- now we can finally drop column
ALTER TABLE [{0}] DROP COLUMN [{1}];";

return FormatExpression(sql, expression.TableName, expression.ColumnName);
}

ConsoleRunner's preview mode does not reflect migration to be performed

For a migrate:up to latest, all SQL between v0 and vCurrent is outputted to the console.

This may be intended, but it would seem more useful to me to have it be based on the version of the database as supplied in the connection argument. That way this preview output could be used in a confirmation user interface.

A dot in the version info table name causes errors

In attempting to work around the lack of a default schema, I tried forming the version info table name like mySchema.VersionInfo. All seems to be well at first - the table is created as dbo.mySchema.VersionInfo.

However, the next time a migration is run, an exception is raised:
"Invalid object name mySchema.VersionInfo".

It looks like the code to create the version info table is properly escaped, but code elsewhere that updates/drops it isn't.

It should be consistent - either fail it when creating or allow it everywhere.

Data deletion is not supported by IDeleteExpressionRoot

The code is all in there, but it hasn't been hooked up.

Can't delete row checking for null value in column

Syntax like Delete.FromTable("table").Row(new { Column = (object)null }) does not work because it generates WHERE Column = NULL instead of WHERE Column IS NULL

I'm submitting a fix shortly.

Alter column to remove default value not working

I have a DateTime column that I created with:

Create.Column("EndDate").OnTable("Project").AsDateTime().NotNullable().WithDefaultValue(new DateTime(2011, 12, 31));

Because I wanted it to not be nullable but then had to give it a default value to populate the current rows with. Now I want to remove the default value and I figured it would make since to try with either:

Alter.Column("EndDate").OnTable("Project").AsDateTime().NotNullable();

But since there isn't a WithDefaultValue it doesn't know to create the AlterDefaultConstraintExpression, so then I tried:

Alter.Column("EndDate").OnTable("Project").AsDateTime().NotNullable().WithDefaultValue(null);

But that doesn't work, it just leaves the default value as is.

I'm not sure what syntax makes the most sense to the users, here are a couple of initial thoughts.

1. Check during the alter column if there is a default value and if one is not provided, delete the default constraint.
2. For the Alter syntax allow WithDefaultValue() as well as WithDefaultValue(object) and if nothing is passed then remove the current default constraint and don't add a new one.
3. For Alter syntax add WithNoDefaultValue() which removes
4. For Alter syntax WithDefaultValue(null) will remove the current default constraint and not create a new one.

I think 2 or 4 make the most sense unless there is a case where you would need to pass in NULL to reset but i think removing would just remove it and then NULL is assumed anyways.

Any thoughts? (sorry for rambling a bit)

SqlServer2000Generator Decimal capacity 19?

Is there a reason for the SqlServer2000Generator to have it's decimal capacity limited to 19?

When doing .AsDecimal(20,5) I'm getting the exception "ERROR: Unsupported DbType 'Decimal'"

I'm seeing the const DecimalCapacity = 19

Feature request: Being able to retrieve a version number from a database table

Hi,

One thing I am missing is the ability to read a version number from an arbitrary table to make decisions if you need to upgrade:

public override Up()
{
int majorVersion;
int minorVersion;
Execute.ReadVersion("SELECT Major, Minor FROM ApplicationVersionTable WHERE AppID='MyApp'", out majorVersion, out minorVersion);
if(majorVersion > 3 && minorVersion >1)
{
// do schema changes etc....
}

}

Now maybe there is a way to do this already, but a quick glance at the code shows that this may be missing.

Currently, we implement this with a simple ADO.NET SQL query, but it would be nice to have this as part of the fluentmigrator toolset.

Friedrich

VersionInfo table is always in dbo schema

The ability to set the schema for SQLServer is great, but is there a way to set the schema of the VersionInfo table?

The problem is that a one needs to be able to have multiple VersionInfo tables if one has multiple schemas in a single database - i.e. where each schema is for a different application and therefore
needs to be versioned independently.

Since VersionInfo is the first thing created, there needs to be an option somewhere to set the default schema.

There would need to be the option to create the schema or use an existing one and this would have to be remembered (in the VersionInfo table?) to allow rollback:all to work correctly.

Rename table in sql server 2008 throws exception

Rename table in sql server 2008 should look like:
sp_rename '[dbo].[Foo]', '[Bar]'
but is generated as:
sp_rename [dbo].[Foo], [Bar]

(like in sql server 2000)

SQL Server 2008 allows brackets in table name when renaming

Rename.Table("Foo").To("Bar");
=> new table name is [Bar]

in migration test:
FluentMigrator.Tests\Integration\Migrations\TestMigration.cs
(after changing SqlServerProcessorTests ,IntegrationTestOptions and IntegrationTestBase to use sql server 2008)
when Foo is renamed to Bar the rename goes wrong and the new table name is [Bar]
Later when trying to delete the table Bar (without the brackets) an error is thrown that there is no such table.

I have Microsoft SQL Server Standard Edition (x86), Version:10.0.2531.0

Delete.ForeignKey().OnTable() not working

Looks like a little while ago the Generators were changed to use the ForeignKey.ForeignTable instead of the PrimaryTable but the DeleteForeignKeyExpression wasn't updated. I'm submitting a fix shortly.

Generate SQL script from migration(s)

Originally Reported by troygoode, Feb 03, 2009

The ability to run the migrations but not execute them, just capture the output. Maybe an extra flag /export script.sql

Specify constraint name

Currently the constraint name is always defaulted to a random constraint name (for sqlserver), add the ability to specify the constraint name for DefaultValue constraint

Square bracket descrepency between database flavors

In the instance of a table called 'Transaction' ms sql runner will add the square brackets but in the sql lite runner it will not. If i add the square brackets on the table name in the migration it will work on sqlite but fail on ms sql.

ColumnDefinition: IsForeignKey, IsIndexed, IsUnique are not implemented

While its possible to set these to true through the builder methods, the current generators neither implement them nor throw an exception when set.

Insert.Row doesn't handle DateTime correctly

I tried to insert data with a DateTime and got the following error
owing to the European date format:
-- ERROR: An error occured executing the following sql:
INSERT INTO [mikescom_oa].Users
VALUES (1,'mike','15/10/2010 17:48:20');
The error was The conversion of a varchar data type to a datetime data
type resulted in an out-of-range value.
The DateTime should be converted using a universal format, e.g.
dateTime.ToString("s") works.

Alter table's schema

Does fluent migrator support Altering a table's schema?

The syntax in SQL Server is alter schema {0} transfer {1}

Index deletion is not supported by IDeleteExpressionRoot.

The code is all in there, but it hasn't been hooked up.

Alter nullable column without specifying Nullable in syntax breaks

The problem is that if you don't specify Nullable or NotNullable then it goes with NotNullable because the property is bool so it defaults to false. This is fine for creates but for alter syntax it seems like it should stay the same like when you don't include it in the SQL syntax.

So maybe we make IsNullable a bool? so we can tell if it's specifically set or not.

Any thoughts?

SQL Server Schema Support

I'm interested in if Fluent Migrator can deal with SQL Server Schemas. For example, to create a table with a schema like: Example.Tablename.

Given what I've seen in the source code, it looks like I could probably just include the schema in the table name and it should work for SQL Server. Since the generated SQL is simply CREATE TABLE [{0}]. But I think setting the table names to Example.Tablename will cause problems with SQLite.

I haven't actually tried this yet, I will, but I thought I'd ask before I got too far with it.

Thanks,
Kevin

Migration fails if nant task doesn't supply profile option

the FindProfilesIn method in migrationloader will fail with a NRE if the nant task doesnt specify profile to run

Column names not delimited

I am creating a table for Sql Server 2008, and it contains a column named 'User'. This is a keyword, but FluentMigrator is not delimiting column names (e.g. [User]), and so an exception is thrown.