DEV Community: Dries Samyn

Kapper 1.6: Faster Performance, Bulk Operations, and a Brand New Documentation Site

Dries Samyn — Thu, 01 Jan 2026 15:51:05 +0000

We're excited to announce Kapper 1.6!
Since the 1.5 release, we've been hard at work delivering some highly requested features and performance improvements.

Here's what's new:

7.5% faster mapping performance with optimized reflection code
New execute API that accepts DTOs as arguments
Bulk operations with the new executeAll API
A brand new documentation website

Performance improvements

In search of even better performance, and with support from the kind people at YourKit who provided a free copy of the YourKit profiler, we went on a mission to find where the biggest performance improvements could be made.

Unsurprisingly, the biggest overhead Kapper has compared to raw JDBC is the reflection-based auto-mapping.
In particular, a few of the Kotlin reflection APIs are slow(ish) even compared to the Java APIs.

Improvements were found by refactoring the reflection-based mapping and adding a caching layer for the most costly operations, resulting in a 7.5% average improvement in the benchmarks compared to the previous version.

There are still some inefficiencies in the Kotlin reflection library, which means that mapping to Java records is marginally faster than to Kotlin data classes. Keep in mind that we're talking fractions of a microsecond per mapping operation. When those microseconds matter, we always suggest using custom mappers, but in most real-world cases, the extra few microseconds probably don't matter.

In any case, Kapper outperforms other ORMs, including Hibernate.

Execute with DTO

Previously, Kapper's execute API required passing in values for named parameters individually.
For example:

dataSource.connection.use {
    it.execute(
        """
        INSERT INTO super_heroes(id, name, email, age) 
        VALUES (:id, :name, :email, :age)
        """,
        "id" to UUID.randomUUID(),
        "name" to "Wonder Woman",
        "email" to "wonder@dc.com",
        "age" to 3000
    )
}

This felt like a natural API to us, but based on user feedback, we added a new extension that allows DTOs to be passed in directly.
Now you can pass your DTO and map its properties:

val hero = SuperHero(UUID.randomUUID(), "Flash", "flash@dc.com", 28)

dataSource.connection.use {
    it.execute(
        "INSERT INTO super_heroes(id, name, email, age) VALUES(:id, :name, :email, :age)",
        hero,
        "id" to SuperHero::id,
        "name" to SuperHero::name,
        "email" to SuperHero::email,
        "age" to SuperHero::age
    )
}

Bulk uploads with executeAll

The addition of the DTO API for execute made it easy to support bulk updates or inserts using the same pattern:

val users = listOf(
    User(null, "User 1", "user1@example.com", 25),
    User(null, "User 2", "user2@example.com", 30),
    User(null, "User 3", "user3@example.com", 35)
)

val results = connection.executeAll(
    "INSERT INTO users(name, email, age) VALUES(:name, :email, :age)",
    users,
    "name" to User::name,
    "email" to User::email,
    "age" to User::age
)

This API is recommended when multiple inserts or updates are required in the same operation.
Kapper uses JDBC's batch statement features to execute this efficiently.

Brand-new documentation website

Kapper's simple and intuitive API didn't require much documentation initially, but the repo's README had outgrown its purpose.
Based on user feedback, and with a helping hand from a friendly LLM, we created a dedicated documentation website that includes a getting started guide as well as an overview of all examples: driessamyn.github.io/kapper

The examples have also been migrated from their dedicated repo to the main Kapper repository, making everything easier to find and maintain.

What's next?

Let us know what you think, what's missing, or what you'd like to see next.
You can open an issue on GitHub or reach out to us directly.

Let 2026 be the year developers fall in love with SQL again and adopt Kapper as their favorite micro-ORM for Kotlin and the JVM!

Kapper 1.5: Celebrating Java's 30th Birthday with Blazing Fast Record Mapping

Dries Samyn — Fri, 13 Jun 2025 07:41:09 +0000

As Java recently celebrates its 30th birthday this year, I'm excited to announce Kapper 1.5 with first-class support for Java Record classes!
While Kapper was designed with Kotlin in mind and provides a Kotlin-idiomatic API, support for other JVM languages was always an important objective.
The addition of Record support emphasizes this commitment to the broader JVM ecosystem.

This release also brings new customization options that make database mapping even more powerful and flexible, while maintaining the blazing-fast performance Kapper is known for.

🚀 What's New in Kapper 1.5

Java Record Support - Fast and Modern

The headline feature of Kapper 1.5 is native support for auto-mapping to Java Record classes.
Records, introduced in Java 14, provide a concise way to model immutable data - perfect for database entities.

Here's how simple it is to use Records with Kapper:

// Define your record
public record SuperHero(UUID id, String name, String email, int age) {}

// Query a list of heroes
try (Connection conn = dataSource.getConnection()) {
    List<SuperHero> heroes = kapper.query(SuperHero.class, conn, 
        "SELECT * FROM super_heroes", Map.of());
}

// Find a single hero by ID
try (Connection conn = dataSource.getConnection()) {
    SuperHero hero = kapper.querySingle(SuperHero.class, conn, 
        "SELECT * FROM super_heroes WHERE id = :id", 
        Map.of("id", heroId));
}

Custom Mappers for Ultimate Flexibility

When introducing support for Record mappers, I made a change to how mappers are registered.
This opened the door to custom mappers, allowing you to define exactly how your data should be mapped from the database to your objects, which is especially useful for performance critical use cases, or where complex queries or mapping logic is required.
By using custom mappers, you avoid the reflection overhead of auto-mapping, making Kapper even faster than other ORMs in these configurations.

Custom mappers are just code, nothing magical, so they are easy to understand, maintain and test.

You can register a custom mapper class:

data class SuperHero(val id: UUID, val name: String, val email: String? = null, val age: Int? = null)

// Custom mapper class
class SuperHeroMapper : Mapper<SuperHero> {
    override fun createInstance(
        resultSet: ResultSet,
        fields: Map<String, Field>,
    ) = SuperHero(
        id = UUID.fromString(resultSet.getString("id")),
        name = resultSet.getString("name"),
        email = resultSet.getString("email"),
        age = resultSet.getInt("age"),
    )
}

Kapper.mapperRegistry.registerIfAbsent<SuperHero>(SuperHeroMapper())

val heroes = connection.query<SuperHero>("SELECT * FROM super_heroes")

Or use a mapper lambda for inline customization:

val heroes = connection.query<SuperHero>(
    "SELECT * FROM super_heroes",
    { resultSet, _ -> 
        SuperHero(
            id = UUID.fromString(resultSet.getString("id")),
            name = resultSet.getString("name"),
            email = resultSet.getString("email"),
            age = resultSet.getInt("age"),
        ) 
    }
)

📊 Performance That Impresses

Benchmark results show that Java Record mapping isn't just convenient - it's fast.
When loading and mapping 100 rows:

Library	Time (μs)
Raw JDBC	73.7
Kapper Record	106.9
Hibernate	127.3
Kapper Data Class	220.5
Ktorm	685.6

Key Performance Insights

Kapper Record auto-mapping outperforms Hibernate entity class mapping (106.9μs vs 127.3μs)
Maintains competitive performance while giving you complete SQL control
Continues to significantly outperform Ktorm by a substantial margin (685.6μs)

Note: Record mapping is currently faster than Kotlin data class mapping (220.5μs), though we plan to optimize data class performance in future releases.

