Data Types

The exposed-java-time extension (org.jetbrains.exposed:exposed-java-time:$exposed_version) provides additional types:

The exposed-json extension (org.jetbrains.exposed:exposed-json:$exposed_version) provides the following additional types. For more information, see How to use Json and JsonB types:

How to use database enum types

For a class like enum class Foo { BAR, BAZ }, you can use the following code for your specific database:

MySQL and H2

val existingEnumColumn = customEnumeration("enumColumn", { value -> Foo.valueOf(value as String) }, { it.name })
val newEnumColumn = customEnumeration("enumColumn", "ENUM('BAR', 'BAZ')", { value -> Foo.valueOf(value as String) }, { it.name })

PostgreSQL

PostgreSQL requires that enum is defined as a separate type, so you have to create it before creating your table. Also, the PostgreSQL JDBC driver returns PGobject instances for such values, so a PGobject with its type manually set to the ENUM type needs to be used for the toDb parameter. The full working sample is provided below:

class PGEnum<T : Enum<T>>(enumTypeName: String, enumValue: T?) : PGobject() {
    init {
        value = enumValue?.name
        type = enumTypeName
    }
}

object EnumTable : Table() {
    val enumColumn = customEnumeration("enumColumn", "FooEnum", { value -> Foo.valueOf(value as String) }, { PGEnum("FooEnum", it) })
}

transaction {
    exec("CREATE TYPE FooEnum AS ENUM ('BAR', 'BAZ');")
    SchemaUtils.create(EnumTable)
}

How to use Json and JsonB types

val exposedVersion: String by project

dependencies {
    implementation("org.jetbrains.exposed:exposed-core:$exposedVersion")
    implementation("org.jetbrains.exposed:exposed-json:$exposedVersion")
}

fun <T : Any> json(name: String, serialize: (T) -> String, deserialize: (String) -> T): Column<T>

fun <T : Any> jsonb(name: String, serialize: (T) -> String, deserialize: (String) -> T): Column<T>

Here's an example that leverages kotlinx.serialization to support @Serializable classes. It uses a simpler form of json() that relies on the library's KSerializer interface:

@Serializable
data class Project(val name: String, val language: String, val active: Boolean)

val format = Json { prettyPrint = true }

object Teams : Table("team") {
    val groupId = varchar("group_id", 32)
    val project = json<Project>("project", format) // equivalent to json("project", format, Project.serializer())
}

transaction {
    val mainProject = Project("Main", "Java", true)
    Teams.insert {
        it[groupId] = "A"
        it[project] = mainProject
    }
    Teams.update({ Teams.groupId eq "A" }) {
        it[project] = mainProject.copy(language = "Kotlin")
    }

    Teams.selectAll().map { "Team ${it[Teams.groupId]} -> ${it[Teams.project]}" }.forEach { println(it) }
    // Team A -> Project(name=Main, language=Kotlin, active=true)
}

Here's how the same Project and Teams would be defined using Jackson with the jackson-module-kotlin dependency and the full form of json():

val mapper = jacksonObjectMapper()

data class Project(val name: String, val language: String, val active: Boolean)

object Teams : Table("team") {
    val groupId = varchar("group_id", 32)
    val project = json("project", { mapper.writeValueAsString(it) }, { mapper.readValue<Project>(it) })
}

Json functions

val projectName = Teams.project.extract<String>("name")
val languageIsKotlin = Teams.project.extract<String>("language").lowerCase() eq "kotlin"
Teams.select(projectName).where { languageIsKotlin }.map { it[projectName] }

val hasActiveStatus = Teams.project.exists(".active")
val activeProjects = Teams.selectAll().where { hasActiveStatus }.count()

// Depending on the database, filter paths can be provided instead, as well as optional arguments
// PostgreSQL example
val mainId = "Main"
val hasMainProject = Teams.project.exists(".name ? (@ == \$main)", optional = "{\"main\":\"$mainId\"}")
val mainProjects = Teams.selectAll().where { hasMainProject }.map { it[Teams.groupId] }

