Testcontainers Tutorial
Part 5 of 20
Abhay 7 min read

PostgreSQL Testing with Testcontainers

Testing database code against H2 gives you a false sense of security. PostgreSQL has different behavior for ON CONFLICT, RETURNING, JSON operators, full-text search, and dozens of other features. A JPA query that works in H2 dialect may fail against real PostgreSQL. This article shows how to test your Spring Data JPA repositories, custom queries, and database constraints against a real PostgreSQL instance using Testcontainers.

What You’ll Learn

  • @ServiceConnection — the Spring Boot 3.1+ zero-configuration approach
  • @DynamicPropertySource — the pre-3.1 approach still useful for non-standard cases
  • @DataJpaTest vs @SpringBootTest for repository tests
  • Testing custom JPQL and native SQL queries
  • Testing database constraints and unique violations
  • SQL script initialization with @Sql
  • Flyway migration testing

Dependencies

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-testcontainers</artifactId>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>org.testcontainers</groupId>
    <artifactId>junit-jupiter</artifactId>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>org.testcontainers</groupId>
    <artifactId>postgresql</artifactId>
    <scope>test</scope>
</dependency>

The Domain Model

The Order entity used throughout this series:

@Entity
@Table(name = "orders")
public class Order {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false)
    private String customerId;

    @Enumerated(EnumType.STRING)
    @Column(nullable = false)
    private OrderStatus status;

    @Column(nullable = false, precision = 10, scale = 2)
    private BigDecimal totalAmount;

    @CreationTimestamp
    private LocalDateTime createdAt;

    @UpdateTimestamp
    private LocalDateTime updatedAt;

    protected Order() {}

    public Order(String customerId, OrderStatus status, BigDecimal totalAmount) {
        this.customerId = customerId;
        this.status = status;
        this.totalAmount = totalAmount;
    }

    // getters and setters
}

public interface OrderRepository extends JpaRepository<Order, Long> {

    List<Order> findByCustomerId(String customerId);

    List<Order> findByStatus(OrderStatus status);

    Page<Order> findByCustomerId(String customerId, Pageable pageable);

    @Query("SELECT o FROM Order o WHERE o.totalAmount > :amount ORDER BY o.totalAmount DESC")
    List<Order> findOrdersAboveAmount(@Param("amount") BigDecimal amount);

    @Query(value = """
        SELECT *
        FROM orders
        WHERE created_at >= CURRENT_DATE - INTERVAL '7 days'
        ORDER BY created_at DESC
        """,
        nativeQuery = true)
    List<Order> findOrdersFromLastWeek();

    @Modifying
    @Query("UPDATE Order o SET o.status = :newStatus WHERE o.customerId = :customerId AND o.status = :oldStatus")
    int updateOrderStatus(
        @Param("customerId") String customerId,
        @Param("oldStatus") OrderStatus oldStatus,
        @Param("newStatus") OrderStatus newStatus
    );
}

@ServiceConnection — The Clean Approach (Spring Boot 3.1+)

@ServiceConnection is a Spring Boot 3.1+ annotation that automatically reads connection details from a Testcontainers container and configures the corresponding Spring Boot auto-configuration. No @DynamicPropertySource required.

@SpringBootTest
@Testcontainers
class OrderRepositoryTest {

    @Container
    @ServiceConnection
    static PostgreSQLContainer<?> postgres =
        new PostgreSQLContainer<>("postgres:16-alpine");

    @Autowired
    private OrderRepository orderRepository;

    @BeforeEach
    void cleanup() {
        orderRepository.deleteAll();
    }

    @Test
    void shouldSaveAndRetrieveOrder() {
        Order order = new Order("customer-1", OrderStatus.PENDING, BigDecimal.valueOf(99.99));
        Order saved = orderRepository.save(order);

        Optional<Order> found = orderRepository.findById(saved.getId());

        assertThat(found).isPresent();
        assertThat(found.get().getCustomerId()).isEqualTo("customer-1");
        assertThat(found.get().getTotalAmount())
            .isEqualByComparingTo(BigDecimal.valueOf(99.99));
    }
}

