Search Results for

    Show / Hide Table of Contents

    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();
    
    Create.ForeignKey() // You can give the FK a name or just let Fluent Migrator default to one
        .FromTable("Users").ForeignColumn("CompanyId")
        .ToTable("Company").PrimaryColumn("Id");
    

    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");
    Rename.Column("LastName").OnTable("Users").To("Surname");
    

    Data Expressions

    Allows you to insert a row into a table using an anonymous type for the row's 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("Users").IsNull("Username"); //Delete all rows where Username is null
    
    Update.Table("Users").Set(new { Name = "John" }).Where(new { Name = "Johnanna" });
    

    Insert data as an non-Unicode string

    If you want to insert a string as non-Unicode (ANSI) then use the NonUnicodeString class:

    Insert.IntoTable("TestTable").Row(new { Name = new NonUnicodeString("ansi string") });
    

    AllRows() Method

    A common task is to add a non-nullable column without a default value. One way this can be achieved is with the 'AllRows' method, via these three steps:

    1. Add new nullable column.
    Alter.Table("Bar")
        .AddColumn("SomeDate")
        .AsDateTime()
        .Nullable();
    
    1. Update all rows to an initial value using the AllRows method.
    Update.Table("Bar")
        .Set(new { SomeDate = DateTime.Today })
        .AllRows();
    
    1. Change the column to be non-nullable.
    Alter.Table("Bar")
        .AlterColumn("SomeDate")
        .AsDateTime()
        .NotNullable();
    

    SetExistingRowsTo alternative to AllRows()`

    As of version 1.3.0, this can be done with a single statement using the SetExistingRowsTo method.

    Alter.Table("Bar")
        .AddColumn("SomeDate")
        .AsDateTime()
        .SetExistingRowsTo(DateTime.Today)
        .NotNullable();
    

    IfDatabase Expression

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

    Database Identifier Alternative identifier(s)
    Microsoft SQL Server 2022 SqlServer2016(1) SqlServer
    Microsoft SQL Server 2019 SqlServer2016(2) SqlServer
    Microsoft SQL Server 2017 SqlServer2016(3) SqlServer
    Microsoft SQL Server 2016 SqlServer2016 SqlServer
    Microsoft SQL Server 2014 SqlServer2014 SqlServer
    Microsoft SQL Server 2012 SqlServer2012 SqlServer
    Microsoft SQL Server 2008 SqlServer2008 SqlServer
    Microsoft SQL Server 2005 SqlServer2005 SqlServer
    Microsoft SQL Server 2000 SqlServer2000 SqlServer
    Microsoft SQL Server Compact Edition(4) SqlServerCe SqlServer
    PostgreSQL Postgres PostgreSQL
    PostgreSQL 9.2 Postgres92 PostgreSQL92
    PostgreSQL 10.0 PostgreSQL10_0 PostgreSQL
    PostgreSQL 11.0 PostgreSQL11_0 PostgreSQL
    PostgreSQL 15.0 PostgreSQL15_0 PostgreSQL
    MySQL 4 MySql4 MySql
    MySQL 5 MySql5 MySql, MariaDB
    MySQL 8 MySQL8 MySql, MariaDB
    Oracle Oracle
    Oracle (managed ADO.NET) OracleManaged Oracle
    Oracle (DotConnect ADO.NET) OracleDotConnect Oracle
    Microsoft JET Engine (Access) Jet
    SQLite Sqlite
    Firebird Firebird
    Amazon Redshift Redshift
    SAP Hana Hana
    SAP SQL Anywhere(5) SqlAnywhere16 SqlAnywhere
    DB2 DB2
    DB2 iSeries DB2 iSeries, IBM DB2 iSeries DB2
    • (1) All integration tests ran without error against an SQL Server 2019 using the SqlServer2016 dialect.
    • (2) All integration tests ran without error against an SQL Server 2019 using the SqlServer2016 dialect.
    • (3) All integration tests ran without error against an SQL Server 2017 using the SqlServer2016 dialect.
    • (4) Support for Microsoft SQL Server Compact Edition is being dropped due to Microsoft end-of-life support date passing.
    • (5) Support for SAP SQL Anywhere is being dropped due to SAP not supporting a .NET Core / .NET 5 database driver.

    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();
    

    Schema.Exists Expressions

    You can write migrations conditional on the pre-existing schema, which comes in handy for getting you out of certain jams. For instance, if you need to make a column but aren't sure if it already exists:

    if (!Schema.Table("Users").Column("FirstName").Exists())
    {
        this.Create.Column("FirstName").OnTable("Users").AsAnsiString(128).Nullable();
    }
    

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

    • Edit this page
    In this article
    Back to top
    Copyright © 2018 Fluent Migrator Project
    Generated by DocFX
    Creative Commons License
    FluentMigrator Documentation by FluentMigrator Project is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License.