add support for specifying "schema_search_path"

[62mkv]

Feature Request

Is your feature request related to a problem? Please describe

We're writing an application that needs to be able to work with two different schemas in same database. We use Postgre in production, and h2 for local development and in tests. Currently it does not seem possible to specify schema_search_path in the H2 Connection configuration, it is possible with PostgreSQL with "options".

Describe the solution you'd like

I would like to see a support for http://www.h2database.com/html/commands.html#set_schema_search_path

so I could specify schema search path either in connection URL, or via ConnectionFactoryOptionsBuilderCustomizer

Describe alternatives you've considered

currently I can make it work with specifying schema name in @Table("schema.table_name") annotation

Teachability, Documentation, Adoption, Migration Strategy

this is how it works with Postgre currently:

    @Override
    public void customize(ConnectionFactoryOptions.Builder builder) {
        builder.option(Option.valueOf("options"), Collections.singletonMap("search_path", "schema1,schema2"));
    }

[62mkv]

correction: specifying ;SCHEMA_SEARCH_PATH=SCHEMA1,SCHEMA2 probably will work, when use in a URL; but @SpringBootTest / @R2dbcTest seem to be using embedded database which I don't know how to configure URL for

[mp911de]

H2 options requires a String while Postgres options are a Map. Additionally, the driver does not pick up SCHEMA_SEARCH_PATH (via H2ConnectionFactoryProvider) as it is not defined in H2ConnectionOption.

Feel free to submit a pull request to add the SCHEMA_SEARCH_PATH option. It would also make sense to check if H2 options is a String or a Map so in the map case properties can be added directly to the H2ConnectionConfiguration.Builder.

[62mkv]

not sure I understood this bit: It would also make sense to check if H2 options is a String or a Map so in the map case properties can be added directly to the H2ConnectionConfiguration.Builder.

you said above that H2 "options" is expecting a String? what else to check here for?

[62mkv]

by the way, this url:

spring.r2dbc.url=r2dbc:h2:mem://test-db;TRACE_LEVEL_FILE=4;DB_CLOSE_ON_EXIT=TRUE;SCHEMA_SEARCH_PATH=PUBLIC,MWCONFIG

is parsed as

{
Option{name='user', sensitive=false}=sa, 
Option{name='database', sensitive=false}=testdb, 
Option{name='driver', sensitive=false}=h2, 
Option{name='protocol', sensitive=false}=mem, 
Option{name='password', sensitive=true}=, 
Option{name='host', sensitive=false}=test-db;TRACE_LEVEL_FILE=4;DB_CLOSE_ON_EXIT=TRUE;SCHEMA_SEARCH_PATH=PUBLIC,MWCONFIG
}

is this expected or is it a bug? seems like options is not even being picked up from the URL at all. Or is it just my incorrect R2DBC URL again?

[mp911de]

This line in H2ConnectionFactoryProvider extracts the options argument from ConnectionFactoryOptions and passes-thru all options to the H2 connection factory builder. It would make sense to check whether the value behind the options option is a String or a Map so that the driver gets more flexible.

Note that typing of ConnectionFactoryOptions aims for programmatic configuration. The R2DBC URL parser converts most types to String when using an URL for configuration. The programmatic configuration API allows for a bit more type flexibility and that is why drivers need to cater for type flexibility.

H2 is a common choice for local development. If we can improve dev experience (when H2 is used locally while Postgres is used for production), then it makes sense to do so.

[62mkv]

crap, there should probably be ? instead of semicolon

[mp911de]

The URL should be either:

r2dbc:h2:mem://test-db?options=TRACE_LEVEL_FILE=4;DB_CLOSE_ON_EXIT=TRUE

or

r2dbc:h2:mem://test-db?TRACE_LEVEL_FILE=4&DB_CLOSE_ON_EXIT=TRUE

The second format (options as query string argument) only works for options that are declared in H2ConnectionOption.

[62mkv]

ah, I see. thanks. by the way, README still gives URL for H2 in-mem database with three slashes instead of colon slash slash: https://github.com/r2dbc/r2dbc-h2#user-content-getting-started

[mp911de]

good catch, the readme issue is fixed now.

[62mkv]

so, it should be THREE slashes after colon? hmmmm

[mp911de]

The reason is that there is no hostname. Embedded databases support only the database name.

[62mkv]

that's confusing me again.

if I specify my test properties as

spring.jdbc.url=jdbc:h2:mem:json-controller-with-persistence;TRACE_LEVEL_FILE=4
spring.r2dbc.url=r2dbc:h2:mem://json-controller-with-persistence?options=TRACE_LEVEL_FILE=4;SCHEMA_SEARCH_PATH=PUBLIC,MWCONFIG

all works (i.e. Liquibase uses same embedded Db as DatabaseClient)

if I however specify it as follows:

spring.jdbc.url=jdbc:h2:mem:json-controller-with-persistence;TRACE_LEVEL_FILE=4
spring.r2dbc.url=r2dbc:h2:mem:///json-controller-with-persistence?options=TRACE_LEVEL_FILE=4;SCHEMA_SEARCH_PATH=PUBLIC,MWCONFIG

tests fail because r2dbc now works with some other database

[62mkv]

hell, it works even if those names are inconsistent at all ... goshhhhhh

[mp911de]

In doubt, configuration possibilities across R2DBC drivers are tied more to the JDBC. It might be nice when drivers unify their options but keep in mind that R2DBC drivers are each individual projects, similar maintenance model as JDBC drivers.

[62mkv]

turned out when specified with just two slashes, database name specified in url was interpreted as host and completely ignored, whereas with three slashes it actually was interpreted and it was different from "testdb" that JDBC has been using (in spite of other name specified in the property).

long story short, it works when specified like that:

spring.r2dbc.url=r2dbc:h2:mem:///testdb?options=SCHEMA_SEARCH_PATH=PUBLIC,MWCONFIG

[62mkv]

given, that one can specify SCHEMA_SEARCH_PATH via options= syntax, I don't see what else could/should be done here