Kotlin ORM-Persistence

SQL and ORM alike Database Access with Kotlin Exposed.


Almost all access to SQL-based databases is performed through the JDBC API. It provides a standardized way for Java programs to interact with databases, enabling seamless communication and data manipulation. It acts as an intermediary layer, shielding the application from the underlying complexities of different database systems.
It also makes use of the SQL language which is a standard to unify the interactions with the database, from inserting, updating, querying, and deleting data.

This SQL language, together with the JDBC API, abstracts away the differences in the database systems. It contains features to increase the performance like prepared statements which also help in securing your application by using parameters and not just ‘pasting’ values into the SQL query.

The API itself is rather old and still relies on a lot of manual actions and statements from the developer. Resources like result sets, statements, and connections need to be closed after we don’t need them anymore. This results in a lot of boilerplate code.

The SQL language itself is seen as problematic by a lot of developers. The SQL Language is completely different from Java itself. And thus not everyone is keen on learning something besides Java. Especially since there are frameworks these days that provide a solution that works completely within Java.

But the traditional ORM frameworks like Hibernate bring a lot of other challenges and problems as I have described in the previous blog – Why ORM and Data frameworks are not your best option. So be careful in what features and functionalities you use in your application.

In this blog, we look at the Exposed library written in Kotlin. It is a lightweight SQL library on top of the JDBC driver for the Kotlin language. Exposed has two flavors of database access: typesafe SQL wrapping DSL and lightweight Data Access Objects (DAO).

It is an alternative for Java-based solutions like Hibernate when you are working with Kotlin. Also, JetBrains recently announced that it will increase the efforts into the framework, see this blog.


Since the Exposed framework has 2 flavours, the dependencies that you should add are separated into different artifacts.

The core dependency contains all the basic code and functionality which is independent of the JDBC or DAO flavour. Depending on the flavour you want to use, you should add the dependency you need.

When using Gradle as the build system, you can add the following dependencies.

