SpringBoot Document how to initialize the database when using R2DBC

In a general web flux application, if added spring-boot-starter-data-r2dbc into dependencies, the schema.sql and data.sql in classpath will be executed when the application is started.

In my example, I created a RSocket server that serves at TCP/7000, I am using the following stack:

Spring Boot 2.3.0.M3
Java 11

The project dependencies.

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-rsocket</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-r2dbc</artifactId>
        </dependency>
        <dependency>
            <groupId>io.r2dbc</groupId>
            <artifactId>r2dbc-h2</artifactId>
            <scope>runtime</scope>
        </dependency>

The project application.properties.

spring.rsocket.server.port=7000
spring.rsocket.server.transport=tcp

I have to declare a ConnectionFactoryInitializer bean to initialize the schema.sql and data.sql myself.

    @Bean
    public ConnectionFactoryInitializer initializer(ConnectionFactory connectionFactory) {

        ConnectionFactoryInitializer initializer = new ConnectionFactoryInitializer();
        initializer.setConnectionFactory(connectionFactory);

        CompositeDatabasePopulator populator = new CompositeDatabasePopulator();
        populator.addPopulators(new ResourceDatabasePopulator(new ClassPathResource("schema.sql")));
        populator.addPopulators(new ResourceDatabasePopulator(new ClassPathResource("data.sql")));
        initializer.setDatabasePopulator(populator);

        return initializer;
    }

Get the complete sample codes here.

Comment From: wilkinsona

When you're using R2DBC, there's no support in Spring Boot for initialising a database using schema.sql or data.sql. This was prototyped in the experimental project but there were a number of problems with it so it did not move over into Spring Boot.

We should clarify the situation by adding some content to the reference documentation. This commit message summarises the situation quite well.

Comment From: hantsy

OK, thanks, understand it.

I just encountered this problem when I created a new project with Spring Boot 2.3.0.M3 today, the former sample projects are based on 2.2.x and the experimental spring data r2dbc starter. So I thought it could be a problem with the change of the dependencies, not realized there are some changes when spring data r2dbc merged into the official Spring Data and Spring Boot train.

Comment From: snicoll

There is a note in the documentation but, reading this again, it's the last paragraph of a quite lengthy section so we should probably reword it. Is that what you have in mind @wilkinsona?

Comment From: wilkinsona

I missed those two sentences when I went looking for something. Thanks for the pointer. I think we should have a separate section for database initialization with R2DBC and provide some more detailed examples.

I also wonder if we should restructure the whole SQL section a bit. We could mention R2DBC in the opening description and then I think we should have separate JDBC and R2DBC subsections. All of the existing content other than the new R2DBC section would move beneath the JDBC section.

Comment From: Thinkenterprise

I have the same problem as @hantsy. I switched the Spring Boot Experimental Project to Spring Boot 2.3.M3 and the loading of the schema no longer works. I think, this is probably due to the test autoconfiguration that is also integrated?? But I'm not sure!!

        <dependency>
            <groupId>org.springframework.boot.experimental</groupId>
            <artifactId>spring-boot-test-autoconfigure-r2dbc</artifactId>
            <scope>test</scope>
        </dependency>

However, there is a workaround for this? Does it make sense to use the DatabaseClient to load a DDL?

It try it

client.execute("CREATE TABLE customer (id SERIAL PRIMARY KEY, first_name VARCHAR(255), last_name VARCHAR(255));").then();

but it fails again.

io.r2dbc.spi.R2dbcBadGrammarException: [42102] [42S02] Table "CUSTOMER" not found; SQL statement:

The insert statements can't be executed because of the missing CUSTOMER TABLE?

Comment From: Thinkenterprise

Sorry @hantsy workaround is already working :-) So my contribution was not so important!!

Comment From: snicoll

Actually, I already documented that in #20742 but I forgot to link the How-To in the main R2DBC section. I just did that.

Comment From: nkonev

Sorry for PR, but I'd written simple migration library and published it to Maven Central

https://github.com/nkonev/r2dbc-migrate

Comment From: langley-agm

@wilkinsona might also be a good idea to update https://spring.io/guides/gs/accessing-data-r2dbc/

Comment From: wilkinsona

Thanks for the suggestion. I've opened https://github.com/spring-guides/gs-accessing-data-r2dbc/issues/1.