:wave: Kotlin crew—stop building SQL with clunky D...

# feed

[Alexander Ioffe]

👋 Kotlin crew—stop building SQL with clunky DSLs and unsafe strings!
Just released*:* my LambdaConf ’25 talk on *ExoQuery*—the compiler-plugin that turns plain Kotlin (including ==, if, when, Elvis, and lambdas) into vendor-perfect SQL _at compile time_—and runs the same query on Postgres, SQLite, or SQL Server across KMP targets!

▶️ Watch the video:

https://www.youtube.com/watch?v=-XDKkBQatvw▾

💻 Grab the code samples: https://github.com/ExoQuery/lambdaconf-2025

Drop your gnarliest query in the thread—let’s see if I can tame it with ExoQuery!

[gildor]

It's super impressive what people would invent just to avoid writing SQL 😄

[andylamax]

I will be adding ExoQuery and SQLDelight plugins so that I may write my koltin code and use ExoQuery to convert that to SQL and then use SQLDelight to generate the necessary kotlin Bindings like a real man

[Alexander Ioffe]

@gildor Have a look at the final part of the talk where I use ExoQuery to build up one of the biggest queries I've seen in my career.

Why don't you send me a gnarly query that you've had to deal with and I'll show you how ExoQuery can make it better.

Chances are it can!

[Alexander Ioffe]

@andylamax I'm working on a schema-first binding and direct SQL Delight integrations. Biggest problem is their driver doesn't expose the underlying session/resultset.

[gildor]

No sarcasm, really impressive.
But again, maybe I'm just burned by ORMs which hide complexity from me and as result I got more problems than solutions

[Alexander Ioffe]

@gildor I've been super-duper-de-duper burned by ORMs... can spend hours talking about my Hibermate/JPA scars.
I'm totally serious about my offer. Post or DM me a gnarly query you've had to deal with and I'll see if I can make it better with ExoQuery.

[andylamax]

This is indeed impressive (Jokes aside). I also share a similar experience as @gildor with ORMs. But we will see

[andylamax]

In regards to your offer, let me if one of the SQLs I have can be generated by ExoQuery

[Alexander Ioffe]

@andylamax If you could post the query or something approximating it I'd be happy to do a demo.

[andylamax]

here it is

CREATE TABLE IF NOT EXISTS ow_core_operational_units(
    uid INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
    name TEXT NOT NULL,
    parent INTEGER,
    leaf BOOLEAN NOT NULL DEFAULT FALSE,

    FOREIGN KEY (parent) REFERENCES ow_core_operational_units(uid) ON DELETE RESTRICT ON UPDATE CASCADE
);

WITH RECURSIVE OperationalUnitAncestors AS (
    SELECT * FROM ow_core_operational_units WHERE uid = ?
    UNION ALL
    SELECT ou.* FROM ow_core_operational_units AS ou
    JOIN OperationalUnitAncestors ON ou.uid = OperationalUnitAncestors.parent
) SELECT * FROM OperationalUnitAncestors;

Not too complicated but even SQLDelight was a bit struggling with it

[Youssef Shoaib [MOD]]

I loved the talk! One point you mentioned is that we don't have for-comprehensions in Kotlin. That's true, but we have something even better! suspend allows us to get delimited continuations, which can be massaged (and with a bit of reflection, or a Gradle plugin to make continuations shallow-clonable) can be made multishot. This allows for direct-style code. I've been working on exactly that, and I wonder if it somehow can simplify or replace the need for a compiler plugin for ExoQuery entirely! == likely wouldn't work because it can't be made suspend, but a lot of other constructs in Kotlin can.

I'll try to make some ExoQuery-like demo to see if this is possible w/o compiler plugin. I'm pretty sure a plugin is necessary to prevent e.g. spurious println inside of the queries, but theoretically that plugin would be just a checker, and wouldn't need to do anything to the generated bytecode

[Alexander Ioffe]

