Introduction
When developing an application using DDD one starts by trying to define a model of the domain for/in which the application should be used. At the same time you try to establish the so called ubiquitous language. At some point you might need to store and or retrieve data into or from a data source. Very often this data source is a relational database. But it's not necessarily always the case. It could as well be a web service or a XML document. That leads me to the notion that "the database is just an implementation detail of the application". Or if I turn this sentence a little bit around "...it should not be the database (-schema) that determines the design of an application but rather should the database schema be a natural outcome of the domain model...". I know, this is NOT what a DBA likes to hear. But trust me I have developed many applications in the past where the first thing that was designed of the application was the entity relationship diagram (ERD)... I have also implemented quite a lot of stored procedures on Oracle and SQL server in the past. So I know both sides of the argument.
Assuming that the database schema should be an implementation detail of the overall application it would be great if the schema could be somehow auto-generated from the domain model. As you might expect, when using NHibernate as the ORM tool this is possible. We have to distinguish between the two possibilities
- (re-) create a new schema from scratch
- updating an existing schema
Create Schema
The model
To start we define a very basic model
The Mappings
In our data layer we define the two mapping files Product.hbm.xml and Category.hbm.xml. The former's content is
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
namespace="Domain"
assembly="Domain">
<class name="Product">
<id name="Id">
<generator class="guid"/>
</id>
<property name="Name"/>
<many-to-one name="Category" class="Category"/>
</class>
</hibernate-mapping>
and the latter's content is
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
namespace="Domain"
assembly="Domain">
<class name="Category">
<id name="Id">
<generator class="guid"/>
</id>
<property name="Name"/>
</class>
</hibernate-mapping>
Note that we have defined only the absolute minimum needed and rely heavily on the (meaningful) defaults provided by NHibernate.
Tests
To analyze the database schema creation script generated by NHibernate we write the following test
[TestFixture]
public class CreateSchema_Fixture
{
private Configuration _cfg;
[SetUp]
public void SetupContext()
{
_cfg = new Configuration();
_cfg.Configure();
_cfg.AddAssembly(Assembly.LoadFrom("DataLayer.dll"));
}
[Test]
public void Create_a_database_schema_creation_script()
{
var export = new SchemaExport(_cfg);
var sb = new StringBuilder();
TextWriter output = new StringWriter(sb);
export.Execute(true, false, false, false, null, output);
}
}
Note that the configuration file hibernate.cfg.xml has the following content in our case
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
<session-factory>
<property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
<property name="dialect">NHibernate.Dialect.MsSql2005Dialect</property>
<property name="connection.driver_class">NHibernate.Driver.SqlClientDriver</property>
<property name="connection.connection_string">Server=(local);Database=NHibernateFAQ;Integrated Security=SSPI;</property>
<property name="show_sql">true</property>
</session-factory>
</hibernate-configuration>
That is, we are using a SQL Server 2005 database called NHibernateFAQ. It is not necessary to have SQL Server 2005 installed though since we are only generating (and analyzing) the scripts generated by NHibernate and not accessing the database itself!
The output generated by the above test is similar to this
if exists (select 1 from sys.objects
where object_id = OBJECT_ID(N'[FK1F94D86A9F364CC5]')
AND parent_object_id = OBJECT_ID('Product'))
alter table Product drop constraint FK1F94D86A9F364CC5
if exists (select * from dbo.sysobjects
where id = object_id(N'Product')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table Product
if exists (select * from dbo.sysobjects
where id = object_id(N'Category')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table Category
create table Product (Id UNIQUEIDENTIFIER not null,
Name NVARCHAR(255) null,
Category UNIQUEIDENTIFIER null,
primary key (Id))
create table Category (Id UNIQUEIDENTIFIER not null,
Name NVARCHAR(255) null,
primary key (Id))
alter table Product add constraint FK1F94D86A9F364CC5
foreign key (Category) references Category
That's really wonderful for a quick shot!
The default for a column is that it is nullable and the default type and length for a string type field is NVARCHAR(255).
Mandatory columns and maximal length of column
But there are a few details that we might want to improve. First we want to make the Name columns mandatory (NOT NULL). Nothing easier than that; just add an attribute not-null="true" to the mapping files where appropriate. Also we want to limit the length of the Name column to given amount, e.g. 50 characters. Just add a length="50" to the appropriate property of the mapping files. The create table part of the schema creation script is then
create table Product (Id UNIQUEIDENTIFIER not null,
Name NVARCHAR(50) not null,
Category UNIQUEIDENTIFIER null,
primary key (Id))
create table Category (Id UNIQUEIDENTIFIER not null,
Name NVARCHAR(20) not null,
primary key (Id))
Foreign Keys
There is still a detail in the script that might disturb you. It's the name of the foreign key constraint between the Product and the Category tables. If we want to choose a name we can use the foreign-key attribute on the many-to-one node of the mapping file, e.g.
<many-to-one name="Category" class="Category" foreign-key="fk_Product_Category"/>
now the corresponding snippet of the script is
if exists (select 1 from sys.objects
where object_id = OBJECT_ID(N'[fk_Product_Category]')
AND parent_object_id = OBJECT_ID('Product'))
alter table Product drop constraint fk_Product_Category
...
alter table Product add constraint fk_Product_Category
foreign key (Category) references Category
Unique Constraints
If we want to guarantee that the Name property of the Category class is unique we can do this by either using business logic to enforce this requirement or define a unique constraint on the database. Let's have a look at the latter. Just add the attribute unique="true" to the mapping of the Name property in the mapping file for the Category class. That is
<property name="Name" not-null="true" length="20" unique="true"/>
and the script generated is this
create table Category (
Id UNIQUEIDENTIFIER not null,
Name NVARCHAR(20) not null unique,
primary key (Id)
)
If you want to define a unique constraint which spans multiple columns then you have to resort to the attribute unique-key. Let's assume we have a Person class like
public class Person
{
public Guid Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
}
and we want to define a unique constraint on the combination of the FirstName and LastName columns then our mapping file looks like this
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
namespace="Domain"
assembly="Domain">
<class name="Person">
<id name="Id">
<generator class="guid"/>
</id>
<property name="FirstName" not-null="true" length="50" unique-key="uk_Person_Name"/>
<property name="LastName" not-null="true" length="50" unique-key="uk_Person_Name"/>
</class>
</hibernate-mapping>
Note that the name you choose for the unique-key is not important (here "uk_Person_Name"). Just use the same name for all columns that make up for a unique constraint. The create table script generated by NHibernate is then
create table Person (
Id UNIQUEIDENTIFIER not null,
FirstName NVARCHAR(50) not null,
LastName NVARCHAR(50) not null,
primary key (Id),
unique (FirstName, LastName)
)
Indices
We might also want to tune our database schema and define some indices. Often we might search for a product by its name and thus the Name column might be a good candidate for an index. How can we do this. Well it's again very easy. Just add the attribute index to the right property tag in the product mapping file and provide a name for the index. That is
<property name="Name" not-null="true" length="50" index="idx_Product_Name"/>
now our create script will look like this (note the second last statement where the index is created)
create table Category (Id UNIQUEIDENTIFIER not null, ...)
create table Product (Id UNIQUEIDENTIFIER not null, ...)
create index idx_Product_Name on Product (Name)
alter table Product add constraint fk_Product_Category ...
Check Constraints
We can also define some check constraints on our columns in the database. Let's assume our product has a UnitsOnStock property which must be greater or equal than zero. To define this constraint on the database column we can define the mapping of UnitsOnStock as follows
<property name="UnitsOnStock" not-null="true" >
<column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
</property>
Note that there is no check attribute defined for the property tag. Thus we have to use the child tag column for this purpose. The create table script is then
create table Product (
Id UNIQUEIDENTIFIER not null,
Name NVARCHAR(50) not null,
UnitsOnStock INT null check( UnitsOnStock >= 0) ,
Category UNIQUEIDENTIFIER not null,
primary key (Id)
)
Update Schema
Once we have an existing database schema which we cannot re-create from scratch (because we might already have a first version of our application in production with productive data...) we need another technique. Fortunately NHibernate provides us the possibility to update an existing schema, that is NHibernate creates an update script which can the be applied to the database.
As usual we want to define a unit test for this situation. In the setup for the unit test I'll predefine a first version of my database schema. This time we need to have a database installed on the system (in our case SQL Server 2005) since this first version of the schema is generated in the database otherwise update schema will not work.
So let's have a look at the setup
[TestFixture]
public class UpdateSchema_Fixture
{
private Configuration _cfg;
public const string product_xml =
"<?xml version='1.0' encoding='utf-8' ?>"+
"<hibernate-mapping xmlns='urn:nhibernate-mapping-2.2'"+
" namespace='Domain'"+
" assembly='Domain'>"+
" <class name='Product'>"+
" <id name='Id'>"+
" <generator class='guid'/>"+
" </id>"+
" <property name='Name' not-null='true' length='20'/>" +
" </class>"+
"</hibernate-mapping>";
public const string category_xml =
"<?xml version='1.0' encoding='utf-8' ?>"+
"<hibernate-mapping xmlns='urn:nhibernate-mapping-2.2'"+
" namespace='Domain'"+
" assembly='Domain'>"+
" <class name='Category'>"+
" <id name='Id'>"+
" <generator class='guid'/>"+
" </id>"+
" </class>"+
"</hibernate-mapping>";
[SetUp]
public void SetupContext()
{
// Setup "old" database schema
_cfg = new Configuration();
_cfg.Configure();
_cfg.AddXml(product_xml);
_cfg.AddXml(category_xml);
var export = new SchemaExport(_cfg);
export.Execute(false, true, false, false);
}
}
I define 2 strings containing the (first version of the) mapping files for the Category and the Product class. In the SetupContext method I create a configuration object and feed it with these two mapping XML fragments. I then export this schema to the database by creating an instance of the SchemaExport class and calling the Execute method.
Note that the Category mapping fragment does not contain a mapping for the Name column and the Product mapping fragment does not contain a mapping for the relation to the Category class as well as no definition of the UnitsInStock column.
Now I want to test the UpdateSchema class and thus implement the following test
[Test]
public void Update_an_existing_database_schema()
{
_cfg = new Configuration();
_cfg.Configure();
_cfg.AddAssembly(Assembly.LoadFrom("DataLayer.dll"));
var update = new SchemaUpdate(_cfg);
update.Execute(true, false);
}
The update script generated by NHibernate is
alter table Category add Name NVARCHAR(20) unique
alter table Product add UnitsOnStock INT check(UnitsOnStock >= 0)
alter table Product add Category UNIQUEIDENTIFIER
alter table Product add constraint
fk_Product_Category foreign key (Category) references Category
As expected I only get alter statements for existing tables and create statements for the elements missing so far.
Code
You can find the code here. Download it with a SVN client like TortoiseSVN. It's a VS 2008 project.
Summary
When developing a application using DDD the database is often considered an "implementation detail". NHibernate provides us tools to auto-generate create and alter scripts for the database schema from the domain model. I have shown you, by using a simple domain model how to create a schema from scratch and how to alter a pre-existing database schema. I have also discussed various optimization techniques used when creating database schemas as there are unique constraints, indices and check constraints.
Enjoy
.