Advanced Features

Polymorphic Query Abstraction

Using generics and interfaces to create reusable query components

Continuing from the section on SQL Fragment Functions, captured functions can use generics and polymorphism in order to create highly abstractable query components.

Interface-Based Polymorphism

The simplest form of polymorphism uses interfaces to define common properties across different entity types:

interface Locateable { val locationId: Int }

@Ser data class Person(val name: String, val age: Int, val locationId: Int) : Locateable
@Ser data class Robot(val model: String, val createdOn: LocalDate, val locationId: Int) : Locateable
@Ser data class Address(val id: Int, val street: String, val zip: String)

You can then use this to create a fragment function that works with any Locateable type:

// Create a captured function that works with any Locateable type:
@SqlFragment
fun <L : Locateable> joinLocation(locateable: SqlQuery<L>): SqlQuery<Pair<L, Address>> =
  sql.select {
    val l = from(locateable)
    val a = join(Table<Address>()) { a -> a.id == l.locationId }
    l to a
  }

Now you can use this function with the Person table:

import io.exoquery.*
import kotlinx.serialization.Serializable

interface Locateable {
  val locationId: Int
}

@Serializable
data class Person(val name: String, val age: Int, override val locationId: Int) : Locateable

@Serializable
data class Address(val id: Int, val street: String, val zip: String)

@SqlFragment
fun <L : Locateable> joinLocation(locateable: SqlQuery<L>): SqlQuery<Pair<L, Address>> =
  sql.select {
    val l = from(locateable)
    val a = join(Table<Address>()) { a -> a.id == l.locationId }
    l to a
  }

//sampleStart
// Use joinLocation with Person table
val people: SqlQuery<Pair<Person, Address>> = sql {
  joinLocation(Table<Person>().filter { p -> p.name == "Joe" })
}
//sampleEnd

suspend fun main() = people.buildPrettyFor.Postgres().runSample()

As well as the Robot table:

import io.exoquery.*
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)

// You can then use this to create a fragment function that works with any Locateable type:

// Create a captured function that works with any Locateable type:
@SqlFragment
fun <L : Locateable> joinLocation(locateable: SqlQuery<L>): SqlQuery<Pair<L, Address>> =
  sql.select {
    val l = from(locateable)
    val a = join(Table<Address>()) { a -> a.id == l.locationId }
    l to a
  }

//sampleStart
// Use joinLocation with Robot table
val robots: SqlQuery<Pair<Robot, Address>> = sql {
  joinLocation(Table<Robot>().filter { r -> r.model == "R2D2" })
}
//sampleEnd

suspend fun main() = robots.buildPrettyFor.Postgres().runSample()

Higher-Order Query Functions

Another powerful ExoQuery pattern is creating query functions that accept other queries or filter functions as parameters. This allows you to build composable query pipelines.

@Serializable
data class House(val id: Int, val ownerId: Int, val origin: String, val hasChargingPort: Boolean)

@Serializable
data class PricingTier(val startYear: Int, val endYear: Int, val pricing: String, val premiumMembership: String)

@Serializable
data class CustomerRecord(val name: String, val age: Int, val membership: String, val customerId: Int, val houseId: Int)

Now define a higher-order function that takes a customer query and additional filter/transformation parameters:

@SqlFragment
fun customerWithHousing(
  customers: SqlQuery<Customer>,
  houseFilter: (House) -> Boolean,
  membershipSelector: (Customer, PricingTier) -> String
): SqlQuery<CustomerRecord> =
  sql.select {
    val c = from(customers)
    val h = join(Table<House>()) { h -> h.ownerId == c.id && houseFilter(h) }
    val p = join(Table<PricingTier>()) { p -> 2024 - c.age > p.startYear && c.age < p.endYear }
    CustomerRecord(c.name, c.age, membershipSelector(c, p), c.id, h.id)
  }

Use it with different filter and transformation logic:

import io.exoquery.*
import kotlinx.serialization.*

