Database integration testing with 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. Let's not touch on unit testing, but database integration testing. What 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 quick 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, they can consist of several modules and additional functionality. Why are the tests for the dao (Data Access Object) layer integration?Because in order 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 working out the methods of the repository class (after all, we know what the end result should be in this or that case). So, let's begin. Topics on connecting the database have long been traveled 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 we have an application on Spring Boot, for the Spring JDBC dao layer (for greater clarity), we have MySQL as the main database, and we will replace it with MariaDB (they are as compatible as possible, and, accordingly, MySQL scripts never there will be no conflicts with the MariaDB dialect, as H2 will).

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 that represents 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 conciseness:

@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 make some digression and see what's going on with the dependencies (only those that are 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 library it is) 22 - starter for tests (where the JUnit we need is sewn in) 28 - starter for working with springJdbc Consider Spring container with the beans required 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 database being created 17 - pulling the configuration 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 communicating with the database being raised. The idea is that we will have a main class for dao tests, from which all dao test classes will be inherited, whose tasks include:

1. launching some scripts used in the main database (scripts for creating tables, changing columns, etc.);
2. running 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 the test configuration 11 - we pass the names of the tables we need as an argument in this method, and he, as a responsible workaholic, 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 to test 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 for the sake of which we all gathered today.

Dao 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 - inherit from the base class for our tests 4 - our tested repository 7 - a method that will run before each test 8 - use the parent class method to load the necessary table tables 11 - initialize our dao 15 - a method that will run after each test, cleaning our base 19 - implementation of our RowMapper, analogue from the dao class We use @Before and @After, which are used before and after one test method, but we could take some lib that allows you to use annotations tied to the start of executions tests of this class and the end. For example, this one, which would significantly speed up the tests, since it would be necessary to create tables and completely delete them every time, but once per class. But we don't do that. Why, you ask? But what if one of the methods changes the structure of the table? For example, delete one column. In this case, the rest of the methods may either fail, or should respond properly (for example, create a column back). We have to admit that this gives us unnecessary connectivity (dependence) of tests from each other, which we do not need. 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 in the table with test data 5 - get the id for the entity we need 6 - use the method being checked 8...12 - check 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 in the table with test data 5 - get the id of the updated entity 7 - build the updated entity 14 - use the method being checked 15 - get the updated entity for verification 20...28 - check the received data with the expected ones Testing the update method is similar to create. At least for me. You can pervert with reconciliations as you please: there are never many checks. I would also like to note that the tests do not guarantee full performance or the absence of bugs. Tests only ensure that the real result of the program (its fragment) is in line with the expected one. In this case, only those parts for which tests were written are checked.

We start the class with tests ...

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

useful links

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

For those of you who have read it, thank you for reading...

*epic music from Star Wars*