Small real-DB subset for tests

1. The value of a small real DB path

Once it is clear that an embedded database does not always prove PostgreSQL behavior, the next engineering question appears: out of the whole data suite, what is actually worth strengthening with a real database. The answer is almost never “everything”.

When you hear the phrase “let’s run tests against a real PostgreSQL”, beginners often switch into maximalism mode: “cool, then let’s move everything to the real database — it will be more reliable.” That thought sounds logical, roughly like “let’s drive to the store only in a tank — that is safer.” The problem is that every strengthening has a price: startup time, environment setup, suite speed, and stability across different machines. So our goal is not “make every test as strong as possible”, but to build a reasonable set of evidence where each expensive check has a clear reason.

The main idea: the real DB path is a trust amplifier inside the same @DataJpaTest, not a new default for the whole suite and not a switch to a separate infrastructure mode. We do not change the layer, we do not turn repository tests into a “mini-integration”, and we do not drag everything onto the real database. It makes sense to strengthen only those scenarios where the risk sits specifically in the behavior of a particular database: constraints, sorting, paging, time, or a custom query. Everything else stays happily on the embedded path and keeps providing fast feedback.

2. An algorithm for choosing tests for real DB

When you are sitting in front of a repository, you usually have two temptations. The first is “I will not do real DB tests at all, this is fine.” The second is “I will move everything to the real DB, let it burn.” Between them there is a calm engineering middle ground: ask a couple of right questions and get a small list of candidates.

Let’s phrase this algorithm in a way that feels less like philosophy and more like a clear route. Imagine that you are advising your future self (who is tired, on a release, and has no energy for romance).

flowchart TD
    A["There is a data test or a data test is planned"] --> B{"Is the risk related to the DB dialect/behavior?"}
    B -- no --> C["Keep the test on the embedded path"]
    B -- yes --> D{"Does the test check: constraint / ordering / paging / time / @Query?"}
    D -- no --> C
    D -- yes --> E{"Is the scenario critical for the API/business?"}
    E -- no --> F["You can keep it embedded, or make 1 test on real DB"]
    E -- yes --> G["Include it in the small real-DB subset"]

What matters here is that we are not looking for an “ideal formula”. We are building a small set of tests with high ROI: checks that either catch a real class of production bugs or protect important reads and constraints. And yes, sometimes the subset contains only 3–6 tests per repository — and that is normal. Sometimes 1–2 — and that is normal too.

3. ContentHub: candidates for the real DB subset

Now comes the part where it helps to think not “which repository method is complex”, but “where will the business actually feel pain”. In ContentHub there are several places where database behavior is critical: slug uniqueness, the public feed of published articles, deterministic sorting, and correct pagination. These are the zones where, if the database behaves “wrong”, the API client starts seeing strange results and the developers start seeing strange bug reports.

slug uniqueness: a constraint as part of domain invariants

With slug it is simple and at the same time tricky. In code you can do everything perfectly: generate a slug, validate something at the service level, even write unit tests. But the last line of defense is a unique index/constraint in the database. And if it suddenly does not work the way you expect (or the migration did not create it), the problem will surface only on the real database.

A mini example of a data test that fits the real DB subset very well:

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

import jakarta.persistence.PersistenceException;
import org.junit.jupiter.api.Test;

@Test
void slug_mustBeUnique() {
    // Save two entities with the same slug: the application may "let it through",
    // but the database must reject it at the unique constraint level.
    entityManager.persist(articleWithSlug("same-slug"));
    entityManager.persist(articleWithSlug("same-slug"));

    // It is important to "touch" the DB explicitly: persist by itself may not
    // send SQL immediately, while flush sends it and forces the constraint to fire.
    assertThatThrownBy(() -> entityManager.flush())
            // For TestEntityManager this is already enough to signal that the write failed
            // at the persistence boundary, not somewhere accidentally inside the test.
            .isInstanceOf(PersistenceException.class);
}

