CRUD operations

CRUD stands for Create Read Update Delete, which are four basic operations for a database to support. This section shows how to perform SQL CRUD operations using Kotlin DSL.

Create

Exposed provides several functions to insert rows into a table:

`insert`

To create a new table row, use the insert query. If the same row already exists in the table, it throws an exception.

        StarWarsFilmsTable.insert {
            it[sequelId] = MOVIE_SEQUEL_ID
            it[name] = "The Force Awakens"
            it[director] = "J.J. Abrams"
        }

The example corresponds to the following SQL statement:

    INSERT INTO STARWARSFILMS (SEQUEL_ID, "name", DIRECTOR)
    VALUES (7, 'The Force Awakens', 'J.J. Abrams')

`insertAndGetId`

To add a new row and return its ID, use insertAndGetId. If the same row already exists in the table, it throws an exception.

        val id = StarWarsFilmsIntIdTable.insertAndGetId {
            it[sequelId] = MOVIE_SEQUEL_ID
            it[name] = "The Force Awakens"
            it[director] = "J.J. Abrams"
        }

             INSERT INTO STAR_WARS_FILMS_TABLE (SEQUEL_ID, "name", DIRECTOR)
             VALUES (7, 'The Force Awakens', 'J.J. Abrams')

`insertIgnore`

To allow insert statements to be executed without throwing any errors, use insertIgnore. This may be useful, for example, when insertion conflicts are possible:

        StarWarsFilmsTable.insert {
            it[sequelId] = MOVIE_SEQUEL_2_ID // column pre-defined with a unique index
            it[name] = "The Last Jedi"
            it[director] = "Rian Johnson"
        }

        StarWarsFilmsTable.insertIgnore {
            it[sequelId] = MOVIE_SEQUEL_2_ID
            it[name] = "The Last Jedi"
            it[director] = "Rian Johnson"
        }

If insert was used instead of insertIgnore, this would throw a constraint violation exception Instead, this new row is ignored and discarded.

`insertIgnoreAndGetId`

insertIgnoreAndGetId adds a new row and returns its ID. If the same row already exists in the table, it ignores it and doesn't throw an exception.

        val rowId = StarWarsFilmsIntIdTable.insertIgnoreAndGetId {
            it[sequelId] = MOVIE_SEQUEL_ID
            it[name] = "The Last Jedi"
            it[director] = "Rian Johnson"
        }

    INSERT IGNORE INTO STAR_WARS_FILMS_TABLE (SEQUEL_ID, "name", DIRECTOR)
    VALUES (8, 'The Last Jedi', 'Rian Johnson')

`batchInsert`

batchInsert allows mapping a list of entities into table rows in a single SQL statement. It is more efficient than using the insert query for each row as it initiates only one statement.

The following example uses a simple list:

        val cityNames = listOf("Paris", "Moscow", "Helsinki")

        CitiesTable.batchInsert(cityNames) { name ->
            this[CitiesTable.name] = name
        }

Here is an example that uses a list of data class instances:

        data class SWFilmData(val sequelId: Int, val name: String, val director: String)

        val films = listOf(
            SWFilmData(MOVIE_SEQUEL_ID, "The Empire Strikes Back", "Irvin Kershner"),
            SWFilmData(MOVIE_ORIGINAL_ID, "A New Hope", "George Lucas"),
            SWFilmData(MOVIE_SEQUEL_2_ID, "Return of the Jedi", "Richard Marquand")
        )

        StarWarsFilmsTable.batchInsert(films) { (id, name, director) ->
            this[StarWarsFilmsTable.sequelId] = id
            this[StarWarsFilmsTable.name] = name
            this[StarWarsFilmsTable.director] = director
        }

If you don't need to get the newly generated values, such as the auto-incremented ID, set the shouldReturnGeneratedValues parameter to false. This increases the performance of batch inserts by batching them in chunks, instead of always waiting for the database to synchronize the newly inserted object state.

