No records are created on Docker Container (Testcontainers + Database Rider + MyBatis)

Question

Problem

I am currently attempting to build a Restful API by using SpringBoot, MyBatis and MySQL.

For testing the MyBatis's Mapper, I use Testcontainers and Database Rider.

I wrote the unit test for checking the operation of SQL query for INSERT like this:

// entity model class
@Getter
@RequiredArgsConstructor
public class ArticleEntity {

    @NonNull
    private final String id;

    @NonNull
    private final String title;

    @NonNull
    private final String body;

    @NonNull
    private final LocalDateTime createdAt;

    @NonNull
    private final LocalDateTime updatedAt;
}

// test target class
@Mapper
public interface ArticleEntityMapper {
    
    int insert(ArticleEntity articleEntity);
}

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="package.of.mapper.ArticleEntityMapper">
    <resultMap id="ArticleEntity" type="package.of.entity.ArticleEntity">
        <constructor>
            <idArg column="id" name="id"/>
            <arg column="title" name="title"/>
            <arg column="body" name="body"/>
            <arg column="created_at" name="createdAt" typeHandler="org.apache.ibatis.type.LocalDateTimeTypeHandler"/>
            <arg column="updated_at" name="updatedAt" typeHandler="org.apache.ibatis.type.LocalDateTimeTypeHandler"/>
        </constructor>
    </resultMap>
    <insert id="insert">
        INSERT INTO
            articles
        (
            id,
            title,
            body,
            created_at,
            updated_at
        )
        VALUES (
            #{id},
            #{title},
            #{body},
            #{createdAt},
            #{updatedAt}
        )
    </insert>
</mapper>

// unit test for target
@DBRider
@MybatisTest
@Testcontainers
@ActiveProfiles(profiles = "test")
public class ArticleEntityMapperTest {

    @Autowired
    private ArticleEntityMapper target;

    @Nested
    class InsertTest {

        @Test
        @DataSet("dataset/empty.yml")
        @ExpectedDataSet("dataset/inserted.yml")
        void insert_returnedNumberInserted() {
            // given
            String id = "************************************";
            String title = "Title";
            String body = "Body";
            LocalDateTime createdAt = LocalDateTime.of(1970, 1, 1, 0, 0, 0);
            LocalDateTime updatedAt = LocalDateTime.of(1970, 1, 1, 0, 0, 0);
            ArticleEntity articleEntity = new ArticleEntity(id, title, body, createdAt, updatedAt);
            // when
            int actual = target.insert(articleEntity);
            // then
            assertEquals(1, actual);
        }
    }

# dataset/empty.yml
articles:

# dataset/inserted.yml
articles:
  - id: ************************************
    title: Title
    body: Body
    created_at: 1970-01-01 00:00:00
    updated_at: 1970-01-01 00:00:00

# dbunit.yml
properties:
  caseSensitiveTableNames: true

# application-test.yml
spring:
  datasource:
    url: jdbc:tc:mysql:8.0:///?TC_INITSCRIPT=file:src/main/resources/schema.sql
    username:
    password:
  test:
    database:
      replace: none

When I execute this, the assertion error has occurred (articles is table name):

row count (table=articles) expected:<[1]> but was:<[0]>
Expected :1
Actual   :0

This result means MyBatis thinks the INSERT was successful, but the record is not inserted in the actual DB at Docker Container. (because assertion for actual is passed)

Please tell me how can I insert a record to DB at Docker Container built by Testcontainers.

Environment

Software

• MacBook Pro
  • OS: Ventura 13.4.1
• Docker: 24.0.2-rd
• Rancher Desktop: 1.9.1
• Intellij IDEA 2021.2.4 (Community Edition)
• Gradle: 8.1.1
• Java: openjdk 17.0.7 2023-04-18 LTS

Library

dependencies {
    implementation 'org.springframework.boot:spring-boot-starter:3.1.0'
    implementation 'org.springframework.boot:spring-boot-starter-web:3.1.0'
    implementation 'org.springframework.boot:spring-boot-starter-validation:3.1.0'
    implementation 'org.mybatis.spring.boot:mybatis-spring-boot-starter:3.0.2'
    implementation 'mysql:mysql-connector-java:8.0.33'
    implementation "org.springdoc:springdoc-openapi-starter-webmvc-ui:2.1.0"
    compileOnly 'org.projectlombok:lombok:1.18.26'
    annotationProcessor 'org.projectlombok:lombok:1.18.26'

    implementation platform('org.testcontainers:testcontainers-bom:1.18.3')
    testImplementation('org.testcontainers:testcontainers')
    testImplementation('org.testcontainers:junit-jupiter')
    testImplementation('org.testcontainers:mysql')
    testImplementation 'org.springframework.boot:spring-boot-starter-test:3.1.0'
    testImplementation('org.mybatis.spring.boot:mybatis-spring-boot-starter-test:3.0.2')
    testImplementation('com.github.database-rider:rider-junit5:1.38.1')
    testImplementation('commons-collections:commons-collections:3.2.2')
    testCompileOnly 'org.projectlombok:lombok:1.18.26'
    testAnnotationProcessor 'org.projectlombok:lombok:1.18.26'
}

Try

1. Execute SELECT test

I tried to execute test for SELECT query like this:

