Integration Testing with a Real Database

In software systems that use a database as their primary data store, it’s important to include the database in integration tests. This provides the most accurate representation of how the system behaves in production. You should never mock the database. But this raises an important question: How do you use a real database in an integration test?

This post presents an approach that uses a real database in integration testing and discusses how to write tests that support this strategy effectively.

This approach is particularly suited for large codebases where minimizing test time (both locally and in CI pipelines) is essential. It generally runs faster than alternatives like Testcontainers (This topic will be covered in an upcoming post focusing on benchmarking different approaches). However, for smaller projects or teams without experienced tech leads, more standardized solutions may be preferable.

The core idea is simple: run your tests against a real database. Doing so requires some structural considerations around how you write and run tests. This post covers:

• The local setup and use of transactions
• Running tests without full isolation
• Configuring this approach in CI pipelines
• When and how to allow committed transactions

This approach works especially well when combined with Test Data Builders for constructing test data.

The local setup and use of transactions

Locally, your tests should run against a real database that’s always up-to-date with the latest migrations. This can be done in one of two ways:

• Reuse the application database

  • Ensures consistency, especially for configuration and shared reference data.
  • However, it often diverges from how tests are executed in the pipeline, where the database is reset on every run.

• Use a separate test-specific database

  • Can be easily set up via a Docker container or local script, ensuring it’s always available.
  • You must actively avoid configuration/data drift from the real database to preserve test accuracy.

Once you have your test database, structure your integration tests using a shared setup and teardown pattern:

Start a transaction
Run the test logic
Roll back the transaction

Each test runs within its own transaction, which is rolled back afterward to ensure test independence.

In JUnit, it might look like this:

public class BaseIntegrationTest {
    DatabaseApi databaseApi;

    @BeforeEach
    public void setup() {
        databaseApi.startTransaction();
    }

    @AfterEach
    public void teardown() {
        databaseApi.rollbackTransaction();
    }
}

And a sample test:

public class ActualTest extends BaseIntegrationTest {
    SystemUnderTest sut;

    @Test
    void test() {
        // Arrange
        final var data = TestData.builder()
            .relatedInfo("Related to test")
            .build();

        // Act
        final var result = sut.consume(data);

        // Assert
        assertTrue(result.isSuccess());
    }
}

This pattern is effective but has some limitations, which we’ll explore next.

Running tests without full isolation

When running integration tests against a shared real database, each test does not get a completely clean, dedicated database instance. Instead, tests run against the same database state, relying on transactions (usually rolled back) to keep their changes isolated. Some tests may even commit transactions, as discussed in a later section. Because of this, tests must be written carefully with these behaviors in mind.

Take this example:

public class DataRepositoryTest extends BaseIntegrationTest {
    DataRepository dataRepository;

    @Test
    void testGetAll() {
        // Arrange
        final var id = 1;
        final var data = TestData.builder()
            .withId(id)
            .build();
        dataRepository.add(data);

        // Act
        final var result = queryHandler.getAll();

        // Assert
        assertEquals(1, result.size());
        assertEquals(id, result.get(0).getId());
    }
}

This test assumes the database is empty, which may not hold true in your shared test environment. Instead, use assertions that allow for other data to be present:

Assertions against collections must verify presence, not exact content

If the method under test returns a subset of data (e.g. pagination) you must ensure your test data always satisfies the query criteria. This can be done by carefully constructing the test data to always appear at the start or end of the result set, depending on how the paging is implemented.

Also, hardcoded values that are subject to unique constraints (like id = 1) are risky, as they can easily conflict with existing data. Instead:

Unique values (especially IDs) should be autogenerated or have a low risk of collision

Rewriting the test using these principles:

public class DataRepositoryTest extends BaseIntegrationTest {
    DataRepository dataRepository;

    @Test
    void testGetAll() {
        // Arrange
        final var data = TestData.builder().build();
        final var id = dataRepository.add(data);

        // Act
        final var result = queryHandler.getAll();

        // Assert
        final var containsId = result.stream()
            .anyMatch(value -> id.equals(value.getId()));
        assertTrue(containsId);
    }
}

This makes the test resilient to pre-existing data and avoids ID collisions.

Configuring this approach in CI pipelines

To run this setup in a CI pipeline, follow these steps:

Start the database
Apply the latest database migrations
Run all tests
Stop the database

This can be done easily using two Docker containers, one for the database and one for running the tests.

In large, long-lived projects, migrations can become slow. A solution is to create a pre-migrated database dump or a Docker image containing a migrated database, which can be spun up quickly to improve test speed.

When and how to allow committed transactions

While the default pattern rolls back transactions after each test, some tests may need to commit data mid-test (e.g., testing multi-transaction behavior). This is still supported, but comes with caveats.

In such cases, all test data must be reproducible. You should be able to run the test multiple times without failure.

Tests that commit transactions must use reproducible, non-colliding data

That said, tests that commit data are harder to maintain and may introduce side effects:

The number of tests that commit transactions should be minimized

You can set up explicit cleanup for these transactions, but it's often difficult to account for all side effects. Instead, if you write the test to generate data that could reasonably exist in the database, you reduce the risk of interference with other tests and avoid the need for manual cleanup. This approach also helps minimize test flakiness and improves reliability by ensuring tests remain isolated and deterministic despite committing data.

Conclusion

Running integration tests against a real database increases the accuracy and reliability of your test suite. By running each test inside a transaction that’s rolled back at the end, you get fast, isolated tests without mocks. To make this work, follow these key principles:

Assertions against collections must verify presence, not exact contents
Unique values (especially IDs) should be autogenerated or have a low risk of collision
Tests that commit transactions must use reproducible, non-colliding data
The number of tests that commit transactions should be minimized

This pattern scales well for both local development and CI pipelines. If you're careful, you could even run rollback-based tests against a production-like environment.