DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Last call! Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workloads.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • Realistic Test Data Generation for Java Apps
  • Getting Started With JPA/Hibernate
  • How To Build Self-Hosted RSS Feed Reader Using Spring Boot and Redis
  • Improving Backend Performance Part 1/3: Lazy Loading in Vaadin Apps

Trending

  • Event-Driven Architectures: Designing Scalable and Resilient Cloud Solutions
  • Docker Base Images Demystified: A Practical Guide
  • Testing SingleStore's MCP Server
  • Unlocking the Benefits of a Private API in AWS API Gateway
  1. DZone
  2. Data Engineering
  3. Data
  4. Build a Multi-Tenant Application Architecture Using Vaadin, Spring, jOOQ and PostreSQL

Build a Multi-Tenant Application Architecture Using Vaadin, Spring, jOOQ and PostreSQL

Learn how to put together a full application stack for a multi-tenant architecture using Vaadin, Postgres, jOOQ, and Spring.

By 
Thomas Kratz user avatar
Thomas Kratz
·
Aug. 14, 15 · Tutorial
Likes (6)
Comment
Save
Tweet
Share
7.3K Views

Join the DZone community and get the full member experience.

Join For Free

In this tutorial we will put together a full application stack for a multi-tenant architecture.

We will be using:

  • Vaadin and Vaadin-Spring for the UI
  • Postgres for storage
  • jooq for the data-layer
  • Spring-Security for authentication
  • Some glue magic to handle multi-tenancy

You can find the complete source code for this example at Github. I will not go into the details of the different technologies used, instead the focus lies on the schema setup and the implementation of the tenancy-logic.

We basically start of with the vaadin-spring-security example, the one you can find at the vaadin4spring repository.

This example gives us a basic setup for a spring based vaadin application, the is able to authenticate a user using spring security.For multi-tenancy I decided to opt for a schema-per tenant solution like this:

 

The master schema contains the table definitions for all table, tenant schemas inherit these definition to sensure we have a consistent data structure across all tenants. All database objects are managed using liquibase scripts. Liquibase is a database refactoring tool, if you never heard about it, find out for yourself at http://www.liquibase.org/ The master.tenant table holds information about the existing tenants, and their database connection properties. For each tenant schema an corresponding database user gets created by liquibase, that has restricted privileges and can only access his own tenant schema.

The master.user table for e.g. is created like this:

<changeSet author="thomas" id="master-create-table-user">
    <createTable tableName="user" schemaName="master" >
        <column name="id" type="BIGINT" autoIncrement="true"/>
        <column name="user_name" type="varchar(255)"/>
        <column name="password_hash" type="varchar(2048)"/>
        <column name="active" type="boolean" defaultValue="false"/>
    </createTable>
</changeSet>

The tenant_xx.user tables are defined as:

<changeSet author="thomas" id="${db.schema}-create-child-table-user" >
    <sql>
        create table ${db.schema}.user () INHERITS (master.user);
    </sql>
</changeSet>

Through this inheritance trick we ensure that the actual structure of the tables in all tenants keep in sync, plus as we'll see later, we are able to query across all tenants from the master schema.

The liquibase scripts in the github example create two tenants (tenant_1 and tenant_2) and one admin user each. the admin user name is for sake of simplicity the same as the tenant name, the password is simply "admin". So if you try to run the example you can log in as tenant_1/tenant_1/admin.

Now we have a basic database schema. The liquibase scripts needed some tuning to run not only on postgres but as well on hsqldb. We need this at least for unit testing, but we also want to be able to generate our jooq mapping and dsl without having to run a postgresql server on our build server.

Running the maven build creates the master schema on a temporary hsqldb instance and runs the jooq code generator afterwards. The idea for this came from http://stanislas.github.io/2014/08/23/liquibase-and-jooq.html

This is all the basic stuff we need, before we now can get into the mutli-tenant stuff. I wanted to have a clean separation of the tenants from a connection perspective. So each tenant has it's own restricted database user and it's own connection pool. This has some drawbacks, as we could probably get a quite bunch of connection pools, but the advantage is, that whatever you try to do with the tenant connection you'll get from spring, you'll never be able to touch another tenant than the one you are authenticated for. The TenantDataSource is a simple proxy wrapper that routes you to the proper connection pool based on the current spring authentication object. Here comes a snippet:

@Component@Qualifier("no-tx")
public class MultiTenantDataSource implements DataSource {

    @Autowired

