1. Overview
The relational data model is very flexible, and it’s still at the heart of many data-driven services out there. As developers of these services, we know structural differences make mapping between object and relational models difficult, tedious, and usually error-prone. In other words, we face every day the Object-relational impedance mismatch.
Luckily, Kotlin language features enable a new generation of ORM tools, targeting seamless integration with our code base, and an overall more frictionless programming experience.
In this tutorial, we’ll learn about Ktorm, a lightweight and easy-to-use ORM tool for Kotlin.
2. Basic Setup
Let’s prepare our environment with minimal Maven dependencies for driving our Ktorm examples:
<dependency>
<groupId>org.ktorm</groupId>
<artifactId>ktorm-core</artifactId>
<version>3.6.0</version>
</dependency>
<dependency>
<groupId>org.ktorm</groupId>
<artifactId>ktorm-support-mysql</artifactId>
<version>3.6.0</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.33</version>
</dependency>
The latest version of Ktorm can be found on Maven Central. We also need to include the MySQL JDBC driver as a dependency, since we’ll later show how Ktorm can support SQL variants using this specific database.
3. Core Abstractions
Over the years, ORM tools and libraries have attempted to mitigate the mismatch between the object and the relational data models. However, some of these tools were more invasive than others.
JPA covered most relational mapping needs, at the price of an annotation process that some may find tedious. MyBatis enabled dynamic SQL generation through configuration, but at the price of type safety.
Ktorm doesn’t force mapping relational data to specific domain objects. It’s entirely possible to work at a lower abstraction layer, the SQL DSL, which provides a type safe approach to dynamic SQL statements generation, both for querying and manipulation.
When we need to work with domain entities, we’ll be using Ktorm’s Entity API, which is built on top of the foundational SQL DSL. This implies we won’t need to annotate our entity classes, for we’ll define our mappings within our table schema definitions.
We’ll see common Ktorm’s usage patterns using both strategies.
4. Domain Model
We’ll use a simple MySQL relational model as a reference for our discussion:
create table items(
id int not null primary key auto_increment,
description varchar(255) not null
);
create table customers(
id int not null primary key auto_increment,
email varchar(255) not null,
UNIQUE KEY(email)
);
create table orders(
id int not null primary key auto_increment,
item_id int not null,
customer_id int not null,
free_text_card varchar(255) null,
amount decimal(10,2),
last_update timestamp(3)
);
We can use Docker to quickly provision a MySQL environment hosting this simple database.
5. Table Schema Definition
Static SQL statements are immutable assets. They model predefined queries, and their structure doesn’t change with the state of our programs. However, most use cases are based on the runtime specification of query criteria, and thus on the dynamic creation of SQL statements.
Ktorm provides a type safe, domain specific language for the creation of dynamic SQL statements. Finally. we’ll be modelling SQL statements within the Kotlin language itself, but we need first to provide Ktorm with the table schemas of our relational model.
We’ll use Ktorm’s Table to translate our relational model to Kotlin code:
class ItemsTable: Table<Nothing>("items") {
val id = int("id").primaryKey()
val description = varchar("description")
}
class CustomersTable: Table<Nothing>("customers") {
val id = int("id").primaryKey()
val email = varchar("email")
}
class OrdersTable: Table<Nothing>("orders") {
val id = int("id").primaryKey()
val itemId = int("item_id")
val customerId = int("customer_id")
val card = base64("free_text_card", Charsets.UTF_8)
val amount = decimal("amount")
val timestamp = timestamp("last_update")
}
By extending Table, we’re translating the relational model to Kotlin code. In the process, wer’e providing references to those Column instances we’ll need later for query modelling.
Table comes with a generic type parameter. We can safely use Nothing here, for in this context we aren’t binding columns to entity properties, and we are simply describing the structure of our relational model.
Ktorm’s Column types are a superset of JDBC types. We can see how ItemsTable, CustomersTable and OrdersTable use common int, varchar and decimal column types. However, Ktorm also allows for the definition of custom SQL types.
5.1. Implementing Custom SQL Types
Our OrdersTable uses a base64 data type, which isn’t a native MySQL data type, nor a supported JDBC type. Indeed, we defined base64 as a custom Ktorm’s SqlType:
class Base64String(private val charset: Charset): SqlType<String>(Types.VARCHAR, "base64") {
override fun doGetResult(rs: ResultSet, index: Int): String? {
val retrievedData = rs.getString(index)
return when {
retrievedData.isNullOrBlank() -> null
else -> Base64.getDecoder().decode(retrievedData).toString(charset)
}
}
override fun doSetParameter(ps: PreparedStatement, index: Int, parameter: String) {
ps.setString(index, Base64.getEncoder().encodeToString(parameter.toByteArray(charset)))
}
}
A Ktorm’s custom SqlType implements a transformation between a Kotlin type T and a standard JDBC type.
We can see Base64String first extends SqlType, and provides the corresponding JDBC type and custom type name. Then, within doSetParameter(), we bind the Kotlin T type serialization to the prepared statement used for persisting data. Here, we’re simply serializing parameter to its Base64 encoding.
Within doGetResults(), we go the other way around, and we decode the persisted base64 string before returning the result.
Finally, we register our custom type definition against Ktorm’s BaseTable to make it available for schema modelling:
fun BaseTable<*>.base64(name: String, charset: Charset) = registerColumn(name, Base64String(charset))
As the example makes clear, a custom SqlType offloads some of our transformation logic to the ORM framework. We should use this capability to keep our codebase clear, say by isolating recurrent data transformations when retrieving data and consuming data sets.
6. Creating a Database
Anything useful in Ktorm starts with creating a Database instance, which will be our entry point for acquiring connections and issuing SQL statements against a relational repository.
We can create a Database on top of a connection pool, or we can let Database create connections on our behalf when needed. In this tutorial, we’ll be using the simplest strategy, and we’ll create an instance by providing the needed configuration in its constructor:
val database = Database.connect(
url = "jdbc:mysql://localhost:3306/KTORM_TEST",
driver = "com.mysql.cj.jdbc.Driver",
user = "root",
password = "root",
dialect = MySqlDialect()
)
We’re using a JDBC driver for creating the connection, and the dialect parameter specifies MySQL as the target for SQL generation. Besides, Ktorm supports almost every mainstream relational engine, and every dialect adds Kotlin extensions for modelling deviations from standard SQL directly as code.
7. Populating the Repository
We need some data to be ready for testing our queries. First, we’ll create instances of our table schemas for later reuse:
val ordersTable = OrdersTable()
val itemsTable = ItemsTable()
val customersTable = CustomersTable()
Using database, we’ll then populate our repository using batch inserts:
database.batchInsert(itemsTable) {
(1..10).forEach { idx ->
item {
set(it.description, "test_item_$idx")
}
}
}
database.bulkInsertOrUpdate(customersTable) {
(1..10).forEach { idx ->
item {
set(it.email, "[email protected]")
}
onDuplicateKey {
set(it.email, it.email)
}
}
}
database.batchInsert(ordersTable) {
(1..10).forEach { idx ->
item {
set(it.itemId, idx)
set(it.customerId, idx)
set(it.amount, 100.20.toBigDecimal())
set(it.card, "Card nr. $idx")
set(it.timestamp, Instant.now())
}
}
}
We’ve been using Ktorm to automate DML creation for adding some orders, items and customers to our repository. We created table rows by building items.
Previously defined schemas will assist in assigning column values, there’s no need to continuously switch back and forth between relational model DDL and code to identify which column belongs to which table.
There’s a gotcha here. Within the bulkInsertOrUpdate() method, we’re using the onDuplicateKey() extension for honouring the MySQL unique constraint we defined within the Customers DDL. Custom actions on constraint violation are specific to MySQL, and this extension is only available here because we specified MySQL as the target dialect for SQL generation when creating the database instance.
8. Type Safe Querying
With all moving parts in place, we can start exploring Ktorm’s type safe querying capabilities.
8.1. Basic Selection
Unconstrained SQL select statements simply require the DSL’s from and select clause specifications:
database
.from(ordersTable)
.select(ordersTable.id, ordersTable.card, ordersTable.amount)
8.2. Conditional Selection
Constrained SQL select statements need specification of a where clause to be used for row filtering:
database
.from(ordersTable)
.select(ordersTable.id, ordersTable.card, ordersTable.amount)
.where(ordersTable.itemId eq 1)
The where clause uses a ColumnDeclaring expression for building the filter content. Should we need more conditions, we could use the whereWithConditions() or whereWithOrConditions() methods. The two variants require filling the implicit MutableList lambda parameter, which Ktorm automatically reduces to a single boolean expression through conjunction or disjunction operators:
database
.from(ordersTable)
.select(ordersTable.id, ordersTable.card, ordersTable.amount)
.whereWithConditions {
it += (ordersTable.itemId eq 1)
it += (ordersTable.customerId eq 1)
}
8.3. Joining Tables
The join clause allows the specification of table joins:
database
.from(ordersTable)
.innerJoin(customersTable, on = ordersTable.customerId eq customersTable.id)
.innerJoin(itemsTable, on = ordersTable.itemId eq itemsTable.id )
.select(ordersTable.id, customersTable.email, itemsTable.description)
8.4. Aggregating Data
As expected, we can express common aggregations, like counting and summing, by using a combination of the groupBy and having clauses:
database
.from(ordersTable)
.innerJoin(customersTable, on = ordersTable.customerId eq customersTable.id)
.innerJoin(itemsTable, on = ordersTable.itemId eq itemsTable.id )
.select(customersTable.id, itemsTable.id, sum(ordersTable.amount).aliased("item_sales"))
.groupBy(customersTable.id, itemsTable.id)
.having(itemsTable.id eq 1)
8.5. Consuming Data
We can iterate over a Query instance to consume our data:
database
.from(ordersTable)
.select(ordersTable.id, ordersTable.card, ordersTable.amount)
.forEach {
println(it[ordersTable.id])
}
Internally, each Query instance delegates iteration to an internal QueryRowSet.
We can think of QueryRowSet as an evolution of the well-known JDBC’s ResultSet. It supports type safe access through a Column reference, and we can also consume data offline, which is independent of the availability of a database connection.
9. Entity API
When using Ktorm’s type safe DSL, we’re essentially using relational abstractions in our code. To link relational data with the object graph of our domain, we need Ktorm’s Entity API.
By using the Entity API, Ktorm maps relational data to our domain entities, which is exactly what we’re expecting from an ORM solution. Hence, the first problem we face is how to declare domain entities in a way Ktorm can understand.
9.1. Declaring Entities
Ktorm’s primary strategy for entity modelling is based on extending the Entity interface:
interface Item : Entity<Item> {
companion object : Entity.Factory<Item>()
val id: Int
var description: String
}
interface Customer : Entity<Customer> {
companion object : Entity.Factory<Customer>()
val id: Int
var email: String
}
interface Order : Entity<Order> {
companion object : Entity.Factory<Order>()
val id: Int
var item: Item
var customer: Customer
var card: String
var amount: BigDecimal
var timestamp: Instant
}
We can see Order is referencing Customer and Item entities through its members. Then, at runtime, Ktorm needs to automatically apply some joining and mapping logic to retrieve any Order instance.
Admittedly, entity modelling through interfaces can be somewhat controversial. However, the latest Ktorm releases support any class to play the role of entity, but we’ll lose support for entity manipulation and column bindings. Whether we can drop these framework capabilities depends on the structure of our program, and on the goal we’re trying to achieve by using an ORM framework.
9.2. Entity Mappings
To map our entities to tables, we’ll slightly update our schema definitions, and include bindings to our entities:
object Items : Table<Item>("items") {
val id = int("id").primaryKey().bindTo { it.id }
val description = varchar("description").bindTo { it.description }
}
object Customers : Table<Customer>("customers") {
val id = int("id").primaryKey().bindTo { it.id }
val email = varchar("email").bindTo { it.email }
}
object Orders : Table<Order>("orders") {
val id = int("id").primaryKey().bindTo { it.id }
val itemId = int("item_id").references(Items) { it.item }
val customerId = int("customer_id").references(Customers) { it.customer }
val card = base64("free_text_card", Charsets.UTF_8).bindTo { it.card }
val amount = decimal("amount").bindTo { it.amount }
val timestamp = timestamp("last_update").bindTo { it.timestamp }
}
We’re still using Table instances to define the relational structure of our repository. However, we’re now using Table’s generic parameter to reference one of the target entities we just defined. This change enables mapping each Column to the pertinent entity property via the bindTo() and references() extensions.
The bindTo() extension works well for mapping top level or nested scalar properties, like numbers and strings. Should we need to create complex structures, we should use references() extension method instead. Then, at runtime, Ktorm left-joins the referenced relational tables for us, and it retrieves the data needed for building the full graph of our complex entities.
9.3. Populating the Database Using Entities
With entity definition and mapping in place, we can use Ktorm’s sequenceOf() to build a sequence view of the entities stored in our database:
val Database.orders get() = this.sequenceOf(Orders)
val Database.customers get() = this.sequenceOf(Customers)
val Database.items get() = this.sequenceOf(Items)
We just extended Database with Sequence properties for each of our entities. Now we can use these sequences add() method for inserting data into our repository:
(1..10).forEach { idx ->
val item = Item {
description = "test_item_$idx"
}
items.add(item)
}
var customer = customers.find {
it.email eq "[email protected]"
}
if (customer == null) {
customer = Customer {
email = "[email protected]"
}
customers.add(customer)
}
(1..10).forEach { idx ->
val order = Order()
val item = items.find { it.id eq idx } ?: items.first()
order.item = item
order.customer = customer
order.amount = 100.20.toBigDecimal()
order.card = "Card nr. $idx"
order.timestamp = Instant.now()
orders.add(order)
}
We can insert independent entities, like Item and Customer, by simply building a corresponding instance and using it as a parameter for the add() method. Order also needs Item and Customer instances for insertion. Indeed, we must first fulfil these dependencies to keep the representation of the entity structure consistent at the database level.
9.4. Basic Entity Selection
We can use our items, customers and orders extension properties to retrieve an entity List from Database:
val orderEntities = database
.orders
.toList()
9.5. Conditional Entity Selection
We can filter our entity sequences in a way much similar to what we are used to with standard sequences:
val filteredOrderEntities = database
.orders
.filter { it.itemId eq 1 }
.toList()
Should we need to add more conditions, we can simply append more filters to an entity sequence:
val multiFilteredOrderEntities = database
.orders
.filter { it.itemId eq 1 }
.filter { it.customerId eq 1 }
.toList()
There’s a gotcha here. The filter() method we’re using is an EntitySequence extension directly provided by Ktorm:
public inline fun <E : Any, T : BaseTable<E>> EntitySequence<E, T>.filter(
predicate: (T) -> ColumnDeclaring<Boolean>
): EntitySequence<E, T>
At runtime, Ktorm transforms filter() extension calls to conditional SQL statements, and then it fetches rows and maps them to create our result. Since Order is a complex entity, here’s what the generated SQL will look like:
SELECT orders.id AS orders_id, orders.item_id AS orders_item_id, orders.customer_id AS orders_customer_id, orders.free_text_card AS orders_free_text_card, orders.amount AS orders_amount, orders.last_update AS orders_last_update, _ref0.id AS _ref0_id, _ref0.description AS _ref0_description, _ref1.id AS _ref1_id, _ref1.email AS _ref1_email
FROM orders
LEFT JOIN items _ref0 ON orders.item_id = _ref0.id
LEFT JOIN customers _ref1 ON orders.customer_id = _ref1.id
WHERE (orders.item_id = ?) AND (orders.customer_id = ?)
We can see that the generated SQL already applies left joins when required. Indeed, our domain referential structure has already been completely declared at the Table schema level. When working with entities, Ktorm honours these declarations, and we no longer need to express table joins in code. However, we can also instruct Ktorm to stop creating automatic joins should this become a performance problem.
10. Conclusion
In this article, we looked at Ktorm, and scratched the surface of this framework’s ORM capabilities.
As developers, we can appreciate the type safe DSL for working with tables and rows abstractions. Such an approach provides low-level control over the behaviour of the repository, well suitable for infrastructural code development. Ktorm’s Entity API can be used instead to fill the gap with domain-modelling patterns, for the entity abstraction enables a different level of reasoning on the structure of our logic.
As always, the code samples can be found over on GitHub.