For a teaching example, this is enough: we confirm that the duplicate was cut off by the database and specifically on flush(). In a real suite, it is useful to latch onto the most stable signal in your stack — for example, Spring DataIntegrityViolationException on the repository path or ConstraintViolationException in the cause-chain of Hibernate.

Public feed: sorting + pagination + secondary order

The public feed is something people touch by hand most often. If we have GET /api/public/articles (we are not testing the web layer right now, just recalling the business meaning of the read), then at the repository level there is usually a method like “give me published items, sort by publication time, paginate”.

The most common mistake in such reads is to assume that sorting is “stable anyway”. The database is not obliged to return rows in a convenient order if no sorting is specified. And if sorting is specified on a field where values can match (for example, several articles are published within the same second), then without a secondary order different databases and different execution plans may return rows differently.

A candidate for the real DB subset is a query with both order by and paging, plus an important result contract. For example:

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;

@Query("""
       select a from Article a
       where a.status = :status
       -- Critical point: two levels of sorting.
       -- publishedAt defines "freshness", and id fixes the order when publishedAt values are equal.
       order by a.publishedAt desc, a.id desc
       """)
Page<Article> findPublicFeed(ArticleStatus status, Pageable pageable);

We are not testing the JPQL string; we are testing the effect: which articles arrive and in what order. And that “in what order” is exactly the place where the real database can reveal sorting nuances that the embedded environment will not show.

Identical publishedAt: determinism

There is an unpleasant class of bugs that sounds funny: “sometimes the order in the feed jumps around.” Know why they are unpleasant? Because they are almost impossible to catch by eye during a manual check: everything is fine today, different tomorrow. And that is exactly why deterministic sorting is a great topic for a strong test.

A mini test idea (with a very short code example) is to prepare articles with the same publishedAt but different id, and verify that the secondary sort by id really fixes the order:

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

import java.time.Instant;

@Test
void publicFeed_orderIsDeterministic() {
    // Create two published items with the same publishedAt:
    // this is a typical "edge case" where, without secondary sorting, the order becomes random.
    persistPublished("a", Instant.parse("2026-03-21T10:00:00Z"));
    persistPublished("b", Instant.parse("2026-03-21T10:00:00Z"));

    // Important: take the first page and explicitly define sorting
    // the way it is expected in the public feed (publishedAt desc, id desc).
    var page = repository.findPublicFeed(PUBLISHED, firstPageSorted());

    // Verify the contract: the order must be deterministic.
    assertThat(page.getContent()).extracting(Article::getSlug)
            .containsExactly("b", "a");
}

Yes, the example is conditional: the real order depends on which ids the database actually assigns. But the logic is clear: if publishedAt values are equal, the order must be decided by secondary sorting, not by randomness.

4. What to leave on the embedded path

To make a “small real DB subset”, you need to honestly tell yourself: some tests do not have to live on the real database. And this is not “saving for the sake of saving”, but controlling complexity. Queries where the risk is mainly that we made a mistake in the derived method name or in an equality filter, and where DB behavior hardly affects the meaning, can stay on the embedded path.

A typical example is “find an article by id and author” or “find by slug and status” (if we are not testing subtle collation/case-sensitivity issues, and so on):

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

import java.util.Optional;
import org.junit.jupiter.api.Test;

@Test
void findByIdAndAuthorUsername_returnsOnlyOwnersArticle() {
    // Test data setup: create a draft that belongs to alice.
    var saved = persistDraft("spring-jpa", "alice");

    // Check the read contract itself: the owner must find their record by the id + authorUsername pair.
    Optional<Article> found = repository.findByIdAndAuthorUsername(saved.getId(), "alice");
    assertThat(found).isPresent();
}

Such a test does a great job of catching defects like “we plugged in the wrong field” and “the repository method does not match the expectation”, but it hardly depends on the dialect. So it can safely live in the cheap mode, without forcing every suite run to go to the real database.

It is important not to confuse this with “simple tests are not needed”. They are needed. They just have a different job: to protect reads quickly and locally, without turning the data suite into an infrastructure quest.

5. Organization: suite + subset without copy-paste

