The purpose of the article: analysis of the structure of a web service on Kotlin, consideration of ways to integrate with a database using the example of a CRUD service.

Currently, services in the form of a web application are especially common. And it's hard to imagine some kind of web application that does not store data in any way. Databases are the most common way to store data today.

From the service side, any database looks like an external integration. And if the service is covered by tests, it means that this integration needs to be tested. There are many ways to test this integration; below; we will analyze the most popular ones.

Description of Infrastructure

For example, a basic CRUD Kotlin service based on Spring Boot is used.

A CRUD web service is a service that provides functionality for creating (C), reading (R), updating (U), deleting (D) entities from a database via HTTP requests.

For example, we will consider a service without delete and update functionality - only creation and reading remain. Since, in principle, we will cover everything that is needed with these two methods.

H2 is used as a database, but any relational one can be used. Since we are only looking at integration testing, the general intent of this article will be relevant for any other popular relational database.

Description of the Web Service Functionality

The area of responsibility of the service from the example is integration with pokeApi to get information about the weight of a pokemon by its name. As well as saving this information to a database and providing the opportunity to get all the saved records.

The following dependencies are used for the main functionality of the service:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jdbc</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
    <groupId>com.fasterxml.jackson.module</groupId>
    <artifactId>jackson-module-kotlin</artifactId>
</dependency>
<dependency>
    <groupId>org.jetbrains.kotlin</groupId>
    <artifactId>kotlin-reflect</artifactId>
</dependency>
<dependency>
    <groupId>org.jetbrains.kotlin</groupId>
    <artifactId>kotlin-stdlib-jdk8</artifactId>
</dependency>
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <scope>runtime</scope>
</dependency>

Database Structure

The table for storing information about the weight of a Pokémon in the application's database looks like this:

create table pokemon (
    id     integer primary key auto_increment,
    name   varchar(25),
    weight integer
);

id - identifier field
name - a field for storing information about the name of the pokemon
weight - a field with information about the weight of the Pokémon.

Description of Functional Endpoints

The web service has two endpoints with functionality that needs to be covered by tests.

POST /pokemon - saving the model to the database.

The model has 2 required fields - name (String) and weight (Integer).

Call example:

###
POST http://localhost:8080/pokemon
Content-Type: application/json

{
  "name": "bulbasaur",
  "weight": 69
}

GET /pokemon is an endpoint that is responsible for providing all records from the database.

In response, the method returns an array of models with 3 fields - id (Long), name (String), weight (Integer).

Call example:

###
GET http://localhost:8080/pokemon

response:

[
  {
    "id": 1,
    "name": "bulbasaur",
    "weight": 69
  },
  {
    "id": 2,
    "name": "ditto",
    "weight": 40
  }
]

Controller code:

@RestController
@RequestMapping("/pokemon")
class PokemonController(private val pokemonDao: PokemonDao) {

    @PostMapping
    fun savePokemon(@RequestBody pokemon: Pokemon) {
        pokemonDao.save(pokemon)
    }

    @GetMapping
    fun getAll(): List<Pokemon> = pokemonDao.getAll()

    @ExceptionHandler
    fun handleException(exception: Exception): ResponseEntity<*> {
        return ResponseEntity(exception.message, HttpStatus.BAD_REQUEST)
    }
}

Description of the DAO Layer

The DAO (Data Access Object) layer is solely responsible for integration with the repository. The jdbcTemplate is used to describe database integrations. JdbcTemplate is a Spring library that allows you to write queries in native SQL.

For ease of mapping entities, a regular objectMapper is used. In a heavy load, such a mapping can be changed to a more optimal one.

DAO layer looks like this:

@Service
class PokemonDao(private val jdbcTemplate: JdbcTemplate, private val objectMapper: ObjectMapper) {

    fun save(pokemon: Pokemon) {
        jdbcTemplate.update(SAVE_POKEMON, pokemon.name, pokemon.weight)
    }

    fun getAll(): List<Pokemon> =
        jdbcTemplate.queryForList(SELECT_ALL_POKEMONS)
            .map { objectMapper.readValue(objectMapper.writeValueAsString(it), Pokemon::class.java) }
}

@Language("Sql")
private const val SAVE_POKEMON = "insert into pokemon values(default, ?, ?)"

@Language("Sql")
private const val SELECT_ALL_POKEMONS = "select * from pokemon"

Database Configuration

Database parameters can be configured outside web service and in application.properties. There is an example:

spring.datasource.url=jdbc:h2:mem:testdb;DATABASE_TO_LOWER=TRUE;CASE_INSENSITIVE_IDENTIFIERS=TRUE;
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=password
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect

DDL can be contained in schema.sql 

In total, we have considered the basic way to implement the integration of a web service with a database using the example of H2 + Spring Boot.

How do you test database integrations? I will be glad to hear your thoughts about your ways to integrate web-services with databases in the comments.

Sources can be found in my GitHub repository: pokemon-app