The performance advantage of Records over Kotlin data classes likely stems from Java vs Kotlin reflection differences - an area I'm actively investigating for future optimizations.

_The benchmark was run on a M3 MacBook Air. Full details are available in the Kapper benchmark results repo, and the benchmark code can be found here.

🏆 Why Records + Kapper = Perfect Match

Hibernate's Record Limitations

While Hibernate is working on Record support, it's still limited and doesn't provide the seamless experience you get with Kapper.
With Kapper, Records work out of the box with zero configuration.

The Kapper Philosophy

Kapper doesn't try to replace SQL - it embraces it. You write the queries you need, and Kapper handles the mapping efficiently:

// Custom joins? No problem!
try (Connection conn = dataSource.getConnection()) {
    List<SuperHeroBattle> battles = kapper.query(SuperHeroBattle.class, conn,
        """
        SELECT s.name as superhero, v.name as villain, b.battle_date as date
        FROM super_heroes s
        INNER JOIN battles b ON s.id = b.super_hero_id
        INNER JOIN villains v ON v.id = b.villain_id
        WHERE s.name = :name
        """,
        Map.of("name", heroName));
}

🛠 Getting Started

Add Kapper 1.5 to your project:

Maven

<dependency>
    <groupId>net.samyn</groupId>
    <artifactId>kapper</artifactId>
    <version>1.5.0</version>
</dependency>

Gradle

dependencies {
    implementation("net.samyn:kapper:1.5.0")
}

🔗 Cross-Language Support

While we're celebrating Java Records, Kapper continues to excel with Kotlin data classes, offering a consistent API across both languages:

// Kotlin - still works beautifully
data class SuperHero(val id: UUID, val name: String, val email: String, val age: Int)

fun findHeroes(): List<SuperHero> = dataSource.connection.use {
    it.query<SuperHero>("SELECT * FROM super_heroes")
}

Head to Kapper Examples for more code samples and documentation.

🎉 Happy 30th Birthday, Java!

Java's journey from Oak to the modern platform powering millions of applications worldwide has been remarkable.
Features like Records show Java's commitment to evolving while maintaining its core strengths.
I am a huge fan of the Kotlin language but wanted to show my appreciation for the Java ecosystem but making Java database programming more enjoyable and performant through Kapper 1.5.

Ready to try Kapper 1.5?
Check out the full release notes and examples to get started.

What do you think about Java Records? Have you tried them with database mapping? Let us know in the comments!

Kapper benchmarks - how does Kapper 1.3 compare to the competition?

Dries Samyn — Fri, 18 Apr 2025 13:44:08 +0000

Kapper is a lightweight Kotlin ORM library designed to be simple and idiomatic.
Unlike other ORM libraries, such as Hibernate and Ktorm, Kapper avoids being a leaky abstraction over SQL.
Instead, it embraces SQL as the optimal language for database operations, offering a straightforward, idiomatic API to reduce boilerplate code.

But how does Kapper's performance measure up against its competitors?

TL;DR

I recently benchmarked Kapper 1.3 against raw JDBC, Hibernate, and Ktorm as a precursor to optimization work.
I feared disappointing results, especially given the potential for improving its reflection-based mapping.
However, the outcomes were better than expected:

Kapper pretty much matches raw JDBC performance when using manual mapping.
Auto-mapping introduces a small penalty of a few microseconds per conversion. While this is slightly slower than Hibernate, it is significantly faster than Ktorm.
Optimizing the reflection-based mapper is less urgent than initially anticipated.

Benchmarking

Benchmarking is often divisive, so I’ve taken care to be transparent about the methodology and results.

The goal of these benchmarks wasn’t to measure Kapper’s absolute performance but to evaluate its relative performance against other libraries.
I used the Java Microbenchmark Harness (JMH), a trusted tool for JVM-based microbenchmarks, which accounts for pitfalls like dead code elimination, constant folding, and JVM warm-up.

I tested the latest library versions at the time of writing:

Kapper: 1.3.0
Hibernate: 6.6.11
Ktorm: 4.1.1

The benchmark code resides in Kapper’s benchmark module, and I’ve published the results here.

Environment

Benchmarks ran on an M3 MacBook Air (16GB RAM).
While a dedicated server with a fixed configuration would be more ideal, this setup suffices for relative performance comparisons.
Tests were repeated multiple times to minimize outliers.
If anyone wants to sponsor a dedicated benchmarking environment, let me know! 😉

Benchmarking Process

The benchmark suite consisted of the following elements:

BenchmarkStrategy interface: Specifies methods to benchmark for each library:
- findById: Select a single row by ID and map it to a DTO.
- find100Heroes: Select 100 rows and map them to a DTO.
- findHeroBattles: Execute a join query and map the results to a DTO.
- insertNewHero: Insert a single row into a table.
- updateHero: Update a single row in a table.
Library Implementations:
- Two strategies for Kapper: one with auto-mapping and one with manual mapping.
- Hibernate and Ktorm implementations.
KapperBenchmark: Runs benchmarks for each strategy, exercising all interface methods.

Each benchmark was tested against both PostgreSQL and SQLite (in-memory) databases.
SQLite tests minimized I/O latency, focusing on library performance.
However, this article focuses on PostgreSQL results for realism.

The database schema included three tables modeling superheroes, villains, and battles.

JMH Parameters

forks=3 - The 'forks' parameter instructs JMH to run each benchmark in 3 separate JVM instances. This provides more reliable results by mitigating the effects of JVM-specific optimizations and ensures the benchmarks aren't influenced by artifacts from previous benchmark runs.
iterations=5 - Each benchmark is executed 5 times in succession within each fork. This helps to establish statistical confidence in the results by providing multiple measurement samples.
time_on_iteration="5s" - Each iteration runs for 5 seconds, during which JMH collects multiple measurement samples. This longer measurement window helps smooth out any momentary performance fluctuations.
warmup_time="2s" - Before the actual measurement begins, JMH runs the benchmark for 2 seconds to 'warm up' the JVM. This ensures that the JVM's just-in-time compiler has had time to optimize the code, simulating the steady-state performance that would be experienced in a real application rather than measuring cold-start performance.

The Results

Find by ID

Benchmark                        (databaseType)          (library)  Mode  Cnt     Score     Error  Units
KapperBenchmark.findById             POSTGRESQL               JDBC  avgt   15    50.736 ±   0.403  us/op
KapperBenchmark.findById             POSTGRESQL             KAPPER  avgt   15    55.228 ±   0.380  us/op
KapperBenchmark.findById             POSTGRESQL  KAPPER-NO-AUTOMAP  avgt   15    51.910 ±   0.192  us/op
KapperBenchmark.findById             POSTGRESQL          HIBERNATE  avgt   15    53.530 ±   0.528  us/op
KapperBenchmark.findById             POSTGRESQL              KTORM  avgt   15   709.320 ± 175.702  us/op

For this benchmark, a single row is selected from one database table, selected by ID (its primary key).
The record is then converted to a DTO, a Kotlin data class in this case.

Kapper’s manual mapping matches raw JDBC, incurring only a 1-2 microsecond penalty.
Auto-mapping adds a ~5 microsecond overhead but remains competitive with Hibernate.
Ktorm lags significantly, averaging 12x slower than Kapper’s auto-mapping.

Select 100 records

