Lightweight Kotlin DSL to Access SQL Database

As a member of the IntelliJ IDEA team, I share the same floor with the guys who develop the Kotlin language. So sometimes I get curious about what they do and how I could make use of it. Just a few days ago I had nothing urgent to do so I gave Kotlin a try, just to see how I could use it to access an SQL database. As you know, Kotlin is a statically typed language that compiles to JVM bytecode and JavaScript. Right now Max from the room next to mine is making Kotlin generate statically typed HTML and CSS with Kara Framework. So why shouldn’t it help me with my SQL?

Surely everyone is aware of more than one way to access an SQL database from an application. However, only two of these ways are recognized as general patterns: Data Access Object and Object Relational Mapping. Each pattern has its own benefits and weaknesses. Whilst ORM is more comprehensive, DAO is more lightweight and flexible.

Personally I prefer flexibility over complexity. That’s why I decided to write a prototype for a small library which helps you write data access objects with purely typed SQL queries in Kotlin language:

select (Cities.name) forEach {
    println(it)
}

This piece of code will generate the following SQL query:

SELECT Cities.name FROM Cities

Before you use such statements in your code, you have to define the table structure in this way:

object Cities : Table() {
    val id = primaryKey("id")
    val name = columnString("name")
}

Then you can not only run select statement, but also create tables and insert or update data.

var db = Database("jdbc:h2:mem:test", driver = "org.h2.Driver")

db.withSession {
        create (Cities)

        insert (Cities.id(1), Cities.name("St. Petersburg"))
        insert (Cities.id(2), Cities.name to "Munich")

        update (Cities.name("München")) where Cities.id.equals(2)

        drop(Cities)
}

What about a more sophisticated table structure? Let’s define one more table with a foreign key and a nullable field:

object Users : Table() {
    val id = primaryKey("id")
    val name = columnString("name")
    val cityId = columnNullableInt("city_id")
    val city = foreignKey(cityId, Cities)
}

Now let’s select users based on a specific criterion and output the city they live in. Or, if the city is not set for a user, let’s output “nowhere”:

select (Users.name, Users.cityId, Cities.name) join Users.city where
          Cities.name.equals("St. Petersburg") or Users.cityId.isNull() forEach {
      val (userName, cityId, cityName) = it
      if (cityId != null) {
         println("$userName lives in $cityName")
      } else {
          println("$userName lives nowhere")
      }
}

As you see we use a very simple construction to join the tables. What is more important, for each row we get a tuple of values with the types defined for the columns, including the nullable property.

The generated SQL query looks as follows:

SELECT Users.name, Cities.name FROM Cities, Users WHERE (Users.id = 1 or Users.name = 'Sergey') and Users.id = 2 and Users.city_id = Cities.id

Unfortunately, right now Kotlin does not allow you to change the type of overloaded operators, otherwise the query might look simpler:

select (Users.name, Users.cityId, Cities.name) join Users.city where
          Cities.name == "St. Petersburg" or Users.cityId.isNull() forEach {
      val (userName, cityId, cityName) = it
      if (cityId != null) {
         println("$userName lives in $cityName")
      } else {
          println("$userName lives nowhere")
      }
}

You could object to this and say that this library has little value since the only thing it allows is writing the same SQL queries. And you’d be right. However, the queries written in this way are a bit friendlier and are checked at compile time, so the likelihood of errors is reduced.

Anyway, this isn’t even a complete library, just a prototype for investigating the use of Kotlin... and asking what you think about it :)

The source code is available in GitHub. Please share your suggestions and ideas!