Automatic Setting of Query Handler Multi-Tenancy Data Source

[OldScotsGuy]

I am looking at implementing multitenancy using query handlers and Sql2o. Currently I have a base handler class:

public class BaseHandler {

protected Sql2o sql2o;

public void assignSql2oSource() {
    var tenant = Optional.ofNullable(CurrentTenantIdHolder.getTenantId())
            .orElse(TenantDetails.DEFAULT_TENANT);
    this.sql2o = TenantDetails.targetSql2oObjects.get(tenant);
}

}

Each Query handler has to extend from this, and call the assignSql2oSource() method to get the correct Sql2o object for the tenant.

@Component public class GetStatusesQueryHandler extends BaseHandler implements Command.Handler<Query, List> {

@Override
public List<StatusDto> handle(Query model) {
    assignSql2oSource();
    final var sql = "SELECT * FROM vw_get_status";

    try (var connection = sql2o.open();
            var query = connection.createQuery(sql)) {
        return query
                .setAutoDeriveColumnNames(true)
                .throwOnMappingFailure(false)
                .executeAndFetch(StatusDto.class);
    }
}

public static class Query implements Command<List<StatusDto>> {

    public Query() {
        // No input parameters for query
    }
}

}

Is there a way, perhaps using middleware, of automatically running a method to assign the data source and also give the handler access to the sql2o object?

[sizovs]

Hi @OldScotsGuy

The only way to exchange data between a middleware (that is setting a value) and handlers (that are reading the value) is via thread-local variables. So yes, you can assign a tenant (or even Sql2o object) in a middleware, and then read it in the handler.

That introduces temporal coupling, though.

To avoid temporal coupling, if that's technically possible, you might want to lazily generate, assign, and cache the tanent in a handler, when the tenant is being read.

[OldScotsGuy]

Thanks for the quick response., if middleware is not a great way to implement this due to the temporal coupling, is there another way to automatically setup the Sql2o data source without explicitly calling a method on the base handler?

[sizovs]

@OldScotsGuy Assuming you're using a dependency injection framework like Spring, you may want to set up a prototype-scoped Sql2o bean and then inject it into your prototype-scoped handlers. That also renders an abstract handler useless and makes custom handlers easier to test. Something like this would do the job:

@Configuration
public class Sql2oConfig {

    @Bean
    @Scope(ConfigurableBeanFactory.SCOPE_PROTOTYPE)
    public Sql2o sql2o() {
            var tenant = Optional.ofNullable(CurrentTenantIdHolder.getTenantId())
            .orElse(TenantDetails.DEFAULT_TENANT);
            return TenantDetails.targetSql2oObjects.get(tenant);
    }
}

@Component
@Scope(ConfigurableBeanFactory.SCOPE_PROTOTYPE)
public class GetStatusesQueryHandler implements Command.Handler<Query, List> {

@Autowired
Sql2o sql2o;

@Override
public List<StatusDto> handle(Query model) {
    final var sql = "SELECT * FROM vw_get_status";

    try (var connection = sql2o.open();
            var query = connection.createQuery(sql)) {
        return query
                .setAutoDeriveColumnNames(true)
                .throwOnMappingFailure(false)
                .executeAndFetch(StatusDto.class);
    }
}

P.S. one can easily forget to add "prototype" scope to the bean, leading to bugs hard to catch. So you may also want to add an ArchUnit test that fails if command handlers that depend on Sql2o do not have prototype scope.

[OldScotsGuy]

Many thanks @sizovs - that has worked perfectly!