CRUD operations

CRUD (Create, Read, Update, Delete) are the four basic operations supported by any database. This section demonstrates how to perform SQL CRUD operations using Exposed's DAO (Data Access Object) API.

These operations can be performed directly through the methods and properties of the EntityClass associated with the table. For more information, see Entity definition.

Create

Create and schedule an insert

To create a new table row, use the .new() function on the entity class associated with the table:

        val movie = StarWarsFilmEntity.new {
            name = "The Last Jedi"
            sequelId = MOVIE_SEQUEL_ID
            director = "Rian Johnson"
        }

In the above example, movie is the entity instance linked to the new row in the StarWarsFilmsTable table.

The .new() function schedules the entity for insert in the transaction's cache. The insert is flushed when you explicitly call the flushCache() function, when another statement is executed in the same transaction, or when the transaction is committed.

With JDBC, the entity's ID and any database-generated columns appear to be available immediately, but accessing these values triggers a flush of the pending INSERT to retrieve the generated values.
With R2DBC, any database-generated columns are not accessible. Attempting to access movie.id.value before the insert is flushed throws an error.

Create and insert automatically

With R2DBC, you can use the .newSuspend() function when you need the insert to be flushed before the function returns:

        val movie = StarWarsFilmEntity.newSuspend {
            name = "The Last Jedi"
            sequelId = (StarWarsFilmEntity.findById(previousFilmId)?.sequelId ?: 0) + 1
            director = "Rian Johnson"
        }

The .newSuspend() function suspends and flushes the INSERT before returning. As a result, generated IDs and other database-generated values are available immediately.

Because the .newSuspend() function suspends, its initializer block does too, so you can call other DAO operations inside it.

When the insert can be deferred, including when you want to create multiple entities and flush their inserts together, use the .new() function instead.

Create with an ID

To provide a manual id value to a new entity, pass the value as an argument to the id parameter:

        val movie2 = StarWarsFilmEntity.new(id = 2) {
            name = "The Rise of Skywalker"
            sequelId = MOVIE2_SEQUEL_ID
            director = "J.J. Abrams"
        }

        val movie2 = StarWarsFilmEntity.new(id = 10) {
            name = "The Rise of Skywalker"
            sequelId = MOVIE2_SEQUEL_ID
            director = "J.J. Abrams"
        }

If the entity is a CompositeEntity, the ID value can be constructed by creating a component column-to-value association using the CompositeID class:

        val directorId = CompositeID {
            it[DirectorsTable.name] = "J.J. Abrams"
            it[DirectorsTable.guildId] = Uuid.random()
        }

        val director = DirectorEntity.new(directorId) {
            genre = Genre.SCI_FI
        }

        val directorId = CompositeID {
            it[DirectorsTable.name] = "J.J. Abrams"
            it[DirectorsTable.guildId] = Uuid.random()
        }

        val director = DirectorEntity.new(directorId) {
            genre = Genre.SCI_FI
        }

If CompositeID contains autoincrement or autogenerated columns, values for such columns are not required to be provided in the CompositeID building block. In this case they are generated by the database.

Batch inserts

Because the .new() function defers the insert, you can create multiple entities and flush them together. Pending inserts for the same table are sent as a single batch:

    fun batchInsert() {
        transaction {
            val films = listOf("A New Hope", "The Empire Strikes Back")
                .map { title ->
                    StarWarsFilmEntity.new {
                        name = title
                    }
                }

            flushCache()

            val ids = films.map {
                it.id.value
            }
            println("Created new records with ids $ids")
        }
    }

    suspend fun batchInsert() {
        suspendTransaction {
            val films = listOf("A New Hope", "The Empire Strikes Back")
                .map { title ->
                    StarWarsFilmEntity.new {
                        name = title
                    }
                }

            flushCache()

            val ids = films.map {
                it.id.value
            }
            println("Created new records with ids $ids")
        }
    }

The flushCache() function explicitly controls when the pending inserts are sent to the database. Pending inserts are also flushed before another statement is executed in the transaction and when the transaction is committed.

To keep inserts in the same batch, avoid executing another database statement between creating the entities. Another .new() function adds to the pending batch, while .newSuspend() flushes the pending inserts before returning and therefore starts a new batch.