val usesKotlin = Teams.project.contains("{\"language\":\"Kotlin\"}")
val kotlinTeams = Teams.selectAll().where { usesKotlin }.count()

// Depending on the database, an optional path can be provided too
// MySQL example
val usesKotlin = Teams.project.contains("\"Kotlin\"", ".language")
val kotlinTeams = Teams.selectAll().where { usesKotlin }.count()

Json arrays

JSON columns also accept JSON arrays as input values. For example, using the serializable data class Project from the example above, the following details some ways to create such a column:

object TeamProjects : Table("team_projects") {
    val memberIds = json<IntArray>("member_ids", Json.Default)
    val projects = json<Array<Project>>("projects", Json.Default)
    // equivalent to:
    // @OptIn(ExperimentalSerializationApi::class) json("projects", Json.Default, ArraySerializer(Project.serializer()))
}

transaction {
    TeamProjects.insert {
        it[memberIds] = intArrayOf(1, 2, 3)
        it[projects] = arrayOf(
            Project("A", "Kotlin", true),
            Project("B", "Java", true)
        )
    }
    // generates SQL
    // INSERT INTO team_projects (member_ids, projects) VALUES ([1,2,3], [{"name":"A","language":"Kotlin","active":true},{"name":"B","language":"Java","active":true}])
}

How to use array types

Exposed allows defining columns as arrays, with the stored contents being any out-of-the-box or custom data type. If the contents are of a type with a supported ColumnType in the exposed-core module, the column can be simply defined with that type:

object Teams : Table("teams") {
    // Single-dimensional arrays
    val memberIds = array<UUID>("member_ids")
    val memberNames = array<String>("member_names")
    val budgets = array<Double>("budgets")

    // Multi-dimensional arrays
    val nestedMemberIds = array<UUID, List<List<UUID>>>(
        "nested_member_ids", dimensions = 2
    )
    val hierarchicalMemberNames = array<String, List<List<List<String>>>>(
        "hierarchical_member_names", dimensions = 3
    )
}

object Teams : Table("teams") {
    // Single-dimensional arrays
    val memberNames = array<String>("member_names", VarCharColumnType(colLength = 32))
    val deadlines = array<LocalDate>("deadlines", KotlinLocalDateColumnType()).nullable()
    val expenses = array<Double?>("expenses", DoubleColumnType()).default(emptyList())

    // Multi-dimensional arrays
    val nestedMemberIds = array<UUID, List<List<UUID>>>(
        "nested_member_ids", dimensions = 2
    )
    val hierarchicalMemberNames = array<String, List<List<List<String>>>>(
        "hierarchical_member_names",
        VarCharColumnType(colLength = 32),
        dimensions = 3
    )
}

An array column accepts inserts and retrieves stored array contents as a Kotlin List:

Teams.insert {
    // Single-dimensional arrays
    it[memberIds] = List(5) { UUID.randomUUID() }
    it[memberNames] = List(5) { i -> "Member ${'A' + i}" }
    it[budgets] = listOf(9999.0)

    // Multi-dimensional arrays
    it[nestedMemberIds] = List(5) { List(5) { UUID.randomUUID() } }
    it[hierarchicalMemberNames] = List(3) { List(3) { List(3) {
            i -> "Member ${'A' + i}"
    } } }
}

Array functions

A single element in a stored array can be accessed using the index reference get() operator:

val firstMember = Teams.memberIds[1]
Teams
    .select(firstMember)
    .where { Teams.expenses[1] greater Teams.budgets[1] }

Teams
    .selectAll()
    .where { Teams.hierarchicalMemberNames[1][1] eq "Mr. Smith" }

Teams.select(Teams.deadlines.slice(1, 3))

In the case of multidimensional arrays, the .slice() calls can be nested:

Teams.select(Teams.hierarchicalMemberNames.slice(1, 2).slice(3, 4))

