Choosing a Source of Test Data

1. The Point of Choosing a Data Source

If you have ever written a data test and thought, “I’ll just throw some data together quickly, as long as the repository returns something,” congratulations: you have already run into the most common @DataJpaTest problem — the test seems to verify a query, but in fact it is testing your talent for inventing random fixtures. Choosing a data source is a way to make a test predictable and readable, rather than just “green.”

In data-layer tests, there comes a moment of adult pain: you realize that data is part of the scenario, not the background. And if the background is created differently every time, the repository may “work” or “not work” not because of the query, but because you accidentally created two articles with the same publishedAt, or forgot to set the status PUBLISHED, or created a category with code "Java" instead of "java".

From a practical point of view, choosing a source of test data is about two properties: first, the test should read from top to bottom like a small story — “what DB state was there → what we did → what we checked.” Second, the test should be resistant to randomness: if you run the suite 100 times, it should behave the same 100 times, not “according to Hibernate’s inspiration.”

To keep data preparation from turning into magic, it helps to keep a simple scheme in mind.

flowchart TD
    A[DB schema] -->|Flyway| B[Database ready]
    B --> C{What data does the test need?}
    C -->|1-2 objects, readability in Java matters| D[Java setup: TestEntityManager]
    C -->|exact strings, id, order, corners matter| E["@Sql: statements/scripts"]
    C -->|common background needed for many tests| F[Test-only migration]
    D --> G[Act: repository method]
    E --> G
    F --> G
    G --> H[Assert]

This diagram is not about “which tool is trendier.” It is about the fact that each tool has a role, and if you confuse those roles, the tests start fighting each other, and you start fighting your own repository.

2. Data Sources: Baseline and Scenario

When we say “test data,” many people imagine one giant bag of data. In practice, it is more convenient to mentally split it into two layers: a common baseline (the background needed by many tests) and a local scenario (what a specific test needs). This trick surprisingly reduces chaos: you stop trying to cram everything into one universal data.sql, which even the bravest developers are afraid to touch later.

If we map these roles to tools, the picture looks like this. Test-only migrations usually work as a baseline: small stable reference data that is needed often, must always be present, and must be deterministic. @Sql is usually good as a local scenario: for one test (or a small group of tests), you load exactly the set of rows needed for verification. Java setup via TestEntityManager is convenient when the scenario is small and easier to describe with objects than with SQL.

It is useful to pin down, in plain language, what each tool promises you as the test author:

And now the most important rule of the day, which sounds boring but saves you weeks of life: one data set — one source of truth. If categories are created “sometimes by migration, sometimes by an SQL script, sometimes by a Java helper,” sooner or later you will get a test that fails because “there are 3 categories in this branch and 4 in that one,” and you will spend 40 minutes convincing yourself the problem is in JPA (no, the problem is your zoo of sources).

3. Java Setup via TestEntityManager

Java setup is often underestimated, because it seems like: “well, this is longer; SQL is faster.” And then it turns out that in SQL you accidentally inserted the wrong category_id, forgot a required field, or started keeping a numeric id in your head that was stable only until the first extra row. Java setup is good because it keeps you in the world of types and the compiler: the IDE suggests fields, you make fewer naming mistakes, and it is easier to see the domain itself rather than just tables.

For @DataJpaTest, the standard tool here is TestEntityManager. It is especially good when you need to bring up 1-3 entities, show a small relationship graph, and immediately call persistAndFlush(). In such a scenario, the test reads in the language of the model: here is the category, here is the article, here is the relationship between them.

But Java setup quickly starts to lose when you are fighting not the scenario, but the infrastructure: you need fixed ids, exact timestamps, deterministic row order, sequences, and a long list of required fields “just so the test will run.” That is a good signal to switch to @Sql.

And one more rule: do not overuse a shared baseline through Java helpers. It is better to keep common background in test-only migrations, and from the test fetch it by a natural key such as code, rather than hope that the category always has the same numeric id.

