Try Ktorm, a lightweight ORM Framework for Kotlin with strong-typed SQL DSL and sequence APIs

What’s Ktorm?

Ktorm is a lightweight and efficient ORM Framework for Kotlin directly based on pure JDBC. It provides strong-typed and flexible SQL DSL and convenient sequence APIs to reduce our duplicated effort on database operations. All the SQLs, of course, are generated automatically. Ktorm is open source under the license of Apache 2.0, and its code can be found on GitHub. Leave your star if it’s helpful to you: vincentlauvlwj/Ktorm

For more documentation, go to our site: https://ktorm.liuwj.me.

Features

  • No configuration files, no XML, no third-party dependencies, lightweight, easy to use.
  • Strong typed SQL DSL, exposing low-level bugs at compile time.
  • Flexible queries, fine-grained control over the generated SQLs as you wish.
  • Entity sequence APIs, writing queries via sequence functions such as filter, map, sortedBy, etc., just like using Kotlin’s native collections and sequences.
  • Extensible design, write your own extensions to support more operators, data types, SQL functions, database dialects, etc.

Quick Start

Ktorm was deployed to maven central and jcenter, so you just need to add a dependency to your pom.xml file if you are using maven:

<dependency>
    <groupId>me.liuwj.ktorm</groupId>
    <artifactId>ktorm-core</artifactId>
    <version>${ktorm.version}</version>
</dependency>

Or Gradle:

compile "me.liuwj.ktorm:ktorm-core:${ktorm.version}"

Firstly, create Kotlin objects to describe your table schemas:

object Departments : Table<Nothing>("t_department") {
    val id by int("id").primaryKey()
    val name by varchar("name")
    val location by varchar("location")
}

object Employees : Table<Nothing>("t_employee") {
    val id by int("id").primaryKey()
    val name by varchar("name")
    val job by varchar("job")
    val managerId by int("manager_id")
    val hireDate by date("hire_date")
    val salary by long("salary")
    val departmentId by int("department_id")
}

Then, connect to your database and write a simple query:

fun main() {
    Database.connect("jdbc:mysql://localhost:3306/ktorm", driver = "com.mysql.jdbc.Driver")

    for (row in Employees.select()) {
        println(row[Employees.name])
    }
}

Now you can run this program, Ktorm will generate a SQL select * from t_employee, selecting all employees in the table and printing their names. You can use the for-each loop because the query object returned by the select function implements the Iterable<T> interface. Any other extension functions on Iterable<T> are also available, eg. map/filter/reduce provided by Kotlin standard lib.

SQL DSL

Let’s add some filter conditions to the query:

val names = Employees
    .select(Employees.name)
    .where { (Employees.departmentId eq 1) and (Employees.name like "%vince%") }
    .map { row -> row[Employees.name] }
println(names)

Generated SQL:

select t_employee.name as t_employee_name 
from t_employee 
where (t_employee.department_id = ?) and (t_employee.name like ?) 

That’s the magic of Kotlin, writing a query with Ktorm is easy and natural, the generated SQL is exactly corresponding to the origin Kotlin code. And moreover, it’s strong-typed, the compiler will check your codes before it runs, and you will be benefited from the IDE’s intelligent sense and code completion.

Dynamic query based on conditions:

val names = Employees
    .select(Employees.name)
    .whereWithConditions {
        if (someCondition) {
            it += Employees.managerId.isNull()
        }
        if (otherCondition) {
            it += Employees.departmentId eq 1
        }
    }
    .map { it.getString(1) }

Aggregation:

val t = Employees
val salaries = t
    .select(t.departmentId, avg(t.salary))
    .groupBy(t.departmentId)
    .having { avg(t.salary) greater 100.0 }
    .associate { it.getInt(1) to it.getDouble(2) }

Union:

Employees
    .select(Employees.id)
    .unionAll(
        Departments.select(Departments.id)
    )
    .unionAll(
        Departments.select(Departments.id)
    )
    .orderBy(Employees.id.desc())