An array column can also be used as an argument for the ANY and ALL SQL operators, either by providing the entire column or a new array expression via .slice():

Teams
    .selectAll()
    .where { Teams.budgets[1] lessEq allFrom(Teams.expenses) }

Teams
    .selectAll()
    .where { stringParam("Member A") eq anyFrom(Teams.memberNames.slice(1, 4)) }

Custom data types

Hierarchical tree-like data

The hierarchy labels are stored as strings, so the existing StringColumnType class be extended with a few overrides:

import org.postgresql.util.PGobject

class LTreeColumnType : StringColumnType() {
    override fun sqlType(): String = "LTREE"

    override fun setParameter(stmt: PreparedStatementApi, index: Int, value: Any?) {
        val parameterValue: PGobject? = value?.let {
            PGobject().apply {
                type = sqlType()
                this.value = value as? String
            }
        }
        super.setParameter(stmt, index, parameterValue)
    }
}

fun Table.ltree(name: String): Column<String> = registerColumn(name, LTreeColumnType())

object TestTable : Table("test_table") {
    val path = ltree("path")

    init {
        index(customIndexName = "path_gist_idx", indexType = "GIST", columns = arrayOf(path))
        index(customIndexName = "path_idx", indexType = "BTREE", columns = arrayOf(path))
    }
}

String values representing hierarchy labels can then be inserted and queried from the path column. The following block shows an update of all records that have a stored path either equal to or a descendant of the path Top.Science, by setting a subpath of the first 2 labels as the updated value:

transaction {
    TestTable.update(
        where = { TestTable.path isDescendantOrEq "Top.Science" }
    ) {
        it[path] = path.subltree(0, 2)
    }
}

fun <T : String?> Expression<T>.subltree(start: Int, end: Int) =
    CustomStringFunction("SUBLTREE", this, intParam(start), intParam(end))

infix fun <T : String?> ExpressionWithColumnType<T>.isDescendantOrEq(other: T) =
    IsDescendantOrEqOp(this, wrap(other))

class IsDescendantOrEqOp<T : String?>(
    left: Expression<T>,
    right: Expression<T>
) : ComparisonOp(left, right, "<@")

Date and time data

This example assumes that the column accepts string input values, but a numerical format is also possible, in which case IntegerColumnType could be extended instead:

class YearColumnType : StringColumnType(), IDateColumnType {
    override fun sqlType(): String = "YEAR"

    override val hasTimePart: Boolean = false

    override fun valueFromDB(value: Any): String = when (value) {
        is java.sql.Date -> value.toString().substringBefore('-')
        else -> error("Retrieved unexpected value of type ${value::class.simpleName}")
    }
}

fun Table.year(name: String): Column<String> = registerColumn(name, YearColumnType())

The IDateColumnType interface is implemented to ensure that any default expressions are handled appropriately. For example, a new object CurrentYear can be added as a default to avoid issues with the strict column typing:

object CurrentYear : Function<String>(YearColumnType()) {
    override fun toQueryBuilder(queryBuilder: QueryBuilder) {
        queryBuilder { +"CURRENT_DATE" }
    }
}

object TestTable : Table("test_table") {
    val established = year("established").defaultExpression(CurrentYear)
}

String values of different formats (depending on the enabled sql_mode) can then be inserted and queried from the year column:

transaction {
    // disable strict mode to allow truncation of full date strings
    exec("SET sql_mode=''")

    val yearData = listOf("1901", "2000", "2023-08-22", "2155")
    TestTable.batchInsert(yearData) { year ->
        this[TestTable.established] = year
    }

    TestTable
        .selectAll()
        .where { TestTable.established less CurrentYear }
        .toList()
}

Ranges of data

If more than one range subtype needs to be used, a base RangeColumnType class could be first introduced with the minimum common logic:

import org.postgresql.util.PGobject

