Micronaut Data JDBC Multitenancy

[rafaelrenanpacheco]

Hello!

I would like to request some guidance on how to achieve multitenancy using the column descriptor strategy on Micronaut Data JDBC. I understand this is not available out of the box, but I could try to implement a custom and generic solution for my project, the problem is I don't know where to start since the queries are generated at compile time.

For instance, a entity could have a dedicated annotation to describe the descriptor column, maybe something like this:

@MappedEntity
public class Inventory {

    @Id
    @AutoPopulated
    private UUID id;

    @Tenant
    private UUID userId;

    private String name;
}

And the regular repository, something like this:

@JdbcRepository(dialect = Dialect.POSTGRES)
public interface InventoryRepository extends CrudRepository<Inventory, UUID> {

}

The idea is when executing inventoryRepository.findById(...) the tenancy interceptor would identify the tenant id (using micronaut-multitenancy or any other helper) and generate a query with AND userId = :tenancyId automatically. The question is: is this even possible to achieve using Micronaut Data JDBC and the code generated at compile time by Micronaut? If yes, would you please share the classes that could help achieve this objective?

Thanks.

[dstepanov]

I would create an abstract InventoryRepository something like:

@JdbcRepository(dialect = Dialect.POSTGRES)
public abstract class InventoryRepository {

  TenantContentProvider provider;

  public Inventory findById(UUID id) {
       return findByIdAndUserId(id, provider.getId());
  }

  // This will use compile-time query
  protected abstract Inventory findByIdAndUserId(UUID id, UUID userId);

}

You would need to implement all repository methods in a similar fashion.

We might support something like:
`@Where("userId = #tenancyId")'
And allow bind query parameters from something else than method parameters.

[rafaelrenanpacheco]

Hello @dstepanov!

I've just read the docs about abstract repositories and looks like it is a good approach.

Thanks 👍