Interacting with a database in Kotlin with Jetbrains Exposed

May 08, 2024

In this article, we’ll demonstrate how to interact with a database in Kotlin using JetBrains Exposed. JetBrains Exposed is a library (maintained by JetBrains the company behind Intellij and other IDEs) designed to simplify database interaction. It provides a DSL (Domain Specific Language) wrapper around SQL that allows you to write Kotlin code such as UserTable.selectAll().where { UserTable.id eq id } which is converted into regular SQL.

Check out the example on GitHub: minibuildsio/kotlin-ktor-database-example.

Required Dependencies

We need to add dependencies for:

Jetbrain Exposed: the SQL library itself
Postgres driver: the driver required to interact with Postgres

implementation("org.jetbrains.exposed:exposed-core:$exposedVersion")
implementation("org.jetbrains.exposed:exposed-jdbc:$exposedVersion")
implementation("org.jetbrains.exposed:exposed-java-time:$exposedVersion")
implementation("org.postgresql:postgresql:$postgresDriverVersion")

Connecting to a database

To connect to a database call Database.connect() passing in the database server details. In the example below the database details are read from the application configuration but could be read from environment variables or similar if needed.

Database.connect(
    driver = "org.postgresql.Driver",
    url = environment.config.property("database.url").getString(),
    user = environment.config.property("database.user").getString(),
    password = environment.config.property("database.password").getString()
)

In a production application where performance is important, you’ll want to use a connection pool. Database.connect() can accept any data source for example a Hikari data source that uses connection pooling.

val config = HikariConfig().also {
    it.driverClassName = "org.postgresql.Driver"
    it.jdbcUrl = environment.config.property("database.url").getString()
    it.username = environment.config.property("database.user").getString()
    it.password = environment.config.property("database.password").getString()    
    it.maximumPoolSize = 5
}

Database.connect(datasource = HikariDataSource(config))

Defining tables

In Exposed we define tables by extending Table and defining the columns and constraints. Classes that extend Table, such as the UserTable below, have methods like selectAll and insert to interact with the database.

object UserTable : Table() {
    val id = integer("id").autoIncrement()
    val name = varchar("name", length = 50)
    val dateOfBirth = date("date_of_birth")

    override val primaryKey = PrimaryKey(id)
}

The table name is derived from the class name “user” in this case. The table name can be passed into the constructor if needed e.g. Table("table_name").

The UserTable class requires an existing table defined as below, which is created by a separate script/migration.

create table "user" (
  id serial primary key,
  name varchar(50),
  date_of_birth date
);

Exposed DSL over SQL

Exposed provides a high-level DSL that allows you to write Kotlin code that For example, the below code:

UserTable.selectAll().where { UserTable.id eq id }

Generates the following SQL:

SELECT "user".id, "user"."name", "user".date_of_birth FROM "user" WHERE "user".id = ?

The queries can be more complex than a simple single clause where statement. The example below returns users whose name begins with G and who were born after 01/01/2000 ordered by data of birth.

UserTable
    .selectAll()
    .where {
        (UserTable.name like "G%") and
                (UserTable.dateOfBirth greaterEq LocalDate.of(2000, 1, 1))
    }
    .orderBy(UserTable.dateOfBirth)

This generates the following SQL:

SELECT "user".id, "user"."name", "user".date_of_birth
FROM "user"
WHERE ("user"."name" LIKE 'G%') AND ("user".date_of_birth >= '2000-01-01')
ORDER BY "user".date_of_birth ASC

Putting it all together in a repository

The Repository pattern is a design pattern used to separate the logic that retrieves and persists data from the rest of the application. Below is the class UserRepository which wraps around UserTable to simplify interaction with the database.

All UserTable methods need an active transaction below we’re using transaction {} to do this. The selectAll(), insert(), etc methods return ResultRow which is like a map of the columns to their values so they can be converted into an object as demonstrated in the toUser() extension function.

data class User(
    val id: Int,
    val name: String,
    val dateOfBirth: LocalDate
)

class UserRepository {
    fun getAllUsers(): List<User> =
        transaction {
            UserTable.selectAll()
                .map { it.toUser() }
        }

    fun getUser(id: Int): User? =
        transaction {
            UserTable
                .select()
                .where { UserTable.id eq id }
                .map { it.toUser() }
                .singleOrNull()
        }

    fun addUser(name: String, dateOfBirth: LocalDate): User? =
        transaction {
            val insertStatement = UserTable.insert {
                it[UserTable.name] = name
                it[UserTable.dateOfBirth] = dateOfBirth
            }
            insertStatement.resultedValues?.first()?.toUser()
        }

    companion object {
        private fun ResultRow.toUser() = User(
            this[UserTable.id],
            this[UserTable.name],
            this[UserTable.dateOfBirth]
        )
    }
}