If you want to check if rewriteBatchedInserts and batchInsert are working correctly, you need to enable JDBC logging for your driver. This is necessary, as Exposed will always show the non-rewritten multiple inserts. For more information, see how to enable logging in PostgresSQL.

Read

`select`

The select() function allows you to select specific columns or/and expressions.

        val filmAndDirector = StarWarsFilmsTable.select(StarWarsFilmsTable.name, StarWarsFilmsTable.director).map {
            it[StarWarsFilmsTable.name] to it[StarWarsFilmsTable.director]
        }

If you want to select only distinct value then use withDistinct() function:

        StarWarsFilmsTable.select(StarWarsFilmsTable.director)
            .where { StarWarsFilmsTable.sequelId less MOVIE_SEQUEL_ID }.withDistinct()
            .map {
                it[StarWarsFilmsTable.director]
            }

Some SQL dialects, such as PostgreSQL and H2, also support the DISTINCT ON clause. You can use this clause with the withDistinctOn() function:

        StarWarsFilmsTable.select(StarWarsFilmsTable.director, StarWarsFilmsTable.name)
            .withDistinctOn(StarWarsFilmsTable.director)
            .orderBy(
                StarWarsFilmsTable.director to SortOrder.ASC,
                StarWarsFilmsTable.name to SortOrder.ASC
            )
            .map {
                it[StarWarsFilmsTable.name]
            }

`selectAll`

To retrieve records from a table, use the selectAll() method:

        StarWarsFilmsTable.selectAll()
            .where { StarWarsFilmsTable.sequelId eq MOVIE_SEQUEL_ID }

Query inherits Iterable so it is possible to traverse it using map() or forEach():

        StarWarsFilmsTable.selectAll()
            .where { StarWarsFilmsTable.sequelId eq MOVIE_SEQUEL_2_ID }
            .forEach { println(it[StarWarsFilmsTable.name]) }

Update

To update a record, use the update() function. By default, it returns the number of updated rows.

        val updatedRowCount = StarWarsFilmsTable.update({ StarWarsFilmsTable.name like "Episode" }) {
            it[director] = "George Lucas"
        }

To update a column value using an expression, such as an increment, use update function or setter:

        val updatedRowsWithIncrement = StarWarsFilmsTable.update({ StarWarsFilmsTable.sequelId eq MOVIE_SEQUEL_ID }) {
            with(SqlExpressionBuilder) {
                it[sequelId] = sequelId + 1
                // or
                it.update(sequelId, sequelId + 1)
            }
        }

Delete

`deleteWhere`

To delete records and return the count of deleted rows, use the deleteWhere function.

Any SqlExpressionBuilder comparison operators or extension functions used in the op parameter lambda block will require inclusion of an import statement like import org.jetbrains.exposed.sql.SqlExpressionBuilder.*.

        val deletedRowsCount = StarWarsFilmsTable.deleteWhere { StarWarsFilmsTable.sequelId eq MOVIE_SEQUEL_ID }

`deleteIgnoreWhere`

To delete records while ignoring any possible errors that occur during the process, use the deleteIgnoreWhere function. The function will return the count of deleted rows.

        val deleteIgnoreRowsCount = StarWarsFilmsTable.deleteIgnoreWhere { StarWarsFilmsTable.sequelId eq MOVIE_SEQUEL_2_ID }

`deleteAll`

To delete all rows in a table and return the count of deleted rows, use the deleteAll function.

val allDeletedRowsCount = StarWarsFilmsTable.deleteAll()

Join `delete`

To delete records from a table in a join relation, use the delete function with a Join as its receiver. Provide the specific table from which records should be deleted as the argument to the parameter targetTable.

        val join = StarWarsFilmsTable.join(ActorsTable, JoinType.INNER, StarWarsFilmsTable.id, ActorsTable.sequelId)

        val deletedActorsCount = join.delete(ActorsTable) { ActorsTable.sequelId greater ACTORS_SEQUEL_ID }

