What is the @Sql annotation in Spring TestContext, and how do you use it to seed data for an integration test?
answer
- SqlScriptsTestExecutionListener runs it
- scripts= files, statements= inline
- BEFORE_TEST_METHOD by default
- class-level vs method-level
- needs a Spring test context
basics
~20 s@Sql runs SQL scripts (or inline statements) against the test's DataSource before a test method. You point it at a .sql file on the classpath, e.g. @Sql("/data.sql"), to insert seed rows before the test runs.
solid answer
~40 s@Sql is a Spring TestContext annotation that executes SQL scripts or statements around a test. You put it on a test class or method: @Sql("classpath:/seed.sql") runs that script before the annotated method. You can list multiple scripts (they run in order), or supply inline SQL via statements = "...". By default execution happens BEFORE the test method (executionPhase = BEFORE_TEST_METHOD), against the ApplicationContext's single DataSource. It's driven by SqlScriptsTestExecutionListener, which is registered automatically. It's the standard way to seed or reset data per test without hand-writing JDBC. Requires a Spring test context (e.g. @SpringBootTest or @JdbcTest) so the listener and DataSource are available.
code
java · 15 lines@SpringBootTest
class OrderRepositoryTest {
@Test
@Sql("/test-data/orders.sql") // runs before this method
void findsSeededOrders() {
// orders.sql already inserted rows
}
@Test
@Sql(statements = "INSERT INTO orders(id, total) VALUES (1, 99)")
void inlineSeed() {
// one-off inline INSERT
}
}go deeper
Should know @Sql points at a .sql file and runs before the test to seed data.
Knows scripts vs statements, ordering, relative vs absolute paths, and the default-path convention.
Understands the listener, DataSource selection, transaction interaction, and default execution phase.
Frames @Sql vs migrations for schema, per-test isolation strategy, and CI data-hygiene tradeoffs.
## What @Sql is `@org.springframework.test.context.jdbc.Sql` is an annotation in the **Spring TestContext Framework** that declaratively runs SQL scripts and/or inline SQL statements against a database while a test runs. It exists so you don't have to write boilerplate JDBC code to seed, mutate, or clean up data for each test. It is powered by `SqlScriptsTestExecutionListener`, one of the default `TestExecutionListener`s that Spring registers automatically whenever you have a Spring test context (`@ExtendWith(SpringExtension.class)`, `@SpringBootTest`, `@JdbcTest`, `@DataJpaTest`, etc.). No extra wiring is needed. ## Where you can put it - **On a test method** — scripts apply to that one method. - **On the test class** — scripts apply to *every* method in the class. Method-level `@Sql` by default *overrides* class-level `@Sql` (see `@SqlMergeMode` to change that). ## Scripts vs. inline statements Two mutually complementary attributes: - `value` / `scripts` — an array of resource paths to `.sql` files: `@Sql("/seed.sql")` or `@Sql(scripts = {"/schema.sql", "/data.sql"})`. - `statements` — inline SQL executed directly: `@Sql(statements = "INSERT INTO t VALUES (1)")`. You may use both on the same annotation; scripts run first, then inline statements. Multiple scripts run **in the order listed**. ## Resource path resolution - A path starting with `/` (or `classpath:`) is an **absolute classpath** resource. - A **relative** path (no leading slash) is relative to the test class's package. - If you give `@Sql` with **no scripts and no statements at all**, Spring uses a **default path convention**: `classpath:com/example/MyTest.methodName.sql` (class + method) or `classpath:com/example/MyTest.sql` (class-level). This is the 'detected default script' behavior. ## When it runs By default, `executionPhase = Sql.ExecutionPhase.BEFORE_TEST_METHOD`, i.e. before the `@Test` method body. You can flip it to `AFTER_TEST_METHOD` for cleanup. (Class-level phases `BEFORE_TEST_CLASS` / `AFTER_TEST_CLASS` also exist for class-scoped scripts in modern Spring.) ## Which DataSource Scripts run against the single `DataSource` bean in the context. If there are several, you must disambiguate via `@SqlConfig(dataSource = "...")` or `@SqlConfig(transactionManager = "...")`. ## Gotchas - Needs a Spring test context — a plain unit test without a context won't trigger the listener. - Transaction semantics: with a test-managed transaction, scripts by default run in that same transaction and get rolled back with the test; standalone otherwise. `@SqlConfig(transactionMode = ...)` tunes this. - Script errors: by default a failing statement fails the test (`FAIL_ON_ERROR`); tune via `@SqlConfig(errorMode = ...)`. ## When to use Use `@Sql` for lightweight, per-test data setup/teardown when the raw SQL is clearer than building entities in code, or to reset state between tests. For schema creation prefer real migrations (Liquibase/Flyway); use `@Sql` for test-specific data.
- If you put @Sql with no scripts and no statements, what happens?Spring falls back to a default classpath script named after the test class (and method for method-level), e.g. classpath:com/example/MyTest.methodName.sql. If that resource is missing you get an error.
- Does @Sql need @SpringBootTest specifically?No — it needs any Spring test context so SqlScriptsTestExecutionListener and a DataSource exist. @JdbcTest, @DataJpaTest, or a plain @ExtendWith(SpringExtension.class) context all work.
saying these in an interview costs you the question
- Thinking @Sql works in a plain JUnit test with no Spring context
- Believing it creates the DataSource itself rather than using the context's
- Assuming inline statements run before file scripts (scripts run first)