Testcontainers で PostgreSQL を使ったデータベーステストを書く

import org.junit.jupiter.api.*;
import org.testcontainers.containers.PostgreSQLContainer;
import org.testcontainers.junit.jupiter.Container;
import org.testcontainers.junit.jupiter.Testcontainers;
import java.sql.*;
import java.util.*;
import static org.junit.jupiter.api.Assertions.*;

@Testcontainers
class UserRepositoryIntegrationTest {

    @Container
    static PostgreSQLContainer<?> postgres =
        new PostgreSQLContainer<>("postgres:16-alpine")
            .withDatabaseName("testdb").withUsername("testuser").withPassword("testpass");

    record User(String id, String name, String email) {}

    static class UserRepository {
        private final String url, user, pass;
        UserRepository(String url, String user, String pass) {
            this.url = url; this.user = user; this.pass = pass;
        }
        Connection conn() throws SQLException { return DriverManager.getConnection(url, user, pass); }

        void save(User u) throws SQLException {
            try (var c = conn(); var ps = c.prepareStatement(
                "INSERT INTO users(id,name,email) VALUES(?,?,?) ON CONFLICT(id) DO UPDATE SET name=EXCLUDED.name,email=EXCLUDED.email")) {
                ps.setString(1, u.id()); ps.setString(2, u.name()); ps.setString(3, u.email()); ps.executeUpdate();
            }
        }
        Optional<User> findById(String id) throws SQLException {
            try (var c = conn(); var ps = c.prepareStatement("SELECT id,name,email FROM users WHERE id=?")) {
                ps.setString(1, id);
                try (var rs = ps.executeQuery()) {
                    return rs.next() ? Optional.of(new User(rs.getString(1), rs.getString(2), rs.getString(3))) : Optional.empty();
                }
            }
        }
        List<User> findAll() throws SQLException {
            var list = new ArrayList<User>();
            try (var c = conn(); var st = c.createStatement(); var rs = st.executeQuery("SELECT id,name,email FROM users ORDER BY id")) {
                while (rs.next()) list.add(new User(rs.getString(1), rs.getString(2), rs.getString(3)));
            }
            return list;
        }
    }

    static UserRepository repo;

    @BeforeAll static void initSchema() throws SQLException {
        repo = new UserRepository(postgres.getJdbcUrl(), postgres.getUsername(), postgres.getPassword());
        try (var c = DriverManager.getConnection(postgres.getJdbcUrl(), postgres.getUsername(), postgres.getPassword());
             var st = c.createStatement()) {
            st.execute("CREATE TABLE IF NOT EXISTS users(id VARCHAR(50) PRIMARY KEY, name VARCHAR(100) NOT NULL, email VARCHAR(200))");
        }
    }

    @BeforeEach void clean() throws SQLException {
        try (var c = DriverManager.getConnection(postgres.getJdbcUrl(), postgres.getUsername(), postgres.getPassword());
             var st = c.createStatement()) { st.execute("TRUNCATE TABLE users"); }
    }

    @Test @DisplayName("保存して取得できる")
    void saveAndFind() throws SQLException {
        repo.save(new User("U001", "田中太郎", "[email protected]"));
        var found = repo.findById("U001");
        assertTrue(found.isPresent());
        assertEquals("田中太郎", found.get().name());
    }

    @Test @DisplayName("存在しない ID は空を返す")
    void notFound() throws SQLException {
        assertTrue(repo.findById("U999").isEmpty());
    }

    @Test @DisplayName("複数件を全件取得できる")
    void findAll() throws SQLException {
        repo.save(new User("U001", "田中", "[email protected]"));
        repo.save(new User("U002", "鈴木", "[email protected]"));
        assertEquals(2, repo.findAll().size());
    }
}