Benchmark                        (databaseType)          (library)   Mode  Cnt     Score     Error  Units 
KapperBenchmark.find100              POSTGRESQL               JDBC   avgt   15    85.867 ±   0.994  us/op
KapperBenchmark.find100              POSTGRESQL             KAPPER   avgt   15   330.150 ± 155.284  us/op
KapperBenchmark.find100              POSTGRESQL  KAPPER-NO-AUTOMAP   avgt   15    86.755 ±   0.295  us/op
KapperBenchmark.find100              POSTGRESQL          HIBERNATE   avgt   15   123.994 ±   0.964  us/op
KapperBenchmark.find100              POSTGRESQL              KTORM   avgt   15   907.302 ± 139.134  us/op

Similar trends emerge when selecting 100 rows.
Kapper’s manual mapping nearly matches raw JDBC.
Auto-mapping adds a performance cost, but Kapper still outperforms Ktorm significantly.

Selecting data using a simple join

Benchmark                        (databaseType)          (library)   Mode  Cnt     Score     Error  Units  
KapperBenchmark.simpleJoin           POSTGRESQL               JDBC   avgt   15    91.490 ±   1.017  us/op
KapperBenchmark.simpleJoin           POSTGRESQL             KAPPER   avgt   15    97.432 ±   0.592  us/op
KapperBenchmark.simpleJoin           POSTGRESQL  KAPPER-NO-AUTOMAP   avgt   15    92.780 ±   0.579  us/op
KapperBenchmark.simpleJoin           POSTGRESQL          HIBERNATE   avgt   15   165.602 ±   2.178  us/op
KapperBenchmark.simpleJoin           POSTGRESQL              KTORM   avgt   15  1103.603 ± 760.116  us/op

The last variation of the "read" benchmark uses a simple JOIN query to select a single row from 3 tables in the DB:

SELECT s.name as superhero, v.name as villain, b.battle_date as date
FROM super_heroes as s
INNER JOIN battles as b on s.id = b.super_hero_id
INNER JOIN villains as v on v.id = b.villain_id
WHERE s.id = ?

In this benchmark, Kapper’s manual mapping again matches raw JDBC.
Auto-mapping incurs a small penalty but outperforms Hibernate and significantly outpaces Ktorm.

Inserting and Updating Records

Insert

Benchmark                        (databaseType)          (library)   Mode  Cnt     Score     Error  Units  
KapperBenchmark.insertSingleRow      POSTGRESQL               JDBC   avgt   15    53.077 ±   0.721  us/op
KapperBenchmark.insertSingleRow      POSTGRESQL             KAPPER   avgt   15    54.457 ±   0.957  us/op
KapperBenchmark.insertSingleRow      POSTGRESQL  KAPPER-NO-AUTOMAP   avgt   15    54.347 ±   0.662  us/op
KapperBenchmark.insertSingleRow      POSTGRESQL          HIBERNATE   avgt   15   162.638 ±   1.792  us/op
KapperBenchmark.insertSingleRow      POSTGRESQL              KTORM   avgt   15   345.546 ± 331.779  us/op

Moving onto the write operations, there are no surprises. Kapper's performance is similar to using raw JDBC.
In this case, there is no difference between the 2 kapper benchmark results, and that is because Kapper does not yet support auto-mapping for the execute API.
Hibernate performs around 3 times slower, and Ktorm slower again, however, the difference is less pronounced than for the read benchmarks.

Updating a record, the odd one out

Benchmark                        (databaseType)          (library)   Mode  Cnt     Score     Error  Units   
KapperBenchmark.updateSingleRow      POSTGRESQL               JDBC   avgt   15   707.729 ± 124.671  us/op
KapperBenchmark.updateSingleRow      POSTGRESQL             KAPPER   avgt   15   705.396 ± 118.716  us/op
KapperBenchmark.updateSingleRow      POSTGRESQL  KAPPER-NO-AUTOMAP   avgt   15   705.013 ± 121.060  us/op
KapperBenchmark.updateSingleRow      POSTGRESQL          HIBERNATE   avgt   15   105.266 ±   2.165  us/op
KapperBenchmark.updateSingleRow      POSTGRESQL              KTORM   avgt   15   846.170 ± 166.406  us/op

Results for the last benchmark, updating a single row, has me puzzled. I was expecting these to be very similar to inserting a record, however, it is significantly slower for all libraries, except Hibernate, for which the update is faster than the insert, and more curiously a lot faster even than using raw JDBC.
When seeing these results, I was convinced this was a blib, so repeated the benchmark run a few times to validate, but I am seeing similar results for all.

Curiously, the SQLITE results look more like what I was expecting:

Benchmark                        (databaseType)          (library)   Mode  Cnt     Score     Error  Units
KapperBenchmark.updateSingleRow          SQLITE               JDBC   avgt   15     2.191 ±   0.032  us/op
KapperBenchmark.updateSingleRow          SQLITE             KAPPER   avgt   15     3.004 ±   0.044  us/op
KapperBenchmark.updateSingleRow          SQLITE  KAPPER-NO-AUTOMAP   avgt   15     3.030 ±   0.018  us/op
KapperBenchmark.updateSingleRow          SQLITE          HIBERNATE   avgt   15     7.319 ±   0.087  us/op
KapperBenchmark.updateSingleRow          SQLITE              KTORM   avgt   15   708.753 ± 175.757  us/op

This anomaly warrants deeper investigation.
I've confirmed the benchmark implementation is correct, and the SQLite results support my expectation that updates should perform similarly to inserts.
This PostgreSQL-specific behavior will be the subject of future profiling and analysis, and if Hibernate has some magic sauce here, I will be sure to copy it, but more likely I am missing something.
If anybody has any ideas, let me know 😉

Conclusion

Benchmarking kapper was actually a lot of fun, and the JMH library is a great tool for this.
I was fearful of what I would find, but the results are better than I expected, and I am happy to see that Kapper's performance can be on par with raw JDBC.
I was expecting to do some optimisations to the reflection based mapping, and that is still the plan, but this doesn't seem as urgent as I had feared.

What I can take away from this is:

Kapper's auto-mapper adds a small single digit microsecond penalty. This is unsurprising given it is using reflection. For most use-cases, this will be fine given this is very small fraction of the overall "cost" considering the I/O nature of DB operations.
If performance is critical, and you are using Kapper, then use manual mapping, it is pretty much as fast as raw JDBC but still gives you the benefits of Kapper's API to reduce boilerplate code.
Hibernate is a little faster than Kapper when using the auto-mapper, but not as fast as when using manual mapping. It is also much harder to use and suffers from the leaky abstraction issue.
Ktorm is significantly slower than Kapper and Hibernate.

While Kapper's auto-mapping performance is already quite good, I plan to optimize it further in upcoming releases and welcome community contributions.

There are several ways you can contribute to making Kapper even better:

Try running these benchmarks on your own systems and share your results
Review the benchmark code and suggest improvements or additional test cases
Help investigate the PostgreSQL update performance anomaly
Consider contributing to Kapper's development, especially if you have experience with performance optimization or code generation
Share your experiences using Kapper in real-world applications

The more diverse environments and use cases we can test, the more we can refine Kapper to be both performant and developer-friendly.

You can find the benchmark code here and the results here.

Join the discussion on GitHub or reach out directly if you're interested in contributing!

