Integration testing of a database using MariaDB to replace MySql

Today I would like to talk about testing, because the more the code is covered with tests, the better and more reliable it is considered. Let's talk not about unit testing, but about integration testing of databases. What exactly is the difference between unit tests and integration tests? Modular (unit) is testing a program at the level of individual modules, methods or classes, that is, tests are fast and easy, affecting the most divisible parts of the functionality. They are also referred to as “one test per method”. Integration ones are slower and heavier, and can consist of several modules and additional functionality. Why are tests for the dao (Data Access Object) layer integration tests? Because to test methods with queries to the database, we need to raise a separate database in RAM, replacing the main one. The idea is that we create the tables we need, fill them with test data and check the correctness of the repository class methods (after all, we know what the final result should be in a given case). So, let's begin. Topics on connecting a database have long been covered far and wide, and therefore today I would not like to dwell on this, and we will consider only the parts of the program that interest us. By default, we will start from the fact that our application is based on Spring Boot, for the Spring JDBC dao layer (for greater clarity), our main database is MySQL, and we will replace it using MariaDB (they are maximally compatible, and accordingly MySQL scripts will never there will be no conflicts with the MariaDB dialect, as there will be with H2). We will also conditionally assume that our program uses Liquibase to manage and apply changes to the database schema, and accordingly, all applied scripts are stored in our application.

Project structure

Only the affected parts are shown: And yes, today we will create robots)) Script for the table, the methods for which we will test today (create_table_robots.sql):

CREATE TABLE `robots`
(
   `id`   BIGINT(20) NOT NULL AUTO_INCREMENT,
   `name` CHAR(255) CHARACTER SET utf8 NOT NULL,
   `cpu`  CHAR(255) CHARACTER SET utf8 NOT NULL,
   `producer`  CHAR(255) CHARACTER SET utf8 NOT NULL,
   PRIMARY KEY (`id`)
) ENGINE = InnoDB
 DEFAULT CHARSET = utf8;

The entity representing this table:

@Builder
@Data
public class Robot {

   private Long id;

   private String name;

   private String cpu;

   private String producer;
}

Interface for the tested repository:

public interface RobotDAO {

   Robot findById(Long id);

   Robot create(Robot robot);

   List<Robot> findAll();

   Robot update(Robot robot);

   void delete(Long id);
}

Actually, here are standard CRUD operations, without exotics, so we will consider the implementation of not all methods (well, this will not surprise anyone), but some - for greater brevity:

@Repository
@AllArgsConstructor
public class RobotDAOImpl implements RobotDAO {

   private static final String FIND_BY_ID = "SELECT id, name, cpu, producer FROM robots WHERE id = ?";

   private static final String UPDATE_BY_ID = "UPDATE robots SET name = ?, cpu = ?, producer = ?  WHERE id = ?";

   @Autowired
   private final JdbcTemplate jdbcTemplate;

   @Override
   public Robot findById(Long id) {
       return jdbcTemplate.queryForObject(FIND_BY_ID, robotMapper(), id);
   }

   @Override
   public Robot update(Robot robot) {
       jdbcTemplate.update(UPDATE_BY_ID,
               robot.getName(),
               robot.getCpu(),
               robot.getProducer(),
               robot.getId());

       return robot;
   }

   private RowMapper<Robot> robotMapper() {
       return (rs, rowNum) ->
               Robot.builder()
                       .id(rs.getLong("id"))
                       .name(rs.getString("name"))
                       .cpu(rs.getString("cpu"))
                       .producer(rs.getString("producer"))
                       .build();
   }

Let's take a little digression and see what's going on with our dependencies (only those used for the demonstrated part of the application are presented):

<dependencies>
   <dependency>
       <groupId>org.mariadb.jdbc</groupId>
       <artifactId>mariadb-java-client</artifactId>
       <version>2.5.2</version>
       <scope>test</scope>
   </dependency>
   <dependency>
       <groupId>org.craftercms.mariaDB4j</groupId>
       <artifactId>mariaDB4j-springboot</artifactId>
       <version>2.4.2.3</version>
       <scope>test</scope>
   </dependency>
   <dependency>
       <groupId>org.projectlombok</groupId>
       <artifactId>lombok</artifactId>
       <version>1.18.10</version>
       <scope>provided</scope>
   </dependency>
   <dependency>
       <groupId>org.springframework.boot</groupId>
       <artifactId>spring-boot-starter-test</artifactId>
       <version>2.2.1.RELEASE</version>
       <scope>test</scope>
   </dependency>
   <dependency>
       <groupId>org.springframework.boot</groupId>
       <artifactId>spring-boot-starter-jdbc</artifactId>
       <version>2.2.1.RELEASE</version>
   </dependency>
</dependencies>

4 - dependency for the MariaDb database itself 10 - dependency for connecting with SpringBoot 16 - Lombok (well, I think everyone knows what kind of lib this is) 22 - starter for tests (where the JUnit we need is embedded) 28 - starter for working with springJdbc Let's take a look Spring container with the beans needed for our tests (in particular, the MariaDB creation bean):

@Configuration
public class TestConfigDB {

