In this section, we'll define a simple DSL-for-SQL language. We won't define the format or grammar for it, but only an example of what it should look like:

val sql = select("name, age", {
              from("users", {
                  where("age > 25")
              }) // Closes from
          }) // Closes select

println(sql) // "SELECT name, age FROM users WHERE age > 25"

The goal of our language is to improve readability and prevent some common SQL mistakes, such as typos (like FORM instead of FROM). We'll get compile time validations and autocompletion along the way.

We'll start with the easiest part—select:

fun select(columns: String, from: SelectClause.()->Unit): 
    SelectClause {
    return SelectClause(columns).apply(from)
}

This is a function that has two parameters. The first is a String, which is simple. The second is another function that receives nothing and returns nothing.

The most interesting part is that we specify the receiver for our lambda: 

SelectClause.()->Unit

This is a very smart trick, so be sure to follow along:

SelectClause.()->Unit == (SelectClause)->Unit

Although it may seem that this lambda receives nothing, it actually receives one argument, an object of type SelectClause.

The second trick lies in the usage of the apply() function we've seen before.

Look at this:

SelectClause(columns).apply(from)

It translates to this:

val selectClause = SelectClause(columns)
from(selectClause)
return selectClause

Here are the steps the preceding code will perform:

1. Initialize SelectClause, which is a simple object that receives one argument in its constructor.
2. Call the from() function with an instance of SelectClause as its only argument.
3. Return an instance of SelectClause.

That code only makes sense if from() does something useful with SelectClause.

Let's look at our DSL example again:

select("name, age", {
    this@select.from("users", {
        where("age > 25")
    })
})

We've made the receiver explicit now, meaning that the from() function will call the from() method on the SelectClause object.

You can start guessing what this method looks like. It clearly receives a String as its first argument, and another lambda as its second:

class SelectClause(private val columns: String) {
    private lateinit var from : FromClause
    fun from(table: String, where: FromClause.()->Unit): FromClause {
        this.from = FromClause(table)
        return this.from.apply(where)
    }
}

That's the first time we've met the lateinit keyword. This keyword is quite dangerous, so use it with some restraint. Remember that the Kotlin compiler is very serious about null safety. If we omit lateinit, it will require us to initialize the variable with a default value. But since we'll know it only at a later time, we ask the compiler to relax a bit. Note that if we don't make good on our promises and forget to initialize it, we'll get UninitializedPropertyAccessException when first accessing it.

Back to our code; all we do is:

1. Create an instance of FromClause
2. Store it as a member of SelectClause
3. Pass an instance of FromClause to the where lambda
4. Return an instance of FromClause

Hopefully, you're starting to get the gist of it:

select("name, age", {
    [email protected]("users", {
        this@from.where("age > 25")
    })
})

What does it mean? After understanding the from() method, this should be much simpler. The FromClause must have a method called where() that receives one argument, of the String type:

class FromClause(private val table: String) {
    private lateinit var where: WhereClause

    fun where(conditions: String) = this.apply {
        where = WhereClause(conditions)
    }
}

Note that we made good on our promise and shortened the method this time.

We initialized an instance of WhereClause with the string we received, and returned it. Simple as that:

class WhereClause(private val conditions: String) {
    override fun toString(): String {
        return "WHERE $conditions"
    }
}

WhereClause only prints the word WHERE and the conditions it received:

class FromClause(private val table: String) {
    // More code here...
    override fun toString(): String {
        return "FROM $table ${this.where}"
    }
}

FromClause prints the word FROM as well as the table name it received, and everything WhereClause printed:

class SelectClause(private val columns: String) {
    // More code here...
    override fun toString(): String {
        return "SELECT $columns ${this.from}"
    }
}

SelectClause prints the word SELECT, the columns it got, and whatever FromClause printed.