r2dbc.BadSqlGrammarException: Table not found

Question

I write an integraton test betwen EventRepository and a H2 database.
I use for these liquibase. I'm trying to get data from a test H2 database, but I'm getting an error that it's empty. If you do not use liquidbase, but simply put the scripts in src/test/resources, then everything works fine!!!! Why?

application-test.yaml `

spring:
  h2:
    console:
      enabled: true
  datasource:
    url: r2dbc:h2:mem:///testdb;DB_CLOSE_DELAY=-1;
    username: sa
    password: password
    driver-class-name: org.h2.Driver
  liquibase:
    url: jdbc:h2:mem:///testdb
    user: sa
    password: password
    change-log: classpath:db.changelog/db.changelog-master.yaml
    enabled: true

EventRepository.java

public interface EventRepository extends R2dbcRepository<Event, Long> {}

EventRepositoryTest

@DataR2dbcTest
@ActiveProfiles("test")
class EventRepositoryTest {

  @Autowired
  private EventRepository eventRepository;

  @Test
  @DisplayName("when find all when receive expected event")
  void findAll(){
    Flux<Event> events = eventRepository.findAll();
    events.doOnNext(e -> System.out.println("events received " + e)).blockLast();
  }
}

db.changelog-master.yaml `

databaseChangeLog:
  - include:
      file: db.changelog/db.changelog-1.0.sql

db.changelog-1.0.sql

--liquibase formatted sql

--changeset deniskaydunov:1
CREATE TABLE IF NOT EXISTS public.event
(
    seq SERIAL PRIMARY KEY,
    type VARCHAR (32) NOT NULL,
    id VARCHAR (32) NOT NULL,
    received TIMESTAMP NOT NULL
);

--changeset deniskaydunov:2 contextFilter:test
INSERT INTO public.event (type, id, received, data) VALUES
('IRS', '123', '2018-02-05 11:59:19');

LOG:

2022-11-01 17:53:29.004  INFO 13320 --- \[           main\] c.a.a.e.a.r.EventRepositoryTest          : Starting EventRepositoryTest using Java 19 on LXIBY1300 with PID 13320 (started by denis.kaydunov in C:\\Users\\denis.kaydunov\\IdeaProjects\\axetrade\\event-api)

2022-11-01 17:53:29.005  INFO 13320 --- \[           main\] c.a.a.e.a.r.EventRepositoryTest          : The following 1 profile is active: "test"

2022-11-01 17:53:29.259  INFO 13320 --- \[           main\] .s.d.r.c.RepositoryConfigurationDelegate : Multiple Spring Data modules found, entering strict repository configuration mode

2022-11-01 17:53:29.260  INFO 13320 --- \[           main\] .s.d.r.c.RepositoryConfigurationDelegate : Bootstrapping Spring Data R2DBC repositories in DEFAULT mode.

2022-11-01 17:53:29.427  INFO 13320 --- \[           main\] .s.d.r.c.RepositoryConfigurationDelegate : Finished Spring Data repository scanning in 161 ms. Found 1 R2DBC repository interfaces.

2022-11-01 17:53:30.353  INFO 13320 --- \[           main\] liquibase.database                       : Set default schema name to PUBLIC

2022-11-01 17:53:30.566  INFO 13320 --- \[           main\] liquibase.lockservice                    : Successfully acquired change log lock

2022-11-01 17:53:30.625  INFO 13320 --- \[           main\] liquibase.changelog                      : Creating database history table with name: PUBLIC.DATABASECHANGELOG

2022-11-01 17:53:30.629  INFO 13320 --- \[           main\] liquibase.changelog                      : Reading from PUBLIC.DATABASECHANGELOG

Running Changeset: db.changelog/db.changelog-1.0.sql::1::deniskaydunov

2022-11-01 17:53:30.676  INFO 13320 --- \[           main\] liquibase.changelog                      : Custom SQL executed

2022-11-01 17:53:30.677  INFO 13320 --- \[           main\] liquibase.changelog                      : ChangeSet db.changelog/db.changelog-1.0.sql::1::deniskaydunov ran successfully in 13ms

Running Changeset: db.changelog/db.changelog-1.0.sql::2::deniskaydunov

2022-11-01 17:53:30.713  INFO 13320 --- \[           main\] liquibase.changelog                      : Custom SQL executed

2022-11-01 17:53:30.714  INFO 13320 --- \[           main\] liquibase.changelog                      : ChangeSet db.changelog/db.changelog-1.0.sql::2::deniskaydunov ran successfully in 9ms

2022-11-01 17:53:30.719  INFO 13320 --- \[           main\] liquibase.lockservice                    : Successfully released change log lock

2022-11-01 17:53:30.799  INFO 13320 --- \[           main\] c.a.a.e.a.r.EventRepositoryTest          : Started EventRepositoryTest in 2.202 seconds (JVM running for 3.193)

2022-11-01 17:53:31.305 DEBUG 13320 --- \[           main\] o.s.r2dbc.core.DefaultDatabaseClient     : Executing SQL statement \[SELECT event.\* FROM event\]

org.springframework.r2dbc.BadSqlGrammarException: executeMany; bad SQL grammar \[SELECT event.\* FROM event\]; nested exception is io.r2dbc.spi.R2dbcBadGrammarException: \[42104\] \[42S04\] Table "EVENT" not found (this database is empty); SQL statement:

SELECT event.\* FROM event \[42104-214\]

enter image description here I expect to receive an event, but table not found, what am I doing wrong?

Answer 1

1. Currently(Spring Boot 2.7.x) it seems we can not activate flyway/liquibase in a Spring Boot WebFlux application,the Jdbc datasource auto configuration can not be activated. (both flyway/liquibase are based on blocking Jdbc APIs, these projects have no plan to support R2dbc till now).

   And in a traditional blocking-APIs based WebMvc application, WebFlux/Reactive compatible R2dbc can not be activated by default unless you excludes auto configs, and configure them yourself.

   In your case, I think you have mixed both(WebMvc and WebFlux) in one project, but from the message, the webflux should not be activated. But I am not sure how you configured your project, I noticed the liquibase and r2dbc logs.

2. When activating a R2dbc connection in a WebFlux application, the auto configuration properties prefix is spring.r2dbc, for example, spring.r2dbc.url,etc, here is an example.

3. As an alternative solution of Flyway/Liquibase, consider R2dbc Migrate in a WebFlux application, which follows the same file patterns of Flyway. Check my WebFlux/R2dbc example project using r2dbc-migrate.