@ServiceConnection on PostgreSQLContainer automatically sets:

  • spring.datasource.url
  • spring.datasource.username
  • spring.datasource.password

Spring Boot detects the PostgreSQLContainer type and knows how to read connection details from it.

@DynamicPropertySource — The Pre-3.1 Approach

For Spring Boot versions before 3.1, or for non-standard configurations that @ServiceConnection does not cover:

@SpringBootTest
@Testcontainers
class OrderRepositoryTest {

    @Container
    static PostgreSQLContainer<?> postgres =
        new PostgreSQLContainer<>("postgres:16-alpine")
            .withDatabaseName("orders_test")
            .withUsername("orders_user")
            .withPassword("orders_pass");

    @DynamicPropertySource
    static void configureDataSource(DynamicPropertyRegistry registry) {
        registry.add("spring.datasource.url", postgres::getJdbcUrl);
        registry.add("spring.datasource.username", postgres::getUsername);
        registry.add("spring.datasource.password", postgres::getPassword);
        registry.add("spring.datasource.driver-class-name", () -> "org.postgresql.Driver");
        registry.add("spring.jpa.database-platform", () -> "org.hibernate.dialect.PostgreSQLDialect");
    }
}

@DynamicPropertySource runs before the Spring context is built, allowing you to inject container-specific values into the property environment.

@DataJpaTest vs @SpringBootTest

@DataJpaTest is a Spring Boot test slice that only loads the JPA-related beans — repositories, EntityManager, datasource, and Flyway/Liquibase. It does not load controllers, services, or other beans. This makes it faster than a full @SpringBootTest.

@DataJpaTest
@Testcontainers
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE) // critical
class OrderRepositorySliceTest {

    @Container
    @ServiceConnection
    static PostgreSQLContainer<?> postgres =
        new PostgreSQLContainer<>("postgres:16-alpine");

    @Autowired
    private OrderRepository orderRepository;

    @Autowired
    private TestEntityManager entityManager;
}

@AutoConfigureTestDatabase(replace = NONE) is critical. By default, @DataJpaTest replaces your datasource with an H2 in-memory database. This annotation tells it to use the datasource you configured — in this case, the PostgreSQL container.

When to use each:

@DataJpaTest@SpringBootTest
What loadsJPA layer onlyFull application context
SpeedFaster (partial context)Slower (full context)
TransactionRolled back after each testNot rolled back (unless @Transactional)
Use caseRepository and query testingIntegration of multiple layers

Testing Custom Queries

The real value of PostgreSQL containers is testing queries that would behave differently against H2.

Testing JPQL Queries

@DataJpaTest
@Testcontainers
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)
class OrderQueryTest {

    @Container
    @ServiceConnection
    static PostgreSQLContainer<?> postgres =
        new PostgreSQLContainer<>("postgres:16-alpine");

    @Autowired
    private OrderRepository orderRepository;

    @Test
    void shouldFindOrdersAboveAmount() {
        orderRepository.save(new Order("customer-1", OrderStatus.PENDING, BigDecimal.valueOf(50.00)));
        orderRepository.save(new Order("customer-1", OrderStatus.PENDING, BigDecimal.valueOf(150.00)));
        orderRepository.save(new Order("customer-1", OrderStatus.PENDING, BigDecimal.valueOf(200.00)));

        List<Order> largeOrders = orderRepository.findOrdersAboveAmount(BigDecimal.valueOf(100.00));

        assertThat(largeOrders).hasSize(2);
        assertThat(largeOrders)
            .extracting(Order::getTotalAmount)
            .allMatch(amount -> amount.compareTo(BigDecimal.valueOf(100.00)) > 0);
        // verify descending sort
        assertThat(largeOrders.get(0).getTotalAmount())
            .isGreaterThan(largeOrders.get(1).getTotalAmount());
    }

