Using NHibernate with multiple databases

I’ve been asked a couple of times about hooking up NHibernate with multiple databases. You may need to do so for sharding purposes or as a business function. For example, we allow customers to deploy multiple databases for verification and/or training purposes in isolation from their main production database. This is quite easy to accomplish using NHibernate, but not readily documented. Let’s look at the basic code we must implement. (Before we get started, you may want to opt for a more complex/elegant solution based on your needs. The goal here is just to go over the mechanics. The NHibernate.Contrib project does have a port of the original Java Hiberhate Shards project (which was built by Google), but I am not familiar with it). In a single database configuration, you’ll typically configure NHibernate via an XML file (or Fluent NHibernate) and build a static SessionFactory with code similar to:

  private static readonly ISessionFactory _sessionFactory = new Configuration().Configure().BuildSessionFactory();

The purpose of NHibernate’s ISessionFactory is to create an ISession associated with the configured database. With your static SessionFactory created, you simply call the thread-safe GetSession() and end up with an ISession. We could (and probably should, and probably will) dedicate a couple posts to the ISession alone, but for now your think of it as an ADO.NET connection. This leads us directly to how multiple databases are supported – simply have one SessionFactory for each database. To do so, configure NHibernate as you normally would, but leave out the connection string. Instead of building your SessionFactory as shown above, we’ll create two specifying a unique connection string for each:

  
  private static readonly ISessionFactory _sessionFactory1 = new Configuration()
                    .Configure()
                    .SetProperty("connection.connection_string", "CONNECTIONSTRING1").BuildSessionFactory();

 private static readonly ISessionFactory _sessionFactory2 = new Configuration()
                    .Configure()
                    .SetProperty("connection.connection_string", "CONNECTIONSTRING2").BuildSessionFactory();

This is a naïve implementation – we likely wouldn’t hard-code the connection strings like this. Instead we might load them from a configuration file or a database. Taking it to the next step, there’s a good chance that you’d want to support a dynamic number of databases. Let’s assume that we have a global database which has a single table called Databases having three columns: Id, Identifier and ConnectionString. Here’s what your code might look like:

  private static readonly ISessionFactory _globalSessionFactory = new Configuration().Configure().BuildSessionFactory();
  private static IDictionary<string, ISessionFactory> _allFactories = LoadAllFactories();

  private static IDictionary<string, ISessionFactory> LoadAllFactories()
  {
    var dataBases = _globalSessionFactory.OpenSession().CreateQuery("from DataBases").List<DataBases>();
    var dictionary = new Dictionary<string, ISessionFactory>(dataBases.Count);
    foreach (var dataBase in dataBases)
    {
      var factory = new Configuration()
          .Configure()
          .SetProperty("connection.connection_string", dataBase.ConnectionString).BuildSessionFactory();
      dictionary.Add(dataBase.Identifier, factory);
    }
    return dictionary;
  }

As you can see, the configuration of the _globalSessionFactory uses the common single-database approach (i.e., its configured via the hibernate configuration file). From that, we are able to load up all other SessionFactories within a Dictionary – keyed by unique identifier. With that configured, our actual data access methods call the GetSession() method to retrieve a session. GetSession loads the current identifier and looks up the appropriate SessionFactory which is used to return a Session (how you implement GetCurrentIdentifier() strongly depends on your needs/goals/situation (we use sub-domains to identify the database to load which is available via a context object):

public void Save(SomeEntityType entity)
{
    var session = GetSession();
    using (var transaction = session.BeginTransaction())
    {
      session.SaveOrUpdate(entity);
      transaction.Commit();
    }   
}
public ISession GetSession()
{
    var currentIdentifier = GetCurrentIdentifier();
    return _allFactories[currentIdentifier].OpenSession();
}  

Again, we’ve only looked at the specific mechanism for handling multiple databases. In a real application you may not want to create a Session for each call. For example, if you’re writing an ASP.NET application you may want to tie your Session to the current context and flush it on RequestEnd (a common approach). This is still doable, but you will need to reshuffle some code around.

This entry was posted in Uncategorized. Bookmark the permalink. Follow any comments here with the RSS feed for this post.

8 Responses to Using NHibernate with multiple databases

  1. karl says:

    lol, a typo, sorry.

  2. Other Alex says:

    Was “Hiberhate” a typo or intentional?

  3. Yaron says:

    Thank you for this post, it’s very helpful!

  4. Rub says:

    Multiple database support is very simple with uNhAddIns library using MultiSessionFactoryProvider

    see this link: http://code.google.com/p/unhaddins/

  5. Davy Brion says:

    if you’re using the 2nd level cache, you also really need to set the cache.region_prefix variable to something different for each database so values from different databases don’t accidentally overwrite each other in the cache

  6. Alex says:

    1. You better use NHibernate.Cfg.Environment.ConnectionString instead of “connection.connection_string”. It looks cleaner and no chance for typos.

    2. You can also switch/build applications that can talk to different databases as in Oracle/MsSQL/MySQL. You just need to set NHibernate.Cfg.Environment.Dialect and NHibernate.Cfg.Environment.ConnectionDriver from code in addition to conneciton string

  7. Alex says:

    1. You better use NHibernate.Cfg.Environment.ConnectionString instead of “connection.connection_string”. It looks cleaner and no chance for typos.

    2. You can also switch/build applications that can talk to different databases as in Oracle/MsSQL/MySQL. You just need to set NHibernate.Cfg.Environment.Dialect and NHibernate.Cfg.Environment.ConnectionDriver from code in addition to conneciton string

  8. karl says:

    There’s a problem with the code snippets not being formatted and being cropped. Looking into it. Apologies.