Hi Sean, Yes, i am tied up to a hibernate solution. There are 100 odd services that are already on production pointing to a single database. Migrating to jOOQ and PostgreSQL would be as complex as migrating to Spring boot.
I did fork the jOOQ bundle present here https://github.com/tbugrara/dropwizard-jooq and went through the approach already. I tried to follow a similar approach and started customizing dropwizard hibernate. Facing exceptions deep in the stack that i cant figure out currently. Best, Vaibhav On Wednesday, 1 March 2017 01:37:02 UTC+5:30, Sean Marshall wrote: > > Vaibhav, > > Are you tied to Hibernate for your solution? I have successfully setup > multi-tenancy (multi-tenant schemas) with Dropwizard, HikariCP, jOOQ, and > PostgreSQL. I also use FlywayDB to handle schema versioning to auto-update > each tenant schema. > > Let me know if you are interested in understanding this approach. > > Regards, > Sean Marshall > > > On Wednesday, February 15, 2017 at 1:40:05 AM UTC-6, Vaibhav Dhawan wrote: >> >> Hi, >> >> While this post is ancient now, i am wondering if there is a solution for >> this. I am in a situation where in i need to achieve multi tenancy and each >> tenant would have a separate database. My DW services use hibernate >> >> Thanks >> >> On Friday, 23 January 2015 09:14:00 UTC+5:30, Daniel Theodosius wrote: >>> >>> We found workaround for this. We created a new annotation called >>> MultiTenantUnitOfWork and MultiTenantUnitOfWorkApplicationListener that is >>> based on UnitOfWork and UnitOfWorkApplicationListener. >>> >>> The difference is inside the MultiTenantUnitOfWorkApplicationListener >>> onEvent method: >>> >>> @Override >>> public void onEvent(RequestEvent event) { >>> if (event.getType() == >>> RequestEvent.Type.RESOURCE_METHOD_START) { >>> this.unitOfWork = this.methodMap.get(event.getUriInfo() >>> >>> .getMatchedResourceMethod().getInvocable().getDefinitionMethod()); >>> if (unitOfWork != null) { >>> this.session = this.sessionFactory.openSession(); >>> setSchema(); //set the schema to the connection >>> try { >>> configureSession(); >>> ManagedSessionContext.bind(this.session); >>> beginTransaction(); >>> } catch (Throwable th) { >>> this.session.close(); >>> this.session = null; >>> >>> ManagedSessionContext.unbind(this.sessionFactory); >>> throw th; >>> } >>> } >>> } else if (event.getType() == >>> RequestEvent.Type.RESOURCE_METHOD_FINISHED) { >>> if (this.session != null) { >>> try { >>> commitTransaction(); >>> } catch (Exception e) { >>> rollbackTransaction(); >>> this.<RuntimeException>rethrow(e); >>> } finally { >>> this.session.close(); >>> this.session = null; >>> >>> ManagedSessionContext.unbind(this.sessionFactory); >>> } >>> } >>> } else if (event.getType() == >>> RequestEvent.Type.ON_EXCEPTION) { >>> if (this.session != null) { >>> try { >>> rollbackTransaction(); >>> } finally { >>> this.session.close(); >>> this.session = null; >>> >>> ManagedSessionContext.unbind(this.sessionFactory); >>> } >>> } >>> } >>> } >>> >>> private void setSchema() { >>> this.session.doWork(connection -> { >>> >>> String schemaStatement = null; >>> final String tenantSchema = <YOUR_SCHEMA>; >>> switch >>> (dataSourceFactory.getProperties().get("hibernate.dialect")) { >>> case "org.hibernate.dialect.PostgreSQL9Dialect": >>> schemaStatement = String.format("SET SEARCH_PATH >>> = %s", tenantSchema); >>> break; >>> default: >>> schemaStatement = String.format("SET SCHEMA = >>> %s", tenantSchema); >>> } >>> >>> PreparedStatement preparedStatement = >>> connection.prepareStatement(schemaStatement); >>> preparedStatement.execute(); >>> }); >>> } >>> >>> The idea is to use the connection from the opened session and set the >>> schema by using the connection. But the fallback is you need to specify >>> each of the SQL statement because most of the jdbc connections do not >>> implement setSchema() method that might be useful. >>> >>> On Wednesday, January 21, 2015 at 4:04:00 PM UTC+11, Yun Zhi Lin wrote: >>>> >>>> >>>> We are also getting Null pointer exception. It may be the same issue. >>>> >>>> It appears that every time "hibernate.multiTenancy" is set, either the >>>> dialect or JdbcService will give NullPointer exception. The error can be >>>> narrowed down to line 1887 to 1888 in >>>> org.hibernate.cfg.Configuration.java: >>>> >>>> final Dialect dialect = serviceRegistry.getService( JdbcServices.class >>>> ).getDialect(); >>>> dialect.contributeTypes( typeContributions, serviceRegistry ); >>>> >>>> >>>> >>>> On Saturday, January 17, 2015 at 2:58:20 AM UTC+11, Lee Carraher wrote: >>>>> >>>>> Have you had any luck getting multi tenancy to work? >>>>> >>>>> I tried editing io.dropwizard.hibernate.UnitOfWorkRequestDispatcher >>>>> as >>>>> public void dispatch(Object resource, HttpContext context) { >>>>> //final Session session = sessionFactory.openSession(); >>>>> final Session session = >>>>> sessionFactory.withOptions().tenantIdentifier("Tenant1").openSession(); >>>>> >>>>> Which fails since the datasource is not configured for multi tenant, >>>>> which lead me to add: >>>>> configuration.setProperty("hibernate.multiTenancy","SCHEMA"); >>>>> >>>>> configuration.setProperty(AvailableSettings.MULTI_TENANT_IDENTIFIER_RESOLVER, >>>>> >>>>> "com.company.CurrentTenantIdentifierResolverImpl"); >>>>> >>>>> configuration.setProperty("hibernate.tenant_identifier_resolver","com.company.MultiTenantConnectionProviderImpl"); >>>>> to buildSessionFactory io.dropwizard.hibernate.SessionFactoryFactory >>>>> >>>>> and implemented CurrentTenantIdentifierResolverImpl and >>>>> MultiTenantConnectionProviderImpl as in >>>>> >>>>> http://blog.sandeepgupta.info/2014/07/making-application-multi-tenant-with.html >>>>> >>>>> (stripping out any spring only stuff) >>>>> however i still get a null pointer somewhere deep in the stack trace >>>>> that i can't resolve. >>>>> >>>>> Has anyone had any luck getting multitenancy to work? >>>>> >>>>> >>>>> >>>>> On Thursday, March 13, 2014 at 11:40:35 AM UTC-4, Gökhan Berberoğlu >>>>> wrote: >>>>>> >>>>>> thank for pointing me UnitOfWorkRequestDispatcher.. will definitelly >>>>>> try. >>>>>> >>>>>> >>>>>> >>>>>> On Monday, March 10, 2014 10:59:53 AM UTC+1, Graham O'Regan wrote: >>>>>>> >>>>>>> Dropwizard doesn’t support Hibernate’s multi-tenancy feature OOTB, >>>>>>> when sessions are created using @UnitOfWork the tenant’s identifier >>>>>>> isn’t >>>>>>> known so it cannot be passed to the SessionFactory’s options. If you >>>>>>> wanted >>>>>>> to add support you could modify the UnitOfWorkRequestDispatcher to >>>>>>> extract >>>>>>> the tenant’s >>>>>>> identifier from the HttpContext and pass it through when the session >>>>>>> is created? >>>>>>> >>>>>>> >>>>>>> >>>>>>> On 9 Mar 2014, at 14:10, Nathan Fisher <[email protected]> >>>>>>> wrote: >>>>>>> >>>>>>> Hi Gökhan, >>>>>>> >>>>>>> There's no reason you *cannot* do it with Dropwizard (that I'm >>>>>>> aware of). I just don't think it's the target fit for dropwizard. The >>>>>>> primary focus of it is restful web-services that are easily deployed >>>>>>> and monitored. Consider the context and organisation where it was built >>>>>>> (Yammer/back end services) and that will give you a sense of >>>>>>> what its target application is. >>>>>>> >>>>>>> All that being said dropwizard has done a pretty good job of not >>>>>>> limiting your options. Folks have successfully integrated a number of >>>>>>> components into dropwizard that weren't included (eg spring, various DB >>>>>>> orms, templating engines, etc). So the can in my mind is yes. >>>>>>> >>>>>>> Questions I would pose; >>>>>>> >>>>>>> 1) how many databases and clients are you looking at? >>>>>>> 2) is this likely to grow in the future, by how much? >>>>>>> 3) is the schema uniform across databases? >>>>>>> 4) what's the expected RPS per client/database and is it relatively >>>>>>> uniform? >>>>>>> 5) do you have sla latency requirements? >>>>>>> 6) do you have sla availability requirements? >>>>>>> >>>>>>> Kind Regards, >>>>>>> Nathan >>>>>>> >>>>>>> On Saturday, 8 March 2014, Gökhan Berberoğlu <[email protected]> >>>>>>> wrote: >>>>>>> >>>>>>>> Hi Nathan, >>>>>>>> >>>>>>>> We're investigating DW for a new project.. We were planning to >>>>>>>> use Hibernate's separate database per tenant approach, possibly with >>>>>>>> DW.. >>>>>>>> Your answer #1 answer is not clear to me, do you think DW is not >>>>>>>> ok for multi-tenant / multi-db environments ? >>>>>>>> >>>>>>>> I assumed since multi-tenancy is natively supported with hibernate >>>>>>>> it should just work with DW, however after OP's post I realize that >>>>>>>> won't >>>>>>>> be the case.. I cannot find any information regarding DW & >>>>>>>> multi-tenancy, >>>>>>>> which is quite worrying.. >>>>>>>> >>>>>>>> Can you elaborate your answer please ? >>>>>>>> >>>>>>>> thanks! >>>>>>>> >>>>>>>> On Thursday, February 6, 2014 4:53:51 PM UTC+1, Nathan Fisher wrote: >>>>>>>>> >>>>>>>>> Hi, >>>>>>>>> >>>>>>>>> Are you trying to architect your platform or integrate with an >>>>>>>>> existing brownfield project? Are the databases shards for scaling or >>>>>>>>> are >>>>>>>>> they distinct datasets? >>>>>>>>> >>>>>>>>> My quick and dirty response with the questions you've asked thus >>>>>>>>> far; >>>>>>>>> >>>>>>>>> 1. If you're connecting to that many DB's I think you're no longer >>>>>>>>> in the land of micro-service architecture. >>>>>>>>> 2. You'd need to test it with your queries. Define the desired >>>>>>>>> performance >>>>>>>>> 3. Use a reverse-proxy or load balancer, DNS load balancing should >>>>>>>>> be your last resort (http://www.tenereillo.com/GSLBPageOfShame.htm >>>>>>>>> ). >>>>>>>>> >>>>>>>>> Kind Regards, >>>>>>>>> Nathan >>>>>>>>> >>>>>>>>> >>>>>>>>> On 6 February 2014 10:33, S Ahmed <[email protected]> wrote: >>>>>>>>> >>>>>>>>>> When dealing with a multi-tenant environment, many issues can >>>>>>>>>> come up if you don't set things up correctly. >>>>>>>>>> >>>>>>>>>> I was wondering if those of you who have large scale DW >>>>>>>>>> deployments, how do you go about setting up DW in terms of multiple >>>>>>>>>> db >>>>>>>>>> connections? >>>>>>>>>> >>>>>>>>>> 1. Say you have 10 databases to connect to, do you create 10 >>>>>>>>>> session contexts? >>>>>>>>>> 2. If you set your active connections to high, say 100, then if >>>>>>>>>> you have 10 servers that means you have 10x100 active connections >>>>>>>>>> into >>>>>>>>>> mysql. What have you found to be a reasonable maximum? >>>>>>>>>> 3. Is it easier to somehow direct traffic to a group of DW >>>>>>>>>> servers that all connect to a single instance? ie. spread traffic >>>>>>>>>> at the >>>>>>>>>> dns level somehow based on subdomain or the like. >>>>>>>>>> >>>>>>>>>> -- >>>>>>>>>> You received this message because you are subscribed to the >>>>>>>>>> Google Groups "dropwizard-user" group. >>>>>>>>>> To unsubscribe from this group and stop receiving emails from it, >>>>>>>>>> send an email to [email protected]. >>>>>>>>>> For more options, visit https://groups.google.com/groups/opt_out. >>>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> -- >>>>>>>>> Nathan Fisher >>>>>>>>> w: http://junctionbox.ca/ >>>>>>>>> >>>>>>>> >>>>>>>> -- >>>>>>>> You received this message because you are subscribed to the Google >>>>>>>> Groups "dropwizard-user" group. >>>>>>>> To unsubscribe from this group and stop receiving emails from it, >>>>>>>> send an email to [email protected]. >>>>>>>> For more options, visit https://groups.google.com/d/optout. >>>>>>>> >>>>>>> >>>>>>> >>>>>>> -- >>>>>>> Nathan Fisher >>>>>>> w: http://junctionbox.ca/ >>>>>>> >>>>>>> >>>>>>> -- >>>>>>> You received this message because you are subscribed to the Google >>>>>>> Groups "dropwizard-user" group. >>>>>>> To unsubscribe from this group and stop receiving emails from it, >>>>>>> send an email to [email protected]. >>>>>>> For more options, visit https://groups.google.com/d/optout. >>>>>>> >>>>>>> >>>>>>> -- You received this message because you are subscribed to the Google Groups "dropwizard-user" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