Joining:

data class Names(val name: String, val managerName: String?, val departmentName: String)

val emp = Employees.aliased("emp")
val mgr = Employees.aliased("mgr")
val dept = Departments.aliased("dept")

val results = emp
    .leftJoin(dept, on = emp.departmentId eq dept.id)
    .leftJoin(mgr, on = emp.managerId eq mgr.id)
    .select(emp.name, mgr.name, dept.name)
    .orderBy(emp.id.asc())
    .map {
        Names(
            name = it.getString(1),
            managerName = it.getString(2),
            departmentName = it.getString(3)
        )
    }

Insert:

Employees.insert {
    it.name to "jerry"
    it.job to "trainee"
    it.managerId to 1
    it.hireDate to LocalDate.now()
    it.salary to 50
    it.departmentId to 1
}

Update:

Employees.update {
    it.job to "engineer"
    it.managerId to null
    it.salary to 100

    where {
        it.id eq 2
    }
}

Delete:

Employees.delete { it.id eq 4 }

Refer to detailed documentation for more usages about SQL DSL.

Entities and Column Binding

In addition to SQL DSL, entity objects are also supported just like other ORM frameworks do. We need to define entity classes firstly and bind table objects to them. In Ktorm, entity classes are defined as interfaces extending from Entity<E>:

interface Department : Entity<Department> {
    val id: Int
    var name: String
    var location: String
}

interface Employee : Entity<Employee> {
    val id: Int?
    var name: String
    var job: String
    var manager: Employee?
    var hireDate: LocalDate
    var salary: Long
    var department: Department
}

Modify the table objects above, binding database columns to entity properties:

object Departments : Table<Department>("t_department") {
    val id by int("id").primaryKey().bindTo { it.id }
    val name by varchar("name").bindTo { it.name }
    val location by varchar("location").bindTo { it.location }
}

object Employees : Table<Employee>("t_employee") {
    val id by int("id").primaryKey().bindTo { it.id }
    val name by varchar("name").bindTo { it.name }
    val job by varchar("job").bindTo { it.job }
    val managerId by int("manager_id").bindTo { it.manager.id }
    val hireDate by date("hire_date").bindTo { it.hireDate }
    val salary by long("salary").bindTo { it.salary }
    val departmentId by int("department_id").references(Departments) { it.department }
}

Naming Strategy: It’s highly recommended to name your entity classes by singular nouns, name table objects by plurals (eg. Employee/Employees, Department/Departments).

Now that column bindings are configured, so we can use those convenient extension functions for entities. For example, finding an employee by name:

val vince = Employees.findOne { it.name eq "vince" }
println(vince)

The findOne function accepts a lambda expression, generating a select sql with the condition returned by the lambda, auto left joining the referenced table t_department . Generated SQL:

select * 
from t_employee 
left join t_department _ref0 on t_employee.department_id = _ref0.id 
where t_employee.name = ?

Some other find* functions:

Employees.findAll()
Employees.findById(1)
Employees.findListByIds(listOf(1))
Employees.findMapByIds(listOf(1))
Employees.findList { it.departmentId eq 1 }
Employees.findOne { it.name eq "vince" }

Save entities to database:

val employee = Employee {
    name = "jerry"
    job = "trainee"
    manager = Employees.findOne { it.name eq "vince" }
    hireDate = LocalDate.now()
    salary = 50
    department = Departments.findOne { it.name eq "tech" }
}

Employees.add(employee)

Flush property changes in memory to database:

val employee = Employees.findById(2) ?: return
employee.job = "engineer"
employee.salary = 100
employee.flushChanges()

Delete a entity from database:

val employee = Employees.findById(2) ?: return
employee.delete()

Detailed usages of entity APIs can be found in the documentation of column binding and entity findings.

Entity Sequence APIs