        @Test
        @DataSet("dataset/selected.yml")
        void entityExists_returnedResult() {
            // given
            String id = "Id";
            // when
            ArticleEntity actual = target.selectById(id);
            // then
            assertEquals("Id", actual.getId());
            assertEquals("Title", actual.getTitle());
            assertEquals("Body", actual.getBody());
            assertEquals(LocalDateTime.of(1970, 1, 1, 9, 0, 0), actual.getCreatedAt());
            assertEquals(LocalDateTime.of(1970, 1, 1, 9, 0, 0), actual.getUpdatedAt());
        }

This test can be passed and I confirm the record Database rider inserted exists:

➜  ~ docker ps | grep mysql
f8a4da7f54b6   mysql:8.0                    "docker-entrypoint.s…"   32 seconds ago   Up 29 seconds   33060/tcp, 0.0.0.0:32771->3306/tcp, :::32771->3306/tcp   stupefied_wiles
➜  ~ docker exec -it f8a4da7f54b6 sh
sh-4.4# mysql -u test -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 8.0.33 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from articles;
+--------------------------------------+-------+------+---------------------+---------------------+
| id                                   | title | body | created_at          | updated_at          |
+--------------------------------------+-------+------+---------------------+---------------------+
| ************************************ | Title | Body | 1970-01-01 09:00:00 | 1970-01-01 09:00:00 |
+--------------------------------------+-------+------+---------------------+---------------------+
1 row in set (0.00 sec)

This means the settings of DataSource may be correct.

2. Check logs of MySQL at Docker Container

When I debug the test for INSERT query, I login Docker Container and check logs of MySQL like this:

➜  ~ docker ps | grep mysql
4db2a3c6f880   mysql:8.0                    "docker-entrypoint.s…"   26 seconds ago   Up 24 seconds   33060/tcp, 0.0.0.0:32773->3306/tcp, :::32773->3306/tcp   brave_stonebraker
➜  ~ docker exec -it 4db2a3c6f880 sh
sh-4.4# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 8.0.33 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SET GLOBAL general_log = 'ON';
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
sh-4.4# cat /var/lib/mysql/4db2a3c6f880.log
/usr/sbin/mysqld, Version: 8.0.33 (MySQL Community Server - GPL). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
2023-07-11T16:52:53.160951Z    20 Quit
2023-07-11T16:53:20.510145Z     9 Query SELECT @@session.transaction_read_only
2023-07-11T16:53:20.529118Z     9 Query INSERT INTO
            articles
        (
            id,
            title,
            body,
            created_at,
            updated_at
        )
        VALUES (
            '************************************',
            'Title',
            'Body',
            '1970-01-01 00:00:00',
            '1970-01-01 00:00:00'
        )

This result means MySQL received the query but not reflected.

3. Execute UPDATE test

I implemented the update method like this:

    <update id="update">
        UPDATE
            articles
        SET
            title = #{title},
            body = #{body},
            updated_at = #{updatedAt}
        WHERE
            id = #{id}
    </update>

Although I wrote and executed test for this, it failed because UPDATE query is not reflected to actual DB like INSERT query.

4. Use @SpringBootTest (Updated 2023/07/13)

Instead of using @MyBtisTest, I tried to use @SpringBootTest.

Then the record is created in actual DB!

But I don't want to use @SpringBootTest for testing mappers because test execution takes too long time.

So I will continue to look for ways to use @MyBatisTest.