4. @Sql: Scenario SQL Setup

There are situations where SQL is not “low-level evil,” but the most honest way to tell the test the truth. When the row itself in the table is part of the scenario, @Sql usually wins. A fixed id, a specific slug, an exact published_at, a known data order, a boundary combination of statuses — all of this is immediately visible in SQL, with no arguments about generators. Here it is perfectly fine to set id = 10 hard: this dataset belongs to one test and does not pretend to be a common baseline.

If there are very few data rows, inline statements are perfectly appropriate. When the scenario is longer than a couple of commands, it is more honest to move it into scripts: the file gets a name, formatting, and reads like a small story of DB state.

But the strength of @Sql is locality. Such a setup should answer the question “what state does this test need,” not “what else can we shove into the database just in case.” Once a single script starts living as common air for half the suite, it is no longer a scenario — it is a candidate for a baseline migration.

5. Test-Only Flyway Migrations

Sometimes data is needed not by one test, but by many. Then @Sql and Java setup start duplicating one another, and the suite gradually turns into a series of “let’s insert the category java again.” In such a situation, test-only migrations are the most sensible source of truth: the schema comes from main migrations, the common reference background comes from test-only migrations, and every @DataJpaTest starts from the same state.

For ContentHub, a classic candidate for such a baseline is Category. It is a reference table that is convenient to rely on in different tests. And here it is safer to stick to a natural key: if a test needs the category java, it is better to look it up by code — for example, via findByCode("java") — than to attach the implicit rule id = 1 to the entire suite. A surrogate id can shift, while a natural key makes the dependency explicit.

The main thing is not to confuse the baseline with a hidden dataset. Once published articles, attachments, and bits of workflow begin living in the “common background,” that is no longer a baseline — it is a local scenario that needs to be moved back into @Sql or Java setup.

6. How to Combine Approaches Without a Mess

In a real project, you almost never have “only Java” or “only SQL.” The normal strategy is combined: the baseline lives in test-only migrations, and local scenarios are created either with Java setup or with @Sql. But it is important not to mix approaches “based on mood,” but to mix them “based on role.” As soon as you mix approaches without roles, you get tests that are hard to read: part of the data “came from somewhere,” part “was inserted somewhere,” and you no longer understand what is actually guaranteed.

Good readability in a data test often looks like the same structure: first you rely on the base background (for example, the category java already exists), then you add a local scenario (for example, create one article), then you call the repository and make an assertion.

An example of such a “mixed, but readable” test: the category comes from the baseline, and the article is created locally via Java setup, because we care about the Article → Category relationship specifically as an object relationship.

import org.junit.jupiter.api.Test; 
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.autoconfigure.orm.jpa.DataJpaTest;
import org.springframework.boot.test.autoconfigure.orm.jpa.TestEntityManager;

import static org.assertj.core.api.Assertions.assertThat;

@DataJpaTest
class MixedSetupDataJpaTest {

    @Autowired
    TestEntityManager em;

    @Autowired
    CategoryRepository categoryRepository;

    @Test
    void shouldPersistDraftInReferenceCategory() {
        // Arrange: the baseline lives in migrations, so we fetch the category by a natural key
        Category category = categoryRepository.findByCode("java").orElseThrow();

        // Arrange: we create the local scenario in Java, because the entity relationship matters
        Article article = new Article("Intro", "intro", ArticleStatus.DRAFT, category);

        // Act: save and flush into the DB so the repository works with real state later
        em.persistAndFlush(article);

        // Assert: verify that the article was really saved
        assertThat(article.getId()).isNotNull();
    }
}

Notice that the baseline category is not attached to id=1: we take the common background by code, and we create the local article where the scenario itself lives.

If you need “exactly this set of rows” (for example, three published articles with predetermined slug and publishedAt), Java setup can become too verbose, and then the local scenario is better done with @Sql. But the baseline should not duplicate these articles: the baseline stays a reference table, and the scenario stays a scenario.