In addition to the find* functions, Ktorm also provides a set of APIs named Entity Sequence, which can be used to obtain entity objects from databases. As the name implies, its style and use pattern are highly similar to the sequence APIs in Kotlin standard lib, as it provides many extension functions with the same names, such as filter, map, reduce, etc.

To create an entity sequence, we can call the extension function asSequence on a table object:

val sequence = Employees.asSequence()

Most of the entity sequence APIs are provided as extension functions, which can be divided into two groups, they are intermediate operations and terminal operations.

Intermediate Operations

These functions don’t execute the internal queries but return new-created sequence objects applying some modifications. For example, the filter function creates a new sequence object with the filter condition given by its parameter. The following code obtains all the employees in department 1 by using filter:

val employees = Employees.asSequence().filter { it.departmentId eq 1 }.toList()

We can see that the usage is almost the same as kotlin.sequences.Sequence, the only difference is the == in the lambda is replaced by the eq function. The filter function can also be called continuously, as all the filter conditions are combined with the and operator.

val employees = Employees
    .asSequence()
    .filter { it.departmentId eq 1 }
    .filter { it.managerId.isNotNull() }
    .toList()

Generated SQL:

select * 
from t_employee 
left join t_department _ref0 on t_employee.department_id = _ref0.id 
where (t_employee.department_id = ?) and (t_employee.manager_id is not null)

Use sortedBy or soretdByDescending to sort entities in a sequence:

val employees = Employees.asSequence().sortedBy { it.salary }.toList()

Use drop and take for pagination:

val employees = Employees.asSequence().drop(1).take(1).toList()

Terminal Operations

Terminal operations of entity sequences execute the queries right now, then obtain the query results and perform some calculations on them. The for-each loop is a typical terminal operation, and the following code uses it to print all employees in the sequence:

for (employee in Employees.asSequence()) {
    println(employee)
}

Generated SQL:

select * 
from t_employee 
left join t_department _ref0 on t_employee.department_id = _ref0.id

The toCollection functions (including toList, toSet, etc.) are used to collect all the elements into a collection:

val employees = Employees.asSequence().toCollection(ArrayList())

The mapColumns function is used to obtain the results of a column:

val names = Employees.asSequenceWithoutReferences().mapColumns { it.name }

Additional, if we want to select two or more columns, we can change to mapColumns2 or mapColumns3, then we need to wrap our selected columns by Pair or Triple in the closure, and the function’s return type becomes List<Pair<C1?, C2?>> or List<Triple<C1?, C2?, C3?>>.

Employees
    .asSequenceWithoutReferences()
    .filter { it.departmentId eq 1 }
    .mapColumns2 { Pair(it.id, it.name) }
    .forEach { (id, name) ->
        println("$id:$name")
    }

Generated SQL:

select t_employee.id, t_employee.name
from t_employee 
where t_employee.department_id = ?

Other familiar functions are also supported, such as fold, reduce, forEach, etc. The following code calculates the total salary of all employees:

val totalSalary = Employees.asSequence().fold(0L) { acc, employee -> acc + employee.salary }

Sequence Aggregation

The entity sequence APIs not only allow us to obtain entities from databases just like using kotlin.sequences.Sequence, but they also provide rich support for aggregations, so we can conveniently count the columns, sum them, or calculate their averages, etc.

The following code obtains the max salary in department 1:

val max = Employees
    .asSequenceWithoutReferences()
    .filter { it.departmentId eq 1 }
    .aggregateColumns { max(it.salary) }

Also, if we want to aggregate two or more columns, we can change to aggregateColumns2 or aggregateColumns3, then we need to wrap our aggregate expressions by Pair or Triple in the closure, and the function’s return type becomes Pair<C1?, C2?> or Triple<C1?, C2?, C3?>. The example below obtains the average and the range of salaries in department 1:

val (avg, diff) = Employees
    .asSequenceWithoutReferences()
    .filter { it.departmentId eq 1 }
    .aggregateColumns2 { Pair(avg(it.salary), max(it.salary) - min(it.salary)) }

