Domain Model over Database Schema. Using fluent nHibernate to generate the database

In domain driven design everything centers on your domain model. Domain objects are persisted to a database. What that database exactly looks like is not known to the model. Nevertheless the database has its technical aspects. To name some

  • Primary and foreign keys to guard integrity
  • Enforce values
  • Efficient storage to limit disk usage and IO
  • Indices to speed up reads

These are aspects unknown to the domain model.

The domain model is mapped to the database using an OR-mapper such as nHibernate. At first sight building the domain model and building the database are two tasks using fluent nhibernate to bridge the two worlds in mapping files. Working domain driven it would be nice to just work on the domain model and generate the database from there. The good thing is that this is possible using fluent nHibernate. The bad thing is that it requires some fiddling to fulfill the aspects I’ve just mentioned. Documentation on this is scattered over the web. In this post I’ll describe how we generate and maintain our database straight from the domain model.

All the basics of using fluent nHibernate are very well documented here.

Generate and update a schema

The fluent API has a method to create or update a database schema.

public static void CreateDataBase(string connectionString)


    Configuration config = Fluently.Configure().

        Database(MsSqlConfiguration.MsSql2008.ConnectionString(c => c.Is(connectionString))).

        Mappings(m => m.FluentMappings.AddFromAssembly(Assembly.GetExecutingAssembly())).



    var schemaExport = new SchemaExport(config);

    schemaExport.Create(false, true);



For a more detailed explanation of the parts read the wiki. The mappings are generated and passed to a new SchemaExport object. The Create method actually creates the database. Depending on the parameters the database is created (overwriting any existing db) or the database is updated. The latter is not that very useful, as only new tables and columns are added, any updates on existing ones are ignored.

The nice thing is that all primary and foreign keys are created in the resulting database. The property marked as Id in the mapping has become the primary key of the table. All References in the mapping have become (nullable) foreign keys.

The bad thing is that as a whole the database schema is not suited as a real production, or even testing, database.  Amongst many other things: all strings have been mapped to nullable nvarchars of length 255

In the remainder of this post I’ll sum up ways to fine tune the mappings to be able to generate a database which does meet all requirements.

Not null columns

Whether a column can be null can be explicitly mapped with .Not.Nullable()

Map(c => c.ResultaatCode).CustomType<ResultaatCodeCOV>().Not.Nullable();


This applies to any type, also on foreign keys

References(a => a.Patient).Not.Nullable();


Varchar columns

By default fluent nhibernate maps strings to nvarchars-255. You must have very specific reasons to use nvarchars instead of varchars. Besides that 255 is not a “one size fits all” value. The type of the column can be specified using the CustomType mapping method. Which takes a string to specify the type.

We have built a tiny helper function in our mapping base class

public static string VarChar(int length)


    return string.Format(“AnsiString({0})”, length);



Note that the name of the type“Ansistring”, might not work in every brand of database. It does in sql server, you’ll have to find your own magic string for your brand…  The method is used in the mappings. Here it used to define a not nullable varchar column of length 100.

Map(b => b.Naam).CustomType(VarChar(100)).Not.Nullable();


Special attention should be give to text columns without a fixed length. In sql server they are typed as varchar(max). When mapping such a column with fluent nhibernate the following is required

Map(b => b.Notities).CustomType(StringClob).CustomSqlType(SqlVarCharMax);


The parameter values are defined in our mapping base class

public const string StringClob = “StringClob”;

public const string SqlVarCharMax =“varchar(max)”;


The extra CustomSqlType() is not important for generating the database but essential when using it. Omitting it will result in saved values to be cut of to the first 4000 characters. Don’t ask me why, we did experience the problem, did find out the problem was real and did solve it this way.

Date columns

By default datetime fields are mapped to a datetime column. In a lot of cases a date column is far more appropriate. Not just for saving some disk space (and io speed). When querying for a date it is far more easy to compare a date to a date then finding the range of datetime’s which fall on a specific date.

We also have a const for mapping dates with the CustomType method .

public const string Date = “date”;


Which is used to map properties where only the date part is important.

Map(b => b.DatumPlan).CustomType(Date).Not.Nullable();


Which will result in a non null date column.


A key has a name. By default a random name is generated every time a new key is created. The bad thing about that is, besides being not very descriptive, that a tool like sqlCompare will always see a new key after every new schema generation. The nice thing is that you can specify the name of the foreign key names in the mapping.

References(a => a.Patient).Not.Nullable().ForeignKey(“FK_Aanmelding_Patient”);

