Spring boot data r2dbc auto create tables

Question

I have a simple question, is it possible to auto-generate tables using spring boot data r2dbc for MySQL or other databases? in JPA I added spring.jpa.hibernate.ddl-auto=update and created tables

This is my pom:

    <dependencies>
    <dependency>
        <groupId>org.springframework.boot.experimental</groupId>
        <artifactId>spring-boot-starter-data-r2dbc</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-webflux</artifactId>
    </dependency>
    <dependency>
        <groupId>dev.miku</groupId>
        <artifactId>r2dbc-mysql</artifactId>
        <scope>runtime</scope>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
        <exclusions>
            <exclusion>
                <groupId>org.junit.vintage</groupId>
                <artifactId>junit-vintage-engine</artifactId>
            </exclusion>
        </exclusions>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot.experimental</groupId>
        <artifactId>spring-boot-test-autoconfigure-r2dbc</artifactId>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>io.projectreactor</groupId>
        <artifactId>reactor-test</artifactId>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>io.netty</groupId>
        <artifactId>netty-all</artifactId>
        <version>4.1.43.Final</version>
    </dependency>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <optional>true</optional>
    </dependency>
</dependencies>

Answer 1

For testing purpose, I tried this scenario by creating two datasources that point to the same database. One is for JDBC and the other one is for R2DBC.

JDBC Datasource

@Configuration
@EnableJpaRepositories( basePackages = "com.ns.reactivetest.repository.jdbc", entityManagerFactoryRef = "jdbcEntityManager", transactionManagerRef = "jdbcTransactionManager" )
@EnableTransactionManagement
public class JdbcConfiguration {

    @Autowired
    Environment env;

    @Bean( name = "jdbcDataSource" )
    @Primary
    public DataSource jdbcDataSource( ) {
        DriverManagerDataSource dataSource = new DriverManagerDataSource( );
        dataSource.setUrl( env.getProperty( "spring.datasource.url" ) );
        dataSource.setUsername( env.getProperty( "spring.datasource.username" ) );
        dataSource.setPassword( env.getProperty( "spring.datasource.password" ) );

        HikariDataSource hikariDataSource = new HikariDataSource( );
        hikariDataSource.setDataSource( dataSource );
        hikariDataSource.setMinimumIdle( Integer.valueOf( env.getProperty( "spring.datasource.hikari.minimum-idle" ) ) );
        hikariDataSource.setMaximumPoolSize( Integer.valueOf( env.getProperty( "spring.datasource.hikari.maximum-pool-size" ) ) );
        hikariDataSource.setSchema( env.getProperty( "spring.datasource.hikari.schema" ) );

        return hikariDataSource;
    }

    @Bean( name = "jdbcEntityManager" )
    @Primary
    public LocalContainerEntityManagerFactoryBean jdbcEntityManager( @Qualifier( "jdbcDataSource" ) DataSource dataSource ) {
        LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean( );
        em.setDataSource( dataSource );
        em.setPackagesToScan( new String[]{ "com.ns.reactivetest.domain" } );
        em.setPersistenceUnitName( "jdbc_persistence_Unit_Name" );

        JpaVendorAdapter jpaVendorAdapter = new HibernateJpaVendorAdapter( );
        em.setJpaVendorAdapter( jpaVendorAdapter );

        Properties properties = new Properties( );
        properties.setProperty( "hibernate.hbm2ddl.auto", env.getProperty( "spring.jpa.hibernate.ddl-auto" ) );
        properties.setProperty( "hibernate.dialect", env.getProperty( "spring.jpa.properties.hibernate.dialect" ) );
        em.setJpaProperties( properties );

        return em;
    }

    @Bean( name = "jdbcTransactionManager" )
    @Primary
    public PlatformTransactionManager jdbcTransactionManager( @Qualifier( "jdbcDataSource" ) DataSource dataSource ) {
        JpaTransactionManager transactionManager = new JpaTransactionManager( );
        transactionManager.setDataSource( dataSource );
        transactionManager.setPersistenceUnitName( "jdbc_persistence_Unit_Name" );
        return transactionManager;
    }

}

