Multitenancy using Spring and PostgreSQL

Let’s talk about multitenancy in Java. There is indeed a lot of possible requirements and even more solutions. When you try googling word “multitenancy” you can find mostly articles meditating on general aspects of the subject. I decided to talk about multitenancy from more practical point of view.

Requirements

My requirements are:

One tenant corresponds to one paying customer (e.g. a company).

Need a web application that is able to serve multiple tenants. This application communicates through https and may utilize http sessions on the server side.

Every customer has unlimited number of users (e.g. employees of the company).

Every customer needs to be supplied any number of application databases he needs. On the client side, every user may switch between these databases as he wants, but he may use only one database at a time.

Every customer/tenant has exactly one shared database. This database is shared by all application databases. It contains list of application databases, list of application users and some more customer-wide data.

All tenants/customers are listed in a special root database. There may also be stored any other content that needs to be shared between tenants.

The multitenancy feature must be quite invisible for a common business logic. It must not require any changes in a business code, DAOs and SQL queries.

The application URL is tenant-specific. Users belonging to tenant42 must access the application through the URL that looks like https://<myapp>/tenant42/<request>. Hence on the server side it is always possible to determine the current tenant we are working for.

The easiest way to understand the requirements is to look at the picture.

Solution – database

Now I need to map the mentioned requirements into a concrete database server. I have chosen PostgreSQL which is both free and usable.

Shared database is represented by another pgsql schema named shared-schema

The solution brings these apparent implications:

Each tenant’s data are completely separated from the other tenants.

A foreign key may be created between application database and shared database since they are located in the same pgsql database (it is possible to create FK from one schema to another).

It is necessary to specify desired pgsql schema in all queries to the database. The server must know which table in which schema it is working with. It may be solved by setting an appropriate search_path before query execution.

Solution – datasources and connection pools

The search_path is always set per database connection. If I’d decided to share connections between tenants, I’d really have to switch search_path before every single query.

But that’s not what I want. Instead, I decided to implement a solution where every schema from every tenant has it’s own dedicated set of database connections. I implemented a “tenant and schema aware” datasource with a smart getConnection() method. It maintains a connection pool for every schema and when called it always returns a connection with an appropriate search_path already set.

Let’s see how the TenantAwareDataSource looks like:

It maps each database schema to its own pooled datasource (e.g. DBCP’s BasicDataSource).

It uses special TenantContext bean to detect current tenant and application database (schema). I will describe it in the next chapter.

So now I have a session-bound bean that should know everything about current tenant. The last thing is how and when to initialize such a bean with an appropriate data. There is a requirement in the first chapter of this post that the application URL is tenant-specific. It means I can deduce what is the current tenant just from looking at the request’s URL.

Here I present a solution based on Spring Security and servlet filters.

Firstly, there is a simple TenantFilter that is called before every request. It manages tenant property of every session-bound TenantContext.

The schemaId property of the TenantContext is set a little bit later in the session when the logged-in user is forced to choose one of the existing schemas. There’s no special magic about it.

Conclusion

I briefly described how to implement multitenancy in Java. I started from the database layer where I presented a database layout using multiple PostgreSQL databases and schemas. Then I showed the implementation of the smart JDBC datasource and related tenant context.

I’m planning to write the second part of this blog post. It wil be focused mainly on implementing a business layer into the multitenant application described here.

Hi, I’m sorry, but I do not plan to post any more sources. It would be too much work for me.
There is no need to restart the server when the tenant is created along with its own database. The tenant is registered and usable immediately after creation.
One tenant may operate on more than one database. All of these databases have exactly the same table set. When user logs in, he must choose one of these “application databases”. A typical use-case for this is when our customer requires one production environment (first app. database) and additionally a set of test&try environments (one app. database per one environment).
Hibernate supports multitenancy in its own way. Try google it. I guess for Hibernate you won’t need my datasource at all. Cheers.

Hi
I tried to implement this. When i start the browser i get this message ;
Error creating bean with name ‘scopedTarget.tenantContext’: Scope ‘session’ is not active for the current thread; consider defining a scoped proxy for this bean if you intend to refer to it from a singleton; nested exception is java.lang.IllegalStateException: No thread-bound request found: Are you referring to request attributes outside of an actual web request, or processing a request outside of the originally receiving thread? If you are actually operating within a web request and still receive this message, your code is probably running outside of DispatcherServlet/DispatcherPortlet: In this case, use RequestContextListener or RequestContextFilter to expose the current request.

I am also facing the same issue as below. Any solutions/insights why this might be happening?

Error creating bean with name ‘scopedTarget.tenantContext’: Scope ‘session’ is not active for the current thread; consider defining a scoped proxy for this bean if you intend to refer to it from a singleton; nested exception is java.lang.IllegalStateException: No thread-bound request found: Are you referring to request attributes outside of an actual web request, or processing a request outside of the originally receiving thread? If you are actually operating within a web request and still receive this message, your code is probably running outside of DispatcherServlet/DispatcherPortlet: In this case, use RequestContextListener or RequestContextFilter to expose the current request.

The error message is probably a very good desciption of the state you are in. Try to think of it. Focus on threads. Which thread gets the original request? Which thread fails with the error? Try to debug the RequestContextHolder class. This is the place where the session-magic is actually done (using ThreadLocal).