    private TenantAuthentication authentication;


    @Autowired

    private TenantDao tenantDao;


    @Autowired

    private TenantHelper tenantHelper;

        

    private Map<String, DataSource> dataSourceMap = new ConcurrentHashMap<>();
    @Override

    public Connection getConnection() throws SQLException {

        DataSource ds = getDataSource();

        return ds.getConnection();

    }

    private DataSource getDataSource() {

        String tenantName = authentication.getTenant(); // will throw if not authenticated

        return  dataSourceMap.computeIfAbsent(tenantName, (key)->{
            Optional<ITenant> tenantOptional = tenantDao.findbyName(tenantName);

            return tenantOptional.map(tenant -> {

                HikariConfig config = tenantHelper.toHikariConfig(tenant);

                return new HikariDataSource(config);

            }).orElseThrow(() -> new IllegalStateException("This should never happen"));

         });
    }

The injected TenantAuthentication is a Proxy Bean around the Authentication object from the SpringSecurityContextHolder:

@Bean(name ="currentToken")
TenantAuthentication currentToken() {

    return ProxyFactory.getProxy(TenantAuthentication.class, new MethodInterceptor() {
        @Override

        public Object invoke(MethodInvocation invocation) throws Throwable {

            SecurityContext securityContext = SecurityContextHolder.getContext();

            TenantAuthenticationToken authentication = (TenantAuthenticationToken)securityContext.getAuthentication();

            if (authentication == null) {

                 throw new AuthenticationCredentialsNotFoundException("No auth..");

            }

            return invocation.getMethod().invoke(authentication, invocation.getArguments());

        }

    });

}

Wiring jooq and spring with transaction management is a little effort, and I solved it as explained in http://www.jooq.org/doc/3.6/manual/getting-started/tutorials/jooq-with-spring/

The last step we have to solve is mapping the jooq schema to the current authenticated user's schema. I solved this again with a proxy bean for the jooq DSLContext:

@Bean

public DSLContext dsl(){

    return ProxyFactory.getProxy(DSLContext.class, new MethodInterceptor() {

        Map<String, DSLContext> contextMap = new ConcurrentHashMap<>();

        @Override

        public Object invoke(MethodInvocation invocation) throws Throwable {

            String tenant = authentication.getTenant(); // will throw if not authenticated

            DSLContext ctx = contextMap.computeIfAbsent(tenant, (key) ->{ 

                Settings settings = new Settings().withRenderMapping(new RenderMapping().withSchemata(new MappedSchema().withInput("master").withOutput(key)));

                DefaultConfiguration configuration = new DefaultConfiguration();

                configuration.setSQLDialect(SQLDialect.POSTGRES_9_4);

                configuration.setSettings(settings);

                configuration.setConnectionProvider(connectionProvider());

                configuration.setExecuteListenerProvider(new DefaultExecuteListenerProvider(new ExceptionTranslator()));

                return new DefaultDSLContext(configuration);

            });

            return invocation.getMethod().invoke(ctx, invocation.getArguments());

        }

    });

}

Now we can inject this dsl into any data access object, and we are sure that we will always access the schema of the current user, using the connection that is authorized to access this schema only:

@Component

public class UserDao {

    /**

     * the dsl context.

     *

     * the context we inject here is actually a proxy bound to the current tenant schema

     * backed by a datasource that has only access to this tenant schema

     */

    @Autowired

    DSLContext dsl;


    public List<IUser> findAll() {

        return dsl.selectFrom(USER).fetchInto(User.class);

    }

}

Finally we need a form of authenticating our users. This is done straightforward using a spring AuthenticationProvider and a view that lets us read all users accross the tenant schemas:

<sql>
    create view "master"."v_user" as
    select n.nspname as tenant, u.user_name, u.password_hash 

    from master.user u left join pg_class p on u.TABLEOID = p.oid 

    left join pg_catalog.pg_namespace n on n.OID =p.relnamespace 

    where u.active=true;
</sql>

Check out the example from github if you'd like to dive into the nifty details!

Spring Framework Database connection application Vaadin Build (game engine) Schema Architecture

Published at DZone with permission of Thomas Kratz, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • Realistic Test Data Generation for Java Apps
  • Getting Started With JPA/Hibernate
  • How To Build Self-Hosted RSS Feed Reader Using Spring Boot and Redis
  • Improving Backend Performance Part 1/3: Lazy Loading in Vaadin Apps

Partner Resources

×

Comments

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends: