SpringBoot, Multi-Tenant


#1

Hey everyone, apologies in advance as I think I’m about to expose lots of my ignorance, very happy to have people point me at docs, and also to collate any info I find under this post.

I’m at a meeting for the FOLIO project - building an open source library services platform (www.folio.org) which is a microservices oriented LSP project. The project has identified a need for workflow, but has decided to implement it’s own workflow engine and UX. I’m worried this might be a mistake (understated). Given that our platform already makes heavy use of Java and docker containers, it seems to be a great fit for Camunda. (?)

The challenge is that the platform is multi-tenant and has it’s own API Gateway that communicates tenant information to modules via a custom header. We also need to dynamically switch on tenants and use schema per tenant isolation (Postgresql).

It looks to me like the SpringBoot version of camunda is the best fit for dynamically configuring new tenants and adding a storage engine to persist engine configuraiton for each tenant.

Right now tho, I’m just trying to figure out : (1) Are there scaling limits for the number of tenants that can be configured? Right now, our other modules are essentially sedentary until a request comes along for a client. I guess the workflow engine is in a different state as it needs timers and events which can trigger activity regardless of incoming web requests. Does anyone have any experience or ideas for how to partition or load balance many schema-per-tenant instances? what happens if I start a second engine on a different physical host for the same tenant (Same DB)? and (2) What might be a good way to convert a custom HTTP header into the equivalent of the Camunda “tenant” property that can be passed to web-api calls like the deployment/create endpoint. We could implement a facade or something similar, but I’m hoping we can use a filter or something to just map our local header onto the appropriate form submission property.

More importantly perhaps, does anyone feel that this sounds really doable, and worth spending some effort to get working, or it just won’t fly and we should continue implementing our own solution. Right now, I’m trying to pull together a POC with camunda and mostly just need to get the dynamic tenant config working, and get a simple process executed for a trivial use case.

Apologies if I should have read more before asking, I’m doing this in the background and really just looking for validation that this is a sensible path as I work it out :slight_smile:

Thanks in advance,
Ian.


#2

Hi @ianibbo,

Camunda supports two ways of multi-tenancy. First, the data of all tenants are in the same database / schema and are separated by a tenant identifier (using one process engine). Second, the data are on different databases / schema and there is one process engine for each tenant.

It seems that the second approach is that you’re looking for.

You can find more information in the documentation: https://docs.camunda.org/manual/7.8/user-guide/process-engine/multi-tenancy/

Best regards,
Philipp


#3

Thanks for that Philipp,

That all matches up with my understanding, and what I’d already managed to glean from the docs, so thats helpful. The thing I’m struggling with is that there seems to be gaps in functionality moving between different containers and frameworks. For example, I’ve pulled together this repo: https://github.com/ianibo/folio-mod-workflow-camunda from different bits of examples and postings. It’s based primarily on the spring/rest setup, and the default engine works OK. What I’ve tried layer on top of this initially is a simple manual setup of a second tenant - called modwf_diku. The repo history actually documents my trying several different combinations of datasource and SpringProcessEngineConfiguration, but no matter what I try, I can’t get tables created automatically for the new tenant. I’m currently manually creating the schema (And am using a schema for the default instance - that works fine), but when I try to build my engine for the new tenant via code like:

SpringProcessEngineConfiguration config = new SpringProcessEngineConfiguration();
config.setDataSource(dataSource)
config.setTransactionManager(transactionManager)
config.setDatabaseSchema('modwf_diku')
config.setDatabaseTablePrefix('modwf_diku.')
config.setJobExecutorActivate(true)
config.setProcessEngineName('diku')
config.setHistory(ProcessEngineConfiguration.HISTORY_FULL)
config.setDatabaseSchemaUpdate('true')
println("config: ${config}");
ProcessEngine processEngine = config.buildProcessEngine();
RuntimeContainerDelegate.INSTANCE.get().registerProcessEngine(processEngine);

Every table throws an exception ending with

Error querying database. Cause: org.postgresql.util.PSQLException: ERROR: relation “modwf_diku.act_ge_property” does not exist

Position: 15

The error may exist in org/camunda/bpm/engine/impl/mapping/entity/Property.xml

The error may involve org.camunda.bpm.engine.impl.persistence.entity.PropertyEntity.selectProperty-Inline

The error occurred while setting parameters

SQL: select * from modwf_diku.ACT_GE_PROPERTY where NAME_ = ?

Cause: org.postgresql.util.PSQLException: ERROR: relation “modwf_diku.act_ge_property” does not exist

This is really odd, because in different configs I get table already exists errors trying to create tables - so there must be some attempt at discovering if the tables need to be created, but I can’t determine what the different routes are.

Has anyone seen anything like this before? I’m aware that the multi-tenant example talks about manually creating the schemas, but looking at the code and the javadocs do seem to imply that databaseSchemaUpdate=true should do this - and, again, it feels tantalisingly close, I just can’t seem to get over the line…

Wondering if anyone has any ideas?

Cheers in advance,
Ian.


#4

Hi @ianibbo,

did you change the database table names in the SQL scripts which create the schema?

In your configuration, the database table prefix is set to “modwf_diku.”. That means all table names must start with “modwf_diku.”. Just one guess.

Best regards,
Philipp


#5

Cheers Philipp, didn’t explain myself at all well, apologies.

I’m not running any scripts - I’d really like the engine to auto-create the tables for me, and I was trying to get that to happen. Independently, I’ve made progress here: https://github.com/ianibo/folio-mod-workflow-camunda/releases/tag/schema_per_tenant where I’ve found that by manually creating a DataSource for the new tenant, and using the postgres JDBC drivers currentSchema=tenant (eg ‘modwf_diku’) I can indeed cause the engine to create the tables for a tenant. (see https://github.com/ianibo/folio-mod-workflow-camunda/blob/spring/mod_workflow_camunda/src/main/groovy/com/k_int/folio/workflow/mod_workflow_camunda/ModWorkflowCamundaApplication.groovy)

This feels a bit like I’m working around a more fundamental issue with the SpringProcessEngineConfiguration’s setDatabaseSchema and setDatabaseTablePrefix methods when working against PostgreSQL, but for my Proof of Concept thats not a big issue.

I think for now, I’ve resolve the blocker that was stopping me build a PoC. The big issue after PoC I think will be having a proper shared connection pool for the tenants, instead of a DataSource per tenant. For now tho, I can live with this, and continue to the meat of the things I need to prove with the PoC.

Thanks for responding to the query tho, appreciate (a) knowing that I’m not totally off reservation with trying this and (b) that people are listening.

Cheers,
e


#6

Hi @ianibbo
I was have same problem.
This is a camunda spring boot starter bug.

The code will search whole schemaes to find out the tables is or not created.
Assume you have to schema
The first one already created the tables.
But the second did not.

When you start your application two things happend.
First : the camunda will check the schemaes to find out tables is or not exists in the database (whole schemaes), because the tables already exists in your first schema so the answer always is true.
Second: camunda will do some query to the two schemas. When send a query to the first schema it will not throw error because the tables are exists.
When send a query to the second schema it will throw an error tell you the tables are not exists.

How I am fixed this ?
Just create the tables in your schemas before start your application.

You can find the sql script at the camunda doc.