Library

Ktorm

Overview

Ktorm is developed as a "lightweight and elegant Kotlin ORM framework" that adopts a SQL-first approach for database access. It leverages the power of functional programming and Kotlin DSL to enable type-safe and expressive SQL operations. Instead of Active Record patterns, it provides highly descriptive query construction through SQL DSL, achieving modern database access layers suitable for contemporary Kotlin application development through compile-time type safety, null safety, and concise expression using lambda expressions for lightweight and high-performance operation.

Details

Ktorm 2025 edition fully supports Kotlin 1.9 and Coroutines 1.7, integrating best practices of asynchronous processing and reactive programming. Built on a lightweight architecture over JDBC, it achieves high performance with minimal overhead. Supporting major databases including PostgreSQL, MySQL, SQLite, SQL Server, and Oracle, it generates SQL adapted to each dialect. Enterprise-level features such as entity relationship mapping, transaction management, connection pool integration, and batch processing are comprehensively supported.

Key Features

• SQL DSL: Type-safe SQL construction through Kotlin-like functional DSL
• Lightweight Design: Minimal dependencies and runtime overhead
• Expressive Queries: Intuitive and readable query description using lambda expressions
• Multi-DB Support: Dialect and feature support for major databases
• Functional Approach: Query composition and reuse through function composition
• Coroutines Integration: Complete support for asynchronous processing and reactive streams

Pros and Cons

Pros

• Very readable and maintainable code through SQL DSL
• Lightweight and high-performance with minimal memory footprint
• Natural expression maximizing Kotlin language features
• Excellent compatibility with functional programming
• Low learning cost, leveraging existing SQL knowledge
• Perfect integration with Coroutines for asynchronous processing

Cons

• Lack of complex ORM features (lazy loading, cascade, etc.)
• Learning cost for developers accustomed to Active Record patterns
• Limited expression of entity relationships in some cases
• Limited advanced features for large enterprise applications
• Limited documentation and Japanese resources
• Few database-specific optimization features

Reference Pages

• Ktorm Official Site
• Ktorm GitHub Repository
• Ktorm Documentation

Code Examples

Setup

// build.gradle.kts
plugins {
    kotlin("jvm") version "1.9.22"
}

dependencies {
    implementation("org.ktorm:ktorm-core:3.6.0")
    implementation("org.ktorm:ktorm-support-mysql:3.6.0")
    implementation("org.ktorm:ktorm-support-postgresql:3.6.0")
    implementation("org.ktorm:ktorm-support-sqlite:3.6.0")
    
    // Database drivers
    implementation("mysql:mysql-connector-java:8.0.33")
    implementation("org.postgresql:postgresql:42.7.1")
    implementation("org.xerial:sqlite-jdbc:3.44.1.0")
    
    // Connection pool
    implementation("com.zaxxer:HikariCP:5.1.0")
    
    // Coroutines support
    implementation("org.jetbrains.kotlinx:kotlinx-coroutines-core:1.7.3")
    
    // JSON support (optional)
    implementation("org.ktorm:ktorm-jackson:3.6.0")
}

-- Database schema example
CREATE TABLE departments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(128) NOT NULL,
    location VARCHAR(128) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(128) NOT NULL,
    job VARCHAR(128) NOT NULL,
    manager_id INT,
    hire_date DATE NOT NULL,
    salary DECIMAL(10, 2),
    department_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (department_id) REFERENCES departments(id),
    FOREIGN KEY (manager_id) REFERENCES employees(id)
);

CREATE TABLE projects (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(128) NOT NULL,
    description TEXT,
    start_date DATE,
    end_date DATE,
    budget DECIMAL(12, 2),
    status VARCHAR(20) DEFAULT 'planning'
);

CREATE TABLE employee_projects (
    employee_id INT,
    project_id INT,
    role VARCHAR(50),
    allocation_percentage DECIMAL(5, 2),
    PRIMARY KEY (employee_id, project_id),
    FOREIGN KEY (employee_id) REFERENCES employees(id),
    FOREIGN KEY (project_id) REFERENCES projects(id)
);

Basic Usage

