Fluent DDL Builder

The fluent builder is a helper that makes easier the writing of Create/Alter table scripts. It is NOT an object to table mapper and it will never be. It's designed to support most common DDL features so that it's usable in the majority of real life usage cases.

Supported databases

• SqlServer
• MySql
• Postgres
• Sqlite - partially, only create table scripts

Generating a Create Table script

The manual way

var db= new DbAccess("sqlConnection");
var builder= db.DatabaseTools.GetCreateTableBuilder("MessageQueue", IfTableExists.DropIt);
builder.Columns
            .Add("Id", DbType.Int64, isNullable: false, autoIncrement: true).AsPrimaryKey("PK_store")
            .Add("MessageId", DbType.Guid, isNullable: false).AsUnique("UK_MessageQueueStore")
            .Add("Name",DbType.AnsiString,"200",false)
            .Add("Body", DbType.Binary)
            .Add("QueuedAt", DbType.DateTime, isNullable: false)
            .Add("ShouldRunAt", DbType.DateTime, isNullable: false)
            .Add("CompletedAt", DbType.DateTime)
            .Add("FailCount", DbType.Int16, isNullable: false, defaultValue: "0")
            .Add("Hash", DbType.AnsiStringFixedLength, "40", false).AsUnique("UC_MessageHash")
            .Add("Priority",DbType.String,"10").IfDatabaseIs(DbEngine.MySql)
                 .RedefineColumnAs("enum('low','normal','high')")
            .Add("IgnoreIt",DbType.Boolean)
                 ;
//customize primary key for a specific rdbms
builder.Constraints["PK_store"]
                .IfDatabaseIs(DbEngine.SqlServer)
                .PrimaryKeyOptions(SqlServerOptions.PrimaryKeyIs.Clustered());

//customize table for a specific rdbms
builder.TableOptionsFor(DbEngine.MySql, MySqlOptions.Table.EngineIs(TableEngineType.InnoDb));

//create unique index
builder.Indexes.AddIndexOn("Name", true);

//execute generated script
builder.ExecuteDDL();

Some guidelines

• If size is not specified, it's assumed to be maximum size
• By default, every column is nullable
• Constraint and index names are optional, however is good practice to have them, especially if you want to customize things for a specific rdbms.
• It isn't required to add the constraints when adding the columns. You can add them later via the [builder].Constraints property. Same with indexes.
• Some rdbms specific options are supported, as shown in the example above.
• Don't forget this is a way of writing DDL scripts using C#, it's not a mapper.

Customize script for different rdbms

Usually, the builder knows how to deal with each of the supported rdbms to generate a valid script. However, sometimes you want to have more control when dealing with a certain db or to activate specific options (in the example, we set the option that for MySql the table engine should be InnoDb).

The supported db specific options are available as "[Dbengine]Options". If you can't find an option, it means it isn't supported.

You can customize items in a couple of ways:

• Columns
  • RedefineAs - tell the builder to use the provided definition for that column. The definition must not contain the column name;
  • SetColumnOptions - Use the [DbEngin]Options class to access supported options