@Sql

Runs SQL scripts or inline statements against the test DataSource before or after a test method or class, to set up and clean up exactly the rows a test needs.

Signature
@Sql(scripts = "/sql/orders.sql", executionPhase = Sql.ExecutionPhase.BEFORE_TEST_METHOD)
  • Added in Spring Boot Spring Framework 4.1

Parameters

ParameterTypeDescription
$scripts / valueoptionalString[]= {} (default script detection)Script resource paths. A leading slash means the classpath root; no slash means relative to the test class's package.
$statementsoptionalString[]= {}Inline SQL statements, run after any scripts.
$executionPhaseoptionalSql.ExecutionPhase= BEFORE_TEST_METHODBEFORE_TEST_METHOD, AFTER_TEST_METHOD, BEFORE_TEST_CLASS or AFTER_TEST_CLASS. Class phases are only valid on the class.
$configoptionalSqlConfig= @SqlConfigLocal settings: DataSource and transaction manager names, transactionMode, encoding, separator, comment prefixes, errorMode.

Return value

n/aExecutes SQL scripts and statements against the test DataSource at the chosen phase of a test method or class.

Errors

The test fails if a script cannot be found or a statement fails, unless SqlConfig.errorMode says to continue or ignore failed drops.

See also

@DataJpaTestSQL Initialisation ScriptsDatabase Migrations with Flyway@SpringBootTest

@Sql belongs to Spring Framework's test support (org.springframework.test.context.jdbc). It executes SQL against the test context's DataSource at a chosen point: before or after each test method, or before or after the whole class. Use it to seed the rows a query test reads, to load reference data, and to clean up after tests that commit.

Scripts and statements

@Sql("/sql/orders.sql") runs a script from the classpath. A path with a leading slash is from the classpath root; a path without one is relative to the test class's package, which surprises people when the file sits in src/test/resources/sql. With no path at all, Spring looks for a default script named after the class or method, such as OrderQueryTest.sql in the same package, and fails if it is missing. For one or two rows, statements keeps the data next to the assertion.

Execution phase

executionPhase defaults to BEFORE_TEST_METHOD. AFTER_TEST_METHOD suits clean-up. BEFORE_TEST_CLASS and AFTER_TEST_CLASS run once per class and are only allowed on the class, not on methods. @Sql is repeatable, so one method can carry a set-up script and a clean-up statement.

A method-level @Sql replaces the class-level one by default. Add @SqlMergeMode(MergeMode.MERGE) to run both.

Transactions decide whether the data stays

This is where most confusion comes from. If the test runs inside a test-managed transaction, as every @DataJpaTest and @Transactional test does, method-level scripts run in that transaction and are rolled back with it. No clean-up needed.

A RANDOM_PORT test has no test transaction, and the server handles the request on another thread. The script runs in its own transaction and commits, which is necessary for the server to see the rows, and they then stay for the next test. Pair it with an AFTER_TEST_METHOD delete, or truncate in a BEFORE_TEST_METHOD script so a failed test cannot poison the next one. @SqlConfig(transactionMode = ISOLATED) forces a separate, committed transaction even when a test transaction exists.

Keep scripts honest

Scripts are not checked against your entities until they run, so a renamed column breaks them at test time. Keep them short and specific to one test class, use explicit ids in a range sequences will not reach, and prefer Flyway migrations for schema and the repository itself for data where that reads better.

Example

Example · java
import static org.assertj.core.api.Assertions.assertThat;

import java.util.List;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.data.jpa.test.autoconfigure.DataJpaTest;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.test.context.jdbc.Sql;

import jakarta.persistence.Entity;
import jakarta.persistence.Id;
import jakarta.persistence.Table;

@Entity
@Table(name = "purchase_orders")
class PurchaseOrder {

    @Id
    Long id;

    String reference;
    String status;

    protected PurchaseOrder() {}
}

interface PurchaseOrderRepository extends JpaRepository<PurchaseOrder, Long> {
    List<PurchaseOrder> findByStatusOrderByReference(String status);
}

@DataJpaTest
class PurchaseOrderQueryTest {

    @Autowired
    PurchaseOrderRepository orders;

    // Runs inside the test transaction, so the rows vanish at rollback.
    @Test
    @Sql("/sql/purchase-orders.sql")
    void findsOpenOrdersInReferenceOrder() {
        List<PurchaseOrder> open = orders.findByStatusOrderByReference("OPEN");

        assertThat(open).extracting(o -> o.reference).containsExactly("PO-1001", "PO-1003");
    }

    @Test
    @Sql(statements = "INSERT INTO purchase_orders (id, reference, status) VALUES (9, 'PO-9', 'CANCELLED')")
    void ignoresOtherStatuses() {
        assertThat(orders.findByStatusOrderByReference("OPEN")).isEmpty();
    }
}

When to use it

  • Seed three orders with known statuses before testing a status filter query.
  • Load a table of currencies or country codes once for a test class that only reads it.
  • Delete rows committed by a RANDOM_PORT test so the next test class starts clean.

Frequently asked questions

Why can't Spring find my @Sql script?

A path without a leading slash is resolved relative to the test class's package. Use "/sql/data.sql" for a file in src/test/resources/sql, or prefix it with classpath: explicitly.

