Integration test - validate PostgreSQL error

resources
Spring Boot Integration Testing with PostgreSQL using Testcontainers | Full Hands-on Guide

wanted to add a test to validate operations on PostgreSQL

things i wanted to test are like

ERROR:  insert or update on table "sample" violates foreign key constraint "sample_fkey"

ERROR:  duplicate key value violates unique constraint

Improvement I made

original

    assertThatThrownBy(() ->
      jdbc.update(/* create duplicate */)
    ).hasMessageContaining("duplicate key value violates unique constraint");

🚩i don't want to hardcode error message

1st thought: create enum

enum that maps error type to message
like unique_violation to "duplicate key value violates unique constraint"
✅ More readable
❌ Messages change across Postgres versions/locales

2nd: use SQLSTATE codes

more info about code: document
it would look like

assertThatThrownBy(() -> jdbc.update(/* create duplicate */))
  .isInstanceOf(DataIntegrityViolationException.class)
  .rootCause()
  .isInstanceOf(org.postgresql.util.PSQLException.class)
  .extracting(e -> ((org.postgresql.util.PSQLException)e).getSQLState())
  .isEqualTo("23505");

✅ More scalable
❌ hardcoding error code. like what does 23505 even mean?

3rd: Use the built-in Postgres JDBC enum

assertThatThrownBy(() -> jdbc.update(/* create duplicate */))
  .isInstanceOf(DataIntegrityViolationException.class)
  .rootCause()
  .isInstanceOf(org.postgresql.util.PSQLException.class)
  .extracting(e -> ((org.postgresql.util.PSQLException)e).getSQLState())
  .isEqualTo(SqlError.UNIQUE_VIOLATION.code());

✅ looks cleaner
❌ so much duplicate logic

we repeat

  .isInstanceOf(DataIntegrityViolationException.class)
  .rootCause()
  .isInstanceOf(org.postgresql.util.PSQLException.class)
  .extracting(e -> ((org.postgresql.util.PSQLException)e).getSQLState())

for each evaluation.

4th: Create a custom validation function

public class PgAssertions {
    private PgAssertions() {}

    /** Assert a specific SQLSTATE was raised (e.g., UNIQUE_VIOLATION). */
    public static void assertSqlState(ThrowableAssert.ThrowingCallable action, PSQLState sqlState) {
        Throwable thrown = catchThrowable(action);
        assertThat(thrown).isInstanceOf(org.springframework.dao.DataIntegrityViolationException.class);

        Throwable root = thrown.getCause();
        assertThat(root).isInstanceOf(org.postgresql.util.PSQLException.class);

        String state = ((org.postgresql.util.PSQLException) root).getSQLState();
        assertThat(state).isEqualTo(sqlState.getState());
    }
}

Before

assertThatThrownBy(() -> jdbc.update(/* create duplicate */))
  .isInstanceOf(DataIntegrityViolationException.class)
  .rootCause()
  .isInstanceOf(org.postgresql.util.PSQLException.class)
  .extracting(e -> ((org.postgresql.util.PSQLException)e).getSQLState())
  .isEqualTo("23505");

After

assertSqlState(
  () -> jdbc.update(/* create duplicate */),
  UNIQUE_VIOLATION
);

✅ readable
✅ less duplicated logic