Documentation

Query4k

---

The following features are included in the Query4k class:

• Execute
• Execute and return auto-generated keys
• Execute and get auto generated key(s) (experimental)
• Query
• Query first
• Query only
• Open transaction

---

Creating an instance of Query4k

This is done using the .create() methods. There are three ways to use it.

Using an URL

val query4k = Query4k.create("postgresql://postgres:postgres@localhost:5432/postgres")

Using a data source

val datasource: DataSource = HikariDataSource(
    HikariConfig().apply {
        jdbcUrl = "jdbc:postgresql://localhost:5432/my-database"
        username = "postgres"
        password = "postgres"
        driverClassName = "org.postgresql.Driver"
    }
)
val query4k = Query4k.create(datasource)

Using a JDBI instance

val query4k = Query4k.create(Jdbi.create(...))

---

Execute and its variations

There are three ways to execute a SQL statement; execute, executeAndReturnAutoGeneratedKeys, and the experimental executeGetKey<A>. All three take a SQL statement, along with an optional parameter map.

// Returns the number of changed rows.
query4k.execute(
    "INSERT INTO my_table (column) VALUES (:someValue)",
    mapOf("someValue" to 42)
)

// Returns a list of all auto-generated keys in all changed rows
query4k.executeAndReturnAutoGeneratedKeys(
    "INSERT INTO my_table (column) VALUES (:someValue)",
    mapOf("someValue" to 42)
)

// Currently experimental! Returns the selected key in a single changed row.
query4k.executeGetKey<Long>(
    "INSERT INTO my_table (column) VALUES (:someValue)",
    "id",
    mapOf("someValue" to 42)
)

// Injection-unsafe insert, required by for example UUID

query4k.execute(
    "INSERT INTO my_table (column) VALUES ($myUUID)",
    mapOf()
)

Query and its variations

Three variations of the query function exist. The simple query<A>, which gives you a list of all results, queryFirst<A>, which simply just gives you the first result should more than one result exist, and finally queryOnly<A>, which will only give you one result, and only if there is only one result. The nice thing with the query functions is that they will automatically map the results to your desired model data class. Do be aware that this requires your model data class to match the table, as well as the columns in the returned row.

One important detail is that to use any query function you must first have your model defined. Models in query4k are simply serializable data classes. If your application already has data classes, all you have to do is wrap them with @Serializable.

@Serializable
data class Person(
    val id: Long,
    val name: String
)

This data class assumes that your schema looks something like this (PSQL):

CREATE TABLE people (
    id BIGSERIAL PRIMARY KEY NOT NULL,
    name VARCHAR NOT NULL
);

Here is how you use the query functions.

val allPeople: List<Person> = query4k.query<Person>("SELECT * FROM people")

val firstPerson: Person = query4k.queryFirst<Person>(
    "SELECT * FROM people WHERE name=:name",
    mapOf("name" to "Bob")
)

val singlePerson: Either<QueryOnlyException, Person> = query4k.queryOnly<Person>(
    "SELECT * FROM people WHERE id=:id",
    mapOf("id" to 42L)
)

---

Transactions

Using the transaction method in Query4k we can open transaction blocks. All functions are indentical inside of these blocks, the only difference is that all methods are in scope:

query4k.transaction {
    val people = query<Person>("SELECT * FROM people")
}

Type support

Currently, query4k supports most "basic" types, such as strings, integers, and so on. However, there are some types that either require special imports or libraries, only have partial support, or are not supported at all:

• BigDecimal (fully supported, requires io.query4k.serializers.BigDecimalSerializer)
• UUID (only partially supported, requires app.softwork:kotlinx-uuid-core dependency, injection-safe inserts do not work)
• LocalDate, LocalTime (fully supported, but requires use of .toSQLParseable() extension method on inserts)
• LocalDateTime (only partially supported, queries are not supported)
• Arrays and lists (unsupported)