Kapper 1.3 supports flows - more Kotlin goodness

Dries Samyn — Sun, 23 Mar 2025 16:37:16 +0000

Kapper, Kotlin's most lightweight and idiomatic ORM library brings more Kotlin goodness with support for Flows.
Kapper 1.3 supports queries returning Kotlin Flows.

What are Flows?

Flows are a Kotlin API for asynchronous streams of data.
They are similar to Rx Observables, but are simpler and more idiomatic to Kotlin.
They are a great fit for asynchronous data processing, particularly for database operations where results may be large or processing needs to happen incrementally.
This makes them a perfect addition to Kapper's existing coroutine support.

Kapper 1.3: Simple Flow Integration

As always with Kapper, the new API is simple and idiomatic to Kotlin and is provided as an extension function rather than a leaky abstraction which you may find in other libraries.

To make use of the new API, simply call the queryAsFlow extension function on a JDBC Connecton instance.
The queryAsFlow function takes the same arguments as the regular, blocking, query function, but instead returns a Flow of the query results.

Example: Basic Flow Query

 val query =
    datasource.withConnection {
        async {
            // SuperHero is a plain Kotlin dataclass
            it.queryAsFlow<SuperHero>("SELECT * FROM super_heroes")
                .map { it.name }
                .toList()
        }
    }
println("Starting query")
val heroes = query.await()
heroes.forEach(::println)

Cancelling a query

The Flow returned by queryAsFlow is a regular Flow and can be cancelled using the regular cancel function.
This can be leveraged to cancel a query if the caller is no longer interested in the results or to return early from a long-running query.

Example: Early Cancellation with Running Total

The query below selects all movies from a database, ordered by their worldwide gross income.
The cumulative gross income is calculated as each result is processed, and the query is cancelled when the cumulative gross income reaches 10 billion.

val job =
    async {
        getDataSource(postgresql).withConnection { connection ->
            var acc = 0L
            delay(50)
            val movies =
                connection.queryAsFlow<PopularMovie>(
                    """
                    SELECT
                     title,
                     gross_worldwide as grossed
                    FROM movies 
                    ORDER BY gross_worldwide DESC
                    """.trimIndent(),
                ).onEach {
                    acc += it.grossed
                    println("Accumulated gross including (${it.title}): ${String.format("%,d", acc)}")
                    if (acc >= 10_000_000_000) {
                        cancel("Gross reached 10 billion")
                    }
                }.toList()
            movies to acc
        }
    }
println("Query started")
try {
    val popularMovies = job.await()
    println(
        "Popular movies are: ${popularMovies.first.map { it.title }}, " +
            "grossing a total of ${String.format("%,d", popularMovies.second)}",
    )
} catch (e: CancellationException) {
    println("Query cancelled: ${e.message}")
}

Example: Using takeWhile for Cleaner Cancellation

The same code could be rewritten to use the takeWhile function for a more functional approach:

val job =
    async {
        getDataSource(postgresql).withConnection { connection ->
            connection.queryAsFlow<PopularMovie>(
                """
                SELECT
                 title,
                 gross_worldwide as grossed
                FROM movies 
                ORDER BY gross_worldwide DESC
                """.trimIndent(),
            )
                .runningFold(0L to emptyList<PopularMovie>()) { (totalGross, movieList), movie ->
                    val newTotal = totalGross + movie.grossed
                    newTotal to (movieList + movie)
                }.takeWhile { (totalGross, _) ->
                    // query will be cancelled here
                    totalGross <= 10_000_000_000
                }.last()
        }
    }
println("Query started")
val popularMovies = job.await()
println(
    "Popular movies are: ${popularMovies.second.map { it.title }}, " +
        "grossing a total of ${String.format("%,d", popularMovies.first)}",
)

NOTE: This code could be re-written to use a window function to calculate the cumulative gross income.
This may be more efficient, however this example is only for illustrative purposes.

Technical Considerations

When a flow is cancelled, Kapper will attempt to cancel the underlying JDBC Statement and ResultSet objects.
For this reason, Kapper sets the fetchSize of the Statement.
This is defaulted to 1,000 but can be changed by setting the fetchSize argument on the queryAsFlow function.
This gives the JDBC driver the opportunity to cancel the query at each batch of rows fetched.
However, it should be noted that not all JDBC drivers support this feature.

Conclusion

With Kapper 1.3, you can now use Kotlin Flows to process the results of database queries, making Kapper even more idiomatic to Kotlin.

The combination of Kotlin's coroutines and Flows with Kapper's simple API provides a powerful yet intuitive way to work with database operations asynchronously.

Kapper 1.3 is available on Maven Central and on GitHub.
Give it a test driver and let me know what you think!
What should be next for Kapper? Let me know in the comments below.

Announcing Kapper 1.2: Cleaner Transactions with Less Boilerplate

Dries Samyn — Sat, 15 Feb 2025 09:28:24 +0000

I'm excited to announce the release of Kapper 1.2, which introduces a small but meaningful enhancement.

In line with Kapper's philosophy of being lightweight and non-intrusive, we've added withTransaction as an extension method to both Connection and DataSource. This addition doesn't abstract away or replace the underlying functionality—it's purely syntactic sugar designed to save you from writing repetitive boilerplate code.

Before: Transactions in Kapper 1.0

Here's an example of how you might execute a couple of queries within a transaction using Kapper 1.0:

dataSource.connection.use {
    try {
        it.autoCommit = false
        it.execute(
            """
             INSERT INTO villains(id, name) 
             VALUES (:id, :name)
              """.trimIndent(),
            "id" to villain.id,
            "name" to villain.name,
        )
        it.execute(
            """
             INSERT INTO battles(super_hero_id, villain_id, battle_date, updated_ts)
             VALUES (:super_hero_id, :villain_id, :date, NOW())
              """.trimIndent(),
            "super_hero_id" to superHero.id,
            "villain_id" to villain.id,
            "date" to date,
        )
        it.commit()
    } catch (ex: SQLException) {
        try {
           it.rollback()
        } catch (rollbackException: Exception) {
           ex.addSuppressed(rollbackException)
        }
        throw ex
    }
}

While this code is clear, it involves a fair amount of boilerplate for managing exceptions and transactions.

After: Transactions in Kapper 1.2

With Kapper 1.2, the same logic can now be written more concisely:

dataSource.withTransaction {
    it.execute(
        """
         INSERT INTO villains(id, name) 
         VALUES (:id, :name)
          """.trimIndent(),
        "id" to villain.id,
        "name" to villain.name,
    )
    it.execute(
        """
         INSERT INTO battles(super_hero_id, villain_id, battle_date, updated_ts)
         VALUES (:super_hero_id, :villain_id, :date, NOW())
          """.trimIndent(),
        "super_hero_id" to superHero.id,
        "villain_id" to villain.id,
        "date" to date,
    )
}

How withTransaction Simplifies Your Code

The withTransaction function automatically:

Creates a connection.
Starts and commits the transaction.
Rolls back the transaction if an exception occurs.
Ensures the connection is properly closed.

This results in cleaner, more readable code while retaining the flexibility to use the native JDBC API whenever needed.

Kapper 1.2 is now available on Maven Central. For more details, check out the GitHub repository or explore the examples.

I’d love to hear your feedback—let me know what you think!

Coroutine support in Kapper 1.1

Dries Samyn — Sun, 02 Feb 2025 15:37:47 +0000