Read

To read a value from a property, simply access it as you would with any property in a Kotlin class:

val movieName = movie.name

Retrieve all

To get all the entity instances associated with this entity class, use the .all() function:

val allMovies = StarWarsFilmEntity.all()

Calling the .all() function flushes all scheduled entity inserts and updates.

Find by condition

To get all the entity instances that conform to the conditional expression, use the .find() function:

        val specificMovie = StarWarsFilmEntity.find { StarWarsFilmsTable.sequelId eq MOVIE_SEQUELID }

        val specificMovie = StarWarsFilmEntity.find { StarWarsFilmsTable.sequelId eq MOVIE_SEQUELID }

Find by ID

To get an entity by its ID value, use the .findById() function:

val movie = StarWarsFilmEntity.findById(2)

The .findById() function uses the transaction's entity cache first to find a matching entity. If the entity is not found there, it queries the database, and pending inserts are flushed before the query is executed.

If the entity is a CompositeEntity, its id property can be used to refer to all composite columns and to get entities, much like the id column of its associated CompositeIdTable:

        val directorId = CompositeID {
            it[DirectorsTable.name] = "J.J. Abrams"
            it[DirectorsTable.guildId] = Uuid.random()
        }

        val director = DirectorEntity.findById(directorId)

        val directors = DirectorEntity.find { DirectorsTable.id eq directorId }

The SQL query would result in something like the following:

        SELECT DIRECTORS."name", DIRECTORS.GUILD_ID, DIRECTORS.GENRE
        FROM DIRECTORS
        WHERE (DIRECTORS."name" = 'J.J. Abrams')
        AND (DIRECTORS.GUILD_ID = '2cc64f4f-1a2c-41ce-bda1-ee492f787f4b')

Read an entity with a join

To find all users who rated the second Star Wars film with a score greater than 5, first define the query using Exposed DSL:

        val query = UsersTable.innerJoin(UserRatingsTable).innerJoin(StarWarsFilmsTable)
            .select(UsersTable.columns)
            .where {
                StarWarsFilmsTable.sequelId eq MOVIE_SEQUELID and (UserRatingsTable.value greater MIN_MOVIE_RATING.toLong())
            }.withDistinct()

To return the results as DAO entities, pass the query to the .wrapRows() function of the corresponding entity class:

val users = UserEntity.wrapRows(query).toList()

The resulting users collection contains UserEntity instances corresponding to the rows returned by the query.

Sort results

The .all() function returns all entity instances associated with the invoking entity class. The type returned by .all() differs between JDBC and R2DBC:

With JDBC, the .all() function returns a SizedIterable which implements Kotlin's Iterable interface. This allows you to call any sorting methods from the Kotlin standard library directly.
With R2DBC, the .all() function returns a SizedIterable that extends kotlinx.coroutines.flow.Flow. This requires you to collect the results into a collection before using any sorting operators.

Ascending order

To sort results in ascending order, use the .sortedBy() function:

val moviesByAscOrder = StarWarsFilmEntity.all().sortedBy { it.sequelId }

        val moviesByAscOrder = StarWarsFilmEntity.all().toList().sortedBy { it.sequelId }

Descending order

To sort results in descending order, use the .sortedByDescending() function:

        val moviesByDescOrder = StarWarsFilmEntity.all().sortedByDescending { it.sequelId }

        val moviesByDescOrder = StarWarsFilmEntity.all().toList().sortedByDescending { it.sequelId }

Update

You can update the value of a property just as you would with any property in a Kotlin class:

movie.name = "Episode VIII – The Last Jedi"

When you set a new property value, Exposed stores the change in the entity's state rather than immediately updating the database. The change is flushed to the database at the end of the transaction or before the next SELECT * statement.

Update by ID

To search for an entity by its ID and apply an update, use the .findByIdAndUpdate() function:

        val updatedMovie = StarWarsFilmEntity.findByIdAndUpdate(2) {
            it.name = "Episode VIII – The Last Jedi"
        }

Update using a query

To search for a single entity by a query and apply an update, use the .findSingleByAndUpdate() function:

        val updatedMovie2 = StarWarsFilmEntity.findSingleByAndUpdate(StarWarsFilmsTable.name eq "The Last Jedi") {
            it.name = "Episode VIII – The Last Jedi"
        }

