Spring R2DBC proper way to initialize database with postgres

Question

I have the following code:

@Component
public class TemplateDatabaseLoader {
    private Logger LOGGER = LoggerFactory.getLogger(TemplateDatabaseLoader.class);

    @Bean
    public CommandLineRunner demo(DatabaseClient databaseClient, ItemRepository itemRepository) {
        return args -> {
            databaseClient.execute(
                    "CREATE TABLE item (" +
                            "id SERIAL PRIMARY KEY," +
                            "name VARCHAR(255)," +
                            "price REAL" +
                        ");"
            ).fetch().all().blockLast(Duration.ofSeconds(10));
            itemRepository.save(new Item("Alf alarm clock", 19.99)).block();
            LOGGER.debug("COMMAND LINE RUNNER");
            itemRepository.save(new Item("Smurf TV tray", 24.99)).block();
        };
    }
}

And:

@SpringBootApplication
public class DemoApplication extends AbstractR2dbcConfiguration {

    public static void main(String[] args) {
        SpringApplication.run(DemoApplication.class, args);
    }

    @Bean
    public ConnectionFactory connectionFactory() {
        PostgresqlConnectionFactory connectionFactory = new PostgresqlConnectionFactory(PostgresqlConnectionConfiguration.builder()
                .host("127.0.0.1")
                .database("cart")
                .username("cart")
                .password("cart").build());
        return connectionFactory;
    }

    @Bean(name={"r2dbcDatabaseClient"})
    DatabaseClient databaseClient() {
        return DatabaseClient.create(connectionFactory());
    }
}

I get the following error:

Suppressed: java.lang.Exception: #block terminated with an error
Caused by: io.r2dbc.postgresql.ExceptionFactory$PostgresqlBadGrammarException: relation "item" already exists

And earlier on the errors:

Caused by: java.lang.ClassNotFoundException: org.springframework.jdbc.CannotGetJdbcConnectionException

If I modify my code to say:

CREATE TABLE IF NOT EXISTS item

Then I no longer get the error about the item relation existing, however, it seems the transaction gets cancelled entirely?

I get the following output:

2020-09-21 17:31:58.476 DEBUG 16639 --- [  restartedMain] com.example.demo.TemplateDatabaseLoader  : COMMAND LINE RUNNER
2020-09-21 17:31:58.476 DEBUG 16639 --- [actor-tcp-nio-2] i.r.postgresql.util.FluxDiscardOnCancel  : received cancel signal

So my questions are

1. What is the proper way to do this?

2. Why does my CommandLineRunner code seem to execute twice? The table does not persist after running the code, so it seems it must be executing twice to get the first error about the table existing.

Thank you.

Answer 1

I got it working. I added a new class to load the schema from a file:

@Configuration
public class InitializerConfiguration {
    private Logger LOGGER = LoggerFactory.getLogger(InitializerConfiguration.class);

    @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")));
        initializer.setDatabasePopulator(populator);

        return initializer;
    }
}

This loads the schema.sql under resources. My TemplateDatabaseLoader now looks like this:

@Component
public class TemplateDatabaseLoader {
    private Logger LOGGER = LoggerFactory.getLogger(TemplateDatabaseLoader.class);

    @Bean
    public CommandLineRunner demo(ItemRepository itemRepository) {
        return args -> {
            itemRepository.save(new Item("Alf alarm clock", 19.99)).block();
            itemRepository.save(new Item("Smurf TV tray", 24.99)).block();
        };
    }
}

This loads the two items.