A big part of the fun of starting a new project is making a design and choosing an appropriate technology stack. If you are a Java developer and need to access a SQL database, a common choice is to use JPA with an ORM framework such as Hibernate. This adds a lot of complexity to your project for multiple reasons. In my experience, writing performing queries requires careful analysis. Writing custom queries is possible but more complex. For starters, JPQL/HQL queries are parsed at runtime and the criteria API is far from user friendly. Moreover, the extensive use of annotations makes it harder to quickly see how the database is structured.

Kotlin Exposed is a lightweight SQL library on top of JDBC that could serve as a good alternative. When using Kotlin Exposed you start by describing your database structure using plain Kotlin code. The code resembles SQL DDL statements very closely and does not require any annotations or reflection! You can use these descriptions to write type safe queries. These queries can be written in two flavors: DSL and/or DAO. This post focuses on the DSL flavor.

Exposed by example

Let’s assume we are building an app that tracks movies and the actors that play in them. It has a simple SQL database that contains three tables; movies, actors and actors_in_movies. The latter is a bridge table to express a many-to-many relationship between movies and actors. See the entity relationship diagram:

exposed erd

The tables movies and actors each have an id column of type integer which is the primary key. The id columns are auto incremented by the database. actors_in_movies has a composite primary key of two columns actor_id and movie_id, referring to the movies and actors tables. The foreign key constraints in actors_in_movies are configured to cascade on delete.

Defining tables

The next step is to define these properties in code:

// File: Movies.kt
object Movies : Table("movies") {
    val id = integer("id").autoIncrement().primaryKey()
    val name = varchar("name", 256)
    val producerName = varchar("producer_name", 255)
    val releaseDate = datetime("release_date")
}

// File: Actors.kt
object Actors : Table("actors") {
    val id = integer("id").autoIncrement().primaryKey()
    val firstName = varchar("first_name", 256)
    val lastName = varchar("last_name", 256)
    val dateOfBirth = date("date_of_birth").nullable()
}

// File: ActorsInMovies.kt
object ActorsInMovies : Table("actors_in_movies") {
    val actorId = integer("actor_id").references(Actors.id, onDelete = ReferenceOption.CASCADE).primaryKey()
    val movieId = integer("movie_id").references(Movies.id, onDelete = ReferenceOption.CASCADE).primaryKey()
}

Each object describes a table. The columns and constraints are defined inside the object in a way that is similar to SQL DDL statements. Notice the absence of annotation and reflection. To me, this definition provides a lot of clarity. Developers not familiar with Kotlin Exposed can still read this definition and understand it.

Kotlin Exposed provides a class SchemaUtils to create, drop, update tables in the database. It can also check for cycles or just print the SQL DDL:

// Create the tables
SchemaUtils.create(Actors, Movies, ActorsInMovies)

// Drop the tables
SchemaUtils.drop(Actors, Movies, ActorsInMovies)

// Create if not exists
SchemaUtils.createMissingTablesAndColumns(Actors, Movies, ActorsInMovies)

// Generate SQL DDL statements
val ddlStatements: List<String> = SchemaUtils.createStatements(Actors, Movies, ActorsInMovies)

However, in production, the database is probably versioned with tools like Flyway. In that case, the tables already exist when you start the application and there is nothing you need to do.

Setting up a connection

Before you can start executing queries, you need to configure your datasource using the Database.connect() method. It accepts a datasource or a jdbc url with a username and password. In the case of H2 it simply looks like:

Database.connect("jdbc:h2:mem:regular;DB_CLOSE_DELAY=-1;", driver = "org.h2.Driver")

All queries will use this connection. Exposed automatically detects the database vendor and uses its SQL dialect while executing queries.

In production, you will probably want to use a database like Postgres with a connection pool such as HikariCP. This can be configured as follows:

val config = HikariConfig()
config.jdbcUrl = "jdbc:postgresql://127.0.0.1:5432/exposed"
config.username = "exposed"
config.password = "exposed"
config.driverClassName = "org.postgresql.Driver"

val hikariDataSource = HikariDataSource(config)

Database.connect(hikariDataSource)
Starting a transaction

We can now start a transaction that automatically uses the configured datasource/connection:

transaction {
    // execute queries
}

The transaction automatically commits when the code block ends, or rolls back when an exception is thrown. You can also use commit() or rollback() inside the transaction block.

The last statement of the transaction block is its return value:

val insertedId = transaction {
    // execute queries
}
Inserting data

The table objects we defined earlier provide multiple ways to execute queries. Actors.insert {} can be used to insert data into the Actors table. This code inserts an actor and returns the generated id:

val insertedActorId = transaction {
    Actors.insert {
        it[firstName] = "Brad"
        it[lastName] = "Pitt"
        it[dateOfBirth] = DateTime.parse("1975-10-12")
    } get Actors.id // fetches the auto generated ID
}

it represents the insert statement. it[firstName] = "Brad" sets 'Brad' as the value for first_name in the new row. Exposed knows that the column type is varchar and, therefore, only accepts a String value in the assignment. timestamp and date columns are mapped using joda’s DateTime. Switching to java.time is on the road map. The generated insert id can be read on the result of the insert statement.