dependencies {
implementation "${exposed.version}"
implementation "${exposed.version}"
implementation "${exposed.version}"

In the case of Maven, the following snippet gives you all the information you need.


Defining the DB connection, which can be based on a standard DataSource object or use Connection Pooling provided by a third-party library like HikariCP, just requires calling the function. This will make sure that all parts of the framework will use the DB connection from this setting. You can create for example following

object DBSettings {

    private val config = HikariConfig().apply {
        jdbcUrl = "jdbc:postgresql://localhost:5432/postgres"
        driverClassName = "org.postgresql.ds.PGSimpleDataSource"
        username = "postgres"
        password = "mysecretpassword"
        maximumPoolSize = 10

    val dataSource by lazy {

    val db by lazy {

Where you obviously retrieve the connection parameters from an external location instead of just hardcoding them in the code as in the previous example.

Typesafe access

One of the challenges we have when working with databases is creating SQL statements and mapping them to instances. We can either execute a SQL query which is basically just a String and map each field by hand to our properties of the objects. This is can very tedious. Or we define some ‘entities’ like in ORM frameworks and let the framework generate the SQL query. But that can lead to unoptimised queries, especially when you need very complex queries.

The Exposed framework, like some other frameworks in Java and Kotlin, try to approach this in a very lightweight way that handles the mapping for you so that your code is more type-safe (is a certain database field used as a String or Number?) and name changes in database columns and code can be handled transparently.

Within the JDBC feature of Exposed, you can define Singletons in Kotlin that extend from the Table class and define the correspondence between the table and code. Due to the expressiveness of Kotlin, these definitions can be highly readable. For example, an instance that maps to a table ‘Users’ that has some fields and a foreign key to the table ‘Cities’ can be defined as

object Users : Table() {
    val id = integer("id").autoIncrement()  // Column<Int>
    val name = varchar("name", length = 50) // Column<String>
    val cityId = (integer("city_id").references( // Column<Int?>

    override val primaryKey = PrimaryKey(id, name = "PK_User_ID") // name is optional here

Due to the ‘inheritance’ from Table, each singleton defined as in the example, gets a lot of functions to perform some actions.

Like inserting into the table, and retrieving the newly assigned id using the auto-increment functionality of the database on the fly.

    val amsterdamId = Cities.insert {
        it[name] = "Amsterdam"
    } get

Or loop over each record in the table

    for (city in Cities.selectAll()) {
        println("${city[]}: ${city[]}")

You can see that in each of these use cases, we only refer to the property of the singleton Users and thus the actual table field name is only defined once in the configuration of the table itself.

But the JDBC feature also allows to create more complex queries, for example

    (Users innerJoin Cities).slice(, {"Emma") }.forEach {
        println("${it[]} works in ${it[]}")

This joins the 2 tables, based on the information within the definition (look at .references( earlier on), selects the 2 name columns, adds a where clause on the user name, and prints out the information in this case.

So this JDBC feature of the Exposed framework solves a few important issues of working with the JDBC API, namely the mapping and the boilerplate code that is required when using the JDBC API purely.
Note however that the mapping of the result sets is done using a Map-like structure where the key represents the column name and the value the database field value. People who are used to working with ORM frameworks will find this a bit awkward but Kotlin gives you a highly readable syntax for it. Most of all, you as the developer are still in control of the queries that are generated and thus performance issues are less likely.

The DAO or ORM solution.

The DAO features of the Exposed Framework take a slightly different approach to the same problem space by making use of ‘Entities’. So you need to define your table mappings and how you run queries differently. This means that both features are not compatible, or at least, combining both approaches did not work quite well for me. They can be used in the same application, but the definition of the table is slightly different and an Entity object is also needed.

object Users : IntIdTable() {
    val name = varchar("name", length = 50) // Column<String>
    val city = reference("city", Cities)

class User(id: EntityID<Int>) : IntEntity(id) {
    companion object : IntEntityClass<User>(Users)

    var name by
    var city by City referencedOn

Here Users contains the definition of the table and assumes an autoincrement Primary Key for the field id. The User class defines an instance of a record where we even have an object for City and not just the foreign key value.

An insert statement can be written as

    val amsterdam = {
        name = "Amsterdam"

which results in an INSERT SQL statement at the moment we access the database again or at the end of the transaction. So it is not flushed to the database immediately.

Retrieving data is also done differently than in the JDBS feature case. An important fact that you need to know is that joins are executed lazily which might not be the optimal case for some of your queries. For example, when retrieving the record for ‘Emma’ and the city she works in, the retrieval of the City name is done separately and after the retrieval of the User record itself.

    User.find {"Emma") }.forEach {
        println("${} works in ${}")

As you see, here again, the inheritance brings some useful methods into the objects like the find method.

Of course, an eager-loading way of working is possible by explicitly defining the joins that need to be made. This again is a positive point for this framework concerning the eager and lazy loading issues you can have with the classic ORM tools like Hibernate. The next snippet shows the same query but now with joining the tables eagerly.

    // Eager loading
    val query = Users.innerJoin(Cities).select("Emma")

    User.wrapRows(query).forEach {
        println("${} works in ${}")

Code examples

You can find the complete example project at this location of my Kotlin Project on GitHub.

If you want to learn more abput the Exposed framework itself, this is the main page of the Wiki for the project.


The Exposed framework is solving the main issues of working with the JDBC API in a completely different way the other frameworks like Hibernate. The lightweight approach to avoid mapping and typesafe access is not unique, but due to the expressiveness of the Kotlin language, results in a DSL-like solution that is highly readable and understandable.

Because the JDBC feature of the framework, tries to stay as close as possible to the JDBC API itself, it makes use of a Map-like structure for the transferred data. If you like real objects for records or result set items, you can make use of the DAO functionality of the framework. This resembles the ORM frameworks but is much easier to use and more performant in the execution.

Training and Support

Interested in a training about efficient ORM tools or Hibernate usage in your project? In need of an expert to help you solve a problem, feel free to contact me.

Do you need a specific training session on Jakarta EE, Quarkus, Kotlin or MicroProfile? Have a look at the training support that I provide on the page and contact me for more information.

Best Practices Kotlin Ktor

Centralised Exception handling within Kotlin Ktor

A centralised Exception handling within a web framework is an important aspect of your application. The functionality that is provided by your code for the client is probably the most important aspect. But not every request can be successfully processed. Not to mention that your application can receive malicious requests that should be handled gracefully.

A centralised handling is highly recommended so that you can handle these exceptional cases uniformly. In this blog, I’ll explain how you can achieve such a centralised handling within Kotlin Ktor that is outside of your business code.

Types of Errors

There are several types of Errors that your applications should handle. Let us go over each of them and how you can handle them. In the next step, we will then handle all Exceptions in a central place.

Missing URL parameters

Within Ktor, we define the template URLs that we like to handle. many of these URLs have a placeholder in them so that we can handle similar requests by the same piece of code. But the values for these placeholders might be missing or wrong.

Let us consider the URL that we define to retrieve detailed information about a product of our webshop based on its id.

GET /product/{productId?}

And we can have multiple URLs in our application that all have a productId parameter in the template. We can use a helper method to retrieve this parameter from the URL and call it in each case we have thus productId parameter.

    fun extractProductId(context: PipelineContext<Unit, ApplicationCall>): String {
        return["productId"] ?: throw HasMissingParameterException("Missing 'productId' in URL")

The HasMissingParameterException is a RuntimeException that we will handle later on. And we can create such a helper function for each template variable our application has and group them in a Kotlin file.

The reason we have defined the parameter as optional (the ? at the end of the name) allows us to handle the absence of the parameter. The client may, on purpose or by accident due to a problem in their own code, send requests like /product/. Due to the optional marker, the same code will be executed within our Ktor application and a HasMissingParameterException will be thrown.

In the next section, we will see how these HasMissingParameterException exceptions will be handled.

Incorrect ids

Related to the previous paragraph where we handled the productId, we should verify if the productId is a valid value.

If we always expect that the productId is a numeric value, a positive long value for example, we can incorporate this check into the extractProductId function we used earlier. Instead of returning the value immediately as in the above example, we can try to convert the String value to a Long.

If that fails, we can throw another exception to indicate this problem.

Further on in the processing of our request, we need to check if the productId that is specified is an existing value. It might be a positive value but the number might not be a product that exists in our database.

When our code finds this out, it can throw an exception to indicate the entity is not found. Our generic Exception handling can pick this up and responds with the HTTP status 404.

See the example application and the BookEntityNotFoundException class for an example of this scenario. (project on GitHub )

Business logic error

The last category of errors we need to handle is business logic errors. If the request that we are handling violates one of the business rules we have defined, an order cannot be made for a customer that has more than x euro open invoices, and the request should be aborted.

Our code can throw an exception at this point and our generic exception handling will do the rest. Our code doesn’t need to know how to handle it, it just needs to show a custom exception.

All these exceptions should extend from a common exception, like BusinessException so that we can handle these cases easily in our ExceptionHandler.

Sending errors to the client

Now that we have proper Exceptions thrown at the various points in our code, we need a central place where we can handle them. This way, our business logic is not tight to the handling of the requests and can be reused in other scenarios.

We can install an ExceptionHandler through the StatusPage functionality of Ktor. So make sure you have added this module to your application. But it is not much trouble to add it later on as it just required the Maven artefact.


Installing the handler within the Ktor framework is also very straightforward. You need the following snippet to forward the exception handling to the handle() method

install(StatusPages) {
    exception<Throwable> { call, cause ->
        ExceptionHandler.handle(call, cause, developmentMode)

The development mode variable is a boolean that indicates if you started the application in development mode. It can be used to give more information in the response or in the log about the exception that occurred. When in production mode, the code makes sure that no internal information is returned to the caller that might give some hints to malicious users about your application internals and how it can be abused. In the end, it is up to you and the additional components you use in your environments how to deal with Exception.

But don’t print a stack trace for each business logic or conversion error from String to Long when retrieving parameters. As these exceptions are thrown to easily handle error responses and are not some kind of error in your code, but are expected.

What does the handle method look like?

   when (cause) {
        is EntityNotFoundException -> {
            // for the cases the user specified an URL parameter where the id doesn't exist.
                ExceptionResponse(cause.message ?: cause.toString(), HttpStatusCode.NotFound.value)

        is BusinessException -> {
            // Some business logic error, status 412 Precondition Failed is appropriate here
                ExceptionResponse(cause.message ?: cause.toString(), cause.messageCode.value)

        is ParameterException -> {
            // The client forgot to define a Path or Query parameter or used a wrong type (string and not a number)
                ExceptionResponse(cause.message ?: cause.toString(), HttpStatusCode.BadRequest.value)
        // We can have other categories
        else -> {
            // All the other Exceptions become status 500, with more info in development mode.
            if (developmentMode) {
                // Printout stacktrace on console
                cause.stackTrace.forEach { println(it) }
                call.respondText(text = "500: $cause", status = HttpStatusCode.InternalServerError)
            } else {
                // We are in production, so only minimal info.
                call.respondText(text = "Internal Error", status = HttpStatusCode.InternalServerError)

This is the code from the example project I prepared for this blog. It can be adapted to the needs of each specific application. The overall idea is that you have a specific HTTP status for each type of problem. The ParameterException, indicating a missing or wrong type for a path or query parameter results in status 400. EntityNotFoundException when an id is not found in the database and BusinessExceptions indicating a business rule violation results in status 404 and 412 respectively.
All other exceptions result in status 500 and when in development mode, more info is available in the response and in the log.

The body of those errors is a JSON object that is created from the data class ExceptionResponse. It contains a code and a short message so that the client has some information about what went wrong. Since most of the HTTP status codes returned by this handler are in the 400 range, meaning they are client errors. the client did send a request which was incorrect so it should receive some feedback on what was wrong.


Proper Exception handling is an important part of your application. As a best practice, not every method should deal with returning the proper response as that would mean that your business code knows about the type of client. Instead, you should throw Exceptions, all having a specific parent that indicates the type of problem that occurred. Types are related to the parameters of the request, non-existing ids that are provided, or violations of business rules. Other types of problems can exist depending on the application. Regardless of the number of types, a centralised exception handler, installed as StatusPage handler within Ktor, the correct HTTP status and response body can be sent back from a single method. And don’t forget to include some code and a short description of the problem so that the client knows what went wrong. Without giving away too many details of the internals of your application in case a malicious user tries to figure out your application by sending random or incorrect requests on purpose.

You can find an example of how you can implement this strategy in the project located at

Training and Support

Do you need a specific training session on Kotlin, Jakarta EE or MicroProfile? Or do you need some help in getting up and running with your next Ktor project? Have a look at the training support that I provide on the page and contact me for more information.

JAX-RS Kotlin Ktor

Why don’t you create your next Backend application with Kotlin and Ktor?

For a long time, Kotlin was on my list of things I wanted to learn more about. In the second half of 2022, I finally got the chance to learn the language and started using Ktor, the ‘web application runtime’ for Kotlin.


Kotlin is a modern programming language that offers several benefits over Java. Firstly, Kotlin supports object-oriented, functional, and procedural programming constructs, making it a versatile language for a wide range of projects.
Secondly, Kotlin is fully interoperable with Java, which means that developers can easily use Kotlin in conjunction with existing Java codebases. Additionally, Kotlin offers support for several concepts, such as data classes, structured concurrency, and virtual threads, which were not available in Java until much later. Kotlin also offers null safety features, which help prevent common errors that can arise due to null pointer exceptions.
Another significant benefit of Kotlin is its compact and highly readable grammar, which makes code more concise and easier to understand. Finally, Kotlin offers extension functions, which allow developers to add new functions to existing classes, making it easier to write reusable code.

Kotlin is a modern, powerful, and highly expressive programming language that helps developers write better code faster and more efficiently.

We will encounter various powerful constructs of Kotlin in this blog series called “project FF”. In this Framework Face-off, I will compare several Java application runtimes, including Spring Boot and Quarkus, with Jakarta EE and how you implement Enterprise functionality.

But today, I give already two examples of the power of Kotlin.

Kotlin extension functions

Kotlin extension functions allow you to add new functions to an existing class without having to inherit from that class or modify its source code.
In other words, extension functions let you extend the functionality of a class in a more flexible and modular way. This can make your code more readable and easier to maintain.

Extension functions are similar to static utility methods, but with the added benefit of being able to call them as if they were instance methods of the class, they are extending. This can make your code more concise and easier to understand.

fun String?.hasSpaces(): Boolean {
   val found = this?.find { it == ' '}
   return found != null

fun main() {
   val data = "A sentence with spaces in of course"
   println(data.hasSpaces()) // True

   val data2 = "word"
   println(data2.hasSpaces()) // False

For a detailed explanation of this code, I refer you to the Kotlin tutorials. In short, the hasSpaces function is defined on a nullable String type. The function body checks if we find a space in the string value. From that point on, we can use the hasSpaces on a String as you can see in the main function.

Creating an interceptor

You can create an interceptor in plain Kotlin using lambdas and high-order functions. Functions and lambdas are first-class citizens and are treated equally as data objects.

inline fun timeBlock(block: () -> T): Pair {
   val startTime = System.nanoTime()
   val result = block()               // execute lambda
   val endTime = System.nanoTime()
   return Pair(result, endTime - startTime)

fun main() {
   val number = 121
   val (isPrimeValue, time) = timeBlock {
      (2..number/2).none { number % it == 0 }

   println("Result of prime check for $number is $isPrimeValue ")
   println("Execution took $time nanoSeconds ")

Here we define a function that accepts a lambda and we time the execution of the lambda. The function returns the result of the lambda and the time it took.

As example, we time the calculation to check if a value is a prime value. Also here you can see the power of Kotlin but still keep it readable.

(2..number/2).none { number % it == 0 }

defines that we go from value 2 until the half of the value we need to check and there should be no value where the modulo of the value with the number is 0.


Ktor is a lightweight, asynchronous web server that easily allows you to define what is needed. In the blog “Time is code, My friend”, Ktor performed really well and performed as good as Quarkus which has a very elaborate and complex pre-processing system to achieve such a fast startup.

Since it is highly modular, you can keep the runtime, and memory usage very low. And since there are modules available for each major topic or framework, you can create applications that integrate with them very easily.

Also, Ktor is created with Kotlin Coroutines support built-in. Kotlin Coroutines provide you with solutions for asynchronous non-blocking programming, structured concurrency and solutions similar to Java Virtual Threads. Things that will only appear in Java in the coming versions but are already available for many years within Kotlin.

Ktor Example

In this blog, I’ll show you how you can create an application with endpoints that handle JSON payload.

You can generate a Maven or Gradle project using the Ktor Project Generator web page or directly from within IntelliJ IDEA.

By default, the Netty engine is selected to handle the HTTP request handling but you can select other engines if you like.

Under the plugin section, make sure you select the ‘kotlinx.serialization‘ plugin to have support for JSON. This automatically brings in the routing plugin which is responsible for calling a certain Kotlin function for a URL pattern and the Content Negotiation plugin that sets header values accordingly to the requirements like JSON types.

The generated project contains a main function within the Application.kt file which starts Netty and an extension function that define the configuration of the modules. This allows for a clear separation of each functionality and quickly lets you find each setting.

To have full JSON support, we don’t need to configure anything, just activate the plugin. The Maven plugin in this case makes sure that any class that is annotated with @Serializable can be used at runtime without the need for any reflection. This makes the solution native compile friendly. This a topic we will discuss later in more detail.

For the routing, we call a function that corresponds to the HTTP method we want to support. It has a URL pattern and lambda expression as a parameter. This way, we can define the function for each URL in a very concise and readable way.

    get("/") {
        call.respondText("Hello World!")

The entire example can be found at the Project FF Github repository:


In a time where modular runtimes that are small and take up little memory are important for some people, Kotlin and Ktor are ideal solutions. Kotlin offers many benefits over Java and offers a compact syntax but still highly readable. It supports many important features already for years that are introduced only recently or planned in Java.

Ktor is the natural solution if you choose Kotlin and need to write a backend application. Although you can combine Kotlin with Spring Boot and Quarkus, Ktor is a lightweight solution that has the same functionality available through its modules and is faster at runtime.

Training and Support

Do you need a specific training session on Kotlin, Jakarta EE or MicroProfile? Or do you need some help in getting up and running with your next Ktor project? Have a look at the training support that I provide on the page and contact me for more information.

This website uses cookies. By continuing to use this site, you accept our use of cookies.  Learn more