    @Test
    void shouldFindOrdersFromLastWeek() {
        // native query uses PostgreSQL INTERVAL syntax — fails against H2
        orderRepository.save(new Order("customer-1", OrderStatus.CONFIRMED, BigDecimal.valueOf(75.00)));

        List<Order> recentOrders = orderRepository.findOrdersFromLastWeek();

        assertThat(recentOrders).isNotEmpty();
    }
}

The findOrdersFromLastWeek() method uses PostgreSQL’s INTERVAL syntax. H2 does not support CURRENT_DATE - INTERVAL '7 days' without compatibility mode. Against a real PostgreSQL container, this test verifies the actual query syntax.

Testing @Modifying Queries

@Test
@Transactional
void shouldBulkUpdateOrderStatus() {
    orderRepository.save(new Order("customer-1", OrderStatus.PENDING, BigDecimal.valueOf(50.00)));
    orderRepository.save(new Order("customer-1", OrderStatus.PENDING, BigDecimal.valueOf(75.00)));
    orderRepository.save(new Order("customer-1", OrderStatus.CONFIRMED, BigDecimal.valueOf(100.00)));

    int updated = orderRepository.updateOrderStatus(
        "customer-1",
        OrderStatus.PENDING,
        OrderStatus.CONFIRMED
    );

    assertThat(updated).isEqualTo(2);

    List<Order> orders = orderRepository.findByCustomerId("customer-1");
    assertThat(orders)
        .extracting(Order::getStatus)
        .containsOnly(OrderStatus.CONFIRMED);
}

Testing Pagination

@Test
void shouldReturnPagedResults() {
    for (int i = 0; i < 15; i++) {
        orderRepository.save(
            new Order("customer-1", OrderStatus.PENDING, BigDecimal.valueOf(10.00 * (i + 1)))
        );
    }

    Page<Order> firstPage = orderRepository.findByCustomerId(
        "customer-1",
        PageRequest.of(0, 5, Sort.by("totalAmount").ascending())
    );

    assertThat(firstPage.getTotalElements()).isEqualTo(15);
    assertThat(firstPage.getTotalPages()).isEqualTo(3);
    assertThat(firstPage.getContent()).hasSize(5);
    assertThat(firstPage.getContent().get(0).getTotalAmount())
        .isEqualByComparingTo(BigDecimal.valueOf(10.00));
}

Testing Database Constraints

Constraint violations require a real database. H2 enforcement is inconsistent for complex constraints.

@Entity
@Table(
    name = "products",
    uniqueConstraints = @UniqueConstraint(columnNames = {"sku"})
)
public class Product {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false, unique = true)
    private String sku;

    @Column(nullable = false)
    private String name;

    @Column(nullable = false)
    private int stock;
}

@Test
void shouldThrowOnDuplicateSku() {
    productRepository.saveAndFlush(new Product("SKU-001", "Widget A", 100));

    assertThatThrownBy(() -> {
        productRepository.saveAndFlush(new Product("SKU-001", "Widget B", 50));
    }).isInstanceOf(DataIntegrityViolationException.class)
      .hasMessageContaining("unique");
}

@Test
void shouldThrowWhenSkuIsNull() {
    assertThatThrownBy(() -> {
        productRepository.saveAndFlush(new Product(null, "Widget A", 100));
    }).isInstanceOf(DataIntegrityViolationException.class);
}

SQL Script Initialization with @Sql

@Sql runs SQL scripts before or after test methods. This is useful for seeding test data from external SQL files:

@Test
@Sql("/test-data/orders.sql")
void shouldLoadOrdersFromScript() {
    List<Order> orders = orderRepository.findAll();
    assertThat(orders).hasSize(5);
}

