June 15, 2025 • By Alexander Ioffe

SQL badly needs Polymorphism. ExoQuery brings it.

How I went from tortuously copying SQL queries for every new business unit to writing them once with Interface-Based Polymorphism. A story of sanity regained.

SQLpolymorphismarchitecturebest-practices

The 30-Second Version

SQL has no polymorphism. There is no better alternative in SQL than copying a query because of that.
ExoQuery provides interface-based polymorphism for SQL. Write query logic once, reuse it across different entity types.
I built ExoQuery because I got tired of maintaining dozens of nearly-identical queries. This post is the beginning of that story.

Want to try it now? Have a look at Interface Based Polymorphism in the examples section.
... or just keep reading. Many code samples below are interactive, edit and run them live.

In the Beginning, There Was a Query

I had a product. It was supposed to serve up an API endpoint.
It was a query.
It was simple.
Life was good.

-- Simple query for Human customers with their addresses
SELECT h.*, a.*
FROM Human h
JOIN Address a ON a.id = h.locationId
WHERE h.segment = 'enterprise'

And then it got more complex. And more complex. And more complex. Joins were added. Filters were added. Business logic was added. But it was still manageable. One query, one business unit, one developer who could still maintain his database logic and come home in time for dinner.

Then Came the Second Business Unit

"Hey, we need to support Robots too."

Okay, what do we do? Well, as in all products using SQL... we copy the whole Query and make a tiny change. There is no better alternative in SQL than copying a query, not CTEs, not UDFs, not table-returning UDFs, and certainly not Stored Procedures. I will go head-to-head with any enterprise architect who claims otherwise, because I have tried all of the above things. To paraphrase Trinity from The Matrix: "I know all of these roads and I know exactly where they end."

Then there's a darker path, the path of composing queries by concatenating strings at runtime. I also know where that road ends and I can attest to the fact that it far worse than all the others. I'll dedicate several future blog posts to it.

So I chose the least of the existing evils and copied the SQL code. I ended up with something like this:

-- ──────── Query for Humans ──────────────────────────────
SELECT h.*, a.*
FROM Human h
JOIN Address a ON a.id = h.locationId
-- 4 other Joins
WHERE h.name = 'Someone' /* 50 more lines of SQL */

-- ──────── Query for Robots (copied and modified) ────────
SELECT r.*, a.*
FROM Robot r
JOIN Address a ON a.id = r.locationId
-- 4 other Joins
WHERE r.model LIKE 'R2%' /* 50 more lines of SQL, mostly identical */

Nearly identical structure. Different tables. Different filters. Same join logic. Same pain.

Then Came the Third Business Unit

"Great news! We're expanding our product line to serve Yetis!"

And now I'm chewing my own skin off because of all the crazy copied complexity. Three queries. Three sets of tests. Three places to update when the address join logic changes. Three opportunities for bugs to creep in when someone (or some LLM) updates one query but forgets the others.

//sampleStart
-- ──────── Query for Humans ──────────────────────────────
SELECT h.*, a.*
FROM Human h
JOIN Address a ON a.id = h.locationId
-- 4 other Joins
WHERE h.name = 'Someone' /* 50 more lines of SQL */

-- ──────── Query for Robots (copied and modified) ────────
SELECT r.*, a.*
FROM Robot r
JOIN Address a ON a.id = r.locationId
-- 4 other Joins
WHERE r.model LIKE 'R2%' /* 50 more lines of SQL, mostly identical */

-- ──────── Query for Yetis (copied and modified) ─────────
SELECT y.*, a.*
FROM Yeti y
JOIN Address a ON a.id = y.locationId
-- 4 other Joins
WHERE y.furColor = 'white' /* 50 more lines of SQL, mostly identical */

//sampleEnd

-- Here's what the other 150 lines could look like...