Insert Or Update

Insert or update (Upsert) is a database operation that either inserts a new row or updates an existing row if a duplicate constraint already exists. The supported functionality of upsert() is dependent on the specific database being used. For example, MySQL's INSERT ... ON DUPLICATE KEY UPDATE statement automatically assesses the primary key and unique indices for a duplicate value, so using the function in Exposed would look like this:

        StarWarsFilmsTable.upsert {
            it[sequelId] = MOVIE_SEQUEL_ID // column pre-defined with a unique index
            it[name] = "The Rise of Skywalker"
            it[director] = "Rian Johnson"
        }
        // updates existing row with the correct [director]
        StarWarsFilmsTable.upsert {
            it[sequelId] = MOVIE_SEQUEL_ID
            it[name] = "The Rise of Skywalker"
            it[director] = "JJ Abrams"
        }

If none of the optional arguments are provided to upsert(), and an onUpdate() block is omitted, the statements in the body block will be used for both the insert and update parts of the operation. This means that, for example, if a table mapping has columns with default values and these columns are omitted from the body block, the default values will be used for insertion as well as for the update operation.

Using another example, PostgreSQL allows more control over which key constraint columns to check for conflict, whether different values should be used for an update, and whether the update statement should have a WHERE clause:

        StarWarsFilmsTable.upsert(
            StarWarsFilmsTable.sequelId,
            onUpdate = { it[StarWarsFilmsTable.sequelId] = StarWarsFilmsTable.sequelId + 1 },
            where = { StarWarsFilmsTable.director like stringLiteral("JJ%") }
        ) {
            it[sequelId] = MOVIE_SEQUEL_ID
            it[name] = "The Rise of Skywalker"
            it[director] = "JJ Abrams"
        }

        StarWarsFilmsTable.upsert(
            onUpdate = {
                it[StarWarsFilmsTable.director] = concat(insertValue(StarWarsFilmsTable.director), stringLiteral(" || "), StarWarsFilmsTable.director)
            }
        ) {
            it[sequelId] = MOVIE_SEQUEL_ID
            it[name] = "The Rise of Skywalker"
            it[director] = "Rian Johnson"
        }

If the update operation should be identical to the insert operation except for a few columns, then onUpdateExclude should be provided as an argument with the specific columns to exclude. This parameter could also be used for the reverse case when only a small subset of columns should be updated but duplicating the insert values is tedious:

        // on conflict, all columns EXCEPT [director] are updated with values from the lambda block
        StarWarsFilmsTable.upsert(onUpdateExclude = listOf(StarWarsFilmsTable.director)) {
            it[sequelId] = MOVIE_SEQUEL_ID
            it[name] = "The Rise of Skywalker"
            it[director] = "JJ Abrams"
        }

        // on conflict, ONLY column [director] is updated with value from the lambda block
        StarWarsFilmsTable.upsert(
            onUpdateExclude = StarWarsFilmsTable.columns - setOf(StarWarsFilmsTable.director)
        ) {
            it[sequelId] = MOVIE_SEQUEL_ID
            it[name] = "The Rise of Skywalker"
            it[director] = "JJ Abrams"
        }

If a specific database supports user-defined key columns and none are provided, the table's primary key is used. If there is no defined primary key, the first unique index is used. If there are no unique indices, each database handles this case differently, so it is strongly advised that keys are defined to avoid unexpected results.

Insert From Select

If you want to use the INSERT INTO ... SELECT SQL clause try the function Table.insert(Query):

        val substring = UsersTable.name.substring(1, 2)

        val insertedRows = CitiesTable.insert(UsersTable.select(substring).orderBy(UsersTable.id).limit(2))