Is data inserted by @Sql rolled back?

Only when the test runs in a test-managed transaction, as with @DataJpaTest or a @Transactional test class, and the script runs at method level. Class-level phases and tests without a test transaction commit.

Why did my class-level @Sql stop running?

A method-level @Sql overrides the class-level declaration. Add @SqlMergeMode(SqlMergeMode.MergeMode.MERGE) to the class or method to run both.

More examples

The seed script

The file lives under <code>src/test/resources/sql</code>, so <code>@Sql("/sql/purchase-orders.sql")</code> finds it from the classpath root. Explicit high ids avoid colliding with rows created through the repository and make failures easy to read. Statements are separated by semicolons, the default separator.

Example · sql
-- src/test/resources/sql/purchase-orders.sql
-- Explicit ids keep assertions readable; use a range no sequence will reach.
INSERT INTO purchase_orders (id, reference, status) VALUES (1001, 'PO-1001', 'OPEN');
INSERT INTO purchase_orders (id, reference, status) VALUES (1002, 'PO-1002', 'SHIPPED');
INSERT INTO purchase_orders (id, reference, status) VALUES (1003, 'PO-1003', 'OPEN');

Reference data once per class

<code>BEFORE_TEST_CLASS</code> loads the data once and <code>AFTER_TEST_CLASS</code> removes it, which is cheaper than reloading for every method when the data is read-only. These phases run outside the per-test transaction and commit, so the explicit delete is what keeps the next class clean. Note the snake-case column <code>minor_units</code>, the name Boot's default naming strategy gives the <code>minorUnits</code> field.

Example · java
import static org.assertj.core.api.Assertions.assertThat;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.data.jpa.test.autoconfigure.DataJpaTest;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.test.context.jdbc.Sql;
import org.springframework.test.context.jdbc.Sql.ExecutionPhase;

import jakarta.persistence.Entity;
import jakarta.persistence.Id;
import jakarta.persistence.Table;

@Entity
@Table(name = "currencies")
class Currency {

    @Id
    String code;

    int minorUnits;

    protected Currency() {}
}

interface CurrencyRepository extends JpaRepository<Currency, String> {}

// Reference data loaded once for the class and removed afterwards.
// Class-level phases run outside the per-test transaction, so they commit.
@DataJpaTest
@Sql(statements = {
        "INSERT INTO currencies (code, minor_units) VALUES ('GBP', 2)",
        "INSERT INTO currencies (code, minor_units) VALUES ('JPY', 0)"
}, executionPhase = ExecutionPhase.BEFORE_TEST_CLASS)
@Sql(statements = "DELETE FROM currencies", executionPhase = ExecutionPhase.AFTER_TEST_CLASS)
class CurrencyRepositoryTest {

    @Autowired
    CurrencyRepository currencies;

    @Test
    void yenHasNoMinorUnits() {
        assertThat(currencies.findById("JPY")).get()
                .extracting(c -> c.minorUnits).isEqualTo(0);
    }

    @Test
    void bothCurrenciesArePresent() {
        assertThat(currencies.count()).isEqualTo(2);
    }
}

Committed data for a real HTTP request

The request is served on a server thread, so rows rolled back on the test thread would be invisible to it. With no test transaction, the script commits and the controller counts three orders. The repeated <code>@Sql</code> with <code>AFTER_TEST_METHOD</code> deletes them so other tests start from an empty table.

Example · java
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.resttestclient.autoconfigure.AutoConfigureRestTestClient;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.boot.test.context.SpringBootTest.WebEnvironment;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.test.context.jdbc.Sql;
import org.springframework.test.context.jdbc.Sql.ExecutionPhase;
import org.springframework.test.web.servlet.client.RestTestClient;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import jakarta.persistence.Entity;
import jakarta.persistence.Id;
import jakarta.persistence.Table;

@Entity
@Table(name = "purchase_orders")
class PurchaseOrder {

    @Id
    Long id;

    String reference;
    String status;

    protected PurchaseOrder() {}
}

interface PurchaseOrderRepository extends JpaRepository<PurchaseOrder, Long> {}

@RestController
class PurchaseOrderStats {

    private final PurchaseOrderRepository orders;

    PurchaseOrderStats(PurchaseOrderRepository orders) {
        this.orders = orders;
    }

    @GetMapping("/api/purchase-orders/count")
    long count() {
        return orders.count();
    }
}

@SpringBootTest(webEnvironment = WebEnvironment.RANDOM_PORT)
@AutoConfigureRestTestClient
class PurchaseOrderStatsTest {

    @Autowired
    RestTestClient client;

    // No test transaction here: the script commits so the server thread
    // can see it, and the second @Sql removes the rows afterwards.
    @Test
    @Sql("/sql/purchase-orders.sql")
    @Sql(statements = "DELETE FROM purchase_orders", executionPhase = ExecutionPhase.AFTER_TEST_METHOD)
    void countsAllOrders() {
        client.get().uri("/api/purchase-orders/count")
                .exchange()
                .expectStatus().isOk()
                .expectBody(Long.class).isEqualTo(3L);
    }
}

Related

sb at datajpatestsb sql initialisation scriptssb datajpatestsb springboottestsb database migrations with flyway

Discussion

  • Be the first to comment on this lesson.