What's Changed

• Upgrade Kotlin version to 1.9.0.
• Support code generation by KSP (Kotlin Symbol Processing). @lookup-cat is the main contributor. Sincere thanks to him.
• Support PostgreSQL array types and array_position function, by @svenallers in #479.

Ktorm KSP Quick Start

Ktorm KSP can help us generate boilerplate code for table schemas. To use Ktorm KSP, you need to apply our plugin to your project first.

For Gradle users:

plugins {
    kotlin("jvm") version "1.9.0"
    id("com.google.devtools.ksp") version "1.9.0-1.0.13"
}

dependencies {
    implementation(kotlin("stdlib"))
    implementation(kotlin("reflect"))
    implementation("org.ktorm:ktorm-core:${ktorm.version}")
    implementation("org.ktorm:ktorm-support-mysql:${ktorm.version}")
    implementation("org.ktorm:ktorm-ksp-annotations:${ktorm.version}")
    ksp("org.ktorm:ktorm-ksp-compiler:${ktorm.version}")
}

ksp {
    arg("ktorm.dbNamingStrategy", "lower-snake-case")
}

For Maven users:

<dependencies>
    <dependency>
        <groupId>org.jetbrains.kotlin</groupId>
        <artifactId>kotlin-stdlib</artifactId>
        <version>${kotlin.version}</version>
    </dependency>
    <dependency>
        <groupId>org.jetbrains.kotlin</groupId>
        <artifactId>kotlin-reflect</artifactId>
        <version>${kotlin.version}</version>
    </dependency>
    <dependency>
        <groupId>org.ktorm</groupId>
        <artifactId>ktorm-core</artifactId>
        <version>${ktorm.version}</version>
    </dependency>
    <dependency>
        <groupId>org.ktorm</groupId>
        <artifactId>ktorm-support-mysql</artifactId>
        <version>${ktorm.version}</version>
    </dependency>
    <dependency>
        <groupId>org.ktorm</groupId>
        <artifactId>ktorm-ksp-annotations</artifactId>
        <version>${ktorm.version}</version>
    </dependency>
</dependencies>

<build>
    <sourceDirectory>src/main/kotlin</sourceDirectory>
    <testSourceDirectory>src/test/kotlin</testSourceDirectory>
    <plugins>
        <plugin>
            <groupId>org.jetbrains.kotlin</groupId>
            <artifactId>kotlin-maven-plugin</artifactId>
            <version>${kotlin.version}</version>
            <executions>
                <execution>
                    <id>compile</id>
                    <phase>compile</phase>
                    <goals>
                        <goal>compile</goal>
                    </goals>
                    <configuration>
                        <compilerPlugins>
                            <compilerPlugin>ksp</compilerPlugin>
                        </compilerPlugins>
                        <pluginOptions>
                            <option>ksp:apoption=ktorm.dbNamingStrategy=lower-snake-case</option>
                        </pluginOptions>
                    </configuration>
                </execution>
                <execution>
                    <id>test-compile</id>
                    <phase>test-compile</phase>
                    <goals>
                        <goal>test-compile</goal>
                    </goals>
                    <configuration>
                        <compilerPlugins>
                            <compilerPlugin>ksp</compilerPlugin>
                        </compilerPlugins>
                        <pluginOptions>
                            <option>ksp:apoption=ktorm.dbNamingStrategy=lower-snake-case</option>
                        </pluginOptions>
                    </configuration>
                </execution>
            </executions>
            <dependencies>
                <dependency>
                    <groupId>org.ktorm</groupId>
                    <artifactId>ktorm-ksp-compiler-maven-plugin</artifactId>
                    <version>${ktorm.version}</version>
                </dependency>
            </dependencies>
        </plugin>
    </plugins>
</build>

Now mark your entity classes with annotation @Table:

@Table("t_department")
interface Department : Entity<Department> {
    @PrimaryKey
    var id: Int
    var name: String
    var location: String
}

@Table("t_employee")
interface Employee : Entity<Employee> {
    @PrimaryKey
    var id: Int
    var name: String
    var job: String
    var managerId: Int?
    var hireDate: LocalDate
    var salary: Long
    @References
    var department: Department
}

Compile the project with ./gradlew assemble or mvn compile to trigger code generation, then you can use entity API like below, without defining table schemas and column bindings manually:

// Create the entity just like there is a constructor.
// Actually Employee() is a function generated by Ktorm KSP.
val employee = Employee(name = "vince", job = "engineer")
database.employees.add(employee)

// Database.employees is an extension property generated by Ktorm KSP returning a default entity sequence of Employee.
// You can also use it.refs to access the referenced table in filter conditions, the generated SQL will be like:
//   select *
//   from t_employee
//   left join t_department _ref0 on t_employee.department_id = _ref0.id
//   where _ref0.name = ? and _ref0.location = ?
val employees = database.employees
    .filter { it.refs.department.name eq "tech" }
    .filter { it.refs.department.location eq "Guangzhou" }
    .toList()

You can also use data classes as entities. In this case, @Table annotation is required, too:

@Table
data class User(
    @PrimaryKey
    var id: Int,
    var username: String,
    var age: Int
)

// Use the generated add function to insert a new row into the table.
// Optional argument useGeneratedKey = true means the user ID will be generated by the database.
val user = User(id = 0, username = "vince", age = 28)
database.users.add(user, useGeneratedKey = true)

// Database.users is an extension property generated by Ktorm KSP.
// Use the entity sequence API to fetch all users under 28 from the table.
val users = database.users.filter { it.age lte 28 }.toList()

Generated Code

Ktorm KSP generates .kt files for each entity class. The generated files are located in the project's build directory. For the examples above, the generated files are listed as follows.

Departments.kt

// Auto-generated by ktorm-ksp-compiler, DO NOT EDIT!
@file:Suppress("RedundantVisibilityModifier")

package org.ktorm.example.model

import org.ktorm.database.Database
import org.ktorm.entity.Entity
import org.ktorm.entity.EntitySequence
import org.ktorm.entity.sequenceOf
import org.ktorm.ksp.annotation.Undefined
import org.ktorm.schema.Column
import org.ktorm.schema.Table
import org.ktorm.schema.int
import org.ktorm.schema.varchar
import kotlin.Int
import kotlin.String
import kotlin.Suppress

/**
 * Table t_department.
 */
public open class Departments(alias: String?) : Table<Department>("t_department", alias, schema = "test") {
    /**
     * Column id.
     */
    public val id: Column<Int> = int("id").primaryKey().bindTo { it.id }

    /**
     * Column name.
     */
    public val name: Column<String> = varchar("name").bindTo { it.name }

    /**
     * Column location.
     */
    public val location: Column<String> = varchar("location").bindTo { it.location }

    /**
     * Return a new-created table object with all properties (including the table name and columns and
     * so on) being copied from this table, but applying a new alias given by the parameter.
     */
    public override fun aliased(alias: String): Departments = Departments(alias)

    /**
     * The default table object of t_department.
     */
    public companion object : Departments(alias = null)
}

/**
 * Return the default entity sequence of [Departments].
 */
public val Database.departments: EntitySequence<Department, Departments> get() = this.sequenceOf(Departments)

/**
 * Create an entity of [Department] and specify the initial values for each property, properties
 * that doesn't have an initial value will leave unassigned.
 */
public fun Department(
    id: Int? = Undefined.of(),
    name: String? = Undefined.of(),
    location: String? = Undefined.of()
): Department {
    val entity = Entity.create<Department>()
    if (id !== Undefined.of<Int>()) {
        entity["id"] = id ?: error("`id` should not be null.")
    }
    if (name !== Undefined.of<String>()) {
        entity.name = name ?: error("`name` should not be null.")
    }
    if (location !== Undefined.of<String>()) {
        entity.location = location ?: error("`location` should not be null.")
    }
    return entity
}

/**
 * Return a deep copy of this entity (which has the same property values and tracked statuses), and
 * alter the specified property values.
 */
public fun Department.copy(
    id: Int? = Undefined.of(),
    name: String? = Undefined.of(),
    location: String? = Undefined.of()
): Department {
    val entity = this.copy()
    if (id !== Undefined.of<Int>()) {
        entity["id"] = id ?: error("`id` should not be null.")
    }
    if (name !== Undefined.of<String>()) {
        entity.name = name ?: error("`name` should not be null.")
    }
    if (location !== Undefined.of<String>()) {
        entity.location = location ?: error("`location` should not be null.")
    }
    return entity
}