By default, it will try to insert into all non auto-increment Table columns in the order they are defined in the Table instance. If you want to specify columns or change the order, provide a list of columns as the second parameter:

        val userCount = UsersTable.selectAll().count()

        val insertedUsers = UsersTable.insert(
            UsersTable.select(
                stringParam("Foo"),
                Random().castTo<String>(VarCharColumnType()).substring(1, MAX_VARCHAR_LENGTH)
            ),
            columns = listOf(UsersTable.name, UsersTable.id)
        )

Replace

The replace() method acts in a similar manner to an upsert(). The only difference is that if an insertion would violate a unique constraint, the existing row is deleted before the new row is inserted.

        StarWarsFilmsTable.replace {
            it[sequelId] = MOVIE_SEQUEL_3_ID
            it[releaseYear] = MOVIE_3_RELEASE_YEAR
            it[name] = "The Rise of Skywalker"
            it[director] = "JJ Abrams"
        }
        // deletes existing row and inserts new row with set [rating]
        StarWarsFilmsTable.replace {
            it[sequelId] = MOVIE_SEQUEL_3_ID
            it[releaseYear] = MOVIE_3_RELEASE_YEAR
            it[name] = "The Rise of Skywalker"
            it[director] = "JJ Abrams"
            it[rating] = MOVIE_RATING
        }

Unlike upsert(), none of the supporting databases allows a WHERE clause. Also, the constraints used to assess a violation are limited to the primary key and unique indexes, so there is no parameter for a custom key set.

The values specified in the statement block will be used for the insert statement, and any omitted columns are set to their default values, if applicable.

In the example above, if the original row was inserted with a user-defined rating and replace() was executed with a block that omitted the rating column, the newly inserted row would store the default rating value. This is because the old row was completely deleted first.

The REPLACE INTO ... SELECT SQL clause can be used by instead providing a query to replace():

        val allRowsWithLowRating: Query = StarWarsFilmsTable.selectAll().where {
            StarWarsFilmsTable.rating less LOW_RAITING_THRESHOLD
        }
        StarWarsFilmsTable.replace(allRowsWithLowRating)

By default, it will try to insert into all non auto-increment Table columns in the order they are defined in the Table instance. If the columns need to be specified or the order should be changed, provide a list of columns as the second parameter:

        val oneYearLater = StarWarsFilmsTable.releaseYear.plus(1)
        val allRowsWithNewYear: Query = StarWarsFilmsTable.select(
            oneYearLater, StarWarsFilmsTable.sequelId, StarWarsFilmsTable.director, StarWarsFilmsTable.name
        )
        StarWarsFilmsTable.replace(
            allRowsWithNewYear,
            columns = listOf(
                StarWarsFilmsTable.releaseYear,
                StarWarsFilmsTable.sequelId,
                StarWarsFilmsTable.director,
                StarWarsFilmsTable.name
            )
        )

Retrieve the count of modified rows

Some databases return a count of the number of rows inserted, updated, or deleted by the CRUD operation. For insert, upsert, and replace, this value can be accessed using the statement class property insertedCount:

        val insertStatement = StarWarsFilmsTable.insertIgnore {
            it[name] = "The Last Jedi"
            it[sequelId] = MOVIE_SEQUEL_3_ID
            it[director] = "Rian Johnson"
        }

        val rowCount: Int = insertStatement.insertedCount

Return data from modified rows

Some databases allow the return of additional data every time a row is either inserted, updated, or deleted. This can be accomplished by using one of the following functions:

Each of them take a list of the required table columns as an argument. If not provided, all table columns will be returned by default:

        val createdProjects: LocalDateTime = Projects.insertReturning {
            it[title] = "Project A"
            it[budget] = PROJECT_BUDGET
        }.single()[Projects.created]

        val updatedBudgets: List<Int> = Projects.updateReturning(listOf(Projects.budget)) {
            it[budget] = Projects.budget.times(INCREASE_BUDGET_BY)
        }.map {
            it[Projects.budget]
        }

Last modified: 05 December 2024

CRUD operations

Create

insert

insertAndGetId

insertIgnore

insertIgnoreAndGetId

batchInsert