SQL allows you to do calculations on columns over multiple rows using aggregate functions like COUNT, SUM, AVG etc. This post explains how to use them in Kotlin Exposed. We will also cover arithmetic operations on one or more columns. If you haven’t used Kotlin Exposed before, you can go here for an introduction.

Consider an application containing two tables in an SQL database: Comment and User. A comment is written by a user, and can receive likes/dislikes. The snippet below shows the table definitions:

This post explains how to use table aliases using Kotlin Exposed. Aliases are necessary when you want to join the same table multiple times. If you haven’t used Kotlin Exposed before, you can go here for an introduction: Kotlin Exposed - A lightweight SQL library.

In this example we have an application containing two tables: Message and User. The Message table has two references to the User table, one to model the 'fromUser' relationship and one for the 'toUser' relationship. The table definitions look as follows:

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.