Bits and blobs of Kotlin/Exposed JDBC framework

TL;DR — In a previous post I described first impressions from Exposed. This time I will dive into some more details from the experience we got in the last couple of months.

We are working with Exposed on the back-end few months. Before that we were using Hibernate, Jooq and internal mechanisms based on plain JDBC.

All in all, we are satisfied with the move to Exposed. Among it’s main advantages I can point out are:

It follows the important design rule that the simple things should be simple and the hard things should be possible. So if we just want a row update or fetch it is pretty straight-forward. As a general philosophy of software developers, we strive to keep our tables simple and leave some work to the application layer itself as opposed to complicated DB schemes with complex SQL queries, so we didn’t tested that roughly.

Secondly, up to a certain point its type safety and auto-completion that gets with its DSL are pretty convenient.

In the rest of the post I would like to discuss some topics we tackeled while working with Exposed.


Or why do I prefer DSL approach?

DAO means Data Access Object — an object that represents a row in DB. It supports CRUD operations: Create, Read, Update, Delete. It’s main advantage is that it is simple to use and support very basic and straightforward way to access and manipulate the data. It reduces the amount of boilerplate and code duplication that is usually required.

DSL stands for Domain Specific Language. In our context it means writing code that is similar to SQL syntax to access the DB. In the case of Exposed it is also type safe so types are checked by the compiler, unlike SQL. So DSL is easy for developers already familiar with SQL. It is usually easy to transform an SQL query into DSL query.

I prefer DSL for couple of reasons. When we used DAO with Hibernate, we had a bug that we read an entity and then write it back to update specific value. It was overwriting updates to DB that happened after the row was read but before the update and was pretty hard to track. When using DSL one will provide the values essential for the update so the above usually prevented. But this was just a symptom. With DAO it is easy to forget that you are actually working with Database. I have seen cases with Hibernate of complex and non-optimized queries that were just too damn easy to write. With DSL it is much easier to reason about the actual SQL.

DSL will sometimes result in code duplication. Every flow and use case requires its own parameters.

I still prefer that over DAO for the reasons above.

Using DSL with data classes

As we are not using DAO, there is an object representing the table schema but there is no type to hold entities from table. We use data class that are configured separately to hold entities.

If we look at the star wars example from the wiki, we will have an object describing the schema and a data class to hold entities from our domain:

In the code above you can see an example of the duplication. This However, allows to decouple domain entities from schema.

I see it as an advantage as it is a real POJO — no metadata annotations at all, and it reduce the binding between table schema and the business model. Once you got such an object (from the data class) you can be sure it left all the magic behind.

The downside of that approach is that there is more duplication than using DAO. Batch insert for example, requires repeating all inserted values.

How does the magic happens?

Photo by David Fedulov on Unsplash

Let’s try to understand what happens under the hood when using Exposed DSL. Consider the following example from the wiki:

Line 2 initiates a connection to the DB. Where is it stored? How is it used? If we look at connect() source code we will see an additional parameter manager. By default it gets a ThreadLocalTransactionManager, which means the connection is kept on the thread. There is another implementation for spring as well.

Line 4 calls a transaction() method. Again, this is a method that is related to our TransactionManager. It accept a statement as a parameter — the lambda we provide to it in curly braces — to be invoked later.

When we call Cities.insert() on line 6, it will get the transaction manager we have on the thread, and will be executed via the connection it hold.

ThreadLocal usage

As seen above, under the hood Exposed uses ThreadLocal to keep the state such as the connection to DB. It allows the cool DSL and put some magic into the syntax reducing boilerplate. However, it impose some limitations on the usage. Concurrency mechanisms that uses threads such as thread pools and coroutines cannot be used. It also limit usage of multiple db connections as well as it makes reasoning of the lib source code more subtle.


Exposed documentation is better now that there is a wiki. Still it can be improved and some parts are missing from docs. In addition, the source code itself lacks of documentation, so it is difficult to browse.

Specific native DB support

As mentioned above, generally we don’t have complex requirements. I still think it would be good if the framework can support specific dialects. In our case we are missing mysql replace which is like insert on duplicate update.

Connection pools

Originally we were using old version of DBCP in Hibernate with no particular issues.

After the move to Expose we found a leak in the pool. Counter of connections was not decreased when connection was free so we left without ability to create new connections. This was a result of a specific edge-case of DBCP since it did not occur before that. In our case it was more complicated to upgrade the version of DBCP than to move to a new pool, so we decided to move to HikariCP. It took us some time and couple of miss-configuration to configure it but eventually it is working very well for us.

Exposed vs Squash vs JOOQ

From time to time, I see questions like which framework to use. From what I understood Exposed and Squash both developed and maintained by Jetbrains employees (currently @tapac and @orangy). However, it seems that both are not “official” Jetbrains product. So both are live and maintained to some extent. I think that it might be that Squash api and design are better, but it is much less mature in terms of documentation and community at the moment so harder to use. That is why I preferred Exposed.

JOOQ on the other hand, is well documented and very popular. We worked with it couple of month but eventually we decided not to pick it for 2 reasons: the product is free but there is also paid version which means it might change in the future — so it feels less “open-source”. In addition I didn’t like the way the schema objects are generated and maintained, In Exposed example it is always simple to type it by hand (crazy ha?!).

To be continued…

One clap, two clap, three clap, forty?

By clapping more or less, you can signal to us which stories really stand out.