Does Spring Test (or Junit?) perform some implicit cleanup?

Do you know why on earth it works? Here's an MRE:

@DataJpaTest
@ActiveProfiles("test")
@Sql(
        executionPhase = Sql.ExecutionPhase.BEFORE_TEST_METHOD,
        scripts = {"/create_user_table.sql", "/insert_user.sql"}
)
public class H2DemoApplicationTest {
    @Test
    void testNothing() {
    }

    @Test
    void testNothingOneMoreTime() {
    }
}

-- create_user_table.sql

CREATE TABLE IF NOT EXISTS users(
  id INTEGER PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50)
);

-- insert_user.sql

INSERT INTO users
VALUES (1, 'John', 'Doe');

# knocked myself out. gimme all them logs!! i don't care about all your properties, spring

spring.jpa.properties.hibernate.show_sql=true
spring.jpa.properties.hibernate.use_sql_comments=true
spring.jpa.properties.hibernate.format_sql=true
logging.level.org.hibernate.type=trace

logging.level.org.springframework.jdbc.core=TRACE
logging.level.org.springframework.jdbc.datasource.init=DEBUG
logging.level.org.springframework.test.context.jdbc=DEBUG

Notice I don't perform any cleanup. In other words, before the second test, inserting a row with the same identifier should throw an exception!

Here's a snippet from the console log:

2023-11-10T13:59:35.438+03:00  INFO 1788 --- [           main] c.example.h2_demo.H2DemoApplicationTest  : Started H2DemoApplicationTest in 6.934 seconds (process running for 10.351)
2023-11-10T13:59:35.581+03:00 DEBUG 1788 --- [           main] .s.t.c.j.SqlScriptsTestExecutionListener : Processing merged @SqlConfig attributes for execution phase [BEFORE_TEST_METHOD] and test class [com.example.h2_demo.H2DemoApplicationTest]
2023-11-10T13:59:35.584+03:00 DEBUG 1788 --- [           main] .s.t.c.j.SqlScriptsTestExecutionListener : Executing SQL scripts: [class path resource [create_user_table.sql], class path resource [insert_user.sql]]
2023-11-10T13:59:35.587+03:00 DEBUG 1788 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : Executing SQL script from class path resource [create_user_table.sql]
2023-11-10T13:59:35.590+03:00 DEBUG 1788 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : 0 returned as update count for SQL: CREATE TABLE IF NOT EXISTS users( id INTEGER PRIMARY KEY, name VARCHAR(50) NOT NULL, last_name VARCHAR(50) )
2023-11-10T13:59:35.594+03:00 DEBUG 1788 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : Executed SQL script from class path resource [create_user_table.sql] in 4 ms.
2023-11-10T13:59:35.597+03:00 DEBUG 1788 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : Executing SQL script from class path resource [insert_user.sql]
2023-11-10T13:59:35.605+03:00 DEBUG 1788 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : 1 returned as update count for SQL: INSERT INTO users VALUES (1, 'John', 'Doe')
2023-11-10T13:59:35.605+03:00 DEBUG 1788 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : Executed SQL script from class path resource [insert_user.sql] in 7 ms.
OpenJDK 64-Bit Server VM warning: Sharing is only supported for boot loader classes because bootstrap classpath has been appended
2023-11-10T13:59:36.900+03:00 DEBUG 1788 --- [           main] .s.t.c.j.SqlScriptsTestExecutionListener : Processing merged @SqlConfig attributes for execution phase [BEFORE_TEST_METHOD] and test class [com.example.h2_demo.H2DemoApplicationTest]
2023-11-10T13:59:36.900+03:00 DEBUG 1788 --- [           main] .s.t.c.j.SqlScriptsTestExecutionListener : Executing SQL scripts: [class path resource [create_user_table.sql], class path resource [insert_user.sql]]
2023-11-10T13:59:36.901+03:00 DEBUG 1788 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : Executing SQL script from class path resource [create_user_table.sql]
2023-11-10T13:59:36.902+03:00 DEBUG 1788 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : 0 returned as update count for SQL: CREATE TABLE IF NOT EXISTS users( id INTEGER PRIMARY KEY, name VARCHAR(50) NOT NULL, last_name VARCHAR(50) )
2023-11-10T13:59:36.902+03:00 DEBUG 1788 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : Executed SQL script from class path resource [create_user_table.sql] in 1 ms.
2023-11-10T13:59:36.903+03:00 DEBUG 1788 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : Executing SQL script from class path resource [insert_user.sql]
2023-11-10T13:59:36.904+03:00 DEBUG 1788 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : 1 returned as update count for SQL: INSERT INTO users VALUES (1, 'John', 'Doe')
2023-11-10T13:59:36.904+03:00 DEBUG 1788 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : Executed SQL script from class path resource [insert_user.sql] in 1 ms.
2023-11-10T13:59:36.924+03:00  INFO 1788 --- [ionShutdownHook] j.LocalContainerEntityManagerFactoryBean : Closing JPA EntityManagerFactory for persistence unit 'default'

I executed the actual code, I checked the target directory

Solution

From the docs:

By default, tests annotated with @DataJpaTest are transactional and roll back at the end of each test. They also use an embedded in-memory database (replacing any explicit or usually auto-configured DataSource).

Please note if you rely on functionality of beans not pulled into the context by @DataJpaTest, it's not an option. Or rather, including that annotation only would not be sufficient

One such example is the "magic bean" that allows you to execute MySQL queries against an H2 database not supported by the H2 dialect (like INSERT IGNORE INTO). See MySQL Compatibility Mode here

jdbc:h2:~/test;MODE=MYSQL

Thank @tgdavies