Parameters

ExoQuery builds on top of kotlinx.serialization in order to encode/decode information into SQL prepared-statements and result-sets. The param function param is used to bring runtime data into capture functions which are processed at compile-time. It does this in an SQL-injection-proof fashion by using parameterized queries on the driver-level.

val runtimeName = "Joe"
val q = sql { Table<Person>().filter { p -> p.name == param(runtimeName) } }
q.buildFor.Postgres().runOn(myDatabase)
//> SELECT p.id, p.name, p.age FROM Person p WHERE p.name = ?

param

The following data-types can be used with param

• Primitives: String, Int, Long, Short, Byte, Float, Double, Boolean
• Time Types: java.util.Date, LocalDate, LocalTime, LocalDateTime, ZonedDateTime, Instant, OffsetTime, OffsetDateTime
• Kotlin Multiplatform Time Types: kotlinx.datetime.LocalDate, kotlinx.datetime.LocalTime, kotlinx.datetime.LocalDateTime, kotlinx.datetime.Instant
• SQL Time Types: java.sql.Date, java.sql.Timestamp, java.sql.Time
• Other: BigDecimal, ByteArray
• Note that in all the time-types Nano-second granularity is not supported. It will be rounded to the nearest millisecond.

Note that for Kotlin native things like java.sql.Date and java.sql.Time do not exist. Kotlin Multiplatform uses kotlinx.datetime objects instead.

params

If you want to do in-set SQL checks with runtime collections, use the params function:

val runtimeNames = listOf("Joe", "Jim", "Jack")
val q = sql { Table<Person>().filter { p -> p.name in params(runtimeNames) } }
q.buildFor.Postgres().runOn(myDatabase)
//> SELECT p.id, p.name, p.age FROM Person p WHERE p.name IN (?, ?, ?)

Internally this is handled almost the same way as param. It finds an appropriate kotlinx.serialization Serializer and uses it to encode the collection into a SQL prepared-statement.

(Note are also paramsCustom and paramsCtx functions that are analogous to paramCustom and paramCtx described below.)

paramCustom

If you want to use a custom serializer for a specific type, you can use the paramCustom function. This is frequently useful when you want to map structured-data to a primitive type:

// Define a serializer for the custom type
object EmailSerializer : KSerializer<Email> {
  override val descriptor: SerialDescriptor = PrimitiveSerialDescriptor("Email", PrimitiveKind.STRING)
  override fun serialize(encoder: Encoder, value: Email) = encoder.encodeString(value.value)
  override fun deserialize(decoder: Decoder) = Email(decoder.decodeString())
}

val email: Email = Email.safeEncode("joe@joesplace.com")
val q = sql {
  Table<User>().filter { p -> p.email == paramCustom(email, EmailSerializer) }
}

If you’re wondering what the User class looks like, remember that using Kotlin serialization, this kind of data most likely uses a property-based-serialization annotation!

@Serializable
data class User(
  val id: Int,
  val name: String,
  @Serializable(with = EmailSerializer::class)
  val email: Email
)

paramCtx

The paramCtx function allows you to use contextual-serialization for a specific type. Essentially this is like telling ExoQuery, and kotlinx.serialization “don’t worry about not having a serializer here… I got this.” This means that you eventually need to provide a low-level Database-Driver encoder/decoder pair into Database-Controller that you are going to use.

Let’s say for example that you have a highly customized encoded type that can only be processed correctly at a low level.

data class ByteContent(val bytes: InputStream) {
  companion object {
    fun bytesFrom(input: InputStream) = ByteContent(ByteArrayInputStream(input.readAllBytes()))
  }
}

Then when creating the Database-Controller, provide a low-level encoder/decoder pair:

val myDatabase = object : JdbcControllers.Postgres(postgres.postgresDatabase) {
  override val additionalDecoders =
    super.additionalDecoders + JdbcDecoderAny { ctx, i -> ByteContent(ctx.row.getBinaryStream(i)) }
  override val additionalEncoders =
    super.additionalEncoders + JdbcEncoderAny(Types.BLOB) { ctx, v: ByteContent, i ->
      ctx.stmt.setBinaryStream(
        i,
        v.bytes
      )
    }
}

Then you can execute queries using ByteContent instances like this:

val bc: ByteContent = ByteContent.bytesFrom(File("myfile.txt").inputStream())
val q = sql {
  Table<MyBlobTable>().filter { b -> b.content == paramCtx(bc) }
}
q.buildFor.Postgres().runOn(myDatabase)

If you are wondering how what MyBlobTable looks like, it is a simple data-class with a ByteContent field that specifies a contextual-serializer. This is in fact required so that you can get instances of MyBlobTable out of the database.

@Serializable
data class Image(val id: Int, @Contextual val content: ByteContent)

Without this q.buildFor.Postgres() will work but .runOn(myDatabase) will not.