Griffio

© 2024. All rights reserved.

More Kotlin (Sql Persistence Comparison)

20 Apr 2023

Database persistence using Sql from Kotlin

The libraries listed below, you will see, are typically described as a light weight Object Relational Mapper or as a Sql Row Mapper.

Another approach is called Active Record where persistence operations are included in the Objects behaviour.

An Object Relational Mapper translation layer is responsible to load and store your Application’s Entity model as a representation of the relational schema. There exists a so-called impedance mismatch where, for example, Kotlin has Maps, Sets and Ordered/UnOrdered Collections but no concept of Tables, Columns and Rows. What can be surprising is that all the rows in the database could be pulled across to keep the semantics of, for example, a Set or ordered collection in the Application. How does a Relational Database handle Object Orientated Polymorphism?

Persisting to the Rdbms involves traversing a given instance of an Entity model and detecting dirty objects that need inserting, updating in the correct order. The job of the Orm is to manage this entangled state back to coherence on the database side.

Associations are represented in Object-Oriented models and can be bidirectional, the Rdbms uses the constraint of foreign keys on the relationship owner side. Working with an Orm library is often finding a balance between modelling the Entity’s lazy-loading, eager-fetching and caching strategy. If you have a clear enough vision of the Object graph and its associations that you are modelling, this can make an Orm the easier choice, to manage through declarative Annotations or Dsl - the Sql is generated at runtime from this configuration.

A Sql Row Mapper focuses on making Sql persistence with the Rdbms Driver more ergonomic - Entity relationships (Foreign Key associations) are often managed manually. Simplicity is often the primary motivation by using the Sql dialect directly. Duplication is favoured over abstraction. It’s much easier to work around problems in legacy schema where you have complete control of the Sql.

Choosing one library over another should consider some of the following questions:

Designed for Kotlin

The following libraries are Kotlin centric even though they may use Java libraries like Jdbc, logging, Jackson

Other Kotlin libraries not covered here but worth comparing are Zeko-Sql-Builder and Zeko-Data-Mapper. These are in less active development.

A recent Kotlin jdbc wrapper to use is lite-for-jdbc for common database interactions.

Another is kotlin-jdsl. Kotlin JDSL provides a domain-specific language (DSL) based on KClass and KProperty instead of code generation to provide type safe dynamic queries.

terpal-sql is a Kotlin library that allows you to write SQL queries in Kotlin using interpolated strings in an SQL-injection-safe way.

kotliquery Jdbc made usefull with Kotlin

The Golang project sqlc includes code generation from the schema for Kotlin jdbc clients.

Exposed Kotlin Sql Framework

Summary

Komapper Kotlin Orm for Jdbc and R2dbc

Summary

Kotysa The idiomatic way to write type-safe Sql in Kotlin

Summary

Ktorm A lightweight Orm framework for Kotlin with strong-typed Sql Dsl and sequence Api.

Summary

SqlDelight Generates typesafe Kotlin APIs from Sql

Summary

Designed for Java with Kotlin support

The following libraries are Java centric with some Kotlin extension functions for use with Generics types.

I have intentionally left out traditional Java frameworks Spring Data Jpa

Jdbi Designed to provide convenient tabular data access in Java; including templated Sql, parameterized and strongly typed queries, and Streams integration

Summary

JOOQ The best way to write Sql in Java

Summary

MyBatis Sql mapper framework for Java

Summary

Previous Posts