Generated SQL:

select avg(t_employee.salary), max(t_employee.salary) - min(t_employee.salary) 
from t_employee 
where t_employee.department_id = ?

Ktorm also provides many convenient helper functions implemented based on aggregateColumns, they are count, any, none, all, sumBy, maxBy, minBy, averageBy.

The following code obtains the max salary in department 1 using maxBy instead:

val max = Employees
    .asSequenceWithoutReferences()
    .filter { it.departmentId eq 1 }
    .maxBy { it.salary }

Additionally, grouping aggregations are also supported, we just need to call groupingBy before calling aggregateColumns. The following code obtains the average salaries for each department. Here, the result’s type is Map<Int?, Double?>, in which the keys are departments’ IDs, and the values are the average salaries of the departments.

val averageSalaries = Employees
    .asSequenceWithoutReferences()
    .groupingBy { it.departmentId }
    .aggregateColumns { avg(it.salary) }

Generated SQL:

select t_employee.department_id, avg(t_employee.salary) 
from t_employee 
group by t_employee.department_id

Ktorm also provides many convenient helper functions for grouping aggregations, they are eachCount(To), eachSumBy(To), eachMaxBy(To), eachMinBy(To), eachAverageBy(To). With these functions, we can write the code below to obtain average salaries for each department:

val averageSalaries = Employees
    .asSequenceWithoutReferences()
    .groupingBy { it.departmentId }
    .eachAverageBy { it.salary }

Other familiar functions are also supported, such as aggregate, fold, reduce, etc. They have the same names as the extension functions of kotlin.collections.Grouping, and the usages are totally the same. The following code calculates the total salaries for each department using fold:

val totalSalaries = Employees
    .asSequenceWithoutReferences()
    .groupingBy { it.departmentId }
    .fold(0L) { acc, employee -> 
        acc + employee.salary 
    }

Detailed usages of entity sequence APIs can be found in the documentation of entity sequence and sequence aggregation.

9 Likes

What are benefits compared to JB Exposed?

3 Likes

@deviant thanks I am asking me the same. At the first sight it looks a bit easier (nearer to SQL) compared to Exposed. But I don’t know anyone of them two. Until today there is no answer and no comparison. So why should anyone choose Ktorm over Exposed? at least for starting

as for me exposed is sort of unpractical to any real use. We do start about 2 new projects a month, ans still use jdbc+scala.anorm as it saves many hundreds dev hours. JB exposed is like a modern kotlin, oriented to JB developers ease and JB chief architect pleasure, not koltin developer needs anymore. So, kotlin is now without traits and with crazy delegation sytnaxt instead of traits or multiple inheritance (amusingly deos the same but requiresus to write much more less readable code), and JB turned to be not DRY and not KISS. Many unneeded repetitions when using exposed, sources turn to be bulky, not readable and not writable. We need alternatives to JB as there is no chance they’ll do us all a favor and fire their new chief architect… As this JB’s kotlin-kiliing will continue, only 3rd pty products leave some hope. This project is one that’s still promising. Keep up a good work :wink:

1 Like

@real.sergeych It’s great that you like the library and it’s nice to have a post comparing your experience with Exposed to Ktorm :+1:

I just wanted to say that your reply has a content focused on Kotlin in general and carries an ad hominem tone with the comment on staffing. Those topics in a reply has a high risk of steering this thread topic away from Ktorm since it brings up external discussion points and people may be prompted to address those unrelated issues you bring up.

For others finding this thread: Let’s try not to reply too much along off-topic lines. :slight_smile:
I ended up not posting my response to @real.sergeych, which was especially hard because I disagree and want to ask clarifying questions to better understand his post.

@real.sergeych You could edit it if you want–I’ll edit/remove this one too if you do to make sure my reply isn’t a distraction from the topic. :slight_smile:

2 Likes

If you (and others) want to discuss this, just open up a new topic. It’ll be interesting to read. But you are right, it’s off-topic here.

1 Like