@Test
@Sql(
    scripts = "/test-data/orders.sql",
    executionPhase = Sql.ExecutionPhase.BEFORE_TEST_METHOD
)
@Sql(
    scripts = "/test-data/cleanup.sql",
    executionPhase = Sql.ExecutionPhase.AFTER_TEST_METHOD
)
void shouldProcessOrdersAndCleanUp() {
    // test body
}

-- src/test/resources/test-data/orders.sql
INSERT INTO orders (customer_id, status, total_amount, created_at)
VALUES
    ('customer-1', 'PENDING', 99.99, NOW()),
    ('customer-1', 'CONFIRMED', 149.99, NOW()),
    ('customer-2', 'PENDING', 49.99, NOW()),
    ('customer-2', 'SHIPPED', 199.99, NOW()),
    ('customer-3', 'DELIVERED', 299.99, NOW());

Flyway Migration Testing

If your project uses Flyway, Testcontainers gives you the ability to run migrations against a real PostgreSQL instance and verify they succeed:

@SpringBootTest
@Testcontainers
class FlywayMigrationTest {

    @Container
    @ServiceConnection
    static PostgreSQLContainer<?> postgres =
        new PostgreSQLContainer<>("postgres:16-alpine");

    @Autowired
    private Flyway flyway;

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Test
    void shouldApplyAllMigrationsSuccessfully() {
        MigrationInfoService info = flyway.info();
        MigrationInfo[] applied = info.applied();

        assertThat(applied).isNotEmpty();
        assertThat(Arrays.stream(applied))
            .allMatch(m -> m.getState() == MigrationState.SUCCESS);
    }

    @Test
    void shouldCreateOrdersTable() {
        List<String> columns = jdbcTemplate.queryForList(
            """
            SELECT column_name
            FROM information_schema.columns
            WHERE table_name = 'orders'
            ORDER BY ordinal_position
            """,
            String.class
        );

        assertThat(columns).contains("id", "customer_id", "status", "total_amount", "created_at");
    }
}

application-test.yml

Keep your test-specific Spring configuration in src/test/resources/application-test.yml. Activate it with @ActiveProfiles("test") or by setting spring.profiles.active=test in test resources:

# src/test/resources/application-test.yml
spring:
  jpa:
    hibernate:
      ddl-auto: create-drop
    show-sql: true
    properties:
      hibernate:
        format_sql: true
  flyway:
    enabled: false  # disable Flyway in unit/slice tests; enable explicitly when needed

For Flyway migration tests, enable Flyway in the specific test class:

@TestPropertySource(properties = "spring.flyway.enabled=true")
class FlywayMigrationTest {

Common Pitfalls

Forgetting @AutoConfigureTestDatabase(replace = NONE) with @DataJpaTest. Without this, Spring Boot replaces your PostgreSQL container datasource with H2. Your test runs against H2, not PostgreSQL, and you get no benefit from the container.

Using save() instead of saveAndFlush() when testing constraints. JPA batches writes by default. save() does not immediately flush to the database, so constraint violations do not trigger until the transaction commits. Use saveAndFlush() to test constraints immediately.

Not cleaning up between tests. Static containers share state. If test A inserts rows and does not clean up, test B finds unexpected data. Add @BeforeEach void cleanup() { repository.deleteAll(); } or use @Transactional (with @DataJpaTest’s automatic rollback).

Summary

PostgreSQL containers with Testcontainers give you reliable database tests that match production behavior. @ServiceConnection eliminates boilerplate in Spring Boot 3.1+. @DataJpaTest with @AutoConfigureTestDatabase(replace = NONE) gives you fast slice tests against real PostgreSQL. Custom JPQL and native SQL queries, database constraints, migrations, and pagination all test correctly against the real database.

The next article covers MySQL testing — including MySQL-specific syntax, character sets, and behavior differences from PostgreSQL.

Next: MySQL Testing with Testcontainers