-- ============================================================
-- Query for Humans (50 lines)
-- ============================================================
SELECT 
  h.id AS human_id,
  h.name AS human_name,
  h.age,
  h.segment,
  h.created_at AS human_created,
  a.id AS address_id,
  a.street,
  a.city,
  a.state,
  a.zip,
  a.country,
  a.region,
  p.base_rate,
  p.tax_rate,
  p.discount_tier,
  p.currency,
  (p.base_rate * (1 + p.tax_rate)) AS effective_rate,
  i.item_count,
  i.reserved_count,
  i.last_restocked,
  i.warehouse_code,
  (i.item_count - i.reserved_count) AS available_count,
  CASE 
    WHEN (i.item_count - i.reserved_count) > 100 THEN 'high'
    WHEN (i.item_count - i.reserved_count) > 20 THEN 'medium'
    ELSE 'low'
  END AS availability_status,
  COALESCE(order_summary.total_orders, 0) AS total_orders,
  COALESCE(order_summary.total_spent, 0.0) AS total_spent
FROM Human h
INNER JOIN Address a ON a.id = h.locationId
INNER JOIN Pricing p ON p.region = a.region
INNER JOIN Inventory i ON i.locationId = h.locationId
LEFT JOIN (
  SELECT customer_id, COUNT(*) AS total_orders, SUM(amount) AS total_spent
  FROM Orders WHERE customer_type = 'human'
  GROUP BY customer_id
) order_summary ON order_summary.customer_id = h.id
WHERE h.segment = 'enterprise'
  AND h.age >= 18
  AND a.country = 'US'
  AND p.discount_tier IN ('gold', 'platinum')
  AND i.item_count > 0
  AND (i.item_count - i.reserved_count) > 5
ORDER BY h.name ASC, order_summary.total_spent DESC NULLS LAST
LIMIT 1000;

-- ============================================================
-- Query for Robots (50 lines, mostly identical structure!)
-- ============================================================
SELECT 
  r.id AS robot_id,
  r.model AS robot_model,
  r.serial_number,
  r.firmware_version,
  r.created_on AS robot_created,
  a.id AS address_id,
  a.street,
  a.city,
  a.state,
  a.zip,
  a.country,
  a.region,
  p.base_rate,
  p.tax_rate,
  p.discount_tier,
  p.currency,
  (p.base_rate * (1 + p.tax_rate)) AS effective_rate,
  i.item_count,
  i.reserved_count,
  i.last_restocked,
  i.warehouse_code,
  (i.item_count - i.reserved_count) AS available_count,
  CASE 
    WHEN (i.item_count - i.reserved_count) > 100 THEN 'high'
    WHEN (i.item_count - i.reserved_count) > 20 THEN 'medium'
    ELSE 'low'
  END AS availability_status,
  COALESCE(order_summary.total_orders, 0) AS total_orders,
  COALESCE(order_summary.total_spent, 0.0) AS total_spent
FROM Robot r
INNER JOIN Address a ON a.id = r.locationId
INNER JOIN Pricing p ON p.region = a.region
INNER JOIN Inventory i ON i.locationId = r.locationId
LEFT JOIN (
  SELECT customer_id, COUNT(*) AS total_orders, SUM(amount) AS total_spent
  FROM Orders WHERE customer_type = 'robot'
  GROUP BY customer_id
) order_summary ON order_summary.customer_id = r.id
WHERE r.model LIKE 'R2%'
  AND r.firmware_version >= '3.0'
  AND a.country = 'US'
  AND p.discount_tier IN ('gold', 'platinum')
  AND i.item_count > 0
  AND (i.item_count - i.reserved_count) > 5
ORDER BY r.model ASC, order_summary.total_spent DESC NULLS LAST
LIMIT 1000;

-- ============================================================
-- Query for Yetis (50 lines, same structure, yet again!)
-- ============================================================
SELECT 
  y.id AS yeti_id,
  y.mountain_name,
  y.fur_color,
  y.altitude_preference,
  y.registered_on AS yeti_created,
  a.id AS address_id,
  a.street,
  a.city,
  a.state,
  a.zip,
  a.country,
  a.region,
  p.base_rate,
  p.tax_rate,
  p.discount_tier,
  p.currency,
  (p.base_rate * (1 + p.tax_rate)) AS effective_rate,
  i.item_count,
  i.reserved_count,
  i.last_restocked,
  i.warehouse_code,
  (i.item_count - i.reserved_count) AS available_count,
  CASE 
    WHEN (i.item_count - i.reserved_count) > 100 THEN 'high'
    WHEN (i.item_count - i.reserved_count) > 20 THEN 'medium'
    ELSE 'low'
  END AS availability_status,
  COALESCE(order_summary.total_orders, 0) AS total_orders,
  COALESCE(order_summary.total_spent, 0.0) AS total_spent