An unique constraint is enforced in the database by a uniquekey, this key can be composed out of multiple columns. Also these keys can defined in the mappings, using the UniqueKey method. Passing the same name to multiple properties will result in a composite unique key

var keyName = string.Format(“CodeBeginDatum_{0}, tableName);

Map(c => c.Code).CustomType(VarChar(12)).UniqueKey(keyName).Not.Nullable();

Map(c => c.BeginDatum).CustomType(Date).UniqueKey(keyName).Not.Nullable();


Here the combination of the columns Code and BeginDatum is unique.



Indices are different from keys. Keys define constraints (and are often indexed), indices are only useful to the database engine to speed up performance. Nevertheless, also indices can be defined in a mapping

References(d => d.ZorgTraject).Not.Nullable().Index(“DBCZorgTraject”).ForeignKey(“FK_DBC_ZorgTraject”);


Here a foreign key is indexed. Easy as that.

Deploying the database

A domain model will change over time. So will the mappings. As said before fluent nHibernate is quite good in creating a new virgin database but not good at all in modifying an existing one. In which SQL-compare absolutely shines. Especially when you watch things like the names of the foreign keys rolling out changes is no big deal.

This is our recipe

  • Generate a fresh database as described in this post
  • Create a snapshot with SQLcompare
  • Copy the snapshot to the testing/production server
  • Use SQLcompare to modify the database

The nice thing with a snapshots is that it can bridge db-server versions. Our development machines run sql 2008 R2, the production server is running R1 and this way we still have no reason to buy the upgrade.

Winding down

This is by far not the full story. Fluent nHibernate has much more to offer, including the conventions API which could be a way to further explore the possibilities. For now this works well. We have a rich domain model which now also completely drives the database design.

As a final note: perhaps the usage of some Dutch in column names might irritate you. But using these names is (still) on purpose. When I write “BeginDatum” this refers to a very specific meaning of a date in that domain entity, not to something as vague a “Begin date”. Also here: it’s all about the DM.

This entry was posted in Uncategorized. Bookmark the permalink. Follow any comments here with the RSS feed for this post.
  • Anonymous

    I hadn’t seen mapping-by-code yet. Googling I found your very informative series on that.
    Thank you for sharing

  • NOtherDev

    The main problem with Fluent NHibernate is that there is almost no active development now. From NHibernate 3.2 there is a powerful alternative built in NHibernate itself – mapping-by-code, which is also very powerful, convention-over-configuration style, much better tied with NH. In case of new NHibernate project, I would no longer consider using FNH.

  • kstenson

    A better question to ask is what problem(s) is EF causing you that you think switching ORM might solve.

  • Anonymous

    You are right, as we are dealing with varchars here that should read fixed maximum length.
    Just follow the link for more information on sql-Compare. Yes, its is a commercial product; but the license is worth every penny. I’m not paid to say that :)

  • Andralyng

    When you mention — “Special attention should be give to text columns without a fixed length”, as far as I know, text column data type hasn’t a fixed length in its declaration. It’s traduced to LONG or CLOB data types.  So, what you wanted to mean is beware of declaring VARCHAR with no fixed length … Also, when you remark the use of “Sql Compare” to avoid tangle database version, which is exactly SC?  a tool ? does it involve buying license?  

  • Harald Nielsen

    Gonna take this answer was to me

    Excellent, I see :) This is certainly better DRY!

  • Anonymous

    It would when this code was in the domain model. We have all this mapping code in a separate assembly which uses the domainmodel and is used by the repositories.

  • Harald Nielsen

    Doesn’t this violate the #DDD, domain models shouldnt care aboute its from a database but its the repository that persists and updates the domain models?

  • Grant Fritchey

    Excellent post. I love seeing an ORM used as an ORM (object to relational mapping). It sure beats all those examples that are basically OOM (object to object mapping) by forcing the object model into the relational database. This is ORM & nHibernate done correctly.

  • Ugyvitel

    I hope is not an “aggressive” question but is it a good idea to switch from EF to nHibernate? Mid-size app, MSSQL, .NET/C#.

  • Anonymous

    That does not fix the varchar vs. nvarchar issue.

  • Mike Cole

    You can simply do Length(x) to set Varchar widths. Better than tying your mappings to a specific database implementation.

  • Feldman Sean

    FluentNHibernate has helped a lot in my case at the initial stages when domain needed to change as our understanding of it was evolving. I can’t even imagine what would it be with raw SQL. Saying this, having no schema at all (at least initially) is even better. But that would be a different subject. Great post.