R2DBC Datasource

@Configuration
@EnableR2dbcRepositories( basePackages = "com.ns.reactivetest.repository.reactive" )
@EnableTransactionManagement
public class R2DbcConfiguration  {

    @Autowired
    private Environment env;


    @Bean
    public ConnectionFactory connectionFactory( ) {
        ConnectionFactory connectionFactory = new PostgresqlConnectionFactory(
                PostgresqlConnectionConfiguration.builder( )
                        .host( env.getProperty( "spring.r2dbc.properties.hostname", "localhost" ) )
                        .database( env.getProperty( "spring.r2dbc.name", "postgres" ) )
                        .username( env.getProperty( "spring.r2dbc.username", "postgres" ) )
                        .password( env.getProperty( "spring.r2dbc.password", "password" ) )
                        .schema( env.getProperty( "spring.r2dbc.properties.schema", "public" ) )
                        .port( Integer.valueOf( env.getProperty( "spring.r2dbc.properties.port", "5432" ) ) )
                        //.options( options )
                        .build( )
        );
        if ( env.getProperty( "spring.r2dbc.pool.enabled", Boolean.class, false ) ) {
            ConnectionPoolConfiguration connectionPoolConfiguration = ConnectionPoolConfiguration.builder( connectionFactory )
                    .maxIdleTime( Duration.ofSeconds( env.getProperty( "spring.r2dbc.pool.max-idle-time", Long.class, 1800L ) ) ) // 30 Minutes
                    .initialSize( env.getProperty( "spring.r2dbc.pool.initial-size", Integer.class, 10 ) )
                    .maxSize( env.getProperty( "spring.r2dbc.pool.max-size", Integer.class, 25 ) )
                    .validationQuery( env.getProperty( "spring.r2dbc.validation-query", String.class, "SELECT 1" ) )
                    .build( );
            return new ConnectionPool( connectionPoolConfiguration );
        } else {
            return connectionFactory;
        }
    }

    @Bean
    ReactiveTransactionManager transactionManager( ConnectionFactory connectionFactory ) {
        return new R2dbcTransactionManager( connectionFactory );
    }

}

Note: Here I have used two pacakges for JDBC and R2DBC Repositoties.

So far it is working fine. But I don't know how will this work for the complex scenarios and requirements.

Answer 2

In order for r2dbc to auto create tables on app startup all you need is a config java class annotated with the @Configuration eg:

import io.r2dbc.spi.ConnectionFactory;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.ClassPathResource;
import org.springframework.r2dbc.connection.init.ConnectionFactoryInitializer;
import org.springframework.r2dbc.connection.init.ResourceDatabasePopulator;

@Configuration
public class DbSchemaInitOnStartup {

    @Bean
    ConnectionFactoryInitializer initializer(@Qualifier("connectionFactory") ConnectionFactory connectionFactory) {
        ConnectionFactoryInitializer initializer = new ConnectionFactoryInitializer();
        initializer.setConnectionFactory(connectionFactory);
        ResourceDatabasePopulator resource = new ResourceDatabasePopulator(new ClassPathResource("schema.sql"));
        initializer.setDatabasePopulator(resource);
        return initializer;
    }

}

The schema.sql file must be placed in /resources folder for above to work.

Answer 3

I know that this is not the exact answer but the only way to create tables in R2DBC is to utilize databasePopulator:

@Bean
fun initializer(connectionFactory: ConnectionFactory) =
    ConnectionFactoryInitializer().apply {
        setConnectionFactory(connectionFactory)
        setDatabasePopulator(CompositeDatabasePopulator()
            .apply {
                addPopulators(ResourceDatabasePopulator(FileSystemResource("src/test/resources/sql/init.sql")))
            })
    }

It can come in handy during tests.