Unit Testing the JDBC Layer Using TestContainers and Liquibase

Database testing has always been a challenging aspect of software development. When it comes to testing the JDBC layer, developers often face a dilemma: use mocks (which don't test actual database interactions) or use a real database (which can be slow and difficult to set up consistently). This is where TestContainers comes in as a game-changer for your Java database testing strategy.

In this comprehensive guide, we'll explore how to effectively unit test your JDBC layer using TestContainers and Liquibase, providing you with reliable tests that run against real database instances without the headache of complex setup and teardown procedures.

What is TestContainers?

TestContainers is a Java library that provides lightweight, throwaway instances of common databases, Selenium web browsers, or anything else that can run in a Docker container. It makes it easy to create and manage Docker containers for your tests programmatically.

Key advantages of TestContainers include:

• Real database testing: Test against actual database instances, not simulations
• Isolation: Each test runs in its own container, preventing test interference
• Consistency: The same database version everywhere (development, CI/CD, production)
• Simplicity: No need for complex mock setups or external database management

What is Liquibase?

Liquibase is an open-source database-independent library for tracking, managing, and applying database schema changes. It works with virtually any database type and provides a structured approach to version control for your database schema.

Key benefits of Liquibase include:

• Database version control: Track all changes to your database schema
• Format flexibility: Define changes in XML, YAML, JSON, or SQL
• Rollback support: Easily revert changes when needed
• Database independence: Works with most major database platforms
• Integration support: Can be used in Java applications, build tools, and CI/CD pipelines

Setting Up TestContainers and Liquibase for JDBC Testing

Prerequisites

Before we dive into the implementation, ensure you have:

1. Java 8 or higher
2. Maven or Gradle
3. Docker installed and running on your machine
4. Basic knowledge of JDBC and unit testing

Adding Dependencies

First, let's add the necessary dependencies to your project.

For Maven:

<dependencies>
    <!-- JDBC driver for your database -->
    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <version>42.6.0</version>
    </dependency>
    
    <!-- TestContainers -->
    <dependency>
        <groupId>org.testcontainers</groupId>
        <artifactId>testcontainers</artifactId>
        <version>1.19.3</version>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>org.testcontainers</groupId>
        <artifactId>junit-jupiter</artifactId>
        <version>1.19.3</version>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>org.testcontainers</groupId>
        <artifactId>postgresql</artifactId>
        <version>1.19.3</version>
        <scope>test</scope>
    </dependency>
    
    <!-- Liquibase -->
    <dependency>
        <groupId>org.liquibase</groupId>
        <artifactId>liquibase-core</artifactId>
        <version>4.25.1</version>
    </dependency>
    
    <!-- Testing frameworks -->
    <dependency>
        <groupId>org.junit.jupiter</groupId>
        <artifactId>junit-jupiter</artifactId>
        <version>5.10.0</version>
        <scope>test</scope>
    </dependency>
</dependencies>

For Gradle:

dependencies {
    // JDBC driver for your database
    implementation 'org.postgresql:postgresql:42.6.0'
    
    // TestContainers
    testImplementation 'org.testcontainers:testcontainers:1.19.3'
    testImplementation 'org.testcontainers:junit-jupiter:1.19.3'
    testImplementation 'org.testcontainers:postgresql:1.19.3'
    
    // Liquibase
    implementation 'org.liquibase:liquibase-core:4.25.1'
    
    // Testing frameworks
    testImplementation 'org.junit.jupiter:junit-jupiter:5.10.0'
}

Setting Up Liquibase

Creating Changelog Files

First, let's set up our Liquibase changelog files in src/main/resources/db/changelog/.

1. Create a master changelog file named db.changelog-master.xml:

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
    http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.4.xsd">
    
    <include file="db/changelog/changes/001-create-users-table.xml"/>
</databaseChangeLog>

2. Create a changeset file named db/changelog/changes/001-create-users-table.xml:

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
    http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.4.xsd">
    
    <changeSet id="001" author="developer">
        <createTable tableName="users">
            <column name="id" type="BIGINT" autoIncrement="true">
                <constraints primaryKey="true" nullable="false"/>
            </column>
            <column name="username" type="VARCHAR(50)">
                <constraints unique="true" nullable="false"/>
            </column>
            <column name="email" type="VARCHAR(100)">
                <constraints nullable="false"/>
            </column>
        </createTable>
    </changeSet>
</databaseChangeLog

Implementing JDBC Layer Tests with TestContainers and Liquibase

Let's create a practical example of testing a UserRepository class that uses JDBC to interact with a PostgreSQL database, using Liquibase for schema management.

1. Creating the User Model and Repository

First, let's define a simple User model:

public class User {
    private Long id;
    private String username;
    private String email;
    
    // Constructors, getters, setters
    public User(String username, String email) {
        this.username = username;
        this.email = email;
    }
    
    public Long getId() { return id; }
    public void setId(Long id) { this.id = id; }
    public String getUsername() { return username; }
    public String getEmail() { return email; }
}

Next, let's create a UserRepository class with JDBC:

public class UserRepository {
    private final String jdbcUrl;
    private final String username;
    private final String password;
    
    public UserRepository(String jdbcUrl, String username, String password) {
        this.jdbcUrl = jdbcUrl;
        this.username = username;
        this.password = password;
    }
    
    private Connection getConnection() throws SQLException {
        return DriverManager.getConnection(jdbcUrl, username, password);
    }
    
    public void save(User user) throws SQLException {
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(
                 "INSERT INTO users (username, email) VALUES (?, ?)",
                 Statement.RETURN_GENERATED_KEYS)) {
            
            pstmt.setString(1, user.getUsername());
            pstmt.setString(2, user.getEmail());
            pstmt.executeUpdate();
            
            try (ResultSet generatedKeys = pstmt.getGeneratedKeys()) {
                if (generatedKeys.next()) {
                    user.setId(generatedKeys.getLong(1));
                }
            }
        }
    }
    
    public User findByUsername(String username) throws SQLException {
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(
                 "SELECT * FROM users WHERE username = ?")) {
            
            pstmt.setString(1, username);
            ResultSet rs = pstmt.executeQuery();
            
            if (rs.next()) {
                User user = new User(rs.getString("username"), rs.getString("email"));
                user.setId(rs.getLong("id"));
                return user;
            }
            return null;
        }
    }
}