   @Bean
   public MariaDB4jSpringService mariaDB4jSpringService() {
       return new MariaDB4jSpringService();
   }

   @Bean
   public DataSource dataSource(MariaDB4jSpringService mariaDB4jSpringService) {
       try {
           mariaDB4jSpringService.getDB().createDB("testDB");
       } catch (ManagedProcessException e) {
         e.printStackTrace();
       }

       DBConfigurationBuilder config = mariaDB4jSpringService.getConfiguration();

       return DataSourceBuilder
               .create()
               .username("root")
               .password("root")
               .url(config.getURL("testDB"))
               .driverClassName("org.mariadb.jdbc.Driver")
               .build();
   }

   @Bean
   public JdbcTemplate jdbcTemplate(DataSource dataSource) {
       return new JdbcTemplate(dataSource);
   }
}

5 - the main component for raising MariaDB (for applications based on the Spring Framework) 10 - defining a database bean 12 - setting the name of the created Database 17 - pulling out the configurations for our case 19 - building a database using the Builder pattern ( a good overview of the pattern ) And finally, what all the fuss is about is the JdbcTemplate bean for communication with the database being raised. The idea is that we will have a main class for Tao tests, from which all Tao test classes will inherit, whose tasks include:

1. launching some scripts used in the main database (scripts for creating tables, changing columns, and others);
2. launching test scripts that fill tables with test data;
3. deleting tables.

@SpringBootTest(classes = TestConfigDB.class)
public abstract class DataBaseIT {

   @Autowired
   private JdbcTemplate jdbcTemplate;

   public JdbcTemplate getJdbcTemplate() {
       return jdbcTemplate;
   }

   public void fillDataBase(String[] initList) {
       for (String x : initList) {
           try {
               jdbcTemplate.update(IOUtils.resourceToString("/db.migrations/" + x, StandardCharsets.UTF_8));
           } catch (IOException e) {
               e.printStackTrace();
           }
       }
   }

   public void cleanDataBase() {
       getJdbcTemplate().update("DROP database testDB");
       getJdbcTemplate().update("CREATE database testDB");
       getJdbcTemplate().update("USE testDB");
   }

   public void fillTables(String[] fillList) {
       for (String x : fillList) {
           try {
               Stream.of(
                       IOUtils.resourceToString("/fill_scripts/" + x, StandardCharsets.UTF_8))
                       .forEach(jdbcTemplate::update);
           } catch (IOException e) {
               e.printStackTrace();
           }
       }
   }
}

1 - using the @SpringBootTest annotation we set a test configuration 11 - as an argument in this method we pass the names of the tables we need, and he, as a responsible hard worker, will load them for us (which gives us the opportunity to reuse this method as much as our heart desires) 21 - we use this method for cleaning, namely, deleting all tables (and their data) from the database 27 - the argument in this method is an array of names of scripts with test data that will be loaded for testing a specific method. Our script with test data:

INSERT INTO robots(name, cpu, producer)
VALUES ('Rex', 'Intel Core i5-9400F', 'Vietnam'),
      ('Molly', 'AMD Ryzen 7 2700X', 'China'),
      ('Ross', 'Intel Core i7-9700K', 'Malaysia')

And now what we have all gathered for today.

Tao testing class

@RunWith(SpringRunner.class)
public class RobotDataBaseIT extends DataBaseIT {

   private static RobotDAO countryDAO;

   @Before
   public void fillData() {
       fillDataBase(new String[]{
               "create_table_robots.sql"
       });
       countryDAO = new RobotDAOImpl(getJdbcTemplate());
   }

