KotliQuery

The .asSingle method returns an optional single value. By default, if the query returns multiple rows, it silently returns only the first row.

To ensure an error is thrown if multiple rows are returned, initialize your Session with strict = true.

// Enable strict mode to catch multiple rows in asSingle
val session = Session(HikariCP.dataSource(), strict = true)

val aliceQuery = queryOf("select id, name, created_at from members where name = ?", "Alice").map(toMember).asSingle
val alice: Member? = session.run(aliceQuery)

To use KotliQuery in your Kotlin project, add the following dependencies to your build.gradle file. Ensure you also include a JDBC driver (like H2) for your database.

// Example build.gradle snippet
dependencies {
    implementation 'org.jetbrains.kotlin:kotlin-stdlib:1.7.20'
    implementation 'com.github.seratch:kotliquery:1.9.0'
    implementation 'com.h2database:h2:2.1.214'
}

dependencies {
    implementation "org.jetbrains.kotlin:kotlin-stdlib:$kotlin_version"
    implementation 'com.github.seratch:kotliquery:1.9.0'
    implementation 'com.h2database:h2:2.1.214'
}

To retrieve data, follow these three steps:

1. Create a Query object using queryOf(sql, params...).
2. Attach a mapping function using .map { row -> ... } to transform a Row into your desired type.
3. Specify the result type using .asList (for multiple rows) or .asSingle (for zero or one row).

Example: Mapping to a Data Class

data class Member(
  val id: Int,
  val name: String?,
  val createdAt: java.time.ZonedDateTime
)

val toMember: (Row) -> Member = { row -> 
  Member(
    row.int("id"), 
    row.stringOrNull("name"), 
    row.zonedDateTime("created_at")
  )
}

val allMembersQuery = queryOf("select id, name, created_at from members").map(toMember).asList
val allMembers: List<Member> = session.run(allMembersQuery)

For production environments, use HikariCP to manage a connection pool. KotliQuery provides a helper to initialize a HikariCP data source.

HikariCP.default("jdbc:h2:mem:hello", "user", "pass")

sessionOf(HikariCP.dataSource()).use { session ->
   // working with the session
}

To avoid loading an entire result set into memory, use the .forEach method on a Session. This allows you to process each row one by one.

session.forEach(queryOf("select id from members")) { row ->
  // process each row individually
}

session.forEach(queryOf("select id from members")) { row ->
  // working with large data set
}

When using positional parameters (?), you can explicitly specify the Java type to help KotliQuery bind parameters correctly (useful for certain databases like PostgreSQL).

Use the Parameter class or the .param<T>() helper method.

// Using Parameter class
val param = Parameter(someValue, String::class.java)
queryOf("select id from members where ? is null or ? = name", param, param)

// Using the .param<T>() helper (recommended)
queryOf(
  "select id, name from members where ? is null or ? = name",
  null.param<String>(),
  null.param<String>()
)

Use session.run() combined with specific query execution modes to perform DDL (Data Definition Language) or DML (Data Manipulation Language) operations.

• DDL: Use .asExecute (returns Boolean).
• DML (Insert/Update/Delete): Use .asUpdate (returns the number of affected rows).

// DDL Example
session.run(queryOf("create table members (id serial not null primary key, name varchar(64), created_at timestamp not null)").asExecute)

// Update Example
val insertQuery = "insert into members (name, created_at) values (?, ?)"
session.run(queryOf(insertQuery, "Alice", Date()).asUpdate)

You can use named parameters starting with : in your SQL strings. This improves readability and allows you to reuse the same parameter multiple times in a single query. KotliQuery uses prepared statements internally, so this is safe from SQL injection.

queryOf(
  """
  select id, name, created_at 
  from members 
  where (name = :name) and (age = :age)
  """, 
  mapOf("name" to "Alice", "age" to 20)
)

A Session is a thin wrapper around a java.sql.Connection. You can create one using sessionOf by providing the JDBC URL, username, and password.

import kotliquery.*

val session = sessionOf("jdbc:h2:mem:hello", "user", "pass")