/**
 * Return the value of [Department.id].
 */
public operator fun Department.component1(): Int = this.id

/**
 * Return the value of [Department.name].
 */
public operator fun Department.component2(): String = this.name

/**
 * Return the value of [Department.location].
 */
public operator fun Department.component3(): String = this.location

Employees.kt

// Auto-generated by ktorm-ksp-compiler, DO NOT EDIT!
@file:Suppress("RedundantVisibilityModifier")

package org.ktorm.example.model

import org.ktorm.database.Database
import org.ktorm.entity.Entity
import org.ktorm.entity.EntitySequence
import org.ktorm.entity.sequenceOf
import org.ktorm.ksp.annotation.Undefined
import org.ktorm.schema.Column
import org.ktorm.schema.Table
import org.ktorm.schema.date
import org.ktorm.schema.int
import org.ktorm.schema.long
import org.ktorm.schema.varchar
import java.time.LocalDate
import kotlin.Int
import kotlin.Long
import kotlin.String
import kotlin.Suppress
import kotlin.collections.List

/**
 * Table t_employee.
 */
public open class Employees(alias: String?) : Table<Employee>("t_employee", alias, schema = "test") {
    /**
     * Column id.
     */
    public val id: Column<Int> = int("id").primaryKey().bindTo { it.id }

    /**
     * Column name.
     */
    public val name: Column<String> = varchar("name").bindTo { it.name }

    /**
     * Column job.
     */
    public val job: Column<String> = varchar("job").bindTo { it.job }

    /**
     * Column manager_id.
     */
    public val managerId: Column<Int> = int("manager_id").bindTo { it.managerId }

    /**
     * Column hire_date.
     */
    public val hireDate: Column<LocalDate> = date("hire_date").bindTo { it.hireDate }

    /**
     * Column salary.
     */
    public val salary: Column<Long> = long("salary").bindTo { it.salary }

    /**
     * Column department_id.
     */
    public val departmentId: Column<Int> = int("department_id").references(Departments) { it.department }

    /**
     * Return a new-created table object with all properties (including the table name and columns and
     * so on) being copied from this table, but applying a new alias given by the parameter.
     */
    public override fun aliased(alias: String): Employees = Employees(alias)

    /**
     * The default table object of t_employee.
     */
    public companion object : Employees(alias = null)
}

/**
 * Return the default entity sequence of [Employees].
 */
public val Database.employees: EntitySequence<Employee, Employees> get() = this.sequenceOf(Employees)

/**
 * Return the refs object that provides a convenient way to access referenced tables.
 */
public val Employees.refs: EmployeesRefs get() = EmployeesRefs(this)

/**
 * Wrapper class that provides a convenient way to access referenced tables.
 */
public class EmployeesRefs(t: Employees) {
    /**
     * Return the referenced table of [Employees.departmentId].
     */
    public val department: Departments = t.departmentId.referenceTable as Departments

    /**
     * Return all referenced tables as a list.
     */
    public fun toList(): List<Table<*>> = listOf(department)
}

/**
 * Create an entity of [Employee] and specify the initial values for each property, properties that
 * doesn't have an initial value will leave unassigned.
 */
public fun Employee(
    id: Int? = Undefined.of(),
    name: String? = Undefined.of(),
    job: String? = Undefined.of(),
    managerId: Int? = Undefined.of(),
    hireDate: LocalDate? = Undefined.of(),
    salary: Long? = Undefined.of(),
    department: Department? = Undefined.of()
): Employee {
    val entity = Entity.create<Employee>()
    if (id !== Undefined.of<Int>()) {
        entity.id = id ?: error("`id` should not be null.")
    }
    if (name !== Undefined.of<String>()) {
        entity.name = name ?: error("`name` should not be null.")
    }
    if (job !== Undefined.of<String>()) {
        entity.job = job ?: error("`job` should not be null.")
    }
    if (managerId !== Undefined.of<Int>()) {
        entity.managerId = managerId
    }
    if (hireDate !== Undefined.of<LocalDate>()) {
        entity.hireDate = hireDate ?: error("`hireDate` should not be null.")
    }
    if (salary !== Undefined.of<Long>()) {
        entity.salary = salary ?: error("`salary` should not be null.")
    }
    if (department !== Undefined.of<Department>()) {
        entity.department = department ?: error("`department` should not be null.")
    }
    return entity
}

