Generate database schema for MySQL using NHibernate hbm2ddl tool

NHibernate has hbm2ddl tool which allows automatically export database tables schema based on provided mappings. I.e. we may define mapping using C# for some POCO class:

public class User
{
    public virtual int Id { get; set; }
    public virtual string FirstName { get; set; }
    public virtual string LastName { get; set; }
}

like that:

public class UserMap : ClassMap<User>
{
    public UserMap()
    {
        Table("[User]");
        Id(x => x.Id, "UserId");
        Map(x => x.FirstName);
        Map(x => x.LastName);
     }
}

and then based on this mapping hbm2ddl will generate the following SQL code for creating User table:

create table `User` (
    UserId INTEGER NOT NULL AUTO_INCREMENT,
    FirstName TEXT,
    LastName TEXT,
    primary key (UserId)
);

That is convenient because we don't need to maintain database schema separately - any change in C# models and mappings will be automatically reflected in db schema. However in order to use hbm2ddl for MySQL we need to add few tweaks to the export code:

• MySQL uses back ticks instead of square brackets
• MySQL syntax requires semicolon after each code line in generated SQL
  

First of all we need to create Fluent NHibernate configuration for MySQL syntax:

var config = Fluently.Configure()
    .Database(
        MySQLConfiguration.Standard
            .ConnectionString(connectionString)
            .AdoNetBatchSize(100)
            .DoNot.ShowSql()
    )
    .Mappings(cfg =>
    {
        cfg.FluentMappings.AddFromAssemblyOf<UserMap>()
            .Conventions.Setup(mappings =>
            {
                mappings.AddAssembly(typeof(UserMap).Assembly);
            });
    });

With this Fluent NHibernate config export code will look like this:

var nhConfig = ... // see above
var export = new SchemaExport(nhConfig);
var sb = new StringBuilder();
export.Create(schema =>
{
    schema = schema.Replace("[", "`").Replace("]", "`");
    if (schema.EndsWith("\r\n"))
    {
        schema = schema.Substring(0, schema.Length - 2) + ";\r\n";
    }
    else
    {
        schema += ";";
    }
    sb.Append(schema);
}, false);

Console.WriteLine(sb.ToString());

Here we replace square brackets by back ticks and add semicolon to the end of each added line. After that we will have valid SQL code for MySQL syntax with database schema.