import org.ktorm.database.*
import org.ktorm.dsl.*
import org.ktorm.entity.*
import org.ktorm.schema.*
import kotlinx.coroutines.*
import java.time.LocalDate
import java.time.LocalDateTime
import java.math.BigDecimal

// Table schema definitions
object Departments : Table<Department>("departments") {
    val id = int("id").primaryKey().bindTo { it.id }
    val name = varchar("name").bindTo { it.name }
    val location = varchar("location").bindTo { it.location }
    val createdAt = datetime("created_at").bindTo { it.createdAt }
}

object Employees : Table<Employee>("employees") {
    val id = int("id").primaryKey().bindTo { it.id }
    val name = varchar("name").bindTo { it.name }
    val job = varchar("job").bindTo { it.job }
    val managerId = int("manager_id").bindTo { it.managerId }
    val hireDate = date("hire_date").bindTo { it.hireDate }
    val salary = decimal("salary").bindTo { it.salary }
    val departmentId = int("department_id").bindTo { it.departmentId }
    val createdAt = datetime("created_at").bindTo { it.createdAt }
}

// Entity classes
interface Department : Entity<Department> {
    companion object : Entity.Factory<Department>()
    val id: Int
    var name: String
    var location: String
    var createdAt: LocalDateTime
}

interface Employee : Entity<Employee> {
    companion object : Entity.Factory<Employee>()
    val id: Int
    var name: String
    var job: String
    var managerId: Int?
    var hireDate: LocalDate
    var salary: BigDecimal?
    var departmentId: Int
    var createdAt: LocalDateTime
}

// Database configuration
class DatabaseConfig {
    companion object {
        fun createDatabase(): Database {
            val dataSource = HikariDataSource().apply {
                jdbcUrl = "jdbc:mysql://localhost:3306/ktorm_demo"
                username = "root"
                password = "password"
                driverClassName = "com.mysql.cj.jdbc.Driver"
                maximumPoolSize = 10
                minimumIdle = 5
            }
            
            return Database.connect(dataSource)
        }
    }
}

// Repository implementation using SQL DSL
class EmployeeRepository(private val database: Database) {
    
    // Create employee
    suspend fun createEmployee(
        name: String,
        job: String,
        managerId: Int?,
        hireDate: LocalDate,
        salary: BigDecimal?,
        departmentId: Int
    ): Int = withContext(Dispatchers.IO) {
        database.insertAndGenerateKey(Employees) {
            set(it.name, name)
            set(it.job, job)
            set(it.managerId, managerId)
            set(it.hireDate, hireDate)
            set(it.salary, salary)
            set(it.departmentId, departmentId)
            set(it.createdAt, LocalDateTime.now())
        } as Int
    }
    
    // Find employee by ID
    suspend fun findEmployeeById(id: Int): Employee? = withContext(Dispatchers.IO) {
        database.sequenceOf(Employees).find { it.id eq id }
    }
    
    // Find all employees
    suspend fun findAllEmployees(): List<Employee> = withContext(Dispatchers.IO) {
        database.sequenceOf(Employees).toList()
    }
    
    // Update employee
    suspend fun updateEmployee(
        id: Int,
        name: String,
        job: String,
        managerId: Int?,
        salary: BigDecimal?
    ): Boolean = withContext(Dispatchers.IO) {
        database.update(Employees) {
            set(it.name, name)
            set(it.job, job)
            set(it.managerId, managerId)
            set(it.salary, salary)
            where { it.id eq id }
        } > 0
    }
    
    // Delete employee
    suspend fun deleteEmployee(id: Int): Boolean = withContext(Dispatchers.IO) {
        database.delete(Employees) { it.id eq id } > 0
    }
}

Complex Queries and JOINs

class AdvancedQueryRepository(private val database: Database) {
    
    // Join employees with departments
    suspend fun findEmployeesWithDepartments(): List<Pair<Employee, Department>> = withContext(Dispatchers.IO) {
        database.from(Employees)
            .innerJoin(Departments, on = Employees.departmentId eq Departments.id)
            .select()
            .map { row ->
                Pair(
                    Employees.createEntity(row),
                    Departments.createEntity(row)
                )
            }
    }
    