Update an entity in a later transaction

If you update an entity in a transaction other than the one in which it was created or loaded, the entity must be attached to the new transaction before you modify it.

With JDBC, when you first update an entity in a new transaction, the DAO automatically re-registers the entity into the new transaction's cache. It does this by first checking whether the entity is already cached, then when it is not found, it queries the database for the most accurate row data representing the entity.

With R2DBC, property delegate setters cannot suspend, so the entity is not re-registered into the new transaction's cache. You must manually request this behavior using the attach() function:

        val movie = suspendTransaction {
            StarWarsFilmEntity.newSuspend {
                name = "Episode VIII – The Last Jedi"
                sequelId = NEW_MOVIE_SEQUEL_ID
                director = "Unknown"
            }
        }
        suspendTransaction {
            StarWarsFilmEntity.attach(movie)
            movie.director = "Rian Johnson"
        }

If the corresponding row no longer exist, the attach() function throws EntityNotFoundException.

If the transaction already caches a different entity instance for the same row and that instance has unflushed changes, attach() throws IllegalStateException.

To deliberately discard the existing changes and attach the specified entity, use attach(movie, force = true).

Delete

To delete a record and remove the corresponding entity from the transaction's cache, use the .delete() function:

movie.delete()

Use queries as expressions

Suppose that you want to sort cities by the number of users in each city. To achieve this, you can write a subquery which counts the users in each city and then order the result by that count. To do so, however, you need to convert the Query to an Expression.

This can be done using the wrapAsExpression() function:

        val expression = wrapAsExpression<Int>(
            UsersTable.select(UsersTable.id.count())
                .where { CitiesTable.id eq UsersTable.cityId }
        )

        val cities = CitiesTable.selectAll()
            .orderBy(expression, SortOrder.DESC)
            .toList()

Add computed fields to entity class

Suppose that you want to use a window function to rank films with each entity fetch. The companion object of the entity class can override any open function in EntityClass. However, to achieve this functionality, you only need to override the searchQuery() function. The results of the function can then be accessed through a property of the entity class:

package org.example.entities

import org.example.tables.StarWarsFilmsWithRankTable
import org.jetbrains.exposed.v1.core.Op
import org.jetbrains.exposed.v1.core.dao.id.EntityID
import org.jetbrains.exposed.v1.dao.IntEntity
import org.jetbrains.exposed.v1.dao.IntEntityClass
import org.jetbrains.exposed.v1.jdbc.Query
import org.jetbrains.exposed.v1.jdbc.select

class StarWarsFilmWithRankEntity(id: EntityID<Int>) : IntEntity(id) {
    var sequelId by StarWarsFilmsWithRankTable.sequelId
    var name by StarWarsFilmsWithRankTable.name
    var rating by StarWarsFilmsWithRankTable.rating

    val rank: Long
        get() = readValues[StarWarsFilmsWithRankTable.rank]

    companion object : IntEntityClass<StarWarsFilmWithRankEntity>(StarWarsFilmsWithRankTable) {
        override fun searchQuery(op: Op<Boolean>): Query {
            return super.searchQuery(op).adjustSelect {
                select(columns + StarWarsFilmsWithRankTable.rank)
            }
        }
    }
}

package org.example.tables

import org.jetbrains.exposed.v1.core.Rank
import org.jetbrains.exposed.v1.core.SortOrder
import org.jetbrains.exposed.v1.core.dao.id.IntIdTable

const val MAX_NAME_LENGTH = 32

object StarWarsFilmsWithRankTable : IntIdTable() {
    val sequelId = integer("sequel_id").uniqueIndex()
    val name = varchar("name", MAX_NAME_LENGTH)
    val rating = double("rating")

    val rank = Rank().over().orderBy(rating, SortOrder.DESC)
}

package org.example.entities

import org.example.tables.StarWarsFilmsWithRankTable
import org.jetbrains.exposed.v1.core.Op
import org.jetbrains.exposed.v1.core.dao.id.EntityID
import org.jetbrains.exposed.v1.dao.r2dbc.IntEntity
import org.jetbrains.exposed.v1.dao.r2dbc.IntEntityClass
import org.jetbrains.exposed.v1.r2dbc.Query
import org.jetbrains.exposed.v1.r2dbc.select

