### Exposed 0.56.0 Help

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

Cities.insert { it[name] = "St. Petersburg" it[country] = Country.RUSSIA it[population] = 500 }

The example corresponds to the following SQL statement:

INSERT INTO CITIES (COUNTRY, "NAME", POPULATION) VALUES ('RUSSIA', 'St. Petersburg', 300)

### `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 = Cities.insertAndGetId { it[name] = "St. Petersburg" it[country] = Country.RUSSIA it[population] = 500 }
INSERT INTO CITIES (COUNTRY, "NAME", POPULATION) VALUES ('RUSSIA', 'St. Petersburg', 300)

### `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:

StarWarsFilms.insert { it[sequelId] = 8 // column pre-defined with a unique index it[name] = "The Last Jedi" it[director] = "Rian Johnson" } StarWarsFilms.insertIgnore { it[sequelId] = 8 it[name] = "The Rise of Skywalker" it[director] = "JJ Abrams" }

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 id = Cities.insertIgnoreAndGetId { it[name] = "St. Petersburg" it[country] = Country.RUSSIA it[population] = 500 }
INSERT IGNORE INTO CITIES (COUNTRY, "NAME", POPULATION) VALUES ('RUSSIA', 'St. Petersburg', 300)

### `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") val allCitiesID = cities.batchInsert(cityNames) { name -> this[cities.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) transaction { // ... val films = listOf( SWFilmData(5, "The Empire Strikes Back", "Irvin Kershner"), SWFilmData(4, "A New Hope", "George Lucas"), SWFilmData(7, "The Force Awakens", "JJ Abrams") ) StarWarsFilms.batchInsert(films) { (id, name, director) -> this[StarWarsFilms.sequelId] = id this[StarWarsFilms.name] = name this[StarWarsFilms.director] = director } StarWarsFilms.selectAll().count() // 3 }

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.

## Read

### `select`

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

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

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

val directors = StarWarsFilms.select(StarWarsFilms.director) .where { StarWarsFilms.sequelId less 5 }.withDistinct() .map { it[StarWarsFilms.director] }

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

val directors = StarWarsFilms.select(StarWarsFilms.director, StarWarsFilms.name) .withDistinctOn(StarWarsFilms.director) .orderBy( StarWarsFilms.director to SortOrder.ASC, StarWarsFilms.name to SortOrder.ASC ) .map { it[StarWarsFilms.name] }

### `selectAll`

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

val query: Query = StarWarsFilms.selectAll().where { StarWarsFilms.sequelId eq 8 }

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

StarWarsFilms.selectAll().where { StarWarsFilms.sequelId eq 8 }.forEach { println(it[StarWarsFilms.name]) }

## Update

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

val updatedCount = StarWarsFilms.update({ StarWarsFilms.name like "Episode%" }) { it[StarWarsFilms.director] = "George Lucas" }

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

StarWarsFilms.update({ StarWarsFilms.sequelId eq 8 }) { it.update(StarWarsFilms.sequelId, StarWarsFilms.sequelId + 1) // or it[StarWarsFilms.sequelId] = StarWarsFilms.sequelId + 1 }

## Delete

### `deleteWhere`

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

StarWarsFilms.deleteWhere { StarWarsFilms.sequelId eq 8 }

### `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.

StarWarsFilms.deleteIgnoreWhere { StarWarsFilms.sequelId eq 8 }

### `deleteAll`

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

StarWarsFilms.deleteAll { StarWarsFilms.sequelId eq 8 }

### 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 = StarWarsFilms innerJoin Actors join.delete(Actors) { Actors.sequelId greater 2 }

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

## 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:

// inserts a new row StarWarsFilms.upsert { it[sequelId] = 9 // column pre-defined with a unique index it[name] = "The Rise of Skywalker" it[director] = "Rian Johnson" } // updates existing row with the correct [director] StarWarsFilms.upsert { it[sequelId] = 9 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.

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:

StarWarsFilms.upsert( StarWarsFilms.sequelId, onUpdate = { it[StarWarsFilms.sequelId] = StarWarsFilms.sequelId + 1 }, where = { StarWarsFilms.director like stringLiteral("JJ%") } ) { it[sequelId] = 9 it[name] = "The Rise of Skywalker" it[director] = "JJ Abrams" } StarWarsFilms.upsert( onUpdate = { it[StarWarsFilms.director] = concat(insertValue(StarWarsFilms.director), stringLiteral(" || "), StarWarsFilms.director) } ) { it[sequelId] = 9 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 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 StarWarsFilms.upsert(onUpdateExclude = listOf(StarWarsFilms.director)) { it[sequelId] = 9 it[name] = "The Rise of Skywalker" it[director] = "JJ Abrams" } // on conflict, ONLY column [director] is updated with value from the lambda block StarWarsFilms.upsert( onUpdateExclude = StarWarsFilms.columns - setOf(StarWarsFilms.director) ) { it[sequelId] = 9 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.

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.

## Insert From Select

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

val substring = users.name.substring(1, 2) cities.insert(users.select(substring).orderBy(users.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 = users.selectAll().count() users.insert( users.select( stringParam("Foo"), Random().castTo<String>(VarCharColumnType()).substring(1, 10) ), columns = listOf(users.name, users.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.

object StarWarsFilms : Table() { val sequelId: Column<Int> = integer("sequel_id").uniqueIndex() val releaseYear: Column<Int> = integer("release_year") val name: Column<String> = varchar("name", 50) val director: Column<String> = varchar("director", 50) val rating: Column<Double> = double("rating").default(10.0) override val primaryKey = PrimaryKey(sequelId, releaseYear) } transaction { // ... // inserts a new row with default rating StarWarsFilms.replace { it[sequelId] = 9 it[releaseYear] = 2019 it[name] = "The Rise of Skywalker" it[director] = "JJ Abrams" } // deletes existing row and inserts new row with set [rating] StarWarsFilms.replace { it[sequelId] = 9 it[releaseYear] = 2019 it[name] = "The Rise of Skywalker" it[director] = "JJ Abrams" it[rating] = 5.2 } }

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 = StarWarsFilms.selectAll().where { StarWarsFilms.rating less 5.0 } StarWarsFilms.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 = StarWarsFilms.releaseYear.plus(1) val allRowsWithNewYear: Query = StarWarsFilms.select( oneYearLater, StarWarsFilms.sequelId, StarWarsFilms.director, StarWarsFilms.name ) StarWarsFilms.replace( allRowsWithNewYear, columns = listOf( StarWarsFilms.releaseYear, StarWarsFilms.sequelId, StarWarsFilms.director, StarWarsFilms.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 = StarWarsFilms.insertIgnore { it[name] = "The Last Jedi" it[sequelId] = 8 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:

object Projects : Table("projects") { val title = varchar("title", 64) val budget = integer("budget") val created = datetime("created").defaultExpression(CurrentDateTime) } // returns all table columns by default val created: LocalDateTime = Projects.insertReturning { it[title] = "Project A" it[budget] = 100 }.single()[Projects.created] val updatedBudgets: List<Int> = Projects.updateReturning(listOf(Projects.budget)) { it[budget] = Projects.budget.times(5) }.map { it[Projects.budget] }

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.

Last modified: 30 October 2024