NHibernate - using tenant specific table prefixes

Question

We are developing a multi-tenant application using NHibernate where all tenants share the same database.

One option we considered was to use a tenant specific prefix for our database objects (I believe this the same approach taken by Orchard).

This would at least give us some kind of recovery model for tenant data and would mean we wouldn't have to include a tenantid filter on most of our queries.

So my question - has anyone employed this strategy? If so, how did you go about it.

Specifically, can we use the same SessionFactory for all tenants and can we use NHibernate to generate a new set of tables for a "new" tenant at runtime (is it safe to do so).

Thanks Ben

[Update]

This was a worthwhile investigation but ultimately we decided that a shared schema was more suitable for our needs. Schema per tenant clearly offers better separation of tenant data but makes maintenance more difficult. Since our tenants are only storing small amounts of data, the thought of having 10 tables * 1K tenants is a little off-putting.

Nick Harrison Nick Harrison · Accepted Answer · 2011-04-25T13:43:44

There are a couple of points of customization / extension that you may want to consider.

I don't think that you will be able to share the same session factory across tenants. I think that the simplest thing to do may be to update the mappings based on the tenant associated with the session factory.

public class EntitytMap:ClassMap<Entity>
{
     public EntitytMap()
    {
        Table("TableName");
        Schema(Session.TenantName);
        Id(p => p.Id, "Id").GeneratedBy.Identity();

If you want to have each tenant in their own schema, this should work. If you want to keep the schema the same but have a prefix on the table, you could change to:

public class EntityMap:ClassMap<Entity>
{
     public EntityMap()
    {
        Table(Session.TenantPrefix + "TableName");
        Schema("SCHEMA");
        Id(p => p.Id, "Id").GeneratedBy.Identity();

You can also try providing your own ConnectionProvider. Derive a class from NHibernate.Connection.DriverConnectionProvider and reference your own copy in the nhibernate configuration file instead of:

 <property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>

use

<property name="connection.provider">My.Project.DAL.NHibernate.CustomConnectionProvider, My.Project.DAL</property>

When the GetConnection provider is called, you can specify your own connection string based on the tenent.

NHibernate - using tenant specific table prefixes

2 Answers