Quick Overview of Spring Boot’s @Sql Annotation for Test Data Management

Spring Boot @Sql annotation enables declarative execution of SQL scripts before or after test methods, typically to set up test data or clean up the database.

Core Knowledge

Annotation placement – can be applied to an entire test class (affecting all methods) or to a single test method.

Default behavior – scripts defined in @Sql run before the test method starts. A method‑level @Sql overrides a class‑level definition; the override can be changed with @SqlMergeMode.

Key Attributes

@SqlConfig

– configures script execution details such as character encoding, statement separator, and transaction mode. @SqlGroup – combines multiple @Sql annotations on the same method (required for Java 7 and earlier). executionPhase – controls when a script runs (e.g., AFTER_TEST_METHOD for cleanup).

Basic Usage – Preparing Test Data

import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.jdbc.Sql;

@SpringBootTest
public class UserServiceTest {
    @Test
    @Sql(scripts = "/scripts/test-users.sql") // runs test‑users.sql from classpath
    public void testUserCount() {
        // test logic, e.g., query user count and assert
        // data from test‑users.sql is already inserted
    }
}

Place test-users.sql under src/test/resources/scripts/. Example content:

-- Clear table and insert test data
DELETE FROM users;
INSERT INTO users (id, username, email) VALUES (1, 'test_user_1', '[email protected]');
INSERT INTO users (id, username, email) VALUES (2, 'test_user_2', '[email protected]');

Combined Usage – Setup and Cleanup

import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.jdbc.Sql;
import static org.springframework.test.context.jdbc.Sql.ExecutionPhase.AFTER_TEST_METHOD;

@SpringBootTest
public class ProductServiceTest {
    @Test
    @Sql(scripts = "/scripts/setup-products.sql")
    @Sql(scripts = "/scripts/cleanup-products.sql", executionPhase = AFTER_TEST_METHOD)
    public void testProductCreation() {
        // test product creation logic
        // after the test, cleanup‑products.sql runs to remove test data
    }
}

Fine‑Grained Configuration with @SqlConfig

import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.jdbc.Sql;
import org.springframework.test.context.jdbc.SqlConfig;

@SpringBootTest
public class AdvancedTest {
    @Test
    @Sql(
        scripts = "/scripts/complex-data-setup.sql",
        config = @SqlConfig(separator = "///", encoding = "UTF-8") // custom separator and encoding
    )
    public void testWithComplexData() {
        // test logic
    }
}

Important Practical Tips

Transaction management – If the test class is annotated with @Transactional, data inserted by @Sql is rolled back after the test. To run cleanup scripts after commit, set @SqlConfig(transactionMode = ISOLATED).

Script location – Place SQL files under src/test/resources; Spring Boot loads them from the classpath automatically.

Execution order – By default, @Sql scripts run before any @BeforeEach (JUnit 5) or @Before (JUnit 4) method.

File masks – @Sql does not support wildcards such as "/folder/*.sql". To load multiple scripts, use ResourcePatternResolver together with ScriptUtils programmatically.

Common Questions (FAQs)

Q: Does a method‑level @Sql merge with class‑level @Sql ? A: No, it overrides the class‑level definition. To merge, apply @SqlMergeMode(SqlMergeMode.MergeMode.MERGE) at the class level.

Q: How to ensure a cleanup script runs even if the test fails? A: Set executionPhase = AFTER_TEST_METHOD ; the script executes regardless of test outcome.

Q: Can @Sql execute inline SQL statements? A: Yes, use the statements attribute, e.g., @Sql(statements = "DELETE FROM users;") .