abstract class RangeColumnType<T : Comparable<T>, R : ClosedRange<T>>(
    val subType: ColumnType<T>,
) : ColumnType<R>() {
    abstract fun List<String>.toRange(): R

    override fun nonNullValueToString(value: R): String {
        return "[${value.start},${value.endInclusive}]"
    }

    override fun nonNullValueAsDefaultString(value: R): String {
        return "'${nonNullValueToString(value)}'"
    }

    override fun setParameter(stmt: PreparedStatementApi, index: Int, value: Any?) {
        val parameterValue: PGobject? = value?.let {
            PGobject().apply {
                type = sqlType()
                this.value = nonNullValueToString(it as R)
            }
        }
        super.setParameter(stmt, index, parameterValue)
    }

    override fun valueFromDB(value: Any): R? = when (value) {
        is PGobject -> value.value?.let {
            val components = it.trim('[', ')').split(',')
            components.toRange()
        }
        else -> error("Retrieved unexpected value of type ${value::class.simpleName}")
    }
}

A class for the type int4range that accepts IntRange values could then be implemented:

class IntRangeColumnType : RangeColumnType<Int, IntRange>(IntegerColumnType()) {
    override fun sqlType(): String = "INT4RANGE"

    override fun List<String>.toRange(): IntRange {
        return IntRange(first().toInt(), last().toInt() - 1)
    }
}

fun Table.intRange(name: String): Column<IntRange> = registerColumn(name, IntRangeColumnType())

If a custom Kotlin implementation for a DateRange is set up (using Iterable and ClosedRange), then a class for the type daterange can also be added. This implementation would require a dependency on exposed-kotlin-datetime:

class DateRangeColumnType : RangeColumnType<LocalDate, DateRange>(KotlinLocalDateColumnType()) {
    override fun sqlType(): String = "DATERANGE"

    override fun List<String>.toRange(): DateRange {
        val endInclusive = LocalDate.parse(last()).minus(1, DateTimeUnit.DAY)
        return DateRange(LocalDate.parse(first()), endInclusive)
    }
}

fun Table.dateRange(name: String): Column<DateRange> = registerColumn(name, DateRangeColumnType())

object TestTable : Table("test_table") {
    val amounts = intRange("amounts").default(1..10)
    val holidays = dateRange("holidays")
}

transaction {
    val holidayEnd = TestTable.holidays.upperBound()
    TestTable
        .select(holidayEnd)
        .where { TestTable.amounts isContainedBy 0..100 }
        .toList()
}

fun <T : Comparable<T>, CR : ClosedRange<T>, R : CR?> ExpressionWithColumnType<R>.upperBound() =
    CustomFunction("UPPER", (columnType as RangeColumnType<T, CR>).subType, this)

infix fun <R : ClosedRange<*>?> ExpressionWithColumnType<R>.isContainedBy(other: R) =
    RangeIsContainedOp(this, wrap(other))

class RangeIsContainedOp<R : ClosedRange<*>?>(
    left: Expression<R>,
    right: Expression<R>
) : ComparisonOp(left, right, "<@")

Predefined string data

To use this type, a new ColumnType could be implemented with all the necessary overrides. This example instead takes advantage of the existing logic in StringColumnType as the base for database storage, then uses a custom ColumnTransformer to achieve the final transformation between a set of enum constants and a string:

class SetColumnType<T : Enum<T>>(
    private val enumClass: KClass<T>
) : StringColumnType() {
    // uses reflection to retrieve elements of the enum class
    private val enumConstants by lazy {
        enumClass.java.enumConstants?.map { it.name } ?: emptyList()
    }

    override fun sqlType(): String = enumConstants
        .takeUnless { it.isEmpty() }
        ?.let { "SET(${it.joinToString { e -> "'$e'" }})" }
        ?: error("SET column must be defined with a list of permitted values")
}

inline fun <reified T : Enum<T>> Table.set(name: String): Column<String> =
    registerColumn(name, SetColumnType(T::class))

