When would you choose @Sql over alternatives like Flyway/Liquibase migrations, spring.sql.init scripts, or programmatic seeding — and what are its transaction/isolation tradeoffs at scale?
answer
- @Sql = per-test data; migrations = schema/shared
- rollback (INFERRED) = fast auto-cleanup
- ISOLATED/commit -> must clean up + parallel collisions
- read-only baselines via MERGE
- schema.sql/data.sql superseded by Flyway/Liquibase
basics
~10 sUse @Sql for per-test data setup/teardown that varies by test. Use Flyway/Liquibase for schema and shared reference data. Prefer transactional rollback for isolation; use @Sql(ISOLATED) or AFTER cleanup only when tests commit.
solid answer
~40 s`@Sql` shines for **test-scoped, per-method data** — small, readable SQL that differs between tests — because it's declarative and colocated with the test. It's the wrong tool for **schema**: real migrations (Flyway/Liquibase) or `spring.sql.init.*` scripts own DDL and shared seed data so tests exercise production-like structure. At scale the key tradeoffs are isolation and speed: rely on the default rolled-back test transaction (INFERRED mode) so `@Sql` inserts vanish automatically — fastest and cleanest. Only reach for `@SqlConfig(transactionMode = ISOLATED)` or `AFTER_TEST_METHOD` cleanup when tests must commit (e.g. testing commit hooks, multiple connections, or `TestTransaction`). Avoid class-level mutable seeds shared across methods; prefer per-method `@Sql` or MERGE baselines that are read-only. Watch multi-DataSource contexts (name the bean) and ordering when combining class+method scripts.
go deeper
Knows @Sql seeds test data.
Distinguishes @Sql for data vs migrations for schema.
Explains rollback vs ISOLATED tradeoffs and multi-datasource handling.
Sets suite-wide policy: isolation strategy, parallel-execution safety, when to commit, idempotent teardown, and the migrations/@Sql/programmatic split.
## The decision space Four common ways to get data/schema into an integration test: 1. **@Sql** — declarative, per-test SQL scripts/statements. Best for *test-specific data* that differs between methods and reads clearly as SQL. 2. **Flyway / Liquibase migrations** — the production migration tooling, run at context startup (Spring Boot auto-runs them). Owns **schema (DDL)** and **shared reference data**. Tests should use these so they run against production-like structure. In this codebase, migrations are Liquibase and are strictly append-only. 3. **spring.sql.init.* (`schema.sql` / `data.sql`)** — Boot's basic SQL init, mainly for embedded/dev DBs; largely superseded by Flyway/Liquibase for real projects, but handy for simple in-memory test contexts. 4. **Programmatic seeding** — building entities via repositories/JdbcTemplate in `@BeforeEach` or a test fixture builder. Best when data needs computed values, references, or type-safety, and when SQL would be brittle. ## When @Sql is the right call - The data is **specific to a test method** and reads more clearly as literal SQL than as builder code. - You want **teardown** colocated with setup (before + AFTER_TEST_METHOD). - You need to exercise a scenario that's awkward via the ORM (e.g. rows that violate current invariants, legacy states). ## When to avoid @Sql - **Schema/DDL** — belongs in migrations, not scattered test scripts, or tests drift from production structure. - **Large shared fixtures** mutated by many tests — leads to inter-test coupling; prefer per-test isolation. - **Data requiring computation or cross-references** — programmatic builders are safer and refactor better. ## Transaction & isolation tradeoffs at scale - Default `@Transactional` test + `@Sql` (INFERRED mode): the seed runs **inside the rolled-back transaction**, so cleanup is automatic and **fast**. This is the preferred default for suite speed and isolation. - `transactionMode = ISOLATED`: script commits in its own transaction — needed when the test itself doesn't roll it back or when other connections must see the data (e.g. testing code that opens a new connection, or `REQUIRES_NEW` propagation). But committed data must be explicitly cleaned (AFTER script), reintroducing teardown cost and flakiness risk. - **Commit-based tests** (`@Commit`, `@Rollback(false)`, testing real commit side effects, DB triggers, generated columns): here you *must* clean up via `AFTER_TEST_METHOD` scripts or a truncate strategy, and ordering across parallel test classes matters. - **Parallel execution**: committed data from `@Sql` can collide across concurrently running test classes sharing a database; rollback-based `@Sql` avoids this. Prefer per-test schemas/containers (Testcontainers) if you need committing tests in parallel. ## Operational conventions worth standardizing - Put reusable seed scripts under a stable classpath location; keep them **idempotent** where they might re-run (`IGNORE_FAILED_DROPS` for teardown). - Standardize `encoding = UTF-8` to avoid CI/local charset drift. - In multi-DataSource apps, always set `@SqlConfig(dataSource=...)`. - Use `@SqlMergeMode(MERGE)` for a read-only class baseline + per-method deltas; keep the baseline immutable to preserve isolation. ## Summary heuristic Schema and shared reference data → migrations. Per-test, scenario-specific data → `@Sql` (or programmatic when computed). Keep isolation via rollback; only commit when the test's purpose demands it, and then own the cleanup explicitly.
- Why is relying on transactional rollback usually preferable to AFTER_TEST_METHOD cleanup scripts?Rollback is automatic, atomic, and avoids partial-cleanup flakiness; it also isolates concurrent tests since uncommitted data isn't visible to other connections. AFTER scripts add maintenance and can leave residue if they fail.
- When does @Sql with default INFERRED mode fail to make data visible to the code under test?When the code opens a separate connection or uses REQUIRES_NEW propagation — that new transaction can't see the uncommitted @Sql inserts. Use transactionMode = ISOLATED so the seed commits first.
- Should you use @Sql to create tables in tests?Generally no — DDL belongs in Flyway/Liquibase migrations (or schema.sql for simple embedded cases) so tests run against production-like schema; use @Sql for data, not structure.
saying these in an interview costs you the question
- Using @Sql to manage schema/DDL instead of migrations
- Assuming committed @Sql data is safe under parallel test execution
- Thinking INFERRED-mode seeds are visible to a REQUIRES_NEW/new-connection code path