Ayende @ Rahien

It's a girl

Database independence with NHibernate

Karl has posted about his experience of porting an NHibernate application from SQL Server to PostgreSQL, long story short, he did it in 1 hour.

He does bring up a few points where the database that he was using bled into the code, hurting the database independence goal. I wanted to look at those and point out the builtin solutions that NHibernate provides for those.

Handling unique constraints violations, Karl has code like this:

try
{
    Session.SaveOrUpdate(user);
    transaction.Commit();
    return true;
}
catch (GenericADOException ex)
{
    transaction.Rollback();
    var sql = ex.InnerException as SqlException;
    if (sql != null && sql.Number == 2601)
    {
        return false;
    }
    throw;
}
catch (Exception)
{
    transaction.Rollback();
    throw;
}

Obviously, this code relies heavily on internal knowledge of SQL Server error codes, and wouldn’t translate to PostgreSQL.

With NHibernate, you deal with those issues by writing ISqlExceptionConverter, you can read Fabio’s post about this, but the gist of it is that you can provide your own exceptions in a database independent way.

The count function may return different types on different databases. NHibernate tries to stay true to whatever the database is giving it, so it wouldn’t truncate information. You can force the issue by overriding the count function definition by writing a derived dialect. You can see an example of that in this post.

Bits & Booleans, while SQL Server accepts 1 as a Boolean value, PostgreSQL requires that you would use ‘1’ instead. This is where NHibernate’s query.substitutions supports come in. I usually define substitutions for true and false and then use true and false in the queries. Based on the database that I am running on, I can select what will be substituted for those values.

Achieving database independence with NHibernate is very easy, as Karl’s 1 hour porting story proves.

Comments

Ryan Cromwell
10/23/2009 01:48 AM by
Ryan Cromwell

Substitutions... great nugget of knowledge I'll take full advantage of.

tobsen
10/23/2009 06:58 AM by
tobsen

Instead of using the ISqlExceptionConverter, there is also a way to use Spring.Net's Exception Translation ( www.springframework.net/.../orm.html) both via Attributes or by using their HibernateTemplate.

Billy Stack
10/23/2009 07:41 AM by
Billy Stack

How often would any application be ported from one database to another? - very rarely in my experience.

Also, I agree that NHibernate gives you db independence but it is very "hard" (in fact impossible) to have a codebase using NHibernate that is 100% "persistent ignorant".

E.g.

Why must I have a default ctor in my entities? - I have yet to see a nice easy way around this...

Why must you have "virtual" properties to use lazy loading?

I know the reasons why to both questions above but in a "persistence ignorant" domain, nothing (not even entities) should be influenced!

Anders
10/23/2009 08:23 AM by
Anders

And supporting multple databases also forces you to use RoR style migrations for schema changes. If you don't do that you are stuck with maintaining one sql release script per database type...

Ayende Rahien
10/23/2009 08:29 AM by
Ayende Rahien

Andres,

a) Having a single migration script per supported DB works much better than explicitly supporting the different DBs.

b) new SchemaUpdate will take care of that anyway.

Ayende Rahien
10/23/2009 08:30 AM by
Ayende Rahien

Billy, actually, it is quite common to have projects that requires database independence.

Most commercial packages do no want to be tied to a DB impl, since that means that they will have less customers.

Richard Armstrong
10/23/2009 08:35 AM by
Richard Armstrong

Even is you are not considering porting data, this does demonstrate good design by way of separation of concerns with NHibernate.

Paul Hatcher
10/23/2009 09:17 AM by
Paul Hatcher

Billy

I've worked on a number of application where the application wasn't ported but there were client requirements from the outset of supporting Access, SQL Server, Oracle, DB2 and MySql.

If you are selling commercial software, sometimes you have to conform to what the client wants e.g. "We're an Oracle house, we won't buy your software if it runs on SQL Server".

RichB
10/23/2009 10:53 AM by
RichB

@BillyStack> My project is to port a system from Oracle to SQL Server. Oh, and this system did not use NHibernate when I started, so the first task is to move 1000 SPs to NHibernate. And I neglected to mention there is still much VBScript/VB6 code in the client apps which have to have their data access layers moved to NHibernate.

So yes, database migration is rare. But it does happen.

Secondly, I'm beginning to understand that Aggregate root Repositories are much better candidates for being database agnostic than DAOs with lots of HQL.

Eyston
10/23/2009 02:33 PM by
Eyston

How often would any application be ported from one database to another? - very rarely in my experience.

Even if you don't, how nice is it to be able to test against a local sqllite, mysql, whatever before deploying to the production db?

E.g.

Why must I have a default ctor in my entities? - I have yet to see a nice easy way around this...

Why must you have "virtual" properties to use lazy loading?

Get over it. If these are the worst influence your persistence layer has over your domain, be thankful.

Billy Stack
10/23/2009 03:39 PM by
Billy Stack

I work mostly with web applications/services area where I must say that I have rarely (only once in fact) been involved in database migration. I think it would be rare in the web applications/services space.

However in commercial packages I can see where not being tied to a DB impl is strategically advantageous! - So I probably underestimated db migration usage in this area...

I fully agree with this post that NH is db independent and the advantages to this are overwhelming, and I am a big fan of NH but I have struggled integrate NH into codebases where "persistence ignorance" is the order of the day.

E.g. if a codebase using NH has segregated the data access layer behind repository abstractions, my entities still get influenced by NH e.g.

Default ctor must exist

Virtual properties must exist to use lazy loading

If using immutable types, and validation through the ctor is required - you cannot directly get nh to call the ctor "easily"

This has been my struggle with nh, but I still think it rocks as an ORM...

Anyone have the same problems?...

Comments have been closed on this topic.