CRUD operations

Insert a single row

To create a new table row, use the .insert() function. 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')

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')

Insert from select

For the INSERT INTO ... SELECT SQL clause, use the .insert() function with a Query parameter:

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

Insert and ignore

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

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

StarWarsFilmsIntIdTable.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.

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')

Batch insert

.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_ORIGINAL_ID, "A New Hope", "George Lucas"),
    SWFilmData(MOVIE_ORIGINAL_2_ID, "The Empire Strikes Back", "Irvin Kershner"),
    SWFilmData(MOVIE_ORIGINAL_3_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
}

note

The .batchInsert() function will still create multiple INSERT statements when interacting with your database.

To convert the INSERT statements into a BULK INSERT, use the rewriteBatchedInserts=true (or rewriteBatchedStatements=true) option of your relevant JDBC driver.

For more information, see the documentation for this option for MySQL and PostgresSQL.

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.

Retrieve a record

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]
    }

Retrieve all records

To retrieve all 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]) }

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:

• .insertReturning()

• .upsertReturning()

• .updateReturning()

• .deleteReturning()

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]
}

note

Unlike the base variants of these CRUD operations, a ReturningStatement behaves like a Query by also extending Iterable, so it will not be run by the database until the first attempt to iterate over its results.

Update a record

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, you can use either the .update() function or the update setter:

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

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.

note

If the update operation should differ from the insert operation, then onUpdate should be provided an argument to set the specific columns to update, as seen in the example below.

If the update operation involves functions that should use the values that would have been inserted, then these columns should be marked using insertValue(), as seen in the example below.

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.

note

Databases that do not support a specific Insert or Update command implement the standard MERGE INTO ... USING statement with aliases and a derived table column list. These include Oracle, SQL Server, and H2 compatibility modes (except for MySQL mode). Any columns defined as key constraints (to be used in the ON clause) must be included in the statement block to avoid throwing an error.

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

Delete with a condition

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

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

Any SqlExpressionBuilder comparison operators or extension functions used in the op parameter lambda block will require inclusion of an import statement:

import org.jetbrains.exposed.sql.SqlExpressionBuilder.*

Delete and ignore

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 = StarWarsFilmsIntIdTable.deleteIgnoreWhere { StarWarsFilmsIntIdTable.sequelId eq MOVIE_SEQUEL_ID }

Delete all

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 = StarWarsFilmsIntIdTable.join(ActorsIntIdTable, JoinType.INNER, StarWarsFilmsIntIdTable.id, ActorsIntIdTable.sequelId)

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

tip

For more information on creating and using a Join, see Joining Tables.