When I set out to build Kapper, I wanted it to be the most Kotlin idiomatic micro ORM, with the goal that it should be intuitive to use by Kotlin users while still being useful for Java developers.
While Kapper 1.0 achieved many of these goals, one obvious gap was the lack of support for Kotlin Coroutines – a feature now being addressed in Kapper 1.1 with the introduction of the kapper-coroutines module.

Using Kapper Coroutines

The kapper-coroutines module is available from Maven Central and adds support for coroutines to Kapper.

At its core, Kapper coroutines are supported by a single extension function: withConnection on the DataSource object.
This function creates a new connection, executes the provided lambda within a coroutine scope, and automatically closes the connection when complete.

By default, this code, including the Kapper API query or execute functions, will use the Dispatchers.IO dispatcher, but another dispatcher can be provided as an optional parameter.
The Dispatchers.IO dispatcher is specifically optimized for input/output operations like database queries and file operations. It maintains a pool of threads that can be used for blocking operations, ensuring that your application remains responsive while executing these potentially time-consuming tasks.

Some Examples

Let's start with the basics of including and using the module, then move on to more complex scenarios that demonstrate the power of coroutines.

Start by including the kapper-coroutines module in your project, for example:

 dependencies {
     implementation("net.samyn:kapper-coroutines:1.1.0")
 }

Let's look at a more complex example using different coroutine builders:

runBlocking: Creates a coroutine scope and blocks the current thread until all launched coroutines complete.
async: Launches a new coroutine that can return a result using await().

Here's how these work together:

// Create a DataSource object, for example using [HikariCP](https://github.com/brettwooldridge/HikariCP)
//  Kapper is un-opinionated about which pooler, if any, you use.
val dataSource = HikariDataSource().apply {
    jdbcUrl = "jdbc:PostgreSQL://localhost:5432/mydatabase"
    username = "username"
    password = "password"
}

// The Kapper API exposes a `withConnection` extension function which can be used instead of the `connection` function.
dataSource.withConnection { connection ->
    try {
        // The connection can be used to execute queries, using the Kapper API or the native JDBC api.
        connection.autoCommit = false
        connection.query<SuperHero>("SELECT * FROM super_heroes where id = :id", "id" to 1)?.also { hero ->
            connection.execute(
                "INSERT INTO other_super_heroes (id, name, email) VALUES (:id, :name, :email)",
                "id" to hero.id,
                "name" to hero.name,
                "email" to hero.email,
            )
        }
        connection.commit()
    }
    catch (e: SQLException) {
        connection.rollback()
        throw e
    }
}

The above code snippet demonstrates how using the withConnection function:

A DataSource object is created using a connection pooler (in this case, HikariCP). Using a connection pooler is optional but usually recommended.
The withConnection function is called on the DataSource object to obtain a connection from the pool.
A JDBC transaction is started by setting autoCommit to false.
A query is executed using the Kapper API to select a single SuperHero object.
An insert statement is executed using the Kapper API to insert the SuperHero object into a different table.
The transaction is committed if no exceptions are thrown, or rolled back if an exception occurs.

The only notable thing about this code is that withConnection is a suspending function, so it can be called from within a coroutine scope.
This means that, for example, the main thread does not need to be blocked and can therefore continue to do other work while the database query is being executed.

For example:

runBlocking {
    val selectJob =
        async {
            println("[${Thread.currentThread().name}] Starting to select heroes.")
            val heroes = it.query<SuperHero>("SELECT * FROM super_heroes")
            println("\n[${Thread.currentThread().name}] Finished selecting heroes")
            heroes
        }
    val logJob =
        launch {
            // print . until the insertJob has completed.
            println("[${Thread.currentThread().name}] ")
            while (selectJob.isActive) {
                delay(100)
                println(".")
            }
        }
    println("Selected ${selectJob.await().joinToString { it.name }}")
    logJob.join()
}

In this example, if the select query would be slow, we can expect the output to be something like:

[Test worker @coroutine#5] Starting to select heroes.
[Test worker @coroutine#6] ...........
[Test worker @coroutine#5] Finished selecting heroes
Selected Superman, Batman, Wonder Woman, Spider-Man, Iron Man, Captain America, Thor

We can see that we were able to continue logging to the console until the select query completed.

A single suspending function

Why not suspend everything?

Some may wonder why Kapper only exposes a single suspending function, withConnection.
Why, for example, are there no suspending versions of the query or execute functions?

A deepdive into coroutines is outside the scope of this blog post, and I can thoroughly recommend a read of Blocking threads, suspending coroutines by Roman Elizarov.
However, I want to try to explain with some simple diagrams why a function to create a connection is the best API to expose as a suspending function.

Understanding sequential execution

Firstly consider what happens when we don't use coroutines for the following code:

connection.use { 
    it.query<SuperHero>("SELECT * FROM super_heroes")
    it.query<Villain>("SELECT * FROM villains")
}

We can visualise this like so:

Using a single DB connection, both queries are executed sequentially, i.e. the second query executed once the first one has completed, and the main thread is blocked until both queries have completed.

The problem with suspending queries

Imagine the query function was suspendable, we could write the following code:

connection.use {
    val first = async { it.query<SuperHero>("SELECT * FROM super_heroes") }
    val second = async { it.query<Villain>("SELECT * FROM villains") }
    awaitAll(first, second)
}

The Kapper approach

This may seem like it would allow the queries to execute concurrently, however, let's have a look at the execution model:

What is going on here is that 2 coroutines are created, but the JDBC operations are blocking, which means that each of the queries, when executing using the same connection, will execute sequentially.

This means one of the coroutines will be waiting, suspended, until the other one completes. The end result is that the queries are still executed sequentially, but with the overhead of creating 2 coroutines.

The end result is that the queries are still executed sequentially, but with the overhead of creating 2 coroutines.

This is why Kapper takes the approach of providing a suspending function to create a connection, which can then be used to execute queries in a coroutine scope, re-enforcing that multiple queries using the same connection will always be executed sequentially.

For example:

val queries = async datasource.withConnection {
  it.query<SuperHero>("SELECT * FROM super_heroes")
  it.query<Villain>("SELECT * FROM villains")
}
queries.await()

In this case, a coroutine is created, which by default will be using the Dispatchers.IO dispatcher, and the queries will be executed sequentially:

Running queries concurrently

As seen above, it is important to recognise that JDBC operations are blocking, and that using the same connection for multiple queries will result in them being executed sequentially.

To execute queries concurrently, you will need to use a separate connection for each query.
This can be done easily using the Kapper API:

val query1 = async datasource.withConnection {
  it.query<SuperHero>("SELECT * FROM super_heroes")
}
val query2 = async datasource.withConnection {
  it.query<Villain>("SELECT * FROM villains")
}
awaitAll(query1, query2)

In this case, each query is executed in its own coroutine, using its own connection, and will be executed concurrently:

There are a few important things to note when doing this:

Each connection will be obtained from the connection pool (or the database directly of no pool is used), so it is important to ensure that the pool is configured to handle the number of connections required. Database connections are a relatively expensive resource, so it is important to ensure that they are used efficiently.
Database transactions are tied to a connection, so if you need to execute multiple queries within a transaction, they have to use the same connection, and in Kapper that means the same coroutine.

Conclusion

Kapper 1.1 adds support for Kotlin Coroutines, making it easier to use Kapper in a coroutine-based application.
This integration brings several key benefits:

Non-blocking database operations that keep your application responsive.
Simple, intuitive API with the withConnection function.
Clear separation between sequential and concurrent query execution.
Automatic resource management with proper connection handling.
Seamless integration with existing coroutine-based applications.

It is however important to understand that JDBC operations are blocking, and the impact this has on the execution of queries in a coroutine scope.
By following the patterns described in this post, you can effectively manage your database connections while taking advantage of Kotlin's powerful coroutine features.

Please check out the Kapper and Kapper-Example repositories for more information and examples, and let me know what you think.

Kapper, a Fresh Look at ORMs for Kotlin and the JVM

Dries Samyn — Wed, 22 Jan 2025 20:28:11 +0000

SQL is not a problem to be solved - it's a powerful tool to be embraced. This is the philosophy behind Kapper...

Introduction

Kapper is a lightweight, Dapper-inspired ORM (Object-Relational Mapping) library for the Kotlin programming language, targeting the JVM ecosystem. While most ORMs attempt to abstract away SQL, Kapper takes a different approach - it embraces SQL as the most effective language for database interactions while providing modern Kotlin conveniences for execution and mapping.

Why Another ORM?

I created Kapper because I found existing solutions in the JVM ecosystem were either too heavy, too abstract, or too intrusive. While working with various ORMs, I consistently ran into what I call the "abstraction trap" —where the supposed convenience of database abstraction actually creates more complexity than it solves.

The Abstraction Trap

Many ORM libraries fall into a common pattern: they try to shield developers from SQL by providing their own DSL or code generation tools. While this seems helpful on the surface, it creates several significant problems:

Learning Curve Multiplication: Instead of just needing to know SQL, developers must now learn both SQL and the ORM's abstraction layer. This is particularly evident when debugging performance issues or writing complex queries.
Limited Expressiveness: No matter how comprehensive an ORM's query API is, it will never capture the full expressiveness of SQL. Developers inevitably hit walls where they need to drop down to raw SQL anyway.
Hidden Complexity: ORMs often mask the actual database operations being performed. A simple-looking query might generate multiple database round trips or inefficient SQL, leading to performance problems that are hard to diagnose.
False Sense of Database Independence: While ORMs promise database independence, in practice, efficient database usage often requires leveraging database-specific features. The abstraction becomes leaky when you need to optimize performance or use advanced features.

The Kapper Philosophy

Instead of adding another abstraction layer, Kapper embraces three core principles:

SQL is the Best Query Language: SQL has evolved over decades to be expressive, powerful, and optimized for database operations. Instead of hiding it, we should leverage it directly.
Minimal Abstraction: Kapper provides just enough abstraction to make database operations comfortable in Kotlin or Java, without trying to reinvent database interactions. Kapper prefers extension of existing APIs than abstraction of them.
Transparency: What you write is what gets executed. There's no magic query generation or hidden database calls.

Real-World Comparisons

In this section, I want to explore the Kapper API and compare it to two popular ORM libraries, implementing the same examples in each.
The source code for all examples in this article, along with the used DB script, including the comparisons, can be found in the Kapper Examples repository.

The Kapper API

Kapper's API is intentionally simple, implemented as an extension of the java.sql.Connection interface. This design choice ensures database connections in Kapper are the same as in JDBC:

// Create a DataSource object, for example using [HikariCP](https://github.com/brettwooldridge/HikariCP)
//  Kapper is un-opinionated about which pooler, if any you use.
val dataSource = HikariDataSource().apply {
    jdbcUrl = "jdbc:PostgreSQL://localhost:5432/mydatabase"
    username = "username"
    password = "password"
}

// The Kapper API is exposed as an extension of the java.sql.Connection interface:
dataSource.connection.use { connection ->
    // Do database stuff
}

The API consists of two main functions:

query/querySingle: Execute SELECT queries.
execute: Perform INSERT, UPDATE, DELETE, and other DML queries.

Example:

dataSource.connection.use {
    it.query<SuperHero>("SELECT * FROM super_heroes")
}

Or select a single superhero:

dataSource.connection.use {
    it.querySingle<SuperHero>(
        "SELECT * FROM super_heroes WHERE id = :id",
        "id" to id,
    )
}

Kapper supports auto-mapping based on column names, but you can also provide custom mapping if needed:

dataSource.connection.use {
    it.querySingle<Map<String, *>>(
        "SELECT * FROM villains WHERE name = :name",
        { resultSet, fields ->
            mapOf(
                "id" to resultSet.getString("id"),
                "name" to resultSet.getString("name"),
            )
        },
        "name" to "Joker",
    )
}

Kapper does not maintain a mapping between the database schema and classes. You are free to write any SQL query map the results in whichever way is necessary.

For example, the following query joins multiple tables and auto-maps the results to a DTO (Data Transfer Object):

dataSource.connection.use {
    it.query<SuperHeroBattle>(
        """
                SELECT s.name as superhero, v.name as villain, b.battle_date as date
                FROM super_heroes as s
                INNER JOIN battles as b on s.id = b.super_hero_id
                INNER JOIN villains as v on v.id = b.villain_id
                WHERE s.name = :name 
                """.trimIndent(),
        "name" to superHeroName,
    )
}

In the following, more complex, example, a lambda function is used to process the results of a window query:

dataSource.connection.use {
    var allTimeRank = 1
    it.query(
        // "complex query" -> it's "just" SQL    
        """
         SELECT
         title,
         release_date, 
         gross_worldwide, 
         AVG(gross_worldwide) OVER() AS total_average_gross,
         AVG(gross_worldwide) OVER(PARTITION BY EXTRACT(YEAR FROM release_date)) AS average_annual_gross
        FROM movies 
        ORDER BY gross_worldwide DESC
        LIMIT 3
        """.trimIndent(),
        { rs, _ ->
            // map each result -> it's "just" code
            val gross = rs.getLong("gross_worldwide")
            val annualAvgGross = rs.getInt("average_annual_gross")
            PopularMovie(
                rs.getString("title"),
                gross,
                gross / annualAvgGross.toDouble(),
                allTimeRank++,
            )
        },
    )
}

This approach feels intuitive and familiar to anyone who has used SQL before.

Following the same paradigm, the execute function can be used to insert, update, or delete data:

dataSource.connection.use {
    it.execute(
        """
        INSERT INTO super_heroes(id, name, email, age) 
        VALUES (:id, :name, :email, :age)
        """.trimIndent(),
        "id" to superHero.id,
        "name" to superHero.name,
        "email" to superHero.email,
        "age" to superHero.age,
    )
}

Database transactions are supported by the existing JDBC API:

dataSource.connection.use {
    try {
        it.autoCommit = false
        it.execute(
            """
            INSERT INTO villains(id, name) 
            VALUES (:id, :name)
            """.trimIndent(),
            "id" to villain.id,
            "name" to villain.name,
        )
        it.execute(
            """
            INSERT INTO battles(super_hero_id, villain_id, battle_date, updated_ts)
            VALUES (:super_hero_id, :villain_id, :date, NOW())
            """.trimIndent(),
            "super_hero_id" to superHero.id,
            "villain_id" to villain.id,
            "date" to date,
        )
        it.commit()
    } catch (ex: SQLException) {
        it.rollback()
        throw ex
    }
}

Kapper doesn't generate SQL, this means that it is easy to use DB-specific features. In this example, we use Postgres' ON CONFLICT clause to only insert a row if it doesn't exist yet:

dataSource.connection.use {
    it.execute(
        """
        INSERT INTO super_heroes(id, name, email, age) 
        VALUES (:id, :name, :email, :age)
        ON CONFLICT DO NOTHING 
        """.trimIndent(),
        "id" to superHero.id,
        "name" to superHero.name,
        "email" to superHero.email,
        "age" to superHero.age,
    )
}

SQL is the native language to interact with database, and I believe that it is therefore the best.

How does Kapper compare to other ORMs?

Let's compare Kapper to two popular ORM libraries in the JVM ecosystem: Hibernate and Ktorm.

Hibernate

If you have worked with Java in the last 20 years or so, it is likely you will have used or come across Hibernate, the Godfather of ORM libraries.

Source code for the examples below can be found in the Kapper Examples repository.

Hibernate is JPA compatible and uses a combination of JPA and Hibernate annotations to map Entity classes to the DB schema.

For example:

@Entity
@Table(name = "super_heroes")
class SuperHeroEntity(
    @Id
    @Column(name = "id")
    var id: UUID,
    @Column(name = "name")
    var name: String,
    @Column(name = "email")
    var email: String?,
    @Column(name = "age")
    var age: Int?,
) {
    // equals method overridden in a non-intuitive way
    //  hibernate subscribes that the id is the only thing that matters
    //  which is not the way most people think about equality
    override fun equals(other: Any?): Boolean {
        if (this === other) return true
        if (other == null || Hibernate.getClass(this) != Hibernate.getClass(other)) return false
        other as SuperHeroEntity

        return id == other.id
    }

    override fun hashCode(): Int = javaClass.hashCode()
}

In the above example, we can see our super_heroes table is mapped to a SuperHeroEntity class. This class is then used to interact with the database.

This allows us to interact with the DB using a Hibernate Session:

// listing some superheroes
sessionFactory.openSession().use {
    it.createQuery("FROM SuperHeroEntity", SuperHeroEntity::class.java).list()
}

// finding a single superhero
sessionFactory.openSession().use {
    it.find(SuperHeroEntity::class.java, id) as SuperHeroEntity
}

// inserting a superhero
sessionFactory.openSession().use {
    it.transaction.begin()
    it.merge(superHero)
    it.transaction.commit()
}

This looks elegant, but there are a few potential problems with this approach.
You may notice, for example, that the entity class is not a Kotlin data class, it has non-standard equals and hashCode overrides, and it is mutable.
Explaining the reasons for this is out-of-scope of this article, but you will find many articles and discussions on the topic.

The important point is that this is opinionated and moves away from the normal way of doing things, adding a layer of complexity and potential confusion.
To hide this complexity and reduce the risk of un-intended side effects, it is commonplace for projects to add another layer of abstraction on top of the Hibernate entities so that Hibernate entities don't leak into the rest of the codebase.

Looking further than the trivial example, we can see further evidence of the abstraction leaking.
For example, consider the above join query which we used in the kapper example to find battles between superheroes and villains. Hibernate's version looks like this:

sessionFactory.openSession().use {
    it.createSelectionQuery(
        """
    SELECT b FROM SuperHeroBattleEntity b 
    WHERE b.superHero.name = :name
""",
        SuperHeroBattleEntity::class.java,
    )
        .setParameter("name", superHeroName)
        .list()
}

On the surface, this looks brilliant. It's so simple and easy, much simpler than the Kapper example.
But, as a reader of this code, do you understand the SQL query that will be generated to support this code?
As somebody who has used Hibernate quite a lot, I still fall back to inspecting the generated query and find myself studying the documentation repeatedly to remind myself how to set up the mapping so that it would generate the query I wanted.

Let's have a look at the queries (!) Hibernate generates in this example.

# join battles with super_heroes
   select
        shbe1_0.battle_date,
        shbe1_0.super_hero_id,
        shbe1_0.villain_id,
        shbe1_0.updated_ts 
    from
        battles shbe1_0 
    join
        super_heroes sh2_0 
            on sh2_0.id=shbe1_0.super_hero_id 
    where
        sh2_0.name=?
# oh, wait, we need villains too, so better query for those
    select
        she1_0.id,
        she1_0.age,
        she1_0.email,
        she1_0.name 
    from
        super_heroes she1_0 
    where
        she1_0.id=?
# oh, what, there's more than one of them?
    select
        ve1_0.id,
        ve1_0.name 
    from
        villains ve1_0 
    where
        ve1_0.id=?
# heh, another!
    select
        ve1_0.id,
        ve1_0.name 
    from
        villains ve1_0 
    where
        ve1_0.id=?

Of course, we cannot blame Hibernate for creating the query as it does, and there is no doubt that the mapping and/or query can be written such that the query is more efficient and matches that of our hand-crafted SQL query, however, it is common to encounter these types of issues in applications, and they are often difficult to troubleshoot. A small mapping change can result in significant consequence.

See the entity mapping source code and the example of how it was tested here.

As you can see from that relatively simple example, a lot of Hibernate specific knowledge is required to be able to use the abstraction that is meant to make things easier.

Lastly, let's have a look at the more complex window query example.
Hibernate supports the concept of Native Queries, so we can re-use the SQL query which we used in the Kapper example:

sessionFactory.openSession().use {
    var allTimeRank = 1
    return it.createNativeQuery(
        """
         SELECT
         title,
         release_date, 
         gross_worldwide, 
         AVG(gross_worldwide) OVER() AS total_average_gross,
         AVG(gross_worldwide) OVER(PARTITION BY EXTRACT(YEAR FROM release_date)) AS average_annual_gross
        FROM movies 
        ORDER BY gross_worldwide DESC
        LIMIT 3
        """.trimIndent(),
        Array<Any>::class.java,
    )
        .resultList.map { row ->
            val result = row as Array<*>
            PopularMovie(
                result[0] as String,
                result[2] as Long,
                (result[2] as Long).toDouble() / (result[3] as BigDecimal).toDouble(),
                allTimeRank++,
            )
        }
}

Using a Hibernate specific API we can extract the data from the result set and map it to our DTO. In this case Hibernate doesn't offer any advantage of native JDBC but still introduces a layer of abstraction.

Ktorm

The new kid on the block, Ktorm describes itself as "a lightweight and efficient ORM Framework for Kotlin directly based on pure JDBC".
This sounds similar in spirit to Kapper, so I wanted to explore it using the same examples as we used for Kapper and Hibernate.

Ktorm provides a pleasant development experience and I will definitely take some inspiration from it. However, I still think it suffers from the same leaky abstraction issues as Hibernate.

First of all, like Kapper, Ktorm isn't opinionated about your domain objects, so I was able to use the same simple data classes as I was using with Kapper:

data class SuperHero(val id: UUID, val name: String, val email: String? = null, val age: Int? = null)
data class SuperHeroBattle(val superhero: String, val villain: String, val date: LocalDateTime)
data class Villain(val id: UUID, val name: String)

Ktorm does however require you to define a mapping to each DB table.
There are a number of ways this can be done. For example:

object SuperheroesTable : Table<Nothing>("super_heroes") {
    val id = uuid("id").primaryKey()
    val name = varchar("name")
    val email = varchar("email")
    val age = int("age")

    fun toDomain(row: QueryRowSet) =
        SuperHero(
            row[id] as UUID,
            row[name] as String,
            row[email],
            row[age],
        )
}

Once the mapping is set up, queries can use these. For example:

// listing some superheroes
database
    .from(superHeroes)
    .select()
    .map(superHeroes::toDomain) 

// finding a single superhero
database
    .from(superHeroes)
    .select()
    .where { superHeroes.id eq id }
    .map(superHeroes::toDomain)
    .firstOrNull()   

// inserting a super hero
database.insert(superHeroes) {
    set(it.id, superHero.id)
    set(it.name, superHero.name)
    set(it.email, superHero.email)
    set(it.age, superHero.age)
}

The join query, while using a custom DSL, was quite easy to write after consulting the documentation and validating the generated SQL:

database
    .from(superHeroes)
    .innerJoin(battles, on = superHeroes.id eq battles.superHeroId)
    .innerJoin(villains, on = battles.villainId eq villains.id)
    .select()
    .where { superHeroes.name eq superHeroName }
    .map {
        SuperHeroBattle(
            it[superHeroes.name] as String,
            it[villains.name] as String,
            it[battles.battleDate] as LocalDateTime,
        )
    }

Finally, looking at the more complex window query example, it took a while to go through the documentation, complemented by studying the source code and getting some direction from Claude AI, I was able to come up with this:

database.from(PopularMovies)
    .select(
        PopularMovies.title,
        PopularMovies.releaseDate,
        PopularMovies.grossWorldwide,
        avg(PopularMovies.grossWorldwide).over().aliased("total_average_gross"),
        avg(PopularMovies.grossWorldwide)
            .over {
                window().partitionBy(
                    // Cannot work this out, so abandoning the attempt
                    // ??("EXTRACT(YEAR FROM ${PopularMovies.releaseDate.name})")
                )
            }
            .aliased("average_annual_gross")
    ).map {
        PopularMovie(
            it[PopularMovies.title] as String,
            it[PopularMovies.grossWorldwide] as Long,
            it["total_average_gross"] as Double,
            it["average_annual_gross"] as Double
        )
    }

This looked promising, but failed at the last hurdle. As far as I could find out, an API to extract the year part from the datetime column is missing, meaning the window query API cannot be used.

This is a good example of one of the major problems with these type of abstractions. However rich they are, they are never complete.
Like the Hibernate example, I reverted to using native SQL to achieve the desired result.

Support for multiple databases

We explored API differences between Kapper, Hibernate and Ktorm, but we haven't yet looked at how the different libraries handle support for different types of databases.

Even though Kapper is currently only tested against PostgreSQL and MySQL, as an extension of the JDBC API, it is database agnostic and should work with any database for which there is a JDBC driver.
Hibernate and Ktorm also support many databases but because they both maintain an abstraction of SQL, they require explicit handling of database dialects.
Hibernate abstract the DB type from the user (which is also leaky as we will see). Ktorm is more explicit and provides different extension libraries for different databases, with different APIs for each.

The tests against the examples in this article were run against PostgreSQL as well as MySQL, which highlighted some issues with both abstractions.

UUID handling

Unlike PostgreSQL, MySQL does not support the UUID type. One approach in this case is to store the UUID as a string.
Kapper does handles this conversion when automapping to a class is used, but, importantly, users can easily use a custom mapping function to convert any DB type to any JVM type.

In hibernate, we needed to add an extra configuration key when building the SessionFactory to instruct Hibernate to use the VARCHAR column type only for MySQL:

if (jdbcUrl.contains(DbType.MySQL.name, ignoreCase = true)) {
    // glad I had Claude AI to help me with this little bit of "magic" :(
    it.setProperty("hibernate.type.preferred_uuid_jdbc_type", "VARCHAR")
}

Handling this in Ktorm required creating a custom column type, which made the mapping code a bit more awkward:

class CustomUUIDSqlType(
    private val dbType: DbType,
) : SqlType<UUID>(Types.VARCHAR, "custom_uuid") {
    override fun doSetParameter(
        ps: PreparedStatement,
        index: Int,
        parameter: UUID,
    ) {
        if (dbType == DbType.MySQL) {
            ps.setString(index, parameter.toString())
        } else {
            ps.setObject(index, parameter)
        }
    }

    override fun doGetResult(
        rs: ResultSet,
        index: Int,
    ): UUID? {
        return if (dbType == DbType.MySQL) {
            UUID.fromString(rs.getString(index))
        } else {
            rs.getObject(index) as UUID?
        }
    }
} 

class SuperheroesTable(dbType: DbType) : Table<Nothing>("super_heroes") {
    val id = registerColumn("id", CustomUUIDSqlType(dbType)).primaryKey()
    val name = varchar("name")
    val email = varchar("email")
    val age = int("age")
}

private val superHeroes = SuperheroesTable(dbType)

This shows how leaky abstractions can result in a lot more complexity than is necessary.

Ignore conflicts

As a second example, our example had a function to record a superhero battle, which would insert superheros and villains only when they didn't exist yet.
Commonly, a ON CONFLICT clause is used to only insert the row if one does not exist.
MySQL does not support this, instead we can use a ON DUPLICATE KEY UPDATE clause and set the primary key to its own value to avoid an update.
Because Kapper doesn't abstract the SQL, this is easy to achieve by simply varying the SQL query based on the DB type, for example:

it.execute(
    """
    INSERT INTO villains(id, name) 
    VALUES (:id, :name)
    ${ignoreConflict("id")}
    """.trimIndent(),
    "id" to villain.id,
    "name" to villain.name,
)

fun ignoreConflict(updateCol: String) =
    if (DbType.MySQL == dbType) {
        "ON DUPLICATE KEY UPDATE $updateCol=$updateCol"
    } else {
        "ON CONFLICT DO NOTHING"
    }

In Ktorm we were able to leverage the dedicated MySQL and PostgreSQL libraries to handle this:

when (dbType) {
    DbType.MYSQL -> {
        this.mySqlInsertOrUpdate(table) {
            block(this, table)
            onDuplicateKey {
                onDuplicateBlock(this, table)
            }
        }
    }
    DbType.POSTGRESQL -> {
        this.postgresqlInsertOrUpdate(table) {
            block(this, table)
            onConflict {
                doNothing()
            }
        }
    }
}

This case can probably be handled in Hibernate a well, but I fell back to using what most examples I have seen:

it.merge(superHero)

Simple, heh, but which query is generated?

// let's check if the villain exists
    select
        ve1_0.id,
        ve1_0.name 
    from
        villains ve1_0 
    where
        ve1_0.id=?
// oh, it doesn't, so let's insert it
    insert 
    into
        villains
        (name, id) 
    values
        (?, ?)

Maybe some people are happy with the extra query, or maybe they aren't aware.

Conclusion

The "abstraction trap" in ORMs stems from a fundamental misconception: that SQL is a problem to be solved rather than a tool to be embraced. Kapper takes a different approach by providing a thin, elegant layer that makes SQL comfortable to use without hiding it.

Kapper empowers developers with direct, efficient database access while preserving modern development benefits. It’s an extension of JDBC, not an abstraction of it—and that makes all the difference.

If you want to give Kapper a try, visit GitHub. I’d love to hear your feedback and suggestions for how we can make Kapper even better.