In this post, you will learn
Why mistakes happen when integrating with databases
What database integration tests are, and
How to set up database integration tests
This post will assume you have some basic knowledge of Kotlin and Postgres.
Why mistakes happen when integrating with databases
When writing SQL code, usually the way to test that the code works as expected is through manual testing.
One way to test the code is to release it to a development environment with existing data, and then, call the application endpoints to see if the code works as expected. In some cases, you may have to insert some data to test different scenarios.
Another way is to test it locally. For example, in order to test a query works, the following steps can be done:
Run a Postgres instance locally
Run migration scripts to setup your schemas and tables
Run your application
Insert some test data
Call your application endpoints and observe the results are as expected
The issue with manual testing (whether it be in a development environment or locally) is that it is slow and tedious. Often times, if a team does not have an easy way to perform these tests, then only a subset of tests are done, or even worse, testing is not done at all. Because of the slow and tedious nature, reviewers of PRs will also tend to approve without testing either. This means bugs usually sneak through in local and development environments, and are only found in test or even production environments.
Let's look at an example scenario where your team does manual testing.
Example Scenario: Batch inserting users
Scenario Your application currently has a method that inserts a single user to the database. This code is already in production and it works fine. However, there is now some requirements that it needs to support batch inserting users due to slow performance.
Let's have a look at the method:
src/main/kotlin/PostgresRepository.kt
fun insertUser(dataSource: DataSource, user: User) {
dataSource.connection.use { connection ->
connection.prepareStatement(INSERT_USER_SQL).use { statement ->
statement.setString(1, user.id)
statement.setString(2, user.name)
statement.execute()
}
}
}
const val INSERT_USER_SQL = """
INSERT INTO users
(id, name)
VALUES (?, ?)
"""
data class User(val id: String, val name: String)
One of the junior engineers in your team decide to raise the following PR to implement this feature. The junior mentioned that he has done some manual testing and it works as expected.
src/main/kotlin/PostgresRepository.kt
fun insertUsers(dataSource: DataSource, users: List<User>) {
dataSource.connection.use { connection ->
connection.prepareStatement(INSERT_USER_SQL).use { statement ->
for (user in users) {
statement.setString(1, user.id)
statement.setString(2, user.name)
statement.addBatch()
}
statement.execute()
}
}
}
As the most senior person in the team, do you approve the PR? Are you confident that the code works as intended?
Because of the lack of any automated tests, the only way to ensure the code works is to manually test it locally or in a development environment. This seems a little inconvenient, especially since you are busy with another project and do not have time to this manual testing. So what do you do?
Thankfully, integration tests can help here.
What are integration tests?
Integration tests allow us to test code that integrates with external systems. This could be testing code that uses the JDBC library which integrates with a Postgres database. Another example is testing code that uses a Ktor HTTP client which integrates with a HTTP server.
Integration tests are not necessarily end-to-end tests. An end-to-end test will run a full application and test the end-to-end functionality - ie. from receiving a request, validating it, updating a database table and responding to the request. An integration test on the other hand may only test against a part of the application such as database access.
In the previous example, if database integration tests were added as part of your CI/CD process, then a passing integration test will give you confidence that the code works as intended. Let's look at how we can set them up.
Setting up database integration tests
In order to set up database integration tests, you will need
Testcontainers. This allows us to spin up a Postgres container inside Docker.
Flyway. This allows us to run migration scripts to setup schemas and tables in our Postgres container
Using the previous example, let's go through a step-by-step guide on how to can set up integration tests for the batch insert method.
1. Setup Testcontainers
Firstly, add the Testcontainers PostgreSQL dependency to the build.gradle.kts file.
dependencies {
testImplementation("org.testcontainers:postgresql:1.17.1")
}
Then, setup a TestDatabase.kt file in your test source set (ie. src/test/kotlin/util/TestDatabase.kt).
object TestDatabase {
private val container = PostgreSQLContainer(DockerImageName.parse("postgres:13.4"))
fun setup(): DataSource {
if (!container.isRunning) {
container.start()
}
return setupDataSource(container)
}
private fun setupDataSource(postgreSQLContainer: PostgreSQLContainer<*>): DataSource {
return PGSimpleDataSource().apply {
setURL(postgreSQLContainer.jdbcUrl)
user = postgreSQLContainer.username
password = postgreSQLContainer.password
}
}
}
The setup method in this file will spin up a Postgres docker container via Testcontainers and also return a data source to be used in your tests.
2. Setup Flyway
As mentioned above, Flyway allows you to run migration scripts to setup schemas and tables before each test. We can also use Flyway to clean schemas and tables before each test.
In order to install flyway, add the following to your build.gradle.kts file.
dependencies {
testImplementation("org.flywaydb:flyway-core:8.5.10")
}
Update the TestDatabase.kt file to run Flyway migrations.
fun setup(): DataSource {
if (!container.isRunning) {
container.start()
}
val dataSource = setupDataSource(container)
val flyway = setupFlyway(dataSource)
flyway.clean()
flyway.migrate()
}
private fun setupFlyway(dataSource: DataSource) = Flyway(
Flyway.configure()
.locations("/migrations")
.dataSource(dataSource)
)
Notice that we are running flyway.clean() before running migrate. This allows us to clear the database in between tests.
You will also need to add in migration scripts inside the migrations folder in order to be picked up by Flyway. The following migration script can be added as src/test/resources/migrations/V1__create_users_table.sql
CREATE TABLE IF NOT EXISTS users (
id TEXT PRIMARY KEY,
name TEXT
);
3. Write the test
Now that we have the TestDatabase.kt set up, we can write the test. Let's start off with a simple test to insert a single user.
class PostgresRepositoryTest {
private lateinit var dataSource: DataSource
private lateinit var repository: PostgresRepository
@BeforeEach
fun setUp() {
dataSource = TestDatabase.setup()
repository = PostgresRepository(dataSource)
}
@Test
fun `when inserting user, then no exception is thrown`() {
val user = User("1", "edrick")
repository.insertUsers(listOf(user))
}
}
Currently, there are no assertions and will only fail if the insertUsers method throws an exception. Let's run the test and see what results we get.
Awesome. It looks like no exception is thrown but does not test whether or not the user is actually stored. We can make this test better by asserting that the user is stored in the database. Let's update the test to below and run it.
@Test
fun `when inserting user, then user is stored in database`() {
val user = User("1", "edrick")
repository.insertUsers(listOf(user))
thenAssertUserInDatabase(user)
}
private val SELECT_USER_SQL = """
SELECT id, name
FROM users
WHERE id = ?
""".trimIndent()
private fun thenAssertUserInDatabase(user: User) {
dataSource.connection.use { connection ->
connection.prepareStatement(SELECT_USER_SQL).use { statement ->
statement.setString(1, user.id)
statement.executeQuery().use { resultSet ->
assertTrue(resultSet.next())
assertEquals(user.name, resultSet.getString("name"))
}
}
}
}
Looks like the test is still passing. Let's now test that it supports adding multiple users. We can add a new test to the file and run this new test.
@Test
fun `when inserting users, then users are stored in database`() {
val user1 = User("1", "edrick")
val user2 = User("2", "josh")
repository.insertUsers(listOf(user1, user2))
thenAssertUserInDatabase(user1)
thenAssertUserInDatabase(user2)
}
Running this yields the following results.
Unfortunately, the new test didn't pass. Looking at the logs revealed that it failed at the following line thenAssertUserInDatabase(user1). After carefully looking at the code again and the JDBC docs, you notice that the code is calling execute instead of executeBatch. You realise that execute only inserts the last item in the batch. So you update the code and run the tests.
src/main/kotlin/PostgresRepository.kt
fun insertUsers(dataSource: DataSource, users: List<User>) {
dataSource.connection.use { connection ->
connection.prepareStatement(INSERT_USER_SQL).use { statement ->
for (user in users) {
statement.setString(1, user.id)
statement.setString(2, user.name)
statement.addBatch()
}
statement.executeBatch()
}
}
}
The test is passing! Using automated database integration has allowed us to catch this bug early on.
Wrap Up
In this post, we learnt that
mistakes can happen when writing integration code
manual integration testing is often skipped or not done completely due to its slow and tedious nature
automated integration tests can help us catch bugs early