Dynamically Mapping Tenant Requests to Tenant Databases in Multi-Tenant Web Applications

I have been thinking about web based multi-tenant SaaS application architectures in my downtime lately and one of the problems that I wanted to investigate was how to route tenant specific requests to tenant specific databases.

One of the scaling challenges in such applications is that of database partioning/sharding. In regular applications there is usually one database and one codebase, all the requests go to the same application database and therefore its fairly easy to just configure the database connection in some sort of config file once and then the repository that’s using the DbContext (for this post assuming Entity Framework and ASP.NET Core) can just connect to that database. The data in the database is logically partitioned by user. Roughly represented, it looks like this:

However, in a true multi-tenant environment there may only be a single codebase running (although I have seen a case where there was one application instance per tenant) but the application database is usually physically sharded/partitioned by tenants, although there are other styles available as well. Partitioning is done to achieve isolation and scalability mainly i.e. if one tenant is doing something rather resource intensive against their database it’s likely not going to affect any other tenants’ requests to their own database. These databases can also be replicated for availability reasons. I would highly recommend downloading/reading this case study on MSDN about multi-tenant apps on Azure, the product is fictional but the architectural concerns are real.

The problem of course (at least for me) is how do you route tenant requests to the correct databases given that you have only one codebase and multiple tenant specific databases?

In this post I present one way I think this problem could be solved, there might be better ways to do this and I am sure to change my views later but this approach will do for now. The gist of the solution is:

1. The connection string stored in the config should not contain the name of any specific database but only a placeholder because I will set this at runtime. For this solution, I chose to name the tenant databases after the tenant names. Tenant provisioning is beyond the scope of this post.
2. Build a custom middleware component in ASP.NET Core that will intercept tenant requests.
3. Extract the tenant name from the request for e.g. tenanta.myapp.com has the tenant name (TenantA) in the host part of the URL. However, in my simple localhost based solution, I am using the URL path instead for e.g. localhost:1234/tenanta/home/index.
4. Inject the tenant name into a DbContext factory instance that will be dependency injected into the repository later.
5. Invoke the factory from the repository that will then replace the tenant placeholder in the connection string template with the injected tenant name and form the correct connection string at runtime. It will do this for each tenant request.

NB: the “+” is just for display purposes here, the string will be a single unbroken string in the appSettings.Development.json file in the web project. The {tenant} placeholder is what will dynamically get replaced by the actual database name which in this case is the same as the tenant name in the URL. You could make the naming convention as sophisticated as you want but this principal should still hold.

For this post, I created a rough and tumble “multi-tenanted” application architecture in Visual Studio using ASP.NET Core MVC and DDD-lite:

In the DataAccess assembly – for the purposes of this example only – I created a simple anemic domain model of a Customer with a few fields for this application whose objective it is to maintain a list of customers for tenants i.e. organisations that use my Software as a Service application. From this domain model and CRMContext class I then created a database for TenantA using EF core code first migrations and then replicated the schema for TenantB using generated SQL from TenantA’s database. For this small demo sample, I am just using LocalDB within Visual Studio.

I then created a simple Repository interface and its implementation that wrapped my CRMContext and exposed one method to load all customers. I am taking a dependency on a IDbContextFactory interface that I created in my DataAccess project. Since the goal is to construct the DbContext instance dynamically based on the tenant name so we can route tenant queries to the appropriate database.

The DbContextFactory below will replace the tenant placeholder in the connection string template with the actual tenant name passed through the URL and use it to instantiate and return an instance of the CRMContext.

DISCLAIMER: Please bear in mind, this implementation of IDbContextFactory only applies to Entity Framework backed repositories, if you are using some other ORM you will have to create a different implementation of the IDbContextFactory and figure out how to construct the appropriate instance of the context in that ORM for e.g. NHibernate’s Session object. I haven’t gone that far for this post for now but may be in time. Also, you might have to create another implementation for NoSQL based data stores and establish the logic to connect to the store dynamically.

I then created an application services project to abstract my MVC controllers from the repository. I put just one method in it for now that loads all the customers:

Then finally, in the HomeController I took a dependency on this ICustomerService interface. Note how none of the higher layers know anything about tenants at this point, most of that concern has been tucked away in the repository and the factory. This was the key goal throughout this exercise, to minimise leaky abstractions.

Next, I created a custom middleware component that intercepts tenant requests. Here, I am extracting the tenant name part of the URL and injecting into the IDbContextFactory instance. I wanted to ensure that the path part is parsed only in debug mode and host part in release mode hence the pragma directives.

This instance of IDbContextFactory was already added as a service in the ConfigureServices() method in the Startup.cs.

Finally, I created a little extension method that I can call from the Configure() method in the Startup.cs, to easily add my middleware into the application pipeline. Note that this call goes just before the call to UseMvc().

In order to make sure that MVC routing can understand the tenant-ed URLs I changed the default route to add tenant parameter as well.

That’s pretty much it!

The only problem with running this solution on localhost is that if your URL doesn’t have the tenant name part in it, the middleware will inject the default controller’s name into the DbContextFactory i.e. “Home” in this case which will result in the Repository trying to make a connection to the “Home” database which doesn’t exist so it will throw an exception. In a real multi-tenant application this should not happen because the tenant name will be a part of the host part instead of the path part of the URL for e.g. tenantname.myapp.com. There are several ways to design tenant URLs in a multi-tenant app, I would recommend reading this to get a better understanding of the options.

To make it work on localhost, one (imperfect) solution could be to set the launch URL in the web project properties to point to a known tenant, for e.g. in my case “TenantA”:

At least this way, local development and testing can still proceed. The other way could be to put a login screen on the home page and upon login navigate to a known tenant URL. You will probably have at least one tenant database that you will be developing and testing against and that should be enough to make this work locally. Of course, before deploying it you will need to un-check this option to let the host based tenant routing take over.

Like I said, this is not a perfect solution and might have loopholes that I haven’t addressed in this post, but its a modestly good starting point. In future posts, I might revisit this with an even better solution or if anyone reading this has one, I will be more than happy to learn about it.

[UPDATE 12-June-2018]: on popular demand, here is the code for this PoC. Ideas and suggestions always welcome!