The executed SQL looks as follows:

INSERT INTO ACTORS (DATE_OF_BIRTH, FIRST_NAME, LAST_NAME)
VALUES ('1975-10-12', 'Brad', 'Pitt')
Selecting data

Selecting data results in a result set. I prefer to map the result set to immutable data classes and return that as the response. The DTO for an actor:

data class ActorDto(
    val id: Int?,
    val firstName: String,
    val lastName: String,
    val dateOfBirth: String?
)

All actors can be selected by:

val actorDtos = transaction {
    Actors.selectAll().map { mapToActorDto(it) }
}

fun mapToActorDto(it: ResultRow) = ActorDto(
    id = it[Actors.id],
    firstName = it[Actors.firstName],
    lastName = it[Actors.lastName],
    dateOfBirth = it[Actors.dateOfBirth]?.toString("yyyy-MM-dd"))

To select a single actor by id:

val actorDto = transaction {
    Actors.select {Actors.id eq actorId}
        .map { mapToActorDto(it) } // returns a standard Kotlin List
        .firstOrNull()
}

It’s also easy to build queries with a conditional where clause. I sometimes use this when building a REST API with query parameter filtering. Suppose you want to support the following REST API:

// List all actors with first name Angelina
curl "http://localhost:8080/actors?firstName=Angelina"

// List all actors with first name Angelina born on 1983-11-10
curl "http://localhost:8080/actors?firstName=Angelina&dateOfBirth=1983-11-10"

With Exposed, it’s easy to add a conditional where conditions:

val actorDtos = transaction {
    val actorsQuery = Actors.selectAll()

    httpRequest.queryParam("id")?.let { actorsQuery.andWhere { Actors.id eq it.toInt() } }
    httpRequest.queryParam("firstName")?.let { actorsQuery.andWhere { Actors.firstName eq it } }
    httpRequest.queryParam("lastName")?.let { actorsQuery.andWhere { Actors.lastName eq it } }
    httpRequest.queryParam("dateOfBirth")?.let { actorsQuery.andWhere { Actors.dateOfBirth eq DateTime.parse(it) } }

    actorsQuery.map { mapToActorDto(it) }
}
Joins

Foreign key relationships are defined in the tables, therefore, Exposed knows how to join tables together. Let’s select a movie with the name 'Guardians of the galaxy' and find all its actors:

val actorsInMovie = transaction {
    Actors.innerJoin(ActorsInMovies)
        .innerJoin(Movies)
        .slice(Actors.columns) // only select the Actors columns
        .select { Movies.name eq "Guardians of the galaxy" }
        .map { mapToActorDto(it) }
}
Joins without a foreign key relationship

It’s also possible to join on a column that’s not defined as a foreign key. Suppose we want to select all movies that have been produced by someone who is also an actor.

val moviesProducedByActors = transaction {
    Join(Actors, Movies, JoinType.INNER, additionalConstraint = { Actors.firstName eq Movies.producerName })
        .slice(Movies.name, Actors.firstName, Actors.lastName)
        .selectAll()
        .map { "Movie ${it[Movies.name]} has been produced by actor ${it[Actors.firstName]} ${it[Actors.lastName]}") }
}
Group by

Suppose we want to list all the movies together with the number of actors starring in them:

val movieActorCount: Map<String, Int> = transaction {
    Movies.innerJoin(ActorsInMovies)
        .innerJoin(Actors)
        .slice(Movies.name, Actors.firstName.count())
        .selectAll()
        .groupBy(Movies.name)
        .map { Pair(it[Movies.name], it[Actors.firstName.count()]) }
        .toMap()
}
Spring integration

All the examples above start transactions using transaction{}. However, many projects nowadays use Spring. Exposed can be integrated with Spring transactions by including spring-transaction dependency:

    implementation "org.jetbrains.exposed:spring-transaction:0.16.1"

And configuring a org.jetbrains.exposed.spring.SpringTransactionManager bean:

@Bean
fun transactionManager(dataSource: HikariDataSource): SpringTransactionManager {
    return SpringTransactionManager(
        dataSource, DEFAULT_ISOLATION_LEVEL, DEFAULT_REPETITION_ATTEMPTS)
}

You can then run queries anywhere where a Spring transaction is active.

Final thoughts

This is a quick overview of Kotlin Exposed. We have covered some basics, but the DSL supports much more. Writing queries using the DSL produces readable code and makes it easy to predict the runtime behavior.

The best way to find out more about Exposed is playing around with some code. I’ve created a project to help you get started: github.com/toefel18/kotlin-exposed-blog.

It’s a simple application that exposes a REST API using Javalin. Simply run MainWithH2 to have a fully functional app with test-data loaded. There is also a MainWithPostgresAndHikari that uses Postgres and Hikari.

Enjoy!

Additional resources:

Exposed README
Exposed wiki with the documentation
Baeldung Guide to the Kotlin Exposed framework
How we use Kotlin with Exposed for SQL access at TouK
Bits and blobs of Kotlin/Exposed JDBC framework

shadow-left