Assertj-DB says h2 table does not exist

I have set up a simple spring-boot app with schema.sql file and a JpaRepository for the Person entity class. I can verify that the schema is initialized and that the repository can write and read to the table, I've enabled show-sql and so for an write operation I can see:

Hibernate: 
    select
        next value for seq_pk_person
Hibernate: 
    insert 
    into
        person
        (name, id) 
    values
        (?, ?)

Evidently there is a table with the name person. However, when I add assertj-db (3.0.0) with a connection field and try to connect to this table:

private AssertDbConnection assertDbConnection = AssertDbConnectionFactory
            .of("jdbc:h2:mem:testdb", "sa", "")
            .create();
...
Person is written exactly in the same as in the hibernate log above
    @Test
    public void testPersistPerson() {

        Table personTable = assertDbConnection.table("person").build();
        Assertions.assertThat(personTable)
                .exists();
        ...

I get

[person table] 
Expecting exist but do not exist
java.lang.AssertionError: [person table] 
Expecting exist but do not exist
    at com.example.demo.PersonRepositoryTest.testPersistPerson(PersonRepositoryTest.java:48)
    at java.base/java.lang.reflect.Method.invoke(Method.java:580)
    at java.base/java.util.ArrayList.forEach(ArrayList.java:1597)
    at java.base/java.util.ArrayList.forEach(ArrayList.java:1597)

I'd like to understand what I'm doing wrong here and how to fix it. The complete project can be found here. Relevant files:

schema.sql:

CREATE TABLE person (
    id BIGINT PRIMARY KEY,
    name VARCHAR(255) check (length(name) < 10),
    job INT
);

CREATE SEQUENCE SEQ_PK_PERSON INCREMENT BY 1;

application.yml:

spring:
  datasource:
    url: jdbc:h2:mem:testdb
    driver-class-name: org.h2.Driver
    username: sa
    password:
    initialization-mode: always
  jpa:
    spring.jpa.database-platform: org.hibernate.dialect.H2Dialect
    hibernate:
      ddl-auto: none
    show-sql: true
    properties.hibernate.format_sql: true
    defer-datasource-initialization: true
  sql:
    init:
      mode: always
  h2:
    console:
      enabled: true

Person.java:

package com.example.demo;

import jakarta.persistence.*;
import lombok.Getter;
import lombok.Setter;

@Entity
@Getter
@Setter
public class Person {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "person_seq")
    @SequenceGenerator(name = "person_seq",
            sequenceName = "SEQ_PK_PERSON", allocationSize = 1)
    private Long id;

    private String name;

}

Solution

They both point to their own in-memory database. You need to reuse the existing datasource for the validation not create a new one. Which is what you are currently doing for the AssertDbConnectionFactory. Hence it doesn't see anything what is in the database. How to do this is explained in the documentation.

public class YourTest {

  @Autowired 
  private DataSource dataSource;

  private AssertDbConnection assertDbConnection

  @BeforeEach
  public void setup() {
    assertDbConnection = AssertDbConnectionFactory
            .of(dataSource)
            .create();
  }

}

Something like that should use the shared datasource one instead of re-creating a new in-memory database.

Now another challenge is the fact that the @DataJpaTest you are using makes the test method @Transactional. Which means the method is transactional and other connections cannot see data until it is commit (and no a flush isn't a commit!). So you would need to re-use the existing connection instead of getting a new one. To accomplish this you can use the TransactionAwareDataSourceProxy which will obtain the existing connection if a transaction is ongoing.

@BeforeEach
public void setup() {
  var dsWrapper = new TransactionAwareDataSourceProxy(dataSource);
  assertDbConnection = AssertDbConnectionFactory
          .of(dsWrapper)
          .create();
}