Test organization is where people most often “leak” into chaos. At first everything is nice: one ArticleRepositoryDataJpaTest. Then we add replace = NONE, then another class, then someone copies tests “just in case”, and suddenly we have two identical sets that sometimes drift apart in expectations. That is a near-guaranteed way to grow pointless maintenance.

A healthier model is this: we have the main data-test package (embedded path), and a separate small class/set of classes that explicitly says in the name that this is a subset. For example:

import org.springframework.boot.test.autoconfigure.jdbc.AutoConfigureTestDatabase;
import org.springframework.boot.test.autoconfigure.orm.jpa.DataJpaTest;

@DataJpaTest
// The key switch: Spring does not replace the DataSource with an embedded one,
// and instead uses the "real" database from the test configuration.
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)
class ArticleRepositoryRealDbSubsetTest {
    // Keep a small set (for example, 3-6) of tests here
    // that are genuinely sensitive to PostgreSQL.
}

Notice the subtle but important idea: we are not building a “second reality”. This is not a second world where “everything is different”. These are the same repositories, the same @DataJpaTest, the same TestEntityManager, flush/clear. The only difference is the environment underneath them: the source of truth is now the project’s real database.

And to avoid copy-paste, it is convenient to move test object factories and small helper methods somewhere they are shared by both paths. This could be, for example, ArticleTestFactory or TestArticles, which creates entities with sensible defaults, while in each test you change only what matters for the scenario.

A mini example of a “factory” code snippet (intentionally short and without magic):

import java.time.Instant;

Article published(String slug, Instant publishedAt) {
    // Minimal set of fields that matters specifically for public feed scenarios:
    // slug (for easy assertions in tests), status (included in the selection), publishedAt (for sorting).
    Article a = new Article();
    a.setSlug(slug);
    a.setStatus(ArticleStatus.PUBLISHED);
    a.setPublishedAt(publishedAt);
    return a;
}

Yes, this is manual code. And it is good precisely because it does not hide from the student which fields are actually important. In tests, we value transparency a little more than elegance.

Below is an example of what a small real DB subset for ArticleRepository might look like if we choose scenarios by risk, not by the number of methods.

Notice that there is no idea of “cover every repository method”. And that is right: the goal of the subset is to close the most expensive bugs, the ones that appear only on the real database and hit application behavior the hardest.

If you want a short rule that genuinely helps in real life, it is this: a test belongs in the real DB subset if you can honestly say one sentence about it — “I am running this on the real database because the database itself is the source of risk here.”

6. Typical mistakes in a real DB subset

Mistake #1: moving everything to real DB “because it is more reliable”.
In practice, this almost always ends with the suite becoming slow and fragile, and the team starting to run tests less often. The irony is that “the most reliable tests” turn into “the most ignored tests”. The real gain comes not from mass migration, but from a targeted subset with high value.

Mistake #2: duplicating the same test on both embedded and real DB without new evidential value.
Sometimes a test is copied “just in case”, but it checks exactly the same fact that does not depend on the dialect or constraints. In the end we just have two identical tests that sometimes fail for different reasons (for example, different error messages), but do not add confidence. Duplication is justified only if the environment really changes the probability of catching a defect.

Mistake #3: changing assertions “for the database” by adjusting expectations.
If a test on embedded says “the order is like this”, and on the real database you have to rewrite the expectations, that is not a reason to silently “adapt the asserts”. It is a reason to stop and understand: either we did not fix determinism in embedded at all (and then the test was weak), or there is no explicit order in the query (and then the query itself is under-specified).

Mistake #4: including tests in the subset without a clear reason.
This looks like “well, I feel this test is important”. Feelings can be useful in testing (especially the feeling of pain when the suite fails), but for a real DB subset it is better to keep a simple discipline: every test has a short reason in one line. If you cannot explain it, it is probably not a candidate.

Mistake #5: turning the subset into a “second full suite” over time.
The most invisible degradation: today we added 4 tests, tomorrow another 4, then another 4 — and suddenly the subset stopped being a subset. The remedy is very simple and boring (which means it works): periodically re-read the list and honestly remove what does not carry unique value.