NHibernate:How to Build Great Mappings and use SchemaExport

NHibernate.Tool.hbm2ddl.SchemaExport: this thing is so cool its not even funny.

Consider the following code:

SchemaExport _schemaExport = new SchemaExport(new Configuration().Configure());
_schemaExport.Create(false,true);

Do you know what that does? It builds a database schema from the mapping files! The first parameter is an option to output the script to the console. The second parameter is an option to execute the script in the database.

All you need is an App.config such as:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>

<configSections>
<section name="hibernate-configuration" type="NHibernate.Cfg.ConfigurationSectionHandler, NHibernate" />
</configSections>

<!--nhibernate configuration-->
<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
<session-factory>
<!--the brand of dbms-->
<property name="dialect">NHibernate.Dialect.MsSql2005Dialect</property>
<!--connection provider-->
<property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
<!--sql client driver-->
<property name="connection.driver_class">NHibernate.Driver.SqlClientDriver</property>
<!--connection string-->
<property name="connection.connection_string">Server=localhost; initial catalog = YOURDBNAME; Integrated Security=SSPI</property>
<!--output generated sql to console window-->
<property name="hibernate.show_sql">true</property>
<!--the assembly with the embedded mapping files-->
<mapping assembly="[YOURASSEMMBLY without the .dll ectension]" />
</session-factory>
</hibernate-configuration>
</configuration>

If you don't have sql server 2005, change the dialect; NHibernate supports 99% of the popular modern databases. These are the steps you need:

• Create an empty database using your DB server
• Create NHibernate entities, create the mappings
• Add the app.config listed above
• Execute the code listed above

And there is your database. If you want to drop it at the end, simply call _schemaExport.Drop.

Do you know what this means? It means that my XML mapping IS my database schema.
You dont need to synchronize the database schema from the mappings if your database schema is CREATED FROM THE MAPPINGS!

If your mappings are the de-facto definition of your database, then you better have a very high degree of control over the database being built. Let's dissect a mapping file that is very conscious to database concerns:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" default-lazy="true" default-cascade="all-delete-orphan">
<class name="acme.widget, acme" table="Widgets" >

Notice how you can set the default-lazy and default-cascade behavior of NHibernate on the class declaration? They define the general behavior of NHibernate in terms of loading objects and saving them, and changing these settings changes the behavior of your program considerably. Learn more about these settings here and here.

<!--primary key mapping-->
<id name="m_id" column="WidgetID" type="int" access="field" unsaved-value="0">
 <generator class="identity" />
</id>

See the unsaved-value=0? This means any Widget object with an ID of 0 warrants an insert call when using the SaveOrUpdate method. Any Widget object with a nonzero ID warrants an update call. See the access="field"? This gives NHibernate the permission to manipulate the ID field through a private variable. This way, you don't need to expose the ID of an object as a public writable property.

<!--property-to-column mappings-->
<property name="WidgetName" not-null="true" type="string" length="30" unique="true" unique-key="myCustomUniqueKeyName" >
 <column name="WidgetName" sql-type="varchar(30)" not-null="true" index="myCustomIndex" unique="true"/>
</property>

See how I add a <column> tag? the column tag gives me specifics about the sql data type of the column, the index, and allows me to set a named unique-constraint on the column (enforced by the database).

<!-- Many To One Relationships -->
<many-to-one name="Factory" column="FactoryID" not-null="true" not-found="exception" class="acme.Factory, acme" index="myIndex" foreign-key="myCustomFKName" />
</hibernate-mapping>

In here I explicitly named an index on this column called "myIndex". SchemaExport automatically builds the foreign key constraints for me, but I can give it my own name 'myCustomFKName'. I forced the property not to allow nulls (rarely do you need nulls) and to throw an exception if, for some reason, the relationship is broken. Redundant, perhaps, but there is nothing wrong with some redundancy to ensure the integrity of the model.

Using this kind of database-conscious mapping technique allows you to build a quality schema automatically from SchemaExport. I have challenged this SchemaExport with difficult schemas, single-table inheritance mapping, multi-table inheritance mapping, many-to-many mappings, and it never falls short.

Almost never. Here are a few examples where this is not enough:

• Views, UDFs, stored procs and all of those other non-NHibernate related database objects
• I want a clustered index on a foreign key, so all of the "child" rows are arranged physically adjacent with respect to the parent rows for faster disk access

Fortunately, SchemaExport gives us a great all-around solution for these kind of custom database concerns using the database-object tag...

<database-object>
<create>create view ....</create>
<drop>drop view....</drop>
</database-object>

You can also write a custom class to create the database object by implementing the NHibernate.Mapping.IAuxiliaryDatabaseObject class.

<database-object>
     <definition class="MyViewDefinition, acme"/>
 </database-object>

This also works for different dialect scopes using the dialect-scope parameter if you need to support different dbms's. all of this information can be found at the bottom of this page.

Bottom line is, the SchemaExport tool is a great shortcut to getting a database up and running from ntohing but the object code and some mappings. To upgrade the schema of existing databases without loss is still a tricky procedure, but this is great for things like:

• Unit tests (destroy and rebuild a fresh data population with every test)
• Early application development lifecycle (where the schema definition is in a high state of flux)
• setup/deployment - creating a database schema within a setup deployment installation program is a snap with this tool