/**
 * Return a deep copy of this entity (which has the same property values and tracked statuses), and
 * alter the specified property values.
 */
public fun Employee.copy(
    id: Int? = Undefined.of(),
    name: String? = Undefined.of(),
    job: String? = Undefined.of(),
    managerId: Int? = Undefined.of(),
    hireDate: LocalDate? = Undefined.of(),
    salary: Long? = Undefined.of(),
    department: Department? = Undefined.of()
): Employee {
    val entity = this.copy()
    if (id !== Undefined.of<Int>()) {
        entity.id = id ?: error("`id` should not be null.")
    }
    if (name !== Undefined.of<String>()) {
        entity.name = name ?: error("`name` should not be null.")
    }
    if (job !== Undefined.of<String>()) {
        entity.job = job ?: error("`job` should not be null.")
    }
    if (managerId !== Undefined.of<Int>()) {
        entity.managerId = managerId
    }
    if (hireDate !== Undefined.of<LocalDate>()) {
        entity.hireDate = hireDate ?: error("`hireDate` should not be null.")
    }
    if (salary !== Undefined.of<Long>()) {
        entity.salary = salary ?: error("`salary` should not be null.")
    }
    if (department !== Undefined.of<Department>()) {
        entity.department = department ?: error("`department` should not be null.")
    }
    return entity
}

/**
 * Return the value of [Employee.id].
 */
public operator fun Employee.component1(): Int = this.id

/**
 * Return the value of [Employee.name].
 */
public operator fun Employee.component2(): String = this.name

/**
 * Return the value of [Employee.job].
 */
public operator fun Employee.component3(): String = this.job

/**
 * Return the value of [Employee.managerId].
 */
public operator fun Employee.component4(): Int? = this.managerId

/**
 * Return the value of [Employee.hireDate].
 */
public operator fun Employee.component5(): LocalDate = this.hireDate

/**
 * Return the value of [Employee.salary].
 */
public operator fun Employee.component6(): Long = this.salary

/**
 * Return the value of [Employee.department].
 */
public operator fun Employee.component7(): Department = this.department

Users.kt

// Auto-generated by ktorm-ksp-compiler, DO NOT EDIT!
@file:Suppress("RedundantVisibilityModifier")

package org.ktorm.example.model

import org.ktorm.database.Database
import org.ktorm.dsl.AliasRemover
import org.ktorm.dsl.QueryRowSet
import org.ktorm.dsl.eq
import org.ktorm.dsl.getGeneratedKey
import org.ktorm.entity.EntitySequence
import org.ktorm.entity.sequenceOf
import org.ktorm.expression.ColumnAssignmentExpression
import org.ktorm.expression.InsertExpression
import org.ktorm.expression.UpdateExpression
import org.ktorm.schema.BaseTable
import org.ktorm.schema.Column
import org.ktorm.schema.int
import org.ktorm.schema.varchar
import kotlin.Boolean
import kotlin.Int
import kotlin.String
import kotlin.Suppress

/**
 * Table user.
 */
public open class Users(alias: String?) : BaseTable<User>("user", alias, schema = "test") {
    /**
     * Column id.
     */
    public val id: Column<Int> = int("id").primaryKey()

    /**
     * Column username.
     */
    public val username: Column<String> = varchar("username")

    /**
     * Column age.
     */
    public val age: Column<Int> = int("age")

    /**
     * Create an entity object from the specific row of query results.
     */
    public override fun doCreateEntity(
        row: QueryRowSet,
        withReferences: Boolean
    ): User = User(id = row[this.id]!!, username = row[this.username]!!, age = row[this.age]!!)

    /**
     * Return a new-created table object with all properties (including the table name and columns and
     * so on) being copied from this table, but applying a new alias given by the parameter.
     */
    public override fun aliased(alias: String): Users = Users(alias)