Another important mixing point is the order of perception. If data comes from baseline migrations, the test should not rely on it “silently.” A good test reads at least one baseline value and thereby makes the dependency visible. This is like a good contract: it does not hope that “everyone already understands,” but explicitly records what matters.

7. Selection Cheat Sheet

When you are sitting over a test, your brain usually wants a simple answer: “what should I choose?” But the right answer is not in the tool — it is in the question “what DB state does this test need.” To keep the choice from turning into philosophy, it is useful to use a small cheat sheet: it is not dogma, but for ordinary data tests it works surprisingly well.

Start with the most practical question: how much data is there, and how “special” is it? If it is 1-2 entities, and they are only needed so the repository method does not run in a vacuum, Java setup is almost always the best option. If the data is “special” and you want fixed ids/slugs/times/order, @Sql is usually more honest. If the data is needed by many tests and is reference data, use a test-only migration. And this rule does not disappear when the test becomes wider and more expensive: what changes is the cost of execution, not the split between schema, common baseline, and local scenario.

Here is a compact decision table:

And now the little “tree” of questions you can keep in your head:

flowchart TD
    Q1[Is the data needed by many tests?] -->|Yes| Q2[Is it reference data, almost never changes?]
    Q2 -->|Yes| MIG[Test-only migration]
    Q2 -->|No| SQL["@Sql, but carefully and locally"]

    Q1 -->|No| Q3[Do you need an exact column value?]
    Q3 -->|Yes| SQL
    Q3 -->|No| JAVA[Java setup via TestEntityManager]

Notice that the tree does not have an “always @Sql” or “always Java” option. Because “always” is usually the road to tests that are either too long or too brittle.

8. Typical Mistakes When Choosing a Data Source

Mistake #1: mixing three approaches without roles.
The most common picture in a project that “grew quickly” is this: in one test, categories are created via Java setup, in another they are pulled in via @Sql, and in a third they suddenly appear from test-only migrations. In the end, you are no longer sure which data is guaranteed all the time and which is local. This is not cured by a new abstraction, but by a simple decision: reference data lives in one place (usually a migration), the scenario lives locally (Java or @Sql), and the test makes its dependency visible. For shared reference data, this usually means a lookup by natural key, not a silent bet on id=1.

Mistake #2: duplicating the same dataset in different sources.
If the category java exists both in a baseline migration, and in base-categories.sql, and also in the TestCategories helper, sooner or later someone will rename the category in one place and forget in the other. Tests will start failing “strangely,” because different tests live in different versions of reality. Here the rule helps: one data type — one source of truth. Even if you really want to duplicate it “just in case,” better not to.

Mistake #3: turning test-only migrations into a “dumping ground for everything.”
Test migrations are great as long as they contain a small, clear baseline. But if data for every new test starts being dumped there (“well, this is convenient, it will always be there”), the baseline stops being a baseline and becomes a hidden dependency. A month later, you get a test that fails because someone tweaked the “common data” without even knowing your test depended on it. A good test migration is like a reference table: short, stable, and boring (and boring in tests is a compliment).

Mistake #4: using @Sql as a universal load of “everything for everyone.”
A big SQL file that “fits all tests” usually sounds like a time saver. In practice, it turns into a monolith that is impossible to maintain: any change breaks dozens of tests, and you are afraid to delete an “extra row” because you do not know who needs it. @Sql is strongest precisely in being scenario-based: a small file, a clear name, one plot.

Mistake #5: a Java setup that is too long and hides the purpose of the test.
Java setup is great as long as it is short. But once half of the test is filling required entity fields, the meaning of the check starts to drown in boilerplate. At that point, it is very easy to make a mistake and create data not the way you think. If you see that preparation in Java has become longer than the assertion, that is a reason to stop and think: maybe @Sql is better (where you explicitly set only the necessary columns), or maybe your scenario should be simplified.