Support Multitenancy via discriminator column

[sdelamo]

Micronaut Data currently supports multi-tenancy via datasource or schema. Not sure how the API should look like for column discriminator multi tenancy .

But I assume something like:

@MappedEntity // (1)
record Book(
        @Id @GeneratedValue @Nullable Long id,
        @DateCreated @Nullable Date dateCreated,
       @TenantId String publisher,
        String title,
        int pages) {
}

and a JdbcRepository

@JdbcRepository(dialect = Dialect.H2
interface BookRepository extends CrudRepository<Book, Long> {

for bookRepository.findAll() we should do select * from book where publisher = 'xxx'.

We should leverage Micronaut Multitenancy for tenant resolution.

See GORM Multi-tenancy API for ideas.

[sdelamo]

@graemerocher @dstepanov @radovanradic thoughts?

[dstepanov]

To implement it, first, we need to support query parameters resolved outside of the method scope.
My idea is to support syntax: select * from users where my_column = #outside_param and my_id = :methodParameter and add some info about it to io.micronaut.data.model.runtime.QueryParameterBinding.
We can support a simple bean interface to resolve global parameters by name: QueryParameterResolver#tryResolve(String) or even support defining special resolvers like TenantQueryParameterResolver

[dstepanov]

@TenantId can be implemented as an annotation annotated with something like @RuntimeQueryParameter(resolver = TenantResolver.class)

[graemerocher]

wouldn't it be better to support the expression language? You could then at an expression context to resolve the current tenant

[dstepanov]

Maybe, but that is a bit more complex and can be added later.

[graemerocher]

Ok, for me personally it doesn't make sense to introduce another overloaded syntax #outside_param for the # character when that is already used for the expression language. It will just confuse users.

[sdelamo]

I agree with @graemerocher I think we should aim to support the expression language in Micronaut Data queries.

I created an issue to support the expression language for tenant resolution.

[dstepanov]

I'm a bit afraid to support all that #{..} syntax in SQL queries

[dstepanov]

@graemerocher Do we know at the compilation time what is the return type of the expression?

[graemerocher]

good question, looking at the code it is possible, but might require changes to the element API

[graemerocher]

the type is available here:

        Object annotationValue = expressionMetadata.annotationValue();

        try {
            ExpressionNode ast = new CompoundEvaluatedEvaluatedExpressionParser(annotationValue).parse();
            ast.compile(ctx);
            pushBoxPrimitiveIfNecessary(ast.resolveType(ctx), evaluateMethodVisitor);

But we would probably need to expose resolveType(..) from the element API AST somehow

[sdelamo]

We added support for expression language in multi tenancy.

Given an application such as:

Entity

package example.micronaut;

import io.micronaut.core.annotation.Nullable;
import io.micronaut.data.annotation.GeneratedValue;
import io.micronaut.data.annotation.Id;
import io.micronaut.data.annotation.MappedEntity;
import io.micronaut.serde.annotation.Serdeable;

@Serdeable
@MappedEntity
public record Book(@Nullable @Id @GeneratedValue Long id,
                   String title,
                   String framework) {
}

Controller

package example.micronaut;

import io.micronaut.data.runtime.multitenancy.TenantResolver;
import io.micronaut.http.annotation.Controller;
import io.micronaut.http.annotation.Get;

import java.util.List;

@Controller("/books")
class BookController {

    private final TenantResolver tenantResolver;
    private final BookRepository bookRepository;

    BookController(TenantResolver tenantResolver, BookRepository bookRepository) {
        this.tenantResolver = tenantResolver;
        this.bookRepository = bookRepository;
    }

    @Get
    List<Book> index() {
        return bookRepository.findAllByTenant(tenantResolver.resolveTenantIdentifier().toString());
    }
}

and:

package example.micronaut;

import io.micronaut.data.annotation.Query;
import io.micronaut.data.jdbc.annotation.JdbcRepository;
import io.micronaut.data.model.query.builder.sql.Dialect;
import io.micronaut.data.repository.CrudRepository;

import java.util.List;

@JdbcRepository(dialect = Dialect.H2)
public interface BookRepository extends CrudRepository<Book, Long>{
    @Query(value = "SELECT * FROM book WHERE framework = :tenant")
    List<Book> findAllByTenant(String tenant);
}

The following tests passes:

package example.micronaut;

import io.micronaut.context.annotation.Property;
import io.micronaut.core.type.Argument;
import io.micronaut.core.util.StringUtils;
import io.micronaut.http.HttpRequest;
import io.micronaut.http.HttpResponse;
import io.micronaut.http.HttpStatus;
import io.micronaut.http.client.BlockingHttpClient;
import io.micronaut.http.client.HttpClient;
import io.micronaut.http.client.annotation.Client;
import io.micronaut.test.extensions.junit5.annotation.MicronautTest;
import org.junit.jupiter.api.Test;

import java.util.List;

import static org.junit.jupiter.api.Assertions.*;

@Property(name = "datasources.default.schema-generate", value = "CREATE_DROP")
@Property(name = "datasources.default.url", value = "jdbc:h2:mem:devDb;LOCK_TIMEOUT=10000;DB_CLOSE_ON_EXIT=FALSE")
@Property(name = "datasources.default.username", value = "sa")
@Property(name = "datasources.default.password", value = "")
@Property(name = "datasources.default.dialect", value = "H2")
@Property(name = "datasources.default.driver-class-name", value = "org.h2.Driver")
@Property(name = "micronaut.multitenancy.tenantresolver.httpheader.enabled", value = StringUtils.TRUE)
@MicronautTest(transactional = false)
class BookControllerTest {

    @Test
    void multitenancyRequest(@Client("/") HttpClient httpClient,
                             BookRepository bookRepository) {
        bookRepository.save(new Book(null, "Building Microservices with Micronaut", "micronaut"));
        bookRepository.save(new Book(null, "Introducing Micronaut", "micronaut"));

        bookRepository.save(new Book(null, "Grails 3 - Step by Step", "grails"));
        bookRepository.save(new Book(null, "Falando de Grail", "grails"));
        bookRepository.save(new Book(null, "Grails Goodness Notebook", "grails"));

        BlockingHttpClient client = httpClient.toBlocking();
        HttpRequest<?> request = HttpRequest.GET("/books").header("tenantId", "micronaut");
        Argument<List<Book>> responseArgument = Argument.listOf(Book.class);
        HttpResponse<List<Book>> response = assertDoesNotThrow(() -> client.exchange(request, responseArgument));
        assertEquals(HttpStatus.OK, response.getStatus());
        List<Book> books = response.body();
        assertNotNull(books);
        assertEquals(2, books.size());

        response = assertDoesNotThrow(() -> client.exchange(HttpRequest.GET("/books").header("tenantId", "grails"), responseArgument));
        assertEquals(HttpStatus.OK, response.getStatus());
        books = response.body();
        assertNotNull(books);
        assertEquals(3, books.size());
    }

}

Supporting expression language in @Query could allow us to simplify the former example as:

package example.micronaut;

import io.micronaut.data.runtime.multitenancy.TenantResolver;
import io.micronaut.http.annotation.Controller;
import io.micronaut.http.annotation.Get;

import java.util.List;

@Controller("/books")
class BookController {

    private final BookRepository bookRepository;

    BookController( BookRepository bookRepository) {
        this.bookRepository = bookRepository;
    }

    @Get
    List<Book> index() {
        return bookRepository.findAllByTenant);
    }
}

Using the EL in the @Query value

package example.micronaut;

import io.micronaut.data.annotation.Query;
import io.micronaut.data.jdbc.annotation.JdbcRepository;
import io.micronaut.data.model.query.builder.sql.Dialect;
import io.micronaut.data.repository.CrudRepository;

import java.util.List;

@JdbcRepository(dialect = Dialect.H2)
public interface BookRepository extends CrudRepository<Book, Long>{
    @Query(value = "SELECT * FROM book WHERE framework = :#{tenanId}")
    List<Book> findAllByTenant();
}

[dstepanov]

I will try to integrate the expression language.

I'm thinking to avoid embedding the language into the SQL query (to simlify parsing, checking for brackets etc) by introducing an annotation that will bind the query parameter to the expression instead of the method parameter:

package example.micronaut;

import io.micronaut.data.annotation.Query;
import io.micronaut.data.jdbc.annotation.JdbcRepository;
import io.micronaut.data.model.query.builder.sql.Dialect;
import io.micronaut.data.repository.CrudRepository;

import java.util.List;

@JdbcRepository(dialect = Dialect.H2)
public interface BookRepository extends CrudRepository<Book, Long>{

    @Query(value = "SELECT * FROM book WHERE framework = :tenanId")
    @ParameterExpression(name = "tenanId", value = "#{...}")
    List<Book> findAllByTenant();
}

And similar notation:

package example.micronaut;

import io.micronaut.data.annotation.Query;
import io.micronaut.data.jdbc.annotation.JdbcRepository;
import io.micronaut.data.model.query.builder.sql.Dialect;
import io.micronaut.data.repository.CrudRepository;

import java.util.List;

@JdbcRepository(dialect = Dialect.H2)
@ParameterExpression(name = "tenanId", value = "#{...}")
public interface BookRepository extends CrudRepository<Book, Long>{

    @Query(value = "SELECT * FROM book WHERE framework = :tenanId")
    List<Book> findAllByTenant();
}

WDYT @sdelamo @graemerocher ?

[sdelamo]

@ParameterExpression looks like a good idea to me.

[graemerocher]

seems reasonable, what about for generated queries? We probably need a way to include a @ParameterExpression in all generated queries if required

[graemerocher]

btw I would probably call it @ParameterValue because I imagine you could use it even without an expression

[dstepanov]

Why would you use it without an expression?
For the tenant scenario we would use the same expression implementation that would extract some interface and the tenant id.

[graemerocher]

for example you might want to use a fixed tenancy:

@ParameterValue(name = "tenantId", value = "default")