Column transformation

Column transformations allow to define custom transformations between database column types and application's data types. This can be particularly useful when you need to store data in one format but work with it in another format within your application.

Consider the following example, where we define a table to store meal times and transform these times into meal types:

        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:

The wrap() function transforms the stored LocalTime values into Meal enums. It checks the hour of the stored time and returns the corresponding meal type.
The unwrap() function transforms Meal enums back into LocalTime values for storage in the database.

Transformation could be also defined as an implementation of the ColumnTransformer interface and reused across different tables:

        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())
        }
    

Last modified: 27 November 2025