FROM Yeti y
INNER JOIN Address a ON a.id = y.locationId
INNER JOIN Pricing p ON p.region = a.region
INNER JOIN Inventory i ON i.locationId = y.locationId
LEFT JOIN (
  SELECT customer_id, COUNT(*) AS total_orders, SUM(amount) AS total_spent
  FROM Orders WHERE customer_type = 'yeti'
  GROUP BY customer_id
) order_summary ON order_summary.customer_id = y.id
WHERE y.fur_color = 'white'
  AND y.altitude_preference > 3000
  AND a.country = 'US'
  AND p.discount_tier IN ('gold', 'platinum')
  AND i.item_count > 0
  AND (i.item_count - i.reserved_count) > 5
ORDER BY y.mountain_name ASC, order_summary.total_spent DESC NULLS LAST
LIMIT 1000;

150 lines of SQL where there should be 60. And every time the business logic changes, I have to update all three. In perfect synchronization. Forever.

Want to know what these 50 lines could look like? Press the + button above if you're brave enough.

If only SQL Had Polymorphism...

If SQL had polymorphism, it would look something like this:

-- Hypothetical polymorphic SQL (this doesn't exist)
CREATE POLYMORPHIC FUNCTION getEntitiesWithAddress<T: Locateable>(
  filter: T -> Boolean
) RETURNS TABLE AS
  SELECT * FROM T 
  WHERE filter(T)
  JOIN Address ON Address.id = T.locationId
  -- 4 other Joins

With a function like this, I'd go from three copies to one. Abstract out the common parts, put them in getEntitiesWithAddress, and call it like this:

SELECT * FROM getEntitiesWithAddress<Human>(h -> h.name = 'Someone')
SELECT * FROM getEntitiesWithAddress<Robot>(r -> r.model LIKE 'R2%')
SELECT * FROM getEntitiesWithAddress<Yeti>(y -> y.furColor = 'white')

...but no, there is no such thing. SQL has no polymorphism and it probably never will because it does not have a type system.

Enter ExoQuery

This is what I built ExoQuery for. To be the polymorphic API that SQL should have had.

All we do is define a common interface, and then write our query logic once.

Here's what that looks like in Kotlin:

// Define the common interface
interface Locateable { val locationId: Int }

@Serializable data class Human(...) : Locateable
@Serializable data class Robot(...) : Locateable
@Serializable data class Yeti(...) : Locateable

@SqlFragment
fun <T : Locateable> withAddress(entities: SqlQuery<T>): SqlQuery<Pair<T, Address>> =
  sql.select {
    val e = from(entities)
    val a = join(Table<Address>()) { a -> a.id == e.locationId }
    e to a
  }

Now I can reuse this function for any Locateable type. Here it is for Humans:

```
import io.exoquery.*
import io.exoquery.annotation.SqlFragment
import kotlinx.serialization.*
import kotlinx.datetime.LocalDate

// Define the common interface
interface Locateable { val locationId: Int }

@Serializable data class Human(val name: String, val age: Int, override val locationId: Int) : Locateable
@Serializable data class Robot(val model: String, @Contextual val createdOn: LocalDate, override val locationId: Int) : Locateable
@Serializable data class Yeti(val mountainName: String, val furColor: String, override val locationId: Int) : Locateable
@Serializable data class Address(val id: Int, val street: String, val zip: String)


// Write the join logic ONCE
@SqlFragment
fun  withAddress(entities: SqlQuery): SqlQuery> =
  sql.select {
    val e = from(entities)
    val a = join(Table()) { a -> a.id == e.locationId }
    e to a
  }

//sampleStart
val humansWithAddresses = sql { 
  withAddress(Table().filter { it.name = 'Someone' }) 
}
//sampleEnd

suspend fun main() = humansWithAddresses.buildPrettyFor.Postgres().runSample()
```
SELECT
  it.name,
  it.age,
  it.locationId,
  a.id,
  a.street,
  a.zip
FROM
  Human it
  INNER JOIN Address a ON a.id = it.locationId
WHERE
  it.age > 25

Now for Robots:

```
import io.exoquery.*
import io.exoquery.annotation.SqlFragment
import kotlinx.serialization.*
import kotlinx.datetime.LocalDate

interface Locateable { val locationId: Int }

@Serializable data class Robot(val model: String, @Contextual val createdOn: LocalDate, override val locationId: Int) : Locateable
@Serializable data class Address(val id: Int, val street: String, val zip: String)

@SqlFragment
fun  withAddress(entities: SqlQuery): SqlQuery> =
  sql.select {
    val e = from(entities)
    val a = join(Table()) { a -> a.id == e.locationId }
    e to a
  }

//sampleStart
// Same function, now with Robots!
val robotsWithAddresses = sql { 
  withAddress(Table().filter { it.model == "R2D2" }) 
}
//sampleEnd

suspend fun main() = robotsWithAddresses.buildPrettyFor.Postgres().runSample()
```
SELECT
  it.model,
  it.createdOn,
  it.locationId,
  a.id,
  a.street,
  a.zip
FROM
  Robot it
  INNER JOIN Address a ON a.id = it.locationId
WHERE
  it.model = 'R2D2'

And yes, Yetis too:

```
import io.exoquery.*
import io.exoquery.annotation.SqlFragment
import kotlinx.serialization.*

interface Locateable { val locationId: Int }

@Serializable data class Yeti(val mountainName: String, val furColor: String, override val locationId: Int) : Locateable
@Serializable data class Address(val id: Int, val street: String, val zip: String)

@SqlFragment
fun  withAddress(entities: SqlQuery): SqlQuery> =
  sql.select {
    val e = from(entities)
    val a = join(Table()) { a -> a.id == e.locationId }
    e to a
  }

//sampleStart
val yetisWithAddresses = sql { 
  withAddress(Table().filter { it.furColor == "white" }) 
}
//sampleEnd

suspend fun main() = yetisWithAddresses.buildPrettyFor.Postgres().runSample()
```
SELECT
  it.mountainName,
  it.furColor,
  it.locationId,
  a.id,
  a.street,
  a.zip
FROM
  Yeti it
  INNER JOIN Address a ON a.id = it.locationId
WHERE
  it.furColor = 'white'

What Changed

One function instead of three copied queries. The compiler ensures withAddress only works with Locateable types. I can chain it with other query functions. When the join logic changes, I change it once.

Before: 3 queries × 50 lines = 150 lines of synchronized complexity
After: 1 function × 10 lines = 10 lines, used 3 ways

ExoQuery generates the kind of SQL I'd write by hand. No nested subqueries unless necessary. No mysterious aliases. No x2, x3, x47 variables that I have to trace through multiple layers at 3 AM in production.

I press compile, I see exactly what will be executed. I can give it to my DBAs. I can say "make an index on this", or "tune this part" because I know exactly what the query will be in advance.

Composing Polymorphic Functions

This gets interesting when chaining these polymorphic functions. What if I need to add pricing logic? What if I need to join with inventory? What if the business comes back with "actually, we need to support Sasquatch too"?

I define the data class, implement Locateable, expand my polymorphic function with pricing and inventory joins, and every business unit gets the new capability automatically. No more copying. No more synchronization nightmares.

import io.exoquery.*
import io.exoquery.annotation.SqlFragment
import kotlinx.serialization.*
import kotlinx.datetime.LocalDate

interface Locateable { val locationId: Int }

// All four entity types implement Locateable
@Serializable data class Human(val name: String, val age: Int, override val locationId: Int) : Locateable
@Serializable data class Robot(val model: String, @Contextual val createdOn: LocalDate, override val locationId: Int) : Locateable
@Serializable data class Yeti(val mountainName: String, val furColor: String, override val locationId: Int) : Locateable
@Serializable data class Sasquatch(val forestName: String, val shoeSize: Int, override val locationId: Int) : Locateable

// Supporting tables
@Serializable data class Address(val id: Int, val street: String, val zip: String, val region: String)
@Serializable data class Pricing(val region: String, val baseRate: Double, val taxRate: Double)
@Serializable data class Inventory(val locationId: Int, val itemCount: Int, val lastRestocked: String)

// Result type with all the joined data
@Serializable data class FullRecord<T>(
  val entity: T, val address: Address, val pricing: Pricing, val inventory: Inventory
)

// ONE polymorphic function handles address, pricing, AND inventory for ANY Locateable!
@SqlFragment
fun <T : Locateable> withFullDetails(entities: SqlQuery<T>): SqlQuery<FullRecord<T>> =
  sql.select {
    val e = from(entities)
    val a = join(Table<Address>()) { it.id == e.locationId }
    val p = join(Table<Pricing>()) { it.region == a.region }
    val i = join(Table<Inventory>()) { it.locationId == e.locationId }
    FullRecord(e, a, p, i)
  }

// Overloaded filter functions - each entity type has its own filtering logic
@SqlFragment @JvmName("namedHuman") fun named(q: SqlQuery<Human>, name: String) = sql { q.filter { it.name == name } }
@SqlFragment @JvmName("namedRobot") fun named(q: SqlQuery<Robot>, model: String) = sql { q.filter { it.model == model } }
@SqlFragment @JvmName("namedYeti") fun named(q: SqlQuery<Yeti>, furColor: String) = sql { q.filter { it.furColor == furColor } }
@SqlFragment @JvmName("namedSasquatch") fun named(q: SqlQuery<Sasquatch>, forestName: String, shoeSize: Int) = sql { q.filter { it.forestName == forestName && it.shoeSize == shoeSize } }

//sampleStart
// All four business units get address + pricing + inventory with ONE function!
val humans = sql { withFullDetails(named(Table<Human>(), "Someone")) }
val robots = sql { withFullDetails(named(Table<Robot>(), "R2D2")) }
val yetis = sql { withFullDetails(named(Table<Yeti>(), "white")) }
val sasquatches = sql { withFullDetails(named(Table<Sasquatch>(), "Klamath-Siskiyou", 20)) }
//sampleEnd

suspend fun main() {
  humans.buildPrettyFor.Postgres().runSample("Humans:")
  robots.buildPrettyFor.Postgres().runSample("Robots:")
  yetis.buildPrettyFor.Postgres().runSample("Yetis:")
  sasquatches.buildPrettyFor.Postgres().runSample("Sasquatches:")
}

-- Humans:
SELECT e.name, e.age, e.locationId, a.id, a.street, a.zip, a.region,
       p.region, p.baseRate, p.taxRate, i.locationId, i.itemCount, i.lastRestocked
FROM Human e
INNER JOIN Address a ON a.id = e.locationId
INNER JOIN Pricing p ON p.region = a.region
INNER JOIN Inventory i ON i.locationId = e.locationId
WHERE e.age >= 18
===
-- Robots:
SELECT e.model, e.createdOn, e.locationId, a.id, a.street, a.zip, a.region,
       p.region, p.baseRate, p.taxRate, i.locationId, i.itemCount, i.lastRestocked
FROM Robot e
INNER JOIN Address a ON a.id = e.locationId
INNER JOIN Pricing p ON p.region = a.region
INNER JOIN Inventory i ON i.locationId = e.locationId
WHERE e.model LIKE 'R2%'
===
-- Yetis:
SELECT e.mountainName, e.furColor, e.locationId, a.id, a.street, a.zip, a.region,
       p.region, p.baseRate, p.taxRate, i.locationId, i.itemCount, i.lastRestocked
FROM Yeti e
INNER JOIN Address a ON a.id = e.locationId
INNER JOIN Pricing p ON p.region = a.region
INNER JOIN Inventory i ON i.locationId = e.locationId
WHERE e.furColor = 'white'
===
-- Sasquatches:
SELECT e.forestName, e.shoeSize, e.locationId, a.id, a.street, a.zip, a.region,
       p.region, p.baseRate, p.taxRate, i.locationId, i.itemCount, i.lastRestocked
FROM Sasquatch e
INNER JOIN Address a ON a.id = e.locationId
INNER JOIN Pricing p ON p.region = a.region
INNER JOIN Inventory i ON i.locationId = e.locationId
WHERE e.shoeSize > 20

Why I Built ExoQuery

SQL has no polymorphism. It probably never will. So I built ExoQuery to be the polymorphism for SQL. It generates queries at compile time, flattens nested queries for better database performance, and catches type errors before they reach production.

Links

Run the live example — edit and execute live in your browser. Click "Download as Gradle project" to get a full ExoQuery gradle project.

Higher-order query functions — more patterns for composing queries.

Full documentation — the complete reference.