   @After
   public void clean() {
       cleanDataBase();
   }

   private RowMapper<Robot> robotMapper() {
       return (rs, rowNum) ->
               Robot.builder()
                       .id(rs.getLong("id"))
                       .name(rs.getString("name"))
                       .cpu(rs.getString("cpu"))
                       .producer(rs.getString("producer"))
                       .build();
   }

2 - we inherit from the main class for our tests 4 - our tested repository 7 - a method that will be launched before each test 8 - we use the parent class method to load the necessary tables 11 - we initialize our dao 15 - a method that will be launched after each test, cleaning our database 19 - implementation of our RowMapper, analogous to the Tao class We use @Before and @After, which are used before and after one test method, but we could take some lib that allows us to use annotations tied to the beginning of executions tests of this class and the end. For example, this one , which would significantly speed up the tests, since tables would have to be created and completely deleted every time, and once per class. But we don't do that. Why, you ask? What if one of the methods changes the structure of the table? For example, delete one column. In this case, the remaining methods may either fail or must respond as expected (for example, create a back column). We have to admit that this gives us unnecessary connection (dependence) of tests on each other, which is of no use to us. But I digress, let's continue...

Testing the findById method

@Test
public void findByIdTest() {
   fillTables(new String[]{"fill_table_robots.sql"});

   Long id = getJdbcTemplate().queryForObject("SELECT id FROM robots WHERE name = 'Molly'", Long.class);
   Robot robot = countryDAO.findById(id);

   assertThat(robot).isNotNull();
   assertThat(robot.getId()).isEqualTo(id);
   assertThat(robot.getName()).isEqualTo("Molly");
   assertThat(robot.getCpu()).isEqualTo("AMD Ryzen 7 2700X");
   assertThat(robot.getProducer()).isEqualTo("China");
}

3 - fill the table with test data 5 - get the id for the entity we need 6 - use the method being tested 8...12 - compare the received data with the expected ones

Update method test

@Test
public void updateTest() {
   fillTables(new String[]{"fill_table_robots.sql"});

   Long robotId = getJdbcTemplate().queryForObject("SELECT id FROM robots WHERE name = 'Rex'", Long.class);

   Robot updateRobot = Robot.builder()
           .id(robotId)
           .name("Aslan")
           .cpu("Intel Core i5-3470")
           .producer("Narnia")
           .build();

   Robot responseRobot = countryDAO.update(updateRobot);
   Robot updatedRobot = getJdbcTemplate().queryForObject(
           "SELECT id, name, cpu, producer FROM robots WHERE id = ?",
           robotMapper(),
           robotId);

   assertThat(updatedRobot).isNotNull();
   assertThat(updateRobot.getName()).isEqualTo(responseRobot.getName());
   assertThat(updateRobot.getName()).isEqualTo(updatedRobot.getName());
   assertThat(updateRobot.getCpu()).isEqualTo(responseRobot.getCpu());
   assertThat(updateRobot.getCpu()).isEqualTo(updatedRobot.getCpu());
   assertThat(updateRobot.getProducer()).isEqualTo(responseRobot.getProducer());
   assertThat(updateRobot.getProducer()).isEqualTo(updatedRobot.getProducer());
   assertThat(responseRobot.getId()).isEqualTo(updatedRobot.getId());
   assertThat(updateRobot.getId()).isEqualTo(updatedRobot.getId());
}

3 - fill the table with test data 5 - get the id of the entity being updated 7 - build the updated entity 14 - use the method being tested 15 - get the updated entity for verification 20...28 - compare the received data with the expected ones Testing the update method is similar to create. At least for me. You can twist the reconciliations as much as you like: there can never be too many checks. I would also like to note that tests do not guarantee full functionality or the absence of bugs. Tests only ensure that the actual result of the program (its fragment) corresponds to the expected one. In this case, only those parts for which tests were written are checked.

Let's start a class with tests...

Victory)) Let's go make tea and get cookies: we deserve it))

useful links

• A good article on the unexamined part of this technology stack.
• An interesting series of articles about Maven, Spring, MySQL, Hibernate.
• Let's refresh our memory a little on unit tests.
• An interesting example of integration testing, but with PostgreSQL substitution.
• Many people, and not only in tests, use MariaDB instead of MySql.

For those who have finished reading, thank you for your attention and...

*epic Star Wars music*