Blog, Trixi

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.

After some thinking, browsing and testing the final solution emerged:

  • Every tenant has its own dedicated pgsql database.
  • Application databases are represented by pgsql schemas named app-schema1, app-schema2, …
  • 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.

TenantAwareDataSource.java


import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSource;
import org.springframework.jdbc.datasource.AbstractDataSource;

public class TenantAwareDataSource extends AbstractDataSource {

	private TenantRepository tenantRepository;

	private TenantContext tenantContext;

	// [tenant,schema] to [DataSource] mapping
	private final Map<String, DataSource> schemaToDataSource = new HashMap<String, DataSource>();

	/**
	 * @return Take current tenant+schema and convert it to string token
	 */
	protected String currentLookupKey() {
		String tenantId = tenantContext.getTenant().getKod();

		if (tenantContext.getSchemaId() == null)
			return tenantId + "/<noschema>";
		else
			return tenantId + "/" + tenantContext.getSchemaId();
	}

	/**
	 * @return DataSource for current tenant+schema
	 */
	protected DataSource determineTargetDataSource() {

		// lookupKey represent current tenant and schema
		String lookupKey = currentLookupKey();

		DataSource dataSource = schemaToDataSource.get(lookupKey);

		if (dataSource == null) {
			dataSource = createDataSourceForTenantAndSchema();
			schemaToDataSource.put(lookupKey, dataSource);
		}

		return dataSource;
	}

	/**
	 * Create new pooled datasource for current tenant and schema. It must be
	 * additionally configured to always return connections with a search path
	 * set to current schema.
	 */
	private DataSource createDataSourceForTenantAndSchema() {
		// tenantContext is a session-scoped spring bean
		String tenantId = tenantContext.getTenant().getId();
		String schemaId = tenantContext.getSchemaId();

		Tenant tenant = tenantRepository.getTenant( tenantId );

		// created simple pooled datasource (like Apache DBCP) based
		// on tenant database connection settings
		DataSource dataSource = createDataSource(tenant) ;

		String appSchema = schemaId != null ? "'" + schemaId + "'," : "";
		String searchPath = "SET search_path = " + appSchema + DbConst.PUBLIC_SCHEMA + ";";

		// set search path for the datasource
		// (for DBCP use BasicDataSource.setConnectionInitSqls(Collection))
		configureSearchPath(dataSource, searchPath);

		return dataSource;
	}

	public void destroy() {
		// dispose all the datasources
	}

	@Override
	public Connection getConnection() throws SQLException {
		DataSource determineTargetDataSource = determineTargetDataSource();
		return determineTargetDataSource.getConnection();
	}

}

Solution – tenant context

I have already mentioned a TenantContext bean. It shall provide information about current tenant and application schema (current means belonging to currently processed request).

TenantContext class is just a simple holder class of the required information.

public class TenantContext implements Serializable {

    private Tenant tenant;

    private String schemaId;

    // getters & setters
    // ...
}

TenantContext bean is set to be session scoped which means that its content is bound to current request’s HTTP session.

<bean id="tenantContext" class="TenantContext" scope="session">
    <aop:scoped-proxy />
</bean>

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.

import javax.servlet.Filter;

public class TenantFilter implements Filter {

 private TenantContext tenantContext;

 private TenantRepository tenantRepository;

 @Override
 public void doFilter( ServletRequest request, ServletResponse response, FilterChain chain ) throws IOException, ServletException {
 final HttpServletRequest servletRequest = ( HttpServletRequest ) request;

 String urlTenantId = parseTenantFromUrl( servletRequest.getPathInfo() );

 if ( tenantContext.getTenant() == null ) {
 // tenant context is not initialized
 // it means this is the first request in the session
 // let's set the tenant context
 Tenant tenant = tenantRepository.getTenant( urlTenantId );

 if ( tenant == null )
 throw new TenantException( "Unknown tenant: " + urlTenantId );

 tenantContext.setTenant( tenant );
 }
 else {
 // tenant context already initialized
 // check whether it is as expected
 String sessionTenantId = tenantContext.getTenant().getKod();

 if ( !sessionTenantId.equals( urlTenantId ) )
 throw new TenantException( "Unexpected tenant [session=" + sessionTenantId + ", url=" + urlTenantId + "]" );
 }

 chain.doFilter( request, response );
 }

 /**
 * Parse tenant identifier from the request's URL.
 */
 private String parseTenantId( String requestRelativeUri ) {
 // ...
 }

 // setters and other uninteresting methods...
 // ...
}

This filter is properly injected into Spring Secutiry framework.

<bean id="tenantFilter" class="TenantFilter">
    <property name="tenantContext" ref="tenantContext" />
    <property name="tenantRepository" ref="tenantRepository" />
</bean>

<security:http auto-config='false' >
    <security:custom-filter before="FIRST" ref="tenantFilter" />
    <!-- ...more security stuff... -->
</security:http>

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.

Tags: , , ,

Posted in programming

8 Responses to “Multitenancy using Spring and PostgreSQL”

  • refptr says:

    hi, thanks for your post.

    will you post the source for the webapp of this tutorial?

    if you have new tenant, you will create a new database. do you need to restart the app server in this case?

    also what do you mean by application database? do one webapp mean one application?

    if i use hibernate, do i just need to inject the TenantAwareDataSource to the session factory?

    many thanks.

    • gargii says:

      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.

  • Metti says:

    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.

    • Amit M says:

      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.

      • gargii says:

        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).

  • [...] you’re not using the full potential of sharing resources in the economy of scale. A sample PostgreSQL implementation of such “dynamc” database creation is as [...]

  • [...] you’re not using the full potential of sharing resources in the economy of scale. A sample PostgreSQL implementation of such “dynamc” database creation is as [...]


Leave a Reply

Your email address will not be published. Required fields are marked *


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>