@Sql Annotation Guide: Declarative Test Data Setup for Spring Boot Tests

If you’re hand-writing INSERT statements with JdbcTemplate every time you need test data for @DataJpaTest or @SpringBootTest, maintenance gets painful as the number of tests grows.

This is where the @Sql annotation comes in handy. As part of the Spring TestContext Framework, it lets you declaratively run SQL scripts before and after test execution, separating test data into SQL files to improve readability and reusability.

Basic syntax at the method level

The basic usage is to attach @Sql to a test method.

@Test
@Sql("/test-data/users.sql")
void 登録済みユーザーを取得できる() {
    var users = userRepository.findAll();
    assertThat(users).hasSize(2);
}

A leading / makes the path absolute from the classpath root; omitting it makes the path relative to the test class’s package. Multiple scripts can be specified as an array, like scripts = {"schema.sql", "users.sql"}, and they run in the order listed.

Class-level @Sql and how to choose between them

Attaching @Sql to a class applies the same script to every test method. When both method-level and class-level annotations are specified, the method-level one overrides (OVERRIDE) the class-level one by default, and the class-level script is not executed. If you want both to run, attach @SqlMergeMode(MergeMode.MERGE) to the class.

@DataJpaTest
@Sql("/test-data/common-categories.sql")
@SqlMergeMode(SqlMergeMode.MergeMode.MERGE)
class ProductRepositoryTest {

    @Test
    @Sql("/test-data/products.sql")
    void 価格帯で商品を検索できる() {
        assertThat(productRepository.findByPriceRange(1000, 3000)).hasSize(2);
    }
}

If you forget to add it, the shared data won’t be inserted and the test will fail.

Using executionPhase to choose BEFORE/AFTER

The executionPhase attribute lets you specify whether the script runs “before the test” or “after the test” (the default is BEFORE_TEST_METHOD). For cleanup scripts, use @SqlGroup to specify BEFORE and AFTER together.

@SqlGroup({
    @Sql("/test-data/setup-orders.sql"),
    @Sql(scripts = "/test-data/cleanup-orders.sql",
         executionPhase = Sql.ExecutionPhase.AFTER_TEST_METHOD)
})
@Test
void 注文一覧を取得できる() { ... }

In environments with automatic rollback, like @DataJpaTest, the AFTER side is largely unnecessary, but it becomes important in configurations that aren’t rolled back (such as RANDOM_PORT).

Controlling errorMode with @SqlConfig

Statements like DROP TABLE can error out if the table doesn’t exist. Control this with the errorMode attribute of @SqlConfig.

@Sql(
    scripts = "/test-data/reset-and-seed.sql",
    config = @SqlConfig(errorMode = SqlConfig.ErrorMode.IGNORE_FAILED_DROPS)
)
@Test
void データをリセットしてから検証する() { ... }

IGNORE_FAILED_DROPS ignores only DROP failures, CONTINUE_ON_ERROR ignores all errors, and the default FAIL_ON_ERROR stops at the first error.

Combining with @DataJpaTest

@DataJpaTest is commonly used for Repository slice tests, and since transactions are rolled back by default, @Sql with just BEFORE_TEST_METHOD, as in the earlier example, is often sufficient.

With the embedded H2 database, if production uses MySQL/PostgreSQL, dialect differences can cause syntax errors, which can sometimes be avoided using compatibility mode (MODE=MySQL). This is covered in detail in the Repository Slice Test Guide.

Combining with @SpringBootTest(RANDOM_PORT)

For E2E-oriented tests using TestRestTemplate, RANDOM_PORT is used, but note that transactions are not automatically rolled back.

@SpringBootTest(webEnvironment = WebEnvironment.RANDOM_PORT)
@Sql("/test-data/users.sql")
class UserApiTest {

    @Test
    void ユーザー一覧APIが取得できる() {
        var response = restTemplate.getForEntity("/api/users", String.class);
        assertThat(response.getStatusCode()).isEqualTo(HttpStatus.OK);
    }
}

Since rollback doesn’t happen, adding a cleanup script with AFTER_TEST_METHOD prevents impact on subsequent tests. This is covered in detail in the TestRestTemplate E2E Testing Guide.

Difference from data.sql and when to use which

data.sql is for initial data loading that runs once at startup, while @Sql runs repeatedly per test. The decision criteria are the following three points:

  • Execution frequency: data.sql runs once at startup; @Sql runs every time for each test
  • Nature of the data: data.sql is suited for production-equivalent initial master data; @Sql is suited for disposable fixtures
  • Maintenance responsibility: Changes to data.sql affect all environments; @Sql is self-contained within that test

Avoid modifying data.sql just for testing purposes — extract test-specific data into @Sql instead.

Common pitfalls

  • Incorrect script path: Mistaking the presence or absence of a leading / causes the path to be treated as relative, and the file isn’t found, causing failure
  • Data conflicts from unique constraint violations: Reusing the same ID across multiple tests causes unique constraint violations in environments without rollback
  • No rollback, affecting subsequent tests: Missing cleanup in AFTER_TEST_METHOD leaves data behind, causing subsequent tests to fail for unclear reasons

Summary

With @Sql, you can move away from hand-written INSERTs and declaratively set up test data. Once you understand the override relationship between class and method levels, along with executionPhase and errorMode, you can use it confidently with either @DataJpaTest or @SpringBootTest(RANDOM_PORT). If you’re reconsidering how you write tests, also check out the Testing Guide with JUnit and Mockito.