Verify PostgreSQL behavior with a Spring Boot Testcontainers test

• Use a PostgreSQL Testcontainer and Spring Boot service connection.
• Use the same Flyway or Liquibase migrations as the application.
• Do not recreate the schema manually in the test.
• Flush the second write so the constraint is observed inside the assertion.
• Keep the tested rule in the database, not only in application validation.

1. Declare one static PostgreSQL container for the test class.
2. Use @ServiceConnection so Spring Boot wires the datasource.
3. Start the real application context and run normal migrations.
4. Persist and flush the first user, then clear or separate the persistence context as needed.
5. Assert the second normalized duplicate fails on saveAndFlush.

@org.testcontainers.junit.jupiter.Testcontainers
@org.springframework.boot.test.context.SpringBootTest
class UserEmailConstraintTest {
    @org.testcontainers.junit.jupiter.Container
    @org.springframework.boot.testcontainers.service.connection.ServiceConnection
    static final org.testcontainers.containers.PostgreSQLContainer<?> postgres =
        new org.testcontainers.containers.PostgreSQLContainer<>("postgres:16-alpine");

    @org.springframework.beans.factory.annotation.Autowired UserRepository users;

    @org.junit.jupiter.api.Test
    void email_uniqueness_is_case_insensitive() {
        users.saveAndFlush(new User("User@Example.com"));

        org.assertj.core.api.Assertions.assertThatThrownBy(() ->
            users.saveAndFlush(new User("user@example.com")))
            .isInstanceOf(org.springframework.dao.DataIntegrityViolationException.class);
    }
}

// Production migration contains:
// create unique index ux_users_email_lower on app_user (lower(email));

The test performs two inserts and one indexed uniqueness check. Container startup dominates runtime, so reuse one container per class or suite while keeping database state isolated.

• A transactional test can become rollback-only after the expected violation; keep assertions after it minimal or isolate the failing write.
• PostgreSQL locale and collation rules may differ from simple lowercase expectations.
• A validation annotation can improve feedback but cannot replace concurrent database enforcement.
• Parallel tests need unique data or cleanup so they do not collide accidentally.

• Verify the datasource product name is PostgreSQL.
• Verify the migration history table contains the expected migration.
• Run with application-side duplicate checking removed and prove the database still protects the invariant.
• Use a distinct email and prove the failure is not caused by unrelated schema configuration.

• H2 compatibility mode can still disagree with PostgreSQL indexes, collations, locking, and SQL.
• Without flush, the exception may occur after the assertion or at transaction completion.
• Creating test-only DDL means the migration itself remains untested.
• A repository exists check races with another transaction unless the database owns uniqueness.