Monday, October 20, 2008

Mapping entities to multiple databases with NHibernate

The legacy application I'm currently replacing features multiple databases for some insane reason. Luckily it's quite easy to get NHibernate to do joins across databases so long as they are on the same server. The technique is detailed by Hector Cruz in this thread on the NHibernate forum. The trick is to specify the schema you are addressing in each mapping file. Because the schema name simply becomes a table prefix, you can also use it to specify cross database joins. So long as you follow good practice and have one mapping file per entity, it means that, in theory, each entity could be persisted to a different database. I've put together a little project to show this working using Northwind. You can download the code here:

I took a backup of Northwind and then restored it to a new database so that I had a Northwind and a Northwind2. I'm going to get the Product entity from the Products table on Northwind and the Supplier from the Suppliers table in Northwind2. The great thing is that you only need a single connection string pointing to one database (in my case the original Northwind).

Here's the NHibernate configuration:

<?xml version="1.0" encoding="utf-8" ?>
    <section name="hibernate-configuration" type="NHibernate.Cfg.ConfigurationSectionHandler,NHibernate" />
  <hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
      <property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
      <property name="connection.driver_class">NHibernate.Driver.SqlClientDriver</property>
      <property name="connection.connection_string">Data Source=.\sqlexpress;Initial Catalog=Northwind;Integrated Security=True</property>
      <property name="dialect">NHibernate.Dialect.MsSql2005Dialect</property>
      <property name="show_sql">true</property>
      <mapping assembly="Mike.NHibernate.Multiple"/>

Nothing special here. I've just nominated the original Northwind database to be my  initial catalogue. Next I've got two entities, Product and Supplier:

namespace Mike.NHibernate.Multiple
    public class Product : Entity
        public virtual string ProductName { get; set; }
        public virtual Supplier Supplier { get; set; }
namespace Mike.NHibernate.Multiple
    public class Supplier : Entity
        public virtual string CompanyName { get; set; }

Once again pure persistence ignorance. You don't have to do anything special with your entities. Now, here's the trick: The mapping file for the Product entity specifies the schema as Northwind.dbo:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" schema="Northwind.dbo" >
  <class name="Mike.NHibernate.Multiple.Product, Mike.NHibernate.Multiple" table="Products">
    <id name="Id" column="ProductID" type="Int32">
      <generator class="identity" />
    <property name="ProductName" />
    <many-to-one name="Supplier" class="Mike.NHibernate.Multiple.Supplier, Mike.NHibernate.Multiple" column="SupplierID" />

While the Supplier mapping file specifies Northwind2.dbo:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" schema="Northwind2.dbo" >
  <class name="Mike.NHibernate.Multiple.Supplier, Mike.NHibernate.Multiple" table="Suppliers">
    <id name="Id" column="SupplierID" type="Int32">
      <generator class="identity" />
    <property name="CompanyName" />

Now, hey-presto! When I run this little console program to retrieve a Product from NHibernate, I get an object graph back with Product entities from Northwind and Supplier entities from Northwind2.

using System;
using NHibernate.Cfg;
namespace Mike.NHibernate.Multiple
    class Program
        static void Main()
            var configuration = new Configuration();
            var sessionFactory = configuration.BuildSessionFactory();
            var session = sessionFactory.OpenSession();
            var product = session.Load<Product>(1);
            Console.WriteLine("Product: {0}, Supplier: {1}", product.ProductName, product.Supplier.CompanyName);

NHibernate generates this SQL:


 product0_.ProductID as ProductID0_0_, 
 product0_.ProductName as ProductN2_0_0_, 
 product0_.SupplierID as SupplierID0_0_ 
FROM Northwind.dbo.Products product0_ 
WHERE product0_.ProductID=@p0; @p0 = '1'
 supplier0_.SupplierID as SupplierID1_0_, 
 supplier0_.CompanyName as CompanyN2_1_0_ 
FROM Northwind2.dbo.Suppliers supplier0_ 
WHERE supplier0_.SupplierID=@p0; @p0 = '1'

As you can see the Product was retrieved from Northwind and the Supplier from Northwind2. It's similar to when you do cross database joins in a stored procedure. The stored procedure has to live in a particular database, but because each table gets prefixed with it's database name the DBMS simply looks up the table in the referenced database.

Note that this trick is simply to deal with a legacy situation that I can't do much about. You really don't want to architect a system like this from scratch.


Dylan Beattie said...

Mike, this is awesome stuff - thanks again!

Anonymous said...

link doesn't work:(

Anonymous said...

Very useful post, especially if you have a legacy domain spread over multi-databases. Something that happen in an enterprise environment.
Thanks for sharing.

Dan said...


This is much simpler than this:

Criteria API also works with this setup.

José Sturniolo said...

What happen if I need to use a different authentication schema in each database? or if I need to use one Database of MS SQL and other MySQL?

Vitaly Kamiansky said...

This is a nice way to use multiple databases, it has though one flaw. NH seems to look for my second database's tables' existance records in a wrong place and tries to create tables already created.

Anonymous said...

I need to do this exact thing with Fluent NHibernate. Is there an equivalent?

Ivan Tapia said...

I have this:
sessionFactory = Fluently.Configure().Database(MsSqlConfiguration.MsSql2008.ConnectionString(Connection)).
Mappings(m => m.FluentMappings.AddFromAssemblyOf()).
Mappings(m => m.FluentMappings.AddFromAssemblyOf()).
[almost 100 mappings entities!!!].

My problem es that it's too late when loading my SessionFactory entity.
May i load this mapping only when i call my query?:

if (sessionSecurity == null) sessionSecurity = sessionFactorySecurity.OpenSession();
if(!(sessionSecurity.IsOpen)) sessionSecurity =sessionFactorySecurity.OpenSession();

string sqlQuery = string.Empty;
/* Query strings... */
IQuery query = (IQuery)sessionSecurity.CreateQuery(sqlQuery);