1. Overview
In this tutorial, we’ll see how powerful Kotlin language features can be used for building type-safe DSLs.
For our example, we’ll create a simple tool for constructing SQL queries, just big enough to illustrate the concept.
The general idea is to use statically-typed user-provided function literals that modify the query builder state when invoked. After all of them are called, the builder’s state is verified and the resulting SQL string is generated.
2. Defining the Entry Point
Let’s start by defining an entry point for our functionality:
class SqlSelectBuilder {
fun build(): String {
TODO("implement")
}
}
fun query(initializer: SqlSelectBuilder.() -> Unit): SqlSelectBuilder {
return SqlSelectBuilder().apply(initializer)
}
Then we can simply use the functions defined:
val sql = query {
}.build()
3. Adding Columns
Let’s add support for defining target columns to use. Here’s how that looks in the DSL:
query {
select("column1", "column2")
}
And the implementation of the select function:
class SqlSelectBuilder {
private val columns = mutableListOf<String>()
fun select(vararg columns: String) {
if (columns.isEmpty()) {
throw IllegalArgumentException("At least one column should be defined")
}
if (this.columns.isNotEmpty()) {
throw IllegalStateException("Detected an attempt to re-define columns to fetch. "
+ "Current columns list: "
+ "${this.columns}, new columns list: $columns")
}
this.columns.addAll(columns)
}
}
4. Defining the Table
We also need to allow specifying the target table to use:
query {
select ("column1", "column2")
from ("myTable")
}
The from function will simply set the table name received in the class property:
class SqlSelectBuilder {
private lateinit var table: String
fun from(table: String) {
this.table = table
}
}
5. The First Milestone
Actually, we now have enough for building simple queries and testing them. Let’s do it!
First, we need to implement the SqlSelectBuilder.build method:
class SqlSelectBuilder {
fun build(): String {
if (!::table.isInitialized) {
throw IllegalStateException("Failed to build an sql select - target table is undefined")
}
return toString()
}
override fun toString(): String {
val columnsToFetch =
if (columns.isEmpty()) {
"*"
} else {
columns.joinToString(", ")
}
return "select $columnsToFetch from $table"
}
}
Now we can introduce a couple of tests:
private fun doTest(expected: String, sql: SqlSelectBuilder.() -> Unit) {
assertThat(query(sql).build()).isEqualTo(expected)
}
@Test
fun `when no columns are specified then star is used`() {
doTest("select * from table1") {
from ("table1")
}
}
@Test
fun `when no condition is specified then correct query is built`() {
doTest("select column1, column2 from table1") {
select("column1", "column2")
from ("table1")
}
}
6. AND Condition
Most of the time we need to specify conditions in our queries.
Let’s start by defining how the DSL should look:
query {
from("myTable")
where {
"column3" eq 4
"column3" eq null
}
}
These conditions are actually SQL AND operands, so let’s introduce the same concept in the source code:
class SqlSelectBuilder {
fun where(initializer: Condition.() -> Unit) {
condition = And().apply(initializer)
}
}
abstract class Condition
class And : Condition()
class Eq : Condition()
Let’s implement the classes one by one:
abstract class Condition {
infix fun String.eq(value: Any?) {
addCondition(Eq(this, value))
}
}
class Eq(private val column: String, private val value: Any?) : Condition() {
init {
if (value != null && value !is Number && value !is String) {
throw IllegalArgumentException(
"Only <null>, numbers and strings values can be used in the 'where' clause")
}
}
override fun addCondition(condition: Condition) {
throw IllegalStateException("Can't add a nested condition to the sql 'eq'")
}
override fun toString(): String {
return when (value) {
null -> "$column is null"
is String -> "$column = '$value'"
else -> "$column = $value"
}
}
}
Finally, we’ll create the And class which holds the list of conditions and implements the addCondition method:
class And : Condition() {
private val conditions = mutableListOf<Condition>()
override fun addCondition(condition: Condition) {
conditions += condition
}
override fun toString(): String {
return if (conditions.size == 1) {
conditions.first().toString()
} else {
conditions.joinToString(prefix = "(", postfix = ")", separator = " and ")
}
}
}
The tricky part here is supporting DSL criteria. We declare Condition.eq as an infix String extension function for that. So, we can use it either traditionally like column.eq(value) or without dot and parentheses – column eq value.
The function is defined in a context of the Condition class, that’s why we can use it (remember that SqlSelectBuilder.where receives a function literal which is executed in a context of Condition).
Now we can verify that everything works as expected:
@Test
fun `when a list of conditions is specified then it's respected`() {
doTest("select * from table1 where (column3 = 4 and column4 is null)") {
from ("table1")
where {
"column3" eq 4
"column4" eq null
}
}
}
7. OR Condition
The last part of our exercise is supporting SQL OR conditions. As usual, let’s define how that should look in our DSL first:
query {
from("myTable")
where {
"column1" eq 4
or {
"column2" eq null
"column3" eq 42
}
}
}
Then we’ll provide an implementation. As OR and AND are very similar, we can re-use the existing implementation:
open class CompositeCondition(private val sqlOperator: String) : Condition() {
private val conditions = mutableListOf<Condition>()
override fun addCondition(condition: Condition) {
conditions += condition
}
override fun toString(): String {
return if (conditions.size == 1) {
conditions.first().toString()
} else {
conditions.joinToString(prefix = "(", postfix = ")", separator = " $sqlOperator ")
}
}
}
class And : CompositeCondition("and")
class Or : CompositeCondition("or")
Finally, we’ll add the corresponding support to the conditions sub-DSL:
abstract class Condition {
fun and(initializer: Condition.() -> Unit) {
addCondition(And().apply(initializer))
}
fun or(initializer: Condition.() -> Unit) {
addCondition(Or().apply(initializer))
}
}
Let’s verify that everything works:
@Test
fun `when 'or' conditions are specified then they are respected`() {
doTest("select * from table1 where (column3 = 4 or column4 is null)") {
from ("table1")
where {
or {
"column3" eq 4
"column4" eq null
}
}
}
}
@Test
fun `when either 'and' or 'or' conditions are specified then they are respected`() {
doTest("select * from table1 where ((column3 = 4 or column4 is null) and column5 = 42)") {
from ("table1")
where {
or {
"column3" eq 4
"column4" eq null
}
"column5" eq 42
}
}
}
While out of the scope of this tutorial, the same concepts can be used for expanding our DSL. For example, we could enhance it by adding support for LIKE, GROUP BY, HAVING, ORDER BY. Feel free to post solutions in the comments!
9. Conclusion
In this article, we saw an example of building a simple DSL for SQL queries. It’s not an exhaustive guide, but it establishes a good foundation and provides an overview of the whole Kotlin type-safe DSL approach.
As usual, the complete source code for this article is available over on GitHub.