    /**
     * The default table object of user.
     */
    public companion object : Users(alias = null)
}

/**
 * Return the default entity sequence of [Users].
 */
public val Database.users: EntitySequence<User, Users> get() = this.sequenceOf(Users)

/**
 * Insert the given entity into the table that the sequence object represents.
 *
 * @param entity the entity to be inserted.
 * @param isDynamic whether only non-null columns should be inserted.
 * @param useGeneratedKey whether to obtain the generated primary key value and fill it into the
 * property [User.id] after insertion.
 * @return the affected record number.
 */
public fun EntitySequence<User, Users>.add(
    entity: User,
    isDynamic: Boolean = false,
    useGeneratedKey: Boolean = false
): Int {
    val isModified = expression.where != null ||
        expression.groupBy.isNotEmpty() ||
        expression.having != null ||
        expression.isDistinct ||
        expression.orderBy.isNotEmpty() ||
        expression.offset != null ||
        expression.limit != null
    if (isModified) {
        val msg = "" +
            "Entity manipulation functions are not supported by this sequence object. " +
            "Please call on the origin sequence returned from database.sequenceOf(table)"
        throw UnsupportedOperationException(msg)
    }

    fun <T : Any> MutableList<ColumnAssignmentExpression<*>>.addVal(column: Column<T>, value: T?) {
        if (useGeneratedKey && column === sourceTable.id) {
            return
        }

        if (isDynamic && value == null) {
            return
        }

        this += ColumnAssignmentExpression(column.asExpression(), column.wrapArgument(value))
    }

    val assignments = ArrayList<ColumnAssignmentExpression<*>>()
    assignments.addVal(sourceTable.id, entity.id)
    assignments.addVal(sourceTable.username, entity.username)
    assignments.addVal(sourceTable.age, entity.age)
    if (assignments.isEmpty()) {
        return 0
    }

    val visitor = database.dialect.createExpressionVisitor(AliasRemover)
    val expression = visitor.visit(InsertExpression(sourceTable.asExpression(), assignments))
    if (!useGeneratedKey) {
        return database.executeUpdate(expression)
    } else {
        val (effects, rowSet) = database.executeUpdateAndRetrieveKeys(expression)
        if (rowSet.next()) {
            val generatedKey = rowSet.getGeneratedKey(sourceTable.id)
            if (generatedKey != null) {
                if (database.logger.isDebugEnabled()) {
                    database.logger.debug("Generated Key: $generatedKey")
                }

                entity.id = generatedKey
            }
        }

        return effects
    }
}

/**
 * Update the given entity to the database.
 *
 * @param entity the entity to be updated.
 * @param isDynamic whether only non-null columns should be updated.
 * @return the affected record number.
 */
public fun EntitySequence<User, Users>.update(entity: User, isDynamic: Boolean = false): Int {
    val isModified = expression.where != null ||
        expression.groupBy.isNotEmpty() ||
        expression.having != null ||
        expression.isDistinct ||
        expression.orderBy.isNotEmpty() ||
        expression.offset != null ||
        expression.limit != null
    if (isModified) {
        val msg = "" +
            "Entity manipulation functions are not supported by this sequence object. " +
            "Please call on the origin sequence returned from database.sequenceOf(table)"
        throw UnsupportedOperationException(msg)
    }

    fun <T : Any> MutableList<ColumnAssignmentExpression<*>>.addVal(column: Column<T>, value: T?) {
        if (isDynamic && value == null) {
            return
        }

        this += ColumnAssignmentExpression(column.asExpression(), column.wrapArgument(value))
    }

    val assignments = ArrayList<ColumnAssignmentExpression<*>>()
    assignments.addVal(sourceTable.username, entity.username)
    assignments.addVal(sourceTable.age, entity.age)
    if (assignments.isEmpty()) {
        return 0
    }

    val visitor = database.dialect.createExpressionVisitor(AliasRemover)
    val conditions = (sourceTable.id eq entity.id)
    val expression = visitor.visit(UpdateExpression(sourceTable.asExpression(), assignments, conditions))
    return database.executeUpdate(expression)
}