Fluent Interface

The FM fluent api allows you to create tables, columns, indexes and (nearly) every construct you need to manipulate your database structure.

Behind the scenes, the fluent api populates a semantic model that FM uses to analyze and apply migrations in batch. The fluent api that is available in your Migration class starts with five main root expressions as follows:

Create Expression

Allows you to create a table, column, index, foreign key and schema.

Create.Table("Users")
    .WithIdColumn() // WithIdColumn is an extension, see below link.
    .WithColumn("Name").AsString().NotNullable();

Example extensions can be found in the example MigrationExtensions.cs.

Otherwise, you can replace WithIdColumn with

.WithColumn("Id").AsInt32().NotNullable().PrimaryKey().Identity();

Alter Expression

Allows you to alter existing tables and columns.

Alter.Table("Bar")
    .AddColumn("SomeDate")
    .AsDateTime()
    .Nullable();

Alter.Table("Bar")
    .AlterColumn("SomeDate")
    .AsDateTime()
    .NotNullable();

Alter.Column("SomeDate")
    .OnTable("Bar")
    .AsDateTime()
    .NotNullable();

Delete Expression

Allows you to delete a table, column, foreign key and schema.

Delete.Table("Users");

Delete Multiple Columns

Delete multiple columns from a table using the syntax in this expression:

Delete.Column("AllowSubscription").Column("SubscriptionDate").FromTable("Users");

Execute Expression

Allows you to execute a block of sql, or a script by name (ie. myscript.sql) or an embedded sql script. To embed an sql script, add the file to your migration project and change the Build Action property to Embedded Resource.

Execute.Script("myscript.sql");
Execute.EmbeddedScript("UpdateLegacySP.sql");
Execute.Sql("DELETE TABLE Users");

Rename Expression

Allows you to rename a column or table.

Rename.Table("Users").To("UsersNew");

Data Expressions

Allows you to insert a row into a table using an anonymous type for the rows contents

Insert.IntoTable("Users").Row(new { FirstName = "John", LastName = "Smith" });

Delete.FromTable("Users").AllRows(); // delete all rows
Delete.FromTable("Users").Row(new { FirstName = "John" }); // delete all rows with FirstName==John
Delete.FromTable("User").IsNull("Username"); //Delete all rows where Username is null

Update.Table("Users").Set(new { Name = "John" }).Where(new { Name = "Johnanna" });

AllRows Attribute

A common task is to add a non-nullable column without a default value. There are three steps:

1. Add new nullable column.

Alter.Table("Bar")
    .AddColumn("SomeDate")
    .AsDateTime()
    .Nullable();

2. Update all rows to an initial value using the AllRows attribute (not in the Nuget version yet).

Update.Table("Bar")
    .Set(new { SomeDate = DateTime.Today })
    .AllRows();

3. Change the column to be non-nullable.

Alter.Table("Bar")
    .AlterColumn("SomeDate")
    .AsDateTime()
    .NotNullable();

IfDatabase Expression

Allows for conditional expressions depending on database type. The current database types supported are:

• SqlServer (this includes Sql Server 2005 and Sql Server 2008)
• SqlServer2000
• SqlServerCe
• Postgres
• MySql
• Oracle
• Jet
• Sqlite

Multiple database types (as specified above) can be passed into the IfDatabase Expression (See Dealing-with-Multiple-Database-Types for more details).

IfDatabase("SqlServer", "Postgres")
    .Create.Table("Users")
    .WithIdColumn()
    .WithColumn("Name").AsString().NotNullable();

IfDatabase("Sqlite")
    .Create.Table("Users")
    .WithColumn("Id").AsInt16().PrimaryKey()
    .WithColumn("Name").AsString().NotNullable();

Next up, Profiles are migrations that if specified, will always run regardless of what other migrations run.