@andylamax ExoQuery doesn't support recursive CTEs yet but if you're willing to "wing it" with free blocks you can do something like this:

data class OwCoreOperationalUnits(val uid: Int, val name: String, val parent: Int? = null, val leaf: Boolean = false)
data class OperationalUnitAncestors(val uid: Int, val name: String, val parent: Int? = null, val leaf: Boolean = false)

val q = capture {
  free("WITH RECURSIVE OperationalUnitAncestors AS (${
    Table<OwCoreOperationalUnits>().filter { it.uid == param(initialUid) }
    unionAll
    select {
      val ou = from(Table<OwCoreOperationalUnits>())
      val oa = join(Table<OperationalUnitAncestors>()) { oa -> oa.parent == ou.uid }
      ou
    }
  })")<SqlQuery<OperationalUnitAncestors>>()
}

It produces this:

WITH RECURSIVE OperationalUnitAncestors AS (
  (
    SELECT
      it.uid AS uid,
      it.name AS name,
      it.parent AS parent,
      it.leaf AS leaf
    FROM
      OwCoreOperationalUnits it
    WHERE
      it.uid = ?
  )
  UNION ALL
  (
    SELECT
      ou.uid AS uid,
      ou.name AS name,
      ou.parent AS parent,
      ou.leaf AS leaf
    FROM
      OwCoreOperationalUnits ou
      INNER JOIN OperationalUnitAncestors oa ON oa.parent = ou.uid
  )
)

That looks right to me.
I'm surprised SQL Delight doesn't like this query. Was the parser having an issue?

[Alexander Ioffe]

@Youssef Shoaib [MOD] I've got an old version of the ExoQuery codebase laying around somewhere in git history that uses @Alejandro Serrano.Mena's Inikio library to fuse the capture.select clauses of whatever I called it back then. The hardest thing to do with that approach would be to produce the SQL queries at compile-time. Theoretically with a introspection-only plugin you could traverse out to all of the IrGet and IrFunction instances involved in a capture call well enough to collect all of that information but it would be an IR traversal nightmare. There's a reason I'm using my own homegrown deconstructive pattern-matching library instead of using the various IR visitors. Building this kind of product is certainly possible with just IR visitors + traversals but in practice it's like pulling teeth (I've tried it). Unless you're a billion-dollar corp like Microsoft that can throw a legion of devs on it (that's how they got C# LINQ), you won't have the sanity or the time to do it that way.

[Youssef Shoaib [MOD]]

Inikio is a great example of what I'm talking about, although I think Kontinuity can go further (especially with multishot)!
I wonder if compile-time generation is truly required though. If we generate at runtime for the first call and cache the query I think it can be fine. It could also be done with a Gradle task. I'm not fully convinced that full code introspection is required to produce SQL. Perhaps some custom types may be needed (e.g. an ExoString with all operations being suspend). User defined types can either use those custom types, or they can maybe have suspend functions.
I think ExoQuery is definitely the end-game though in terms of clarity! I'm just wondering if one can get pretty close without needing to, you know, maintain a compiler plugin and all that. Maybe a combo of suspend, a KSP plugin, and an optional plugin that produces compile-time errors on a best-effort basis (but doesn't affect code generation) could be sufficient.

Anyways, I'll refrain from brain-dumping here and instead try to actually make a PoC!

[Alexander Ioffe]

> I'm just wondering if one can get pretty close without needing to, you know, maintain a compiler plugin and all that.
Heh, it's been a continual conundrum of mine for the better part of the last decade wherein I maintained a similar product called Quill in the Scala space. I think the recipe for this kind of product is very temperamental, the second you try to modify the ingredients, unexpected oddities start to happen (like the entire Ast encoding being open as opposed to closed).

In terms of long-term support, I'm hoping that by virtue of launching a set of commercial extensions to ExoQuery later this year (I mentioned the OSS vs PRO features at the end of the talk) I'll be able to use some hired-help and ultimately have the ability to work on ExoQuery full time. Will see how it goes.