Database Testing with Testcontainers and Kotlin Exposed ORM
Tags: testcontainers, kotlin, exposed
Published: 2023-06-29 02:01:45

In this article, we will explore how to use [Testcontainers](https://testcontainers.com/)
and [Exposed](https://github.com/JetBrains/Exposed), a lightweight ORM framework for Kotlin, to create a controlled environment for testing MySQL Database operations.


### Setup a Kotlin project with gradle

First we install the latest [gradle](https://gradle.org/install/) version using SDKMAN
```bash
sdk install gradle 8.1.1
```

Then create a new Gradle project using `gradle init` with Kotlin support
```bash
mkdir kotlin-project
cd kotlin-project
gradle init --type kotlin-application --dsl kotlin
```


### Add dependencies to gradle (build.gradle.kts)
```kotlin
  // Exposed
  implementation("org.jetbrains.exposed", "exposed-core", "0.40.1")
  implementation("org.jetbrains.exposed", "exposed-dao", "0.40.1")
  implementation("org.jetbrains.exposed", "exposed-jdbc", "0.40.1")
  // MySQL JDBC Driver
  implementation("mysql:mysql-connector-java:8.0.19")

  // Connection pool
  implementation("com.zaxxer:HikariCP:5.0.1")

  // Test containers
  testImplementation("org.testcontainers:mysql:1.18.3")
```


### Define the entity using Exposed DAO flavor
Exposed supports DSL (Domain Specific Language) and DAO (Data Access Object). 
The DAO flavor in Exposed is a lightweight ORM for performing CRUD operations on entities [1](https://github.com/JetBrains/Exposed/wiki/Getting-Started).

```kotlin
object Users: LongIdTable() {
    val name = varchar("name", 50)
}

class User(id: EntityID<Long>) : LongEntity(id) {
    companion object : LongEntityClass<User>(Users)

    var name by Users.name
}
```

`LongIdTable` uses an auto-incrementing Long primary key


### Use Testcontainers to start MySQL container, and perform CRUD operations on User entity

We use a single test container to improve test speed by avoiding repetitive container startup and shutdown for every test or test-class

```kotlin
object TestDatabase {

    private val mySQLContainer: MySQLContainer<Nothing> = MySQLContainer<Nothing>("mysql:8.0.26").apply {
        withDatabaseName("test-db")
        withUsername("test-user")
        withPassword("test-password")
        start() // Start the container
    }

    init {
        val config = HikariConfig().apply {
            jdbcUrl = mySQLContainer.jdbcUrl
            username = mySQLContainer.username
            password = mySQLContainer.password
            driverClassName = "com.mysql.cj.jdbc.Driver"
            maximumPoolSize = 10
        }
        val dataSource = HikariDataSource(config)

        // This doesn't connect to the database but provides a descriptor for future use
        // In the main app, we would do this on system start up
        // https://github.com/JetBrains/Exposed/wiki/Database-and-DataSource
        Database.connect(dataSource)

        // Create the schema
        transaction {
            SchemaUtils.create(Users)
        }
    }
}
```

We create a simple test to test the crud operations. 

> **Note**: To run the tests on your local machine, you must have Docker installed and running. 
If you're using a Mac, you can use [Docker Desktop for Mac](https://www.docker.com/products/docker-desktop).


```kotlin
@TestInstance(TestInstance.Lifecycle.PER_CLASS)
class UserRepositoryTest {

    @BeforeAll
    fun setUp() {
        // Use the TestDatabase singleton to initialize the database
        TestDatabase
    }

    @Test
    fun crudUser() {
        transaction {
            // Create
            val newUser = User.new {
                name = "Alice"
            }

            // Read
            val retrievedUser = User.findById(newUser.id)
            assertEquals("Alice", retrievedUser?.name)

            // Update
            retrievedUser?.apply {
                name = "Bob"
            }

            val updatedUser = User.findById(newUser.id)
            assertEquals("Bob", updatedUser?.name)

            // Delete
            updatedUser?.delete()
            val deletedUser = User.findById(newUser.id)
            assertNull(deletedUser)
        }
    }
}
```


### Summary
We've explored how to efficiently set up and automate Kotlin database testing using Testcontainers and the Exposed framework.

- Testcontainers allow us to run isolated database instance in Docker containers
- Exposed, with its lightweight DAO, make it easy to perform CRUD operations.
- Using a single test container throughout our test suit significantly improves performance.

You can find the complete source code for this tutorial on [GitHub](https://github.com/vuongddang/til/tree/main/kotlin-exposed-mysql-testcontainers)

## Further Reading
- [Testcontainers Official Documentation](https://testcontainers.com)
- [Exposed Wiki on GitHub](https://github.com/JetBrains/Exposed/wiki)