class EnumListColumnType<T : Enum<T>>(
    private val enumClass: KClass<T>
) : ColumnTransformer<String, List<T>> {
    private val enumConstants by lazy {
        enumClass.java.enumConstants?.associateBy { it.name } ?: emptyMap()
    }

    override fun unwrap(value: List<T>): String {
        return value.joinToString(separator = ",") { it.name }
    }

    override fun wrap(value: String): List<T> = value
        .takeUnless { it.isEmpty() }?.let {
            it.split(',').map { e ->
                enumConstants[e]
                    ?: error("$it can't be associated with any value from ${enumClass.qualifiedName}")
            }
        }
        ?: emptyList()
}

enum class Vowel { A, E, I, O, U }

object TestTable : Table("test_table") {
    val vowel: Column<List<Vowel>> = set<Vowel>("vowel")
        .transform(EnumListColumnType(Vowel::class))
        .default(listOf(Vowel.A, Vowel.E))
}

Lists of enum constants can then be inserted and queried from the set column. The following block shows a query for all records that have Vowel.O stored at any position in the set column string:

transaction {
    TestTable.insert { it[vowel] = listOf(Vowel.U, Vowel.E) }
    TestTable.insert { it[vowel] = emptyList() }
    TestTable.insert { it[vowel] = Vowel.entries }

    TestTable
        .selectAll()
        .where { TestTable.vowel.findInSet(Vowel.O) greater 0 }
        .toList()
}

fun <T : Enum<T>> Expression<List<T>>.findInSet(enum: T) =
    CustomFunction("FIND_IN_SET", IntegerColumnType(), stringParam(enum.name), this)

Key-Value pair data

The existing StringColumnType class can be extended with a few overrides:

import org.postgresql.util.PGobject

class HStoreColumnType : TextColumnType() {
    override fun sqlType(): String = "HSTORE"

    override fun setParameter(stmt: PreparedStatementApi, index: Int, value: Any?) {
        val parameterValue: PGobject? = value?.let {
            PGobject().apply {
                type = sqlType()
                this.value = value as? String
            }
        }
        super.setParameter(stmt, index, parameterValue)
    }
}

A table extension function can then be added to register a new column with this type. This example assumes that the input values will be of type Map<String, String>, so transform() is used on the string column to handle parsing:

fun Table.hstore(name: String): Column<String> = registerColumn(name, HStoreColumnType())

object TestTable : Table("test_table") {
    val bookDetails = hstore("book_details").transform(
        wrap = {
            it.trim('{', '}').split(", ")
                .associate { pair ->
                    pair.substringBefore("=") to pair.substringAfter("=")
                }
        },
        unwrap = {
            it.entries.joinToString(separator = ",") { (k, v) ->
                "\"$k\"=>\"$v\""
            }
        }
    )
}

Map values representing key-value pairs of strings can then be inserted and queried from the bookDetails column. The following block queries the value associated with the title key from all bookDetails records:

transaction {
    TestTable.insert {
        it[bookDetails] = mapOf(
            "title" to "Kotlin in Action",
            "edition" to "2"
        )
    }

    val bookTitle = TestTable.bookDetails.getValue("title")
    TestTable
        .select(bookTitle)
        .toList()
}

fun <T : Map<String, String>> Expression<T>.getValue(key: String) =
    CustomOperator("->", TextColumnType(), this, stringParam(key))

Case insensitive data

The existing StringColumnType class can be extended with a few overrides:

import org.postgresql.util.PGobject

class CitextColumnType(
    colLength: Int
) : VarCharColumnType(colLength) {
    override fun sqlType(): String = "CITEXT"

    override fun setParameter(stmt: PreparedStatementApi, index: Int, value: Any?) {
        val parameterValue: PGobject? = value?.let {
            PGobject().apply {
                type = sqlType()
                this.value = value as? String
            }
        }
        super.setParameter(stmt, index, parameterValue)
    }
}

