Introduction to ExoQuery

Language Integrated Query for Kotlin Multiplatform

• SQL Queries at Compile Time
• Forget eq, use regular EqEq
• Forget Case().When, use regular if and when.
• Forget Column<T>, use regular primitives!
• Cross-Platform: JVM, iOS, Android, Linux, Windows, MacOS, JS-coming soon!
• Functional, Composable, Powerful, and Fun!

Why does querying a database need to be any harder than traversing an array?

Let’s say something like:

people.map { p -> p.name }

Naturally we’re pretty sure it should look something like:

SELECT name
FROM Person

That is why in C# when you write a statement like this:

var q = people.Select(p => p.name);   // Select is C#'s .map function

In LINQ we understand that it’s this:

var q = from p in people
        select p.name

So in Kotlin, let’s just do this:

val q = sql.select {
  val p = from(people)
  p.name
}

Then build it for a specific SQL dialect and run it on a database!

val data: List<Person> = q.buildFor.Postgres().runOn(myDatabase)
//> SELECT p.name FROM Person p

Welcome to ExoQuery!

Don’t databases have complicated things like joins, case-statements, and subqueries?

Let’s take some data:

@Ser
data class Person(val name: String, val age: Int, val companyId: Int)

@Ser
data class Address(val city: String, val personId: Int)

@Ser
data class Company(val name: String, val id: Int)
// Going to use @Ser as a concatenation of @Serializeable for now
val people: SqlQuery<Person> = sql { Table<Person>() }
val addresses: SqlQuery<Address> = sql { Table<Address>() }
val companies: SqlQuery<Company> = sql { Table<Company>() }

Here is a query with some Joins:

sql.select {
  val p: Person = from(people)
  val a: Address = join(addresses) { a -> a.personId == p.id }
  Data(p.name, a.city)
}
//> SELECT p.name, a.city FROM Person p JOIN Address a ON a.personId = p.id

Compared to Microsoft LINQ where it would look like this:

var q = from p in people
        join a in addresses on a.personId == p.id
        select Data(p.name, a.city)

Let’s add some case-statements:

sql.select {
  val p = from(people)
  val a = join(addresses) { a -> a.personId == p.id }
  Data(p.name, a.city, if (p.age > 18) 'adult' else 'minor')
}
//> SELECT p.name, a.city, CASE WHEN p.age > 18 THEN 'adult' ELSE 'minor' END FROM Person p JOIN Address a ON a.personId = p.id

Now let’s try a subquery:

sql.select {
  val (c, p) = from(
    select {
      val c: Company = from(companies)
      val p: Person = join(people) { p -> p.companyId == c.id }
      c to p
    } // -> SqlQuery<Pair<Company, Person>>
  )
  val a: Address = join(addresses) { a -> a.personId == p.id }
  Data(p.name, c.name, a.city)
}
//> SELECT p.name, c.name, a.city FROM (
//   SELECT p.name, p.age, p.companyId FROM Person p JOIN companies c ON c.id = p.companyId
//  ) p JOIN Address a ON a.personId = p.id

Notice how the types compose completely fluidly? The output of a subquery is the same datatype as a table.

How can you use EqEq, or regular if or regular case classes in a DSL?

By using the sql construct to delineate relevant code snippets and a compiler-plugin to transform them, I can synthesize a SQL query the second your code is compiled in most cases.

You can even see it in the build output in a file. Have a look at the build/generated/exoquery directory.

Can I just use normal Kotlin to write Queries?

That’s right! You can use regular Kotlin constructs that you know and love in order to write SQL code including:

• Elvis operators

  people.map { p ->
    p.name ?: "default"
  }
  //> SELECT CASE WHEN p.name IS NULL THEN 'default' ELSE p.name END FROM Person p

• Question marks and nullary .let statements

  people.map { p ->
      p.name?.let { free("mySuperSpecialUDF($it)").asPure<String>() } ?: "default"
  }
  //> SELECT CASE WHEN p.name IS NULL THEN 'default' ELSE mySuperSpecialUDF(p.name) END FROM Person p

• If and When

  people.map { p ->
    when {
      p.age >= 18 -> "adult"
      p.age < 18 && p.age > 10 -> "minor"
      else -> "child"
    } 
  }
  //>  SELECT CASE WHEN p.age >= 18 THEN 'adult' WHEN p.age < 18 AND p.age > 10 THEN 'minor' ELSE 'child' END AS value FROM Person p

• Simple arithmetic, simple functions on datatypes

  @SqlFragment
  fun peRatioWeighted(stock: Stock, weight: Double): Double = sql.expression {
    (stock.price / stock.earnings) * weight
  }
  sql {
    Table<Stock>().map { stock -> peRatioWeighted(stock, stock.marketCap/totalWeight) } 
  }
  //> SELECT (s.price / s.earnings) * s.marketCap / totalWeight FROM Stock s

• Pairs and Tuples

  val query: SqlQuery<Pair<String, String>> = sql {
    Table<Person>().map { p -> p.name to p.age }  
     // or Triple(p.name, p.age, p.companyId), or MyDataClass(p.name, p.age, p.companyId)
  }
  //> SELECT p.name, p.age FROM Person p

  You can use pairs and tuples with the whole row too! In fact, you can output any simple data-class.