Verify data insertion and retrieval using Testcontainers with PostgreSQL

Preconditions (3)

Step 1: Start a PostgreSQL container using Testcontainers

Step 2: Connect to the PostgreSQL database using JDBC

Step 3: Create a table named 'users' with columns 'id' (int) and 'name' (varchar)

Step 4: Insert a record with id=1 and name='Alice' into the 'users' table

Step 5: Query the 'users' table to retrieve the record with id=1

Step 6: Verify that the retrieved name is 'Alice'

✅ Expected Result: The test should pass confirming that the record was inserted and retrieved correctly from the PostgreSQL container database

Automation Requirements - JUnit 5 with Testcontainers

Assertions Needed:

Assert that the retrieved name equals 'Alice'

Best Practices:

Use @Testcontainers and @Container annotations to manage container lifecycle

Use explicit waits or retries if needed for container readiness

Use try-with-resources for JDBC connections and statements

Keep test isolated and independent

Automated Solution

JUnit

import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.extension.ExtendWith;
import org.testcontainers.containers.PostgreSQLContainer;
import org.testcontainers.junit.jupiter.Container;
import org.testcontainers.junit.jupiter.Testcontainers;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import static org.junit.jupiter.api.Assertions.assertEquals;

@Testcontainers
public class PostgresTestcontainersTest {

    @Container
    public static PostgreSQLContainer<?> postgresContainer = new PostgreSQLContainer<>("postgres:15.3")
            .withDatabaseName("testdb")
            .withUsername("testuser")
            .withPassword("testpass");

    @Test
    public void testInsertAndRetrieveUser() throws Exception {
        String jdbcUrl = postgresContainer.getJdbcUrl();
        String username = postgresContainer.getUsername();
        String password = postgresContainer.getPassword();

        try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password)) {
            try (Statement stmt = conn.createStatement()) {
                stmt.executeUpdate("CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(255))");
            }

            try (PreparedStatement insertStmt = conn.prepareStatement("INSERT INTO users (id, name) VALUES (?, ?)");) {
                insertStmt.setInt(1, 1);
                insertStmt.setString(2, "Alice");
                insertStmt.executeUpdate();
            }

            try (PreparedStatement queryStmt = conn.prepareStatement("SELECT name FROM users WHERE id = ?")) {
                queryStmt.setInt(1, 1);
                try (ResultSet rs = queryStmt.executeQuery()) {
                    if (rs.next()) {
                        String name = rs.getString("name");
                        assertEquals("Alice", name, "The retrieved name should be Alice");
                    } else {
                        throw new AssertionError("No user found with id=1");
                    }
                }
            }
        }
    }
}

This test class uses JUnit 5 and Testcontainers to run a PostgreSQL database inside a Docker container.

The @Container annotation manages the lifecycle of the PostgreSQL container automatically.

Inside the test method, it connects to the database using JDBC with credentials provided by the container.

It creates a table, inserts a user record, then queries the record to verify the name matches the expected value.

Try-with-resources ensures all database resources are closed properly.

The assertion checks that the retrieved name is exactly 'Alice', confirming the database operations worked inside the container.

Common Mistakes - 4 Pitfalls

Not using @Container annotation and manually starting/stopping containers

Hardcoding JDBC URL, username, or password instead of using container getters

Not closing JDBC connections, statements, or result sets

Assuming the container is ready immediately after start without waiting

Bonus Challenge

Now add data-driven testing with 3 different user records (id and name) to verify insertion and retrieval for each.

Show Hint