fun Table.citext(name: String, length: Int): Column<String> =
    registerColumn(name, CitextColumnType(length))

object TestTable : Table("test_table") {
    val firstName = citext("first_name", 32)
}

String values can then be inserted and queried from the firstName column in a case-insensitive manner:

transaction {
    val allNames = listOf("Anna", "Anya", "Agna")
    TestTable.batchInsert(allNames) { name ->
        this[TestTable.firstName] = name
    }

    TestTable
        .selectAll()
        .where { TestTable.firstName like "an%" }
        .toList()
}

Column transformation

enum class Meal {
    BREAKFAST,
    LUNCH,
    DINNER
}

object Meals : Table() {
    val mealTime: Column<Meal> = time("meal_time")
        .transform(
            wrap = {
                when {
                    it.hour < 10 -> Meal.BREAKFAST
                    it.hour < 15 -> Meal.LUNCH
                    else -> Meal.DINNER
                }
            },
            unwrap = {
                when (it) {
                    Meal.BREAKFAST -> LocalTime(8, 0)
                    Meal.LUNCH -> LocalTime(12, 0)
                    Meal.DINNER -> LocalTime(18, 0)
                }
            }
        )
}

The .transform() function is used to apply custom transformations to the mealTime column:

class MealTimeTransformer : ColumnTransformer<LocalTime, Meal> {
    override fun wrap(value: LocalTime): Meal = when {
        value.hour < 10 -> Meal.BREAKFAST
        value.hour < 15 -> Meal.LUNCH
        else -> Meal.DINNER
    }

    override fun unwrap(value: Meal): LocalTime = when (value) {
        Meal.BREAKFAST -> LocalTime(8, 0)
        Meal.LUNCH -> LocalTime(12, 0)
        Meal.DINNER -> LocalTime(18, 0)
    }
}

object Meals : Table() {
    val mealTime: Column<Meal> = time("meal_time").transform(MealTimeTransformer())
}

Null transform

The .nullTransform() method applies a special transformation that allows a non-nullable database column to accept and/or return values as null on the client side.

This transformation does not alter the column's definition in the database, which will still be NON NULL. It enables reflecting non-null values from the database as null in Kotlin (e.g., converting an empty string from a non-nullable text column, empty lists, negative IDs, etc., to null).

class MealTimeNullTransformer : ColumnTransformer<LocalTime, Meal?> {
    override fun wrap(value: LocalTime): Meal? = when {
        value.hour == 0 && value.minute == 0 -> null
        value.hour < 10 -> Meal.BREAKFAST
        value.hour < 15 -> Meal.LUNCH
        else -> Meal.DINNER
    }

    override fun unwrap(value: Meal?): LocalTime = when (value) {
        Meal.BREAKFAST -> LocalTime(8, 0)
        Meal.LUNCH -> LocalTime(12, 0)
        Meal.DINNER -> LocalTime(18, 0)
        else -> LocalTime(0, 0)
    }
}

object Meals : Table() {
    val mealTime: Column<Meal?> = time("meal_time").nullTransform(MealTimeNullTransformer())
}

Data Types﻿

note

note

How to use database enum types﻿

MySQL and H2﻿

PostgreSQL﻿

How to use Json and JsonB types﻿

Json functions﻿

note

Json arrays﻿

How to use array types﻿

Array functions﻿

note

Custom data types﻿

Hierarchical tree-like data﻿

note

note

Date and time data﻿

Ranges of data﻿

Predefined string data﻿

tip

Key-Value pair data﻿

tip

note

Case insensitive data﻿

note

Column transformation﻿

note

Null transform﻿

Data Types

How to use database enum types

MySQL and H2

PostgreSQL

How to use Json and JsonB types

Json functions

Json arrays

How to use array types

Array functions

Custom data types

Hierarchical tree-like data

Date and time data

Ranges of data

Predefined string data

Key-Value pair data

Case insensitive data

Column transformation

Null transform