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
image
The Mappings
In our data layer we define the two mapping files Product.hbm.xml and Category.hbm.xml. The former's content is
namespace="Domain"
assembly="Domain">
and the latter's content is
namespace="Domain"
assembly="Domain">
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
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.
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
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
namespace="Domain"
assembly="Domain">
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
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
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 =
""+
"
" namespace='Domain'"+
" assembly='Domain'>"+
"
"
"
"
"
"
"";
public const string category_xml =
""+
"
" namespace='Domain'"+
" assembly='Domain'>"+
"
"
"
"
"
"";
[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
Blog Signature Gabriel .
Print | posted on Monday, April 28, 2008 9:52 AM
Comments on this post
# re: Create and Update Database Schema
Requesting Gravatar...
I am having problems getting NHibernate to create the PERSISTED columns:
CREATE TABLE [dbo].[Resource](
[Resource_Id] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](1000) NOT NULL,
[Short_Name] AS ([dbo].[GetNameWithFlat]([Name])) PERSISTED,
[Name_Checksum] AS (checksum([Name])) PERSISTED)
I tried using Formula, but it doesn't create the columns
Left by Dennis on May 21, 2008 10:03 PM
# re: Create and Update Database Schema
Requesting Gravatar...
@Dennis: what is "PERSISTED"? Which Database Product do you use?
Left by Gabriel Schenker on May 22, 2008 12:15 AM
# re: Create and Update Database Schema
Requesting Gravatar...
Hi,
ich have a question to the parameter "unique-key".
What I really want is, that the name would be published too.
Like this:
create table Person (
Id UNIQUEIDENTIFIER not null,
FirstName NVARCHAR(50) not null,
LastName NVARCHAR(50) not null,
primary key (Id),
unique uk_Person_Name (FirstName, LastName)
)
Is this possible?
Thanks
Left by Sandra on Jun 24, 2008 2:44 AM
# re: Create and Update Database Schema
Requesting Gravatar...
@Sandra: as far as I know it is NOT possible!
Left by Gabriel Schenker on Jun 25, 2008 8:45 PM
# re: Create and Update Database Schema
Requesting Gravatar...
@ Gabriel Schenker
What a shame! So migrations of a existing database scheme to a new version becomes more difficult.
Left by Sandra on Jun 25, 2008 9:48 PM
# re: Create and Update Database Schema
Requesting Gravatar...
@Sandra: to migrate an existing schema (in production) to a new version I don't recommend using UpdateSchema but rather a tool like Red Gate's Sql Compare which can produce the necessary alter scripts.
Use UpdateSchema during developement to sync the db's of each developer...
Left by Gabriel Schenker on Jun 26, 2008 7:24 PM
# re: Create and Update Database Schema
Requesting Gravatar...
Hi, Gabriel
Thank you for the great article.
I have a
Left by leilei on Jul 13, 2008 1:34 AM
# re: Create and Update Database Schema
Requesting Gravatar...
@leilei: if you NEED a primary key in the association table you might have a look at "ID-BAG" (please consult the nhibernate doc, or if you have time to wait I'll post an article on this very topic soon)
Left by Gabriel Schenker on Jul 20, 2008 6:07 PM
# Generating constraint names
Requesting Gravatar...
@Gabriel, I too would like to see the constraint names in the generated scripts, as per Sandra's comment.
In my previous job we ran into a lot of trouble when we let SQL Server generate random names for constraints. When the time came to change them, we had problems running our script against mutiple copies of the database as each database had a different name for the constraint!
However I really like this approach as there is no need to keep your scripts under source control any more, just the mapping files.
I wonder if it might be possible for me to submit a patch for NHibernate which will provide this functionality. I have not contributed to OSS before but this could be my first effort.
Left by Ross Neilson on Aug 20, 2008 2:24 AM
# re: Create and Update Database Schema
Requesting Gravatar...
@Ross: every body is highly welcome to provide patches to NHibernate!
You still have some scripts to keep under source control though (when you migrate your productive database from one version to the next you'll probably want to use a tool like Redgate's Sql Compare to generate the alter script...)
Please have a look at:
http://blogs.hibernatingrhinos.com/nhibernate/archive/2008/08/04/manage-sql-databases.aspx
Left by Gabriel Schenker on Aug 20, 2008 4:25 PM
# re: Create and Update Database Schema
Requesting Gravatar...
Gabriel,
We did use Sql Compare, for just that task, it really is a very useful piece of software.
I have got the NHibernate source code downloaded to my PC at home so I will try to produce a patch over the weekend.
No comments:
Post a Comment