@Serializable data class Customer(val id: Int, val name: String, val age: Int, val membership: String)
@Serializable data class House(val id: Int, val ownerId: Int, val origin: String, val hasChargingPort: Boolean)
@Serializable data class PricingTier(val startYear: Int, val endYear: Int, val pricing: String, val premiumMembership: String)
@Serializable data class CustomerRecord(val name: String, val age: Int, val membership: String, val customerId: Int, val houseId: Int)

@SqlFragment
fun customerWithHousing(
  customers: SqlQuery<Customer>,
  houseFilter: (House) -> Boolean,
  membershipSelector: (Customer, PricingTier) -> String
): SqlQuery<CustomerRecord> =
  sql.select {
    val c = from(customers)
    val h = join(Table<House>()) { h -> h.ownerId == c.id && houseFilter(h) }
    val p = join(Table<PricingTier>()) { p -> 2024 - c.age > p.startYear && c.age < p.endYear }
    CustomerRecord(c.name, c.age, membershipSelector(c, p), c.id, h.id)
  }

//sampleStart
// Use customerWithHousing with filter and transformation logic
val result = sql {
  customerWithHousing(
    // Pass in a customer query
    Table<Customer>().filter { it.age > 25 },
    // Filter houses - only those with charging ports
    { h: House -> h.hasChargingPort },
    // Select membership based on pricing tier
    { c: Customer, p: PricingTier -> 
      if (p.pricing == "standard") c.membership else p.premiumMembership 
    }
  )
}
//sampleEnd

suspend fun main() = result.buildPrettyFor.Postgres().runSample()

Combining Multiple Entity Types with Union

You can combine queries from different entity types using union operations, then pass the unified result to downstream query functions:

import io.exoquery.*
import kotlinx.serialization.*

@Serializable data class Customer(val id: Int, val name: String, val age: Int, val membership: String)

//sampleStart
@SqlFragment
fun customersByMembership(membership: String): SqlQuery<Customer> =
  sql { Table<Customer>().filter { it.membership == membership } }

// Combine different customer queries with union
val allCustomers = sql {
  customersByMembership("gold") union customersByMembership("platinum")
}
//sampleEnd

suspend fun main() = allCustomers.buildPrettyFor.Postgres().runSample()

Common Union-Type Polymorphism

Even if you do not have a common class or interface, you can still achieve a similar effect as polymorphic query abstraction by creating a common union-type for your different tables and mapping to that. Let's go back to our previous example of Persons and Robots:

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

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

// Create a common union-type that both Person and Robot can be mapped into
@Serializable data class Humanoid(val name: String, val locationId: Int)

@SqlFragment
fun humanoidWithAddress(humanoids: SqlQuery<Humanoid>): SqlQuery<Pair<Humanoid, Address>> =
  sql.select {
    val h = from(humanoids)
    val a = join(Table<Address>()) { a -> a.id == h.locationId }
    h to a
  }

//sampleStart
// Filter and map Person and Robot to a common Humanoid type, then union them
val allHumanoids = sql {
  (
  Table<Person>().filter { it.name == "Joe" }
    .map { Humanoid(it.name, it.locationId) }
  ) union (
  Table<Robot>().filter { it.model == "R2D2" }
    .map { Humanoid(it.model, it.locationId) }
  )
}

// Now pass the unified humanoid query to a downstream function
val humanoidsWithAddresses = sql { humanoidWithAddress(allHumanoids) }
//sampleEnd

suspend fun main() = humanoidsWithAddresses.buildPrettyFor.Postgres().runSample()

The ability to filter different entity types, map them to a common structure, union them together, and then pass the result to downstream query functions—all while ExoQuery generates the correct SQL—is perhaps the most powerful feature of ExoQuery. It enables you to compose complex queries from simple building blocks while maintaining full type safety and database portability.

Key Takeaways

  • Interface polymorphism lets you write generic query functions that work with any entity implementing a common interface
  • Union-type polymorphism allows mapping different entities to a common type for unified processing
  • Higher-order query functions accept queries and expressions as parameters for maximum composability
  • Union operations combine results from different entity types into a unified stream
  • Use @SqlFragment for compile-time query composition and @SqlDynamic when runtime selection is needed

These patterns allow you to build sophisticated, domain-specific query APIs while maintaining type safety and query optimization