class StarWarsFilmWithRankEntity(id: EntityID<Int>) : IntEntity(id) {
    var sequelId by StarWarsFilmsWithRankTable.sequelId
    var name by StarWarsFilmsWithRankTable.name
    var rating by StarWarsFilmsWithRankTable.rating

    val rank: Long
        get() = readValues[StarWarsFilmsWithRankTable.rank]

    companion object : IntEntityClass<StarWarsFilmWithRankEntity>(StarWarsFilmsWithRankTable) {
        override fun searchQuery(op: Op<Boolean>): Query {
            return super.searchQuery(op).adjustSelect {
                select(columns + StarWarsFilmsWithRankTable.rank)
            }
        }
    }
}

package org.example.tables

import org.jetbrains.exposed.v1.core.Rank
import org.jetbrains.exposed.v1.core.SortOrder
import org.jetbrains.exposed.v1.core.dao.id.IntIdTable

const val MAX_NAME_LENGTH = 32

object StarWarsFilmsWithRankTable : IntIdTable() {
    val sequelId = integer("sequel_id").uniqueIndex()
    val name = varchar("name", MAX_NAME_LENGTH)
    val rating = double("rating")

    val rank = Rank().over().orderBy(rating, SortOrder.DESC)
}

Then, creating and fetching entities would look like this:

    fun readComputedField() {
        transaction {
            StarWarsFilmWithRankEntity.new {
                sequelId = MOVIE_SEQUELID
                name = "The Last Jedi"
                rating = MOVIE_RATING
            }
        }

        transaction {
            StarWarsFilmWithRankEntity
                .find { StarWarsFilmsWithRankTable.name like "The%" }
                .map { it.name to it.rank }
        }
    }

    suspend fun readComputedField() {
        suspendTransaction {
            StarWarsFilmWithRankEntity.new {
                sequelId = MOVIE_SEQUELID
                name = "The Last Jedi"
                rating = MOVIE_RATING
            }
        }

        suspendTransaction {
            StarWarsFilmWithRankEntity
                .find { StarWarsFilmsWithRankTable.name like "The%" }
                .toList()
                .map { it.name to it.rank }
        }
    }

Auto-fill columns on entity change

Suppose that you need all your table objects to have at minimum two columns for storing the date and time when a record is created and modified. You can define a BaseTable to automatically handle registering these columns to any table that extends this abstract class:

package org.example.tables

import kotlinx.datetime.LocalDateTime
import org.jetbrains.exposed.v1.core.Column
import org.jetbrains.exposed.v1.core.dao.id.IntIdTable
import org.jetbrains.exposed.v1.datetime.CurrentDateTime
import org.jetbrains.exposed.v1.datetime.datetime

abstract class BaseTable(name: String = "") : IntIdTable(name) {
    val created: Column<LocalDateTime> = datetime("created")
        .defaultExpression(CurrentDateTime)
    val modified: Column<LocalDateTime?> = datetime("updated").nullable()
}

You can then define an abstract BaseEntity to automatically associate fields to the relevant columns:

package org.example.entities

import kotlinx.datetime.LocalDateTime
import org.example.tables.BaseTable
import org.jetbrains.exposed.v1.core.dao.id.EntityID
import org.jetbrains.exposed.v1.dao.IntEntity

abstract class BaseEntity(id: EntityID<Int>, table: BaseTable) : IntEntity(id) {
    val created: LocalDateTime by table.created
    var modified: LocalDateTime? by table.modified
}

package org.example.entities

import kotlinx.datetime.LocalDateTime
import org.example.tables.BaseTable
import org.jetbrains.exposed.v1.core.dao.id.EntityID
import org.jetbrains.exposed.v1.dao.r2dbc.IntEntity

abstract class BaseEntity(id: EntityID<Int>, table: BaseTable) : IntEntity(id) {
    val created: LocalDateTime by table.created
    var modified: LocalDateTime? by table.modified
}

When a new entity is instantiated, the created column is filled with the database default defined by CurrentDateTime, while the modified column is filled with a null value.

