Jan Blaha

Blog about software development

NHibernate multitenancy in shared database

.NET NHibernate

NHibernate still rocks! Even with quite a bit old code base it is still at the top of the c# frameworks we daily use. Especially the framework extensiblity is tremendous. Now I am going to show you how we use NHibernate in the multitenant fashion.

I think the definition of a multitenant system is quite clear only thing I should point out is the multitenancy data approach we use. Our system uses shared sql schema and shared database for all tenants. With sharding it gets little bit more complicated but main principle is obvious. Every entity has basically a column TenantId which identifies where the particular database rows belongs.

Now lets convince NHibernate to respect our tenant boundaries.

Implicitly extend queries

One of the important tasks to do when implementing a multitenant systems is to abstract and automate data layer in a way where you don't need to care about multitenancy and it just work out of the box in the most of the cases. For NHibernate it means for example that calling Session.Query<Invoice>() returns just invoices from the logged in tenant and not all of them. Explicitly adding where conditions to every query is very error prone and I recommend spend some time and do it correctly.

Fortunately this task can be easily fulfilled using NHibernate filters. This feature allows you to add an extra condition to every sql statement and this is exactly what we want. The condition in our case should simple equalize TenantId property with the logged in tenant id.

Then you can use hbm.xml or fluent mapping to add the filter to the every entity.

At the very start of your request, job or any action you just enable the filter as soon you authenticated user and find out the logging in tenant.

Session.EnableFilter("multitenanct").SetParameter("tenantId", Scope.CurrentTenant.Id);

This assures that in every NHibernate query you issue afterwards you get only the data you actually want.

Implicitly verify all data modifications

The NHibernate filters are great for extending queries but unfortunately it doesn't work with data modifications. This means you can still for example update an entity from another tenant even with the enabled filter what is quite a security hole. This force you to add everywhere a validation that an identifier coming in the update request really belongs to the tenant sending the request. Fortunately it can be solved globally at just one place using another NHibernate hook which is more common to use. I am talking about NHibernate listeners.

The listener can hook into every entity modification action and verify that it is a valid operation for logged in tenant.

Not only that filter doesn't limit the update actions but it can even throw exception when you do updates on collections with message Unable to recreate collection with filter enabled. For this reason we do flushes without filter enabled and this is done again using the NHibernate listener.

Database schema considerations

The common question when designing database schema for a multitenant system is what should be the primary key and clustered index. In our system the primary keys are composites from the TenantId and Id in this order. Reflecting such a composite key in NHibernate is quite difficult task and we decided to map NHibernate just for Id column and have composite key only in physical database schema. This means we can continue to use Session.Load<Invoice>(115) and still have everything consistent in the db.

Although calling Session.Load still uses only an uncluttered index on Id the performance is quite good. The problem is rather with joining bigger collections where is important to join over the clustered index (TenantId, Id). Fortunately adding the previously described NHibernate filter on the collection adds the TenantId also to the sql joins giving us clustered index to use and great performance. So performance is not an issue in queries with filter any more.

The problem we encountered with such a hybrid primary key was in the database row locks...

//select ... from invoice with row lock
Session1.Load<Invoice>(132, LockMode.Upgrade); 

//waiting on lock
Session2.Load<Invoice>(132, LockMode.Upgrade); 

//update Invoice set ... where Id = 132
//deadlock
Session1.SaveOrUpdate(invoice);

The trouble is that sql update doesn't work correctly with row locks when you put just part of the primary key to the where condition and it can very simply cause deadlocks. To make it working correctly the where condition needs to also include AND TenantId=?.

NHibernate extensibility comes into game one last time. We use custom entity persister which adds this condition into the updates and we are done.

Summary

NHibernate extensibility is really fabulous and it is a joy to work with it. Using filters, listeners and custom persisters you can adapt NHibernate to work nicely with you multitenant database schema in the very elegant way.

last blog posts


09-10-2015 14:09 AWS

Such a very common thing like adding an existing external volume to Amazon elastic beanstalk is not easily supported out of the box. The official blog mentions only how to attach a snapshot or how to attach and overwrite a new volume every time the service starts. It took me a while to make the config file actually adding an existing volume without formatting it every time so I share it here with you...

read more

04-10-2015 14:09 jsreport

The best practice when adding email notifications feature to your system is to separate as much as you can from email body assembling to email sending outside of the core system. The emails templates quite likely often changes and you don't want to deploy the system because of every single notification change. The best is to just separate everything into an external system and give the access to your PR or Marketing department so they change emails as the time goes without affecting the core system.

read more

03-22-2015 14:09 .NET NHibernate

NHibernate still rocks! Even with quite a bit old code base it is still at the top of the c# frameworks we daily use. Especially the framework extensiblity is tremendous. Now I am going to show you how we use NHibernate in the multitenant fashion.

read more

Jan Blaha

About author

Hi! My name is Jan Blaha. I'm software developer and startup enthusiastic. See my current work.