How we use Kotlin with Exposed for SQL access at TouK

5 min readFeb 12, 2019

Why Kotlin?

At TouK, we try to early adopt technologies. We don’t have a starter project skeleton that is reused in every new project, we want to try something that fits the project needs, even if it’s not that popular yet. We tried Kotlin first it mid 2016, right after reaching 1.0.2 version. It was getting really popular in Android development, but almost nobody used it on the backend, especially — with production deployment. After reading some “hello world” examples, including this great article by Sebastien Deleuze we decided to try Kotlin as main language a new MVNO project. The project was mainly a backend for mobile app, with some integrations with external services (chat, sms, payments) and background tasks regarding customer’s subscription. We felt that Kotlin would be something fresh and more pleasant for developers, but also liked the “not reinventing the wheel” approach — reusing large parts of Java/JVM ecosystem we were happy with for existing projects (Spring, Gradle, JUnit, Mockito).

Why Exposed?

From the beginning, we felt that Kotlin + JPA/Hibernate is not a perfect match. Kotlin’s functional nature with first-class immutability support was not something that could seemly integrate with full-blown ORM started in pre-Java8 era. But Sebastien’s article led us to try Exposed — a SQL access library maintained by JetBrains. From the beginning, we really liked the main assumptions of Exposed:

not trying to be full ORM framework
two flavors — typesafe SQL DSL and DAO/ActiveRecord style
lightweight, no reflection
no code generation
Spring integration
no annotations on your domain classes (in SQL DSL flavor)
open for extension (e.g. PostGIS and new DB dialects)

TL;DR

If you want to see how we use Kotlin + Exposed duo in our projects, check out this Github repo. It’s a Spring Boot app exposing REST API with implementation of Medium clone as specified in http://realworld.io (“The mother of all demo apps”).

Another nice example is this repo by Seb Schmidt.

SQL DSL

In our projects we decided to try the “typesafe SQL DSL” flavor of Exposed. In this approach you don’t have to add anything to your domain classes, just need to write simple a schema mapping using Kotlin in configuration-as-code manner:

And then you can write type/null-safe queries with direct mapping to your domain classes:

RefIds

We like type-safe RefIds in our domain code. This is particularly useful in DDD-ish architectures, where you can keep those RefIds in shared domain and use them to communicate between contexts.

So we wrap plan ids (longs, strings) into simple wrapper classes (e.g. UserId, ArticleId, Username, Slug). Exposed allows to easily register your own column types or even generic WrapperColumnType implementation that you can find in our repo.

Using this technique you can rewrite this mapping to something like this:

And now we can query by type-safe RefIds:

Relationship mapping

One of the biggest selling point of ORM’s is how to easy is to deal with relations. You just annotate the related field/collection with OneToOne or OneToMany and then can fetch the whole graph of objects at once. In theory — quite a nice idea, but in practice things often go wrong. I’m not going to dig into details, instead I recommend you reading e.g. these fragments of “Opinionated JPA with Querydsl” book:

In Exposed SQL DSL approach you have to do relationship mapping by yourself — if you need to. Let’s consider Article and Tag case from our project’s domain. We have a many-to-many relation here, so we need additional “article_tags” table:

When creating Article, we have to attach all the associated Tags by populating Article’s generated id into ArticleTabTable entries:

The funny part is the mapping of Article with Tags in query methods — in API specification Tags are always returned with the Article — so we need to eagerly fetch tags by using leftJoin :

After joining, we have then one ResultRow per one Article-Tag pair, so we have to group them by ArticleId, and build correct Article object by adding Tags for each matching resultRow:

This implementation allows us to solve all the possible cases:

no articles (fold just returns empty map)
articles with no tags (tag is null, so listOfNotNull(tag) is empty)
articles with many tags (article with single tag is inserted into the map, then other tags are added in copy method)

However, consider when you need to fetch the dependent structure with the root object? For tags it makes sense, since you always want the tags with the article, and the count of tags for any article should not be that huge. What about the comments? You definitely don’t want all the comments each time you fetch the article, instead you’ll need some kind of paging or even making parent-child hierarchy for comments for the article. That’s why we recommend to have this relationship mapped indirectly — every Comment should have ArticleId property, and the CommentRepository could have methods like:

Extendibility

Exposed is by design open for extension, making it even easier with Kotlin’s support for extension methods. You can define your own column type or expressions, e.g. for PostGIS point type support as Sebastian showed in his article. We used similar PostGIS extension in our project too. We were also able to implement a simple support for Java8 DateTime column type — for now Exposed has Joda-time support, generic approach for various date/time libraries is planned in the roadmap.

The bigger thing was Oracle DB dialect — we were forced to migrate to Oracle at some time in our project. We submitted a pull-request with foundations of Oracle 12 support, being tested in production for a while (then, we moved back to PostgreSQL…). The implementation was rather straightforward, with DataType- and FunctionProvider interfaces to provide and just few tweaks in batch insert support.

Final thoughts

Our developer experience with Kotlin+Exposed duo was really pleasant. If you don’t plan to map many relations directly, just use simple data classes, connected by RefIds, it works really well. The Exposed library itself may need more exhaustive documentation and removing some annoying details (e.g. transaction management via thread-local — which is already on the roadmap), but we definitely recommend you give it a try in your project!