    // Complex query: Department statistics
    suspend fun getDepartmentStatistics(): List<DepartmentStats> = withContext(Dispatchers.IO) {
        database.from(Departments)
            .leftJoin(Employees, on = Departments.id eq Employees.departmentId)
            .select(
                Departments.id,
                Departments.name,
                Departments.location,
                count(Employees.id).aliased("employee_count"),
                avg(Employees.salary).aliased("avg_salary"),
                max(Employees.salary).aliased("max_salary"),
                min(Employees.salary).aliased("min_salary")
            )
            .groupBy(Departments.id, Departments.name, Departments.location)
            .map { row ->
                DepartmentStats(
                    departmentId = row[Departments.id]!!,
                    departmentName = row[Departments.name]!!,
                    location = row[Departments.location]!!,
                    employeeCount = row.getInt("employee_count"),
                    avgSalary = row.getBigDecimal("avg_salary"),
                    maxSalary = row.getBigDecimal("max_salary"),
                    minSalary = row.getBigDecimal("min_salary")
                )
            }
    }
    
    // Window function: Employee rank by salary within department
    suspend fun getEmployeeRankingBySalary(): List<EmployeeRanking> = withContext(Dispatchers.IO) {
        database.from(Employees)
            .innerJoin(Departments, on = Employees.departmentId eq Departments.id)
            .select(
                Employees.id,
                Employees.name,
                Employees.salary,
                Departments.name.aliased("dept_name"),
                rank().over(
                    partitionBy = Employees.departmentId,
                    orderBy = Employees.salary.desc()
                ).aliased("salary_rank")
            )
            .orderBy(Departments.name.asc(), Employees.salary.desc())
            .map { row ->
                EmployeeRanking(
                    employeeId = row[Employees.id]!!,
                    employeeName = row[Employees.name]!!,
                    salary = row[Employees.salary],
                    departmentName = row.getString("dept_name")!!,
                    salaryRank = row.getInt("salary_rank")
                )
            }
    }
}

// Data classes for complex query results
data class DepartmentStats(
    val departmentId: Int,
    val departmentName: String,
    val location: String,
    val employeeCount: Int,
    val avgSalary: BigDecimal?,
    val maxSalary: BigDecimal?,
    val minSalary: BigDecimal?
)

data class EmployeeRanking(
    val employeeId: Int,
    val employeeName: String,
    val salary: BigDecimal?,
    val departmentName: String,
    val salaryRank: Int
)

Transactions and Error Handling

class TransactionalService(private val database: Database) {
    
    // Simple transaction
    suspend fun transferEmployee(employeeId: Int, newDepartmentId: Int, newSalary: BigDecimal): Boolean = 
        withContext(Dispatchers.IO) {
            database.useTransaction { 
                // Verify employee exists
                val employee = database.sequenceOf(Employees).find { it.id eq employeeId }
                    ?: throw IllegalArgumentException("Employee not found: $employeeId")
                
                // Verify department exists
                val department = database.sequenceOf(Departments).find { it.id eq newDepartmentId }
                    ?: throw IllegalArgumentException("Department not found: $newDepartmentId")
                
                // Update employee
                val updated = database.update(Employees) {
                    set(it.departmentId, newDepartmentId)
                    set(it.salary, newSalary)
                    where { it.id eq employeeId }
                }
                
                updated > 0
            }
        }
    
    // Batch employee creation
    suspend fun batchCreateEmployees(employeesData: List<EmployeeData>): List<Int> = 
        withContext(Dispatchers.IO) {
            database.useTransaction {
                employeesData.map { data ->
                    database.insertAndGenerateKey(Employees) {
                        set(it.name, data.name)
                        set(it.job, data.job)
                        set(it.managerId, data.managerId)
                        set(it.hireDate, data.hireDate)
                        set(it.salary, data.salary)
                        set(it.departmentId, data.departmentId)
                        set(it.createdAt, LocalDateTime.now())
                    } as Int
                }
            }
        }
}

// Data class for batch operations
data class EmployeeData(
    val name: String,
    val job: String,
    val managerId: Int?,
    val hireDate: LocalDate,
    val salary: BigDecimal?,
    val departmentId: Int
)