Monday, September 17, 2012

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

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


        CurrentSessionContext<ThreadStaticSessionContext>().


        BuildConfiguration();


    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.


Keys


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


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.










DIGITAL JUICE

No comments:

Post a Comment

Thank's!