To automatically populate the modified field when an existing entity is updated, you can subscribe the entity to an entity lifecycle interceptor, EntityHook:

package org.example.entities

import kotlinx.datetime.LocalDateTime
import kotlinx.datetime.TimeZone
import kotlinx.datetime.toLocalDateTime
import org.example.tables.BaseTable
import org.jetbrains.exposed.v1.core.Column
import org.jetbrains.exposed.v1.core.exposedLogger
import org.jetbrains.exposed.v1.dao.*
import kotlin.time.Clock
import kotlin.time.ExperimentalTime

abstract class BaseEntityClass<out E : BaseEntity>(
    table: BaseTable
) : IntEntityClass<E>(table) {
    init {
        EntityHook.subscribe { change ->
            val changedEntity = change.toEntity(this)
            when (val type = change.changeType) {
                EntityChangeType.Updated -> {
                    val now = nowUTC()
                    changedEntity?.let {
                        if (it.writeValues[table.modified as Column<Any?>] == null) {
                            it.modified = now
                        }
                    }
                    logChange(changedEntity, type, now)
                }
                else -> logChange(changedEntity, type)
            }
        }
    }

    @OptIn(ExperimentalTime::class)
    private fun nowUTC() = Clock.System.now().toLocalDateTime(TimeZone.UTC)

    private fun logChange(entity: E?, type: EntityChangeType, dateTime: LocalDateTime? = null) {
        entity?.let {
            val entityClassName = this::class.java.enclosingClass.simpleName
            exposedLogger.info(
                "$entityClassName(${it.id}) ${type.name.lowercase()} at ${dateTime ?: nowUTC()}"
            )
        }
    }
}

package org.example.entities

import kotlinx.datetime.LocalDateTime
import kotlinx.datetime.TimeZone
import kotlinx.datetime.toLocalDateTime
import org.example.tables.BaseTable
import org.jetbrains.exposed.v1.core.exposedLogger
import org.jetbrains.exposed.v1.dao.r2dbc.EntityChangeType
import org.jetbrains.exposed.v1.dao.r2dbc.EntityHook
import org.jetbrains.exposed.v1.dao.r2dbc.IntEntityClass
import org.jetbrains.exposed.v1.dao.r2dbc.toEntity
import kotlin.let
import kotlin.time.Clock
import kotlin.time.ExperimentalTime

abstract class BaseEntityClass<out E : BaseEntity>(
    table: BaseTable
) : IntEntityClass<E>(table) {
    init {
        EntityHook.subscribe { change ->
            val changedEntity = change.toEntity(this)
            when (val type = change.changeType) {
                EntityChangeType.Updated -> {
                    val now = nowUTC()
                    changedEntity?.let {
                        it.modified = now
                    }
                    logChange(changedEntity, type, now)
                }
                else -> logChange(changedEntity, type)
            }
        }
    }

    @OptIn(ExperimentalTime::class)
    private fun nowUTC() = Clock.System.now().toLocalDateTime(TimeZone.UTC)

    private fun logChange(entity: E?, type: EntityChangeType, dateTime: LocalDateTime? = null) {
        entity?.let {
            val entityClassName = this::class.java.enclosingClass.simpleName
            exposedLogger.info(
                "$entityClassName(${it.id}) ${type.name.lowercase()} at ${dateTime ?: nowUTC()}"
            )
        }
    }
}

The example above invokes EntityHook.subscribe() in an abstract BaseEntityClass that can be used as a companion object for any BaseEntity implementation. The interceptor can be subscribed to and unsubscribed from on the transaction level as well.

The subscription only performs an action when a record is updated (detected by EntityChangeType.Updated).

When an update occurs, the modified column is populated with the current UTC time using the nowUTC() method.

With JDBC, you can check if the modified field has already been set by using Entity.writeValues, which holds the column-value mappings for an entity instance before being flushed to the database. Performing this check ensures that filling the modified column does not trigger the interceptor itself.

With R2DBC, pending changes are tracked internally in the entity cache and are not exposed through an equivalent public API. Therefore, an additional check on the modified field using Entity.writeValues cannot be implemented.

Additionally, every change (creation, update, or deletion) is logged using the exposedLogger.

16 October 2026