2. Creating a LiquibaseManager Class

Let's create a utility class to handle Liquibase migrations:

public class LiquibaseManager {
    
    public static void runMigrations(String jdbcUrl, String username, String password) throws Exception {
        try (Connection connection = DriverManager.getConnection(jdbcUrl, username, password)) {
            Database database = DatabaseFactory.getInstance()
                .findCorrectDatabaseImplementation(new JdbcConnection(connection));
            
            Liquibase liquibase = new Liquibase(
                "db/changelog/db.changelog-master.xml", 
                new ClassLoaderResourceAccessor(), 
                database
            );
            
            liquibase.update("");
        }
    }
}

3. Writing TestContainers Tests with Liquibase

Now, let's create a test class that uses both TestContainers and Liquibase:

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

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

@Testcontainers
public class UserRepositoryTest {
    
    // Define the PostgreSQL container
    @Container
    private static final PostgreSQLContainer<?> postgres = new PostgreSQLContainer<>("postgres:14")
            .withDatabaseName("testdb")
            .withUsername("test")
            .withPassword("test");
    
    private UserRepository userRepository;
    
    @BeforeEach
    void setUp() throws Exception {
        // Initialize repository with container connection details
        userRepository = new UserRepository(
            postgres.getJdbcUrl(),
            postgres.getUsername(),
            postgres.getPassword()
        );
        
        // Run Liquibase migrations
        LiquibaseManager.runMigrations(
            postgres.getJdbcUrl(),
            postgres.getUsername(),
            postgres.getPassword()
        );
    }
    
    @Test
    void testSaveAndFindUser() throws SQLException {
        // Create and save a user
        User user = new User("johndoe", "This email address is being protected from spambots. You need JavaScript enabled to view it.");
        userRepository.save(user);
        
        // The user should have an ID after saving
        assertNotNull(user.getId());
        
        // Find the user by username
        User foundUser = userRepository.findByUsername("johndoe");
        
        // Verify the found user
        assertNotNull(foundUser);
        assertEquals("johndoe", foundUser.getUsername());
        assertEquals("This email address is being protected from spambots. You need JavaScript enabled to view it.", foundUser.getEmail());
    }
}

Advanced TestContainers and Liquibase Integration

Custom Changelog Path for Tests

For test-specific database schema, you might want to use a different changelog path:

@BeforeEach
void setUp() throws Exception {
    // Custom test changelog path
    System.setProperty("liquibase.changeLogFile", "db/changelog/test-db.changelog-master.xml");
    
    LiquibaseManager.runMigrations(
        postgres.getJdbcUrl(),
        postgres.getUsername(),
        postgres.getPassword()
    );
}

Integration with Spring's LiquibaseDataSource

If you're using Spring, you can integrate TestContainers with Spring's Liquibase support:

@Bean
@Primary
public DataSource dataSource() {
    PostgreSQLContainer<?> postgres = new PostgreSQLContainer<>("postgres:14")
            .withDatabaseName("testdb")
            .withUsername("test")
            .withPassword("test");
    postgres.start();
    
    return DataSourceBuilder.create()
            .url(postgres.getJdbcUrl())
            .username(postgres.getUsername())
            .password(postgres.getPassword())
            .build();
}

@Bean
public SpringLiquibase liquibase(DataSource dataSource) {
    SpringLiquibase liquibase = new SpringLiquibase();
    liquibase.setDataSource(dataSource);
    liquibase.setChangeLog("classpath:db/changelog/db.changelog-master.xml");
    liquibase.setContexts("test");
    return liquibase;
}

Using Liquibase Contexts for Test Data

You can use Liquibase contexts to manage test-specific data:

<changeSet id="002-test-data" author="developer" context="test">
    <insert tableName="users">
        <column name="username" value="testuser"/>
        <column name="email" value="This email address is being protected from spambots. You need JavaScript enabled to view it."/>
    </insert>
</changeSet>

Then in your test setup:

liquibase.update("test"); // Only run changesets with test context

Using TestContainers with Liquibase for Different Database Types

MySQL Example

@Container
private static final MySQLContainer<?> mysql = new MySQLContainer<>("mysql:8.0")
        .withDatabaseName("testdb")
        .withUsername("test")
        .withPassword("test");

@BeforeEach
void setUp() throws Exception {
    // MySQL-specific JDBC URL
    String jdbcUrl = mysql.getJdbcUrl();
    
    LiquibaseManager.runMigrations(
        jdbcUrl,
        mysql.getUsername(),
        mysql.getPassword()
    );
}

Oracle Example

@Container
private static final OracleContainer oracle = new OracleContainer("oracle/database:19.3.0-se2")
        .withDatabaseName("XEPDB1")
        .withUsername("test")
        .withPassword("test");

@BeforeEach
void setUp() throws Exception {
    LiquibaseManager.runMigrations(
        oracle.getJdbcUrl(),
        oracle.getUsername(),
        oracle.getPassword()
    );
}

Best Practices for JDBC Testing with TestContainers and Liquibase

1. Keep Your Changesets Small and Focused

Small, focused changesets are easier to manage and troubleshoot:

<!-- Good: One focused changeset -->
<changeSet id="001" author="developer">
    <createTable tableName="users">
        <!-- columns... -->
    </createTable>
</changeSet>

<changeSet id="002" author="developer">
    <createIndex tableName="users" indexName="idx_users_username">
        <column name="username"/>
    </createIndex>
</changeSet>

2. Use Rollback Tags for Test Cleanup

<changeSet id="001" author="developer">
    <createTable tableName="users">
        <!-- columns... -->
    </createTable>
    <rollback>
        <dropTable tableName="users"/>
    </rollback>
</changeSet>

3. Organize Changesets by Feature

Keep related changes together:

db/changelog/
├── db.changelog-master.xml
└── changes/
    ├── user-management/
    │   ├── 001-create-users-table.xml
    │   └── 002-add-user-roles.xml
    └── product-catalog/
        ├── 001-create-products-table.xml
        └── 002-add-product-categories.xml

4. Use Preconditions for Safer Migrations

<changeSet id="001" author="developer">
    <preConditions onFail="MARK_RAN">
        <not>
            <tableExists tableName="users"/>
        </not>
    </preConditions>
    <createTable tableName="users">
        <!-- columns... -->
    </createTable>
</changeSet>

5. Create Parallel Test-Specific Changelogs

src/
├── main/
│   └── resources/
│       └── db/changelog/
│           └── db.changelog-master.xml
└── test/
    └── resources/
        └── db/changelog/
            └── test-db.changelog-master.xml

Optimizing Test Performance

1. Reuse TestContainers

Use the @Testcontainers annotation at the class level and the @Container annotation with static containers:

@Testcontainers
public class UserRepositoryTest {
    
    @Container
    private static final PostgreSQLContainer<?> postgres = new PostgreSQLContainer<>("postgres:14");
    
    // Tests...
}

2. Use Shared Container Instances

Create a singleton container for all tests:

public class DatabaseTestContainer {
    private static final PostgreSQLContainer<?> postgres;
    
    static {
        postgres = new PostgreSQLContainer<>("postgres:14")
                .withDatabaseName("testdb")
                .withUsername("test")
                .withPassword("test");
        postgres.start();
        
        // Run Liquibase migrations once for all tests
        try {
            LiquibaseManager.runMigrations(
                postgres.getJdbcUrl(),
                postgres.getUsername(),
                postgres.getPassword()
            );
        } catch (Exception e) {
            throw new RuntimeException("Failed to run migrations", e);
        }
    }
    
    public static PostgreSQLContainer<?> getContainer() {
        return postgres;
    }
}

3. Use Connection Pooling

Even for tests, connection pooling can improve performance:

// Setup connection pool
HikariConfig config = new HikariConfig();
config.setJdbcUrl(postgres.getJdbcUrl());
config.setUsername(postgres.getUsername());
config.setPassword(postgres.getPassword());
config.setMaximumPoolSize(5);

HikariDataSource dataSource = new HikariDataSource(config);

// Use in your repository
userRepository = new UserRepository(dataSource);

Comparing Testing Approaches

Real-World Example: Complex Schema with Relationships

Let's expand our example to include more tables and relationships:

1. Add more tables to our Liquibase changelog:

<!-- 002-create-roles-table.xml -->
<changeSet id="002" author="developer">
    <createTable tableName="roles">
        <column name="id" type="BIGINT" autoIncrement="true">
            <constraints primaryKey="true" nullable="false"/>
        </column>
        <column name="name" type="VARCHAR(50)">
            <constraints unique="true" nullable="false"/>
        </column>
        <column name="description" type="VARCHAR(200)"/>
    </createTable>
</changeSet>

<!-- 003-create-user-roles-table.xml -->
<changeSet id="003" author="developer">
    <createTable tableName="user_roles">
        <column name="user_id" type="BIGINT">
            <constraints nullable="false" foreignKeyName="fk_user_roles_user" references="users(id)"/>
        </column>
        <column name="role_id" type="BIGINT">
            <constraints nullable="false" foreignKeyName="fk_user_roles_role" references="roles(id)"/>
        </column>
    </createTable>
    <addPrimaryKey tableName="user_roles" columnNames="user_id, role_id" constraintName="pk_user_roles"/>
</changeSet>

2. Update our test to use these tables:

@Test
void testUserWithRoles() throws SQLException {
    // First create roles
    try (Connection conn = DriverManager.getConnection(
            postgres.getJdbcUrl(), postgres.getUsername(), postgres.getPassword());
         PreparedStatement pstmt = conn.prepareStatement(
             "INSERT INTO roles (name, description) VALUES (?, ?)",
             Statement.RETURN_GENERATED_KEYS)) {
        
        pstmt.setString(1, "ADMIN");
        pstmt.setString(2, "Administrator role");
        pstmt.executeUpdate();
        
        long roleId;
        try (ResultSet generatedKeys = pstmt.getGeneratedKeys()) {
            generatedKeys.next();
            roleId = generatedKeys.getLong(1);
        }
        
        // Create user
        User user = new User("admin", "This email address is being protected from spambots. You need JavaScript enabled to view it.");
        userRepository.save(user);
        
        // Assign role to user
        try (PreparedStatement rolePstmt = conn.prepareStatement(
                 "INSERT INTO user_roles (user_id, role_id) VALUES (?, ?)")) {
            rolePstmt.setLong(1, user.getId());
            rolePstmt.setLong(2, roleId);
            rolePstmt.executeUpdate();
        }
        
        // Verify user has role
        try (PreparedStatement queryPstmt = conn.prepareStatement(
                 "SELECT r.name FROM roles r " +
                 "JOIN user_roles ur ON r.id = ur.role_id " +
                 "WHERE ur.user_id = ?")) {
            queryPstmt.setLong(1, user.getId());
            ResultSet rs = queryPstmt.executeQuery();
            
            assertTrue(rs.next());
            assertEquals("ADMIN", rs.getString("name"));
        }
    }
}

Conclusion

Using TestContainers and Liquibase together provides a powerful solution for testing your JDBC layer with real databases:

1. TestContainers gives you isolated, ephemeral database instances that match your production environment.
2. Liquibase ensures your database schema is version-controlled and consistently applied across all environments.

This combination addresses the key challenges of database testing:

• Realism: Test against actual production-like databases
• Isolation: Each test runs in a clean environment
• Consistency: The same schema everywhere
• Maintainability: Database changes are tracked and versioned
• Reliability: Tests accurately reflect how your code will behave in production

By following the patterns and practices outlined in this guide, you can